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.