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 teachers teach only students in 8th grade?
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
Well, that sounds about right.
0:00
Now before you head out for the day,
0:01
there's a few questions about teachers
they're hoping you can help them answer.
0:03
First, the principal wants to do something
nice for the eighth grade teachers.
0:07
If you could get them a list of
which teachers teach eighth grade,
0:11
that would be awesome.
0:14
To figure out which teachers teach
only students in eighth grade,
0:16
let's start by looking
at the teachers table.
0:20
Unfortunately, it doesn't look like
we have any great information here.
0:23
So we're going to have to join out to some
other tables to get that information.
0:27
Looking at the classes table,
0:31
this would be a good table to
join to with our teacher ID.
0:33
And it also gives us access to
the subject ID of the class.
0:36
And in the subjects table,
that's where we have a grade column.
0:39
So, let's start by selecting
everything from the teachers table.
0:43
Then let's JOIN from
here to the CLASSES table
0:49
on TEACHERS.ID = CLASSES.TEACHER_ID.
0:54
And if we run it at this point, we can
see that we've got all the teachers and
1:00
classes, and
we've got this subject ID column.
1:04
Let's use this subject ID column
to join out to the subject table.
1:08
JOIN SUBJECTS ON SUBJECTS.ID
1:11
= CLASSES.SUBJECT_ID.
1:16
And if we run this,
1:23
now we've got the grade information
that we were looking for.
1:25
Last but not least, let's filter
this to only show eighth grade.
1:28
WHERE GRADE, and since there's only one
column in this set that's called grade,
1:32
we don't need to specify
it with any table.
1:38
So WHERE GRADE = 8.
1:40
And here we have all the teachers
that are teaching eight grade.
1:44
However, since we joined through to the
classes table, we're getting one row for
1:48
each class.
1:52
So we're going to need to use
the distinct keyword again.
1:53
Also, we don't need to
return all of this data,
1:57
just the teacher's name
would probably suffice.
2:00
So let's change from selecting everything,
2:03
to just selecting FIRST_NAME,
and LAST_NAME.
2:06
And let's add a DISTINCT on FIRST_NAME.
2:09
And there we go, those are the teachers
that teach eighth grade.
2:15
However, while this does work in this
example, if we had two teachers with
2:18
the same first name,
we'd only be getting one of them here.
2:23
So a better idea is to add
the DISTINCT onto the ID.
2:26
But ID is a column that's in,
well, all of these tables.
2:32
So we need to specify which one.
2:37
Let's use the teachers one.
2:41
And there we go.
2:43
All of the teachers that
teach eighth grade.
2:44
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