1 00:00:00,220 --> 00:00:03,180 Great, he's just ordered them all a fruit basket. 2 00:00:03,180 --> 00:00:06,080 Also, the principal has been hearing some really good things 3 00:00:06,080 --> 00:00:08,450 about the new seventh grade science teacher. 4 00:00:08,450 --> 00:00:10,900 Can you figure out who teaches seventh grade science so 5 00:00:10,900 --> 00:00:12,360 he can get them a fruit basket too? 6 00:00:13,740 --> 00:00:16,750 >> This query is gonna be pretty similar to the last one. 7 00:00:16,750 --> 00:00:19,270 So to make it seem a little different, I'm going to do this from 8 00:00:19,270 --> 00:00:22,220 the perspective of the subjects table instead of the teachers table. 9 00:00:23,500 --> 00:00:28,383 So let's start by selecting everything from the subjects table and 10 00:00:28,383 --> 00:00:30,704 then join to the classes table. 11 00:00:30,704 --> 00:00:37,997 And we'll join it on SUBJECTS.ID = CLASSES.SUBJECT_ID. 12 00:00:37,997 --> 00:00:41,937 Then from here, we'll need to 13 00:00:41,937 --> 00:00:49,526 JOIN TEACHERS ON TEACHERS.ID = CLASSES.TEACHER_ID. 14 00:00:49,526 --> 00:00:54,349 Then in the where clause we need to make sure that we're only looking at seventh 15 00:00:54,349 --> 00:00:58,827 grade science classes, and that information is in the subjects table. 16 00:00:58,827 --> 00:00:59,434 So WHERE, and 17 00:00:59,434 --> 00:01:03,390 let's look in the subjects table to see what those columns are called. 18 00:01:03,390 --> 00:01:08,030 And so we're going to say subjects name is equal to science. 19 00:01:08,030 --> 00:01:11,230 And subject's grade is equal to seven. 20 00:01:11,230 --> 00:01:19,157 So WHERE NAME = 'Science' AND 21 00:01:19,157 --> 00:01:22,860 GRADE = 7. 22 00:01:22,860 --> 00:01:25,030 And it looks like you need to have a FROM keyword. 23 00:01:25,030 --> 00:01:27,110 And that’s true, you need to have the FROM keyword. 24 00:01:30,225 --> 00:01:33,550 And it needs to be before the table that you’re selecting from. 25 00:01:37,075 --> 00:01:41,433 And then just like the last table, we'll wanna bring in the distinct keyword on 26 00:01:41,433 --> 00:01:44,570 the ID, and select just the first and last name columns. 27 00:01:44,570 --> 00:01:49,306 And remember, we wanna use the teacher's ID column and 28 00:01:49,306 --> 00:01:54,010 then first name and last name, and there we go. 29 00:01:54,010 --> 00:01:56,380 Isabella Little is the 7th grade science teacher.