Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed SQL Reporting by Example!
You have completed SQL Reporting by Example!
Preview
Which subject/s are taught in the largest room?
This video doesn't have any notes.
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
And also,
which subjects are taught in that room?
0:00
For this query, we'll need to find a way
to tie together the subjects table, and
0:04
the rooms table.
0:08
And to do that,
we can use the classes table.
0:09
The CLASSES table has both the SUBJECT_ID
column and a ROOM_ID column.
0:12
So, let's start by
selecting everything from
0:17
the classes table WHERE
the ROOM_ID is = to 19.
0:22
Then from here,
we need to join to the subjects table, so
0:28
we can resolve these subjects into
whatever subjects they actually are.
0:31
So to do this, we're going to join from,
0:36
CLASSES.SUBJECT_ID to SUBJECTS.ID.
0:40
So back in the query, let's add a JOIN,
and let's join to SUBJECTS on
0:45
CLASSES.SUBJECT_ID = SUBJECTS.ID.
0:54
And if we run it now, We can see
the subjects table pulling in over here.
1:02
So it looks like the two classes
taught in room 19 are band and choir.
1:07
But how would we get this down to just
two rows that say band and choir?
1:13
Well, for starters,
let's not select everything.
1:17
Let's just select the NAME, and
since there's only one NAME column,
1:21
this NAME is going to be
from the subjects table.
1:25
And if we run this, we get band,
band, band and choir, choir, choir.
1:28
And real quick,
why we're getting six rows for
1:33
this, is just because there's
three classes of each one.
1:36
One row for each class.
1:39
So, getting back to our names here,
band, band, band, choir, choir, choir.
1:41
If we wanna get these down to two rows,
we just need to use the distinct keyword,
1:46
which will return one row for
each instance of a value and a column.
1:51
And now if we run it,
we see that I spelled distinct wrong.
1:57
So let's add a C.
2:02
And then we get the coloring to let
us know this is actually a keyword.
2:04
And we can run it, and
2:07
we get band and choir as the subjects
that are taught in room 19.
2:09
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up