8th Grade Teachers2:47 with Ben Deitch
Which 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
You need to sign up for Treehouse in order to download course files.Sign up