1
00:00:00,310 --> 00:00:01,790
Well, that sounds about right.

2
00:00:01,790 --> 00:00:03,490
Now before you head out for the day,

3
00:00:03,490 --> 00:00:07,690
there's a few questions about teachers
they're hoping you can help them answer.

4
00:00:07,690 --> 00:00:11,780
First, the principal wants to do something
nice for the eighth grade teachers.

5
00:00:11,780 --> 00:00:14,830
If you could get them a list of
which teachers teach eighth grade,

6
00:00:14,830 --> 00:00:15,630
that would be awesome.

7
00:00:16,910 --> 00:00:20,490
To figure out which teachers teach
only students in eighth grade,

8
00:00:20,490 --> 00:00:22,300
let's start by looking
at the teachers table.

9
00:00:23,840 --> 00:00:27,210
Unfortunately, it doesn't look like
we have any great information here.

10
00:00:27,210 --> 00:00:31,340
So we're going to have to join out to some
other tables to get that information.

11
00:00:31,340 --> 00:00:33,310
Looking at the classes table,

12
00:00:33,310 --> 00:00:36,180
this would be a good table to
join to with our teacher ID.

13
00:00:36,180 --> 00:00:39,790
And it also gives us access to
the subject ID of the class.

14
00:00:39,790 --> 00:00:43,700
And in the subjects table,
that's where we have a grade column.

15
00:00:43,700 --> 00:00:48,050
So, let's start by selecting
everything from the teachers table.

16
00:00:49,420 --> 00:00:54,842
Then let's JOIN from
here to the CLASSES table

17
00:00:54,842 --> 00:01:00,167
on TEACHERS.ID = CLASSES.TEACHER_ID.

18
00:01:00,167 --> 00:01:04,790
And if we run it at this point, we can
see that we've got all the teachers and

19
00:01:04,790 --> 00:01:08,170
classes, and
we've got this subject ID column.

20
00:01:08,170 --> 00:01:11,228
Let's use this subject ID column
to join out to the subject table.

21
00:01:11,228 --> 00:01:16,506
JOIN SUBJECTS ON SUBJECTS.ID

22
00:01:16,506 --> 00:01:21,380
= CLASSES.SUBJECT_ID.

23
00:01:23,270 --> 00:01:24,280
And if we run this,

24
00:01:25,360 --> 00:01:27,480
now we've got the grade information
that we were looking for.

25
00:01:28,610 --> 00:01:32,216
Last but not least, let's filter
this to only show eighth grade.

26
00:01:32,216 --> 00:01:38,120
WHERE GRADE, and since there's only one
column in this set that's called grade,

27
00:01:38,120 --> 00:01:40,770
we don't need to specify
it with any table.

28
00:01:40,770 --> 00:01:44,740
So WHERE GRADE = 8.

29
00:01:44,740 --> 00:01:47,150
And here we have all the teachers
that are teaching eight grade.

30
00:01:48,150 --> 00:01:52,900
However, since we joined through to the
classes table, we're getting one row for

31
00:01:52,900 --> 00:01:53,842
each class.

32
00:01:53,842 --> 00:01:57,056
So we're going to need to use
the distinct keyword again.

33
00:01:57,056 --> 00:02:00,390
Also, we don't need to
return all of this data,

34
00:02:00,390 --> 00:02:03,420
just the teacher's name
would probably suffice.

35
00:02:03,420 --> 00:02:06,503
So let's change from selecting everything,

36
00:02:06,503 --> 00:02:09,920
to just selecting FIRST_NAME,
and LAST_NAME.

37
00:02:09,920 --> 00:02:11,992
And let's add a DISTINCT on FIRST_NAME.

38
00:02:15,258 --> 00:02:18,948
And there we go, those are the teachers
that teach eighth grade.

39
00:02:18,948 --> 00:02:23,350
However, while this does work in this
example, if we had two teachers with

40
00:02:23,350 --> 00:02:26,970
the same first name,
we'd only be getting one of them here.

41
00:02:26,970 --> 00:02:30,509
So a better idea is to add
the DISTINCT onto the ID.

42
00:02:32,077 --> 00:02:37,780
But ID is a column that's in,
well, all of these tables.

43
00:02:37,780 --> 00:02:39,460
So we need to specify which one.

44
00:02:41,140 --> 00:02:42,249
Let's use the teachers one.

45
00:02:43,896 --> 00:02:44,991
And there we go.

46
00:02:44,991 --> 00:02:47,030
All of the teachers that
teach eighth grade.