Finding the Troublemaker

58, that's not too bad. 0:00 But before we get to the next query, 0:02 there's something funny going on with the school system. 0:04 And I think it has to do with a student who has fifth period science and 0:06 seventh period art. 0:10 Can you figure out which students have fifth period science and 0:11 seventh period art? 0:14 To figure out which students have fifth period science and seventh period art, 0:16 a good approach would be to get a data set of fifth period science students. 0:20 And a separate data set of 7th period art students and 0:24 then to join them and see what's inside the inner section. 0:27 So let's start by figuring out which students have fifth period science. 0:31 So let's select everything from the student's table. 0:35 And then to get which classes they are taking, we're going to need to join to 0:39 the schedule table and then to the classes table. 0:43 So let's JOIN to the SCHEDULE table 0:45 ON STUDENTS.ID = SCHEDULE.STUDENT_ID. 0:50 And then if we run this, that gives us the class ID. 0:57 And from here we'll be able to join to the classes table. 0:59 Which from there will have the subject ID and then we can join to the subject table. 1:04 And we can get the rest of the information we need. 1:08 So we need to JOIN from here to CLASSES ON CLASSES.ID = SCHEDULE.CLASS_ID. 1:11 And remember that gives us the subject ID, 1:19 which means we can JOIN to SUBJECTS ON SUBJECTS.ID = and 1:22 remember this is from the CLASSES table .SUBJECT_ID. 1:27 Then, now that we've got all the information 1:32 that we need let's add a WHERE clause. 1:38 And say WHERE PERIOD_ID = 5 AND 1:43 SUBJECT.NAME = 'Science'. 1:48 And if we Run this, we should have 1:52 only period 5 and only science. 1:56 Perfect. 2:01 Now, instead of selecting everything let's only select the Student ID number. 2:02 So let's just SELECT STUDENT_ID. 2:10 And let's Run this, and perfect. 2:13 Now let's change this to be part of a CTE, which stands for common table expression. 2:16 So WITH, and let's call this FIFTH_SCIENCE AS that. 2:20 And then let's add another parenthesis down here. 2:24 Tab these out. 2:29 And so those are our fifth period science students. 2:30 Then let's copy this, except for we don't need to copy the whole thing. 2:35 Let's just copy from the AS. 2:40 And when you're doing multiple common table expressions, what you wanna do, 2:43 is you add a comma after your last one, then you name your next one. 2:50 So SEVENTH_ART and then you add the common table expression. 2:56 So for this we want to change the period to 7 and 3:03 we want to change the name of the subject to Art. 3:07 Finally, once we've got our two common table expressions, 3:10 we just need to join them together. 3:13 So let's SELECT * FROM FIFTH_SCIENCE and 3:14 let's JOIN that to SEVENTH_ART ON. 3:23 And since I'd rather not keep writing these out, 3:30 I'm going to alias Fifth science as A and Seventh Art as B. 3:33 So ON A.STUDENT_ID = B.STUDENT_ID and 3:36 if we run this, we get one student back. 3:43 So to figure out which student this is, we just need to join to the students table. 3:49 So let's JOIN to STUDENTS, and since we've been aliasing this, let's go ahead and 3:53 alias this one as C. 3:57 So join ON A, and since they have the same ID, 3:59 it doesn't matter if we use A or B, .STUDENT_ID = C .ID. 4:03 And instead of selecting everything, let's just select what's in the students' table. 4:08 So let's SELECT C .*. 4:13 And there we go. 4:16 It looks like it's Bobby Tables. 4:18