Preview
Video Player
00:00
00:00
8th Grade Teachers
2:47Which teachers teach only students in 8th grade?
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