1 00:00:00,000 --> 00:00:02,770 58, that's not too bad. 2 00:00:02,770 --> 00:00:04,480 But before we get to the next query, 3 00:00:04,480 --> 00:00:06,900 there's something funny going on with the school system. 4 00:00:06,900 --> 00:00:10,310 And I think it has to do with a student who has fifth period science and 5 00:00:10,310 --> 00:00:11,740 seventh period art. 6 00:00:11,740 --> 00:00:14,470 Can you figure out which students have fifth period science and 7 00:00:14,470 --> 00:00:15,250 seventh period art? 8 00:00:16,830 --> 00:00:20,568 To figure out which students have fifth period science and seventh period art, 9 00:00:20,568 --> 00:00:24,100 a good approach would be to get a data set of fifth period science students. 10 00:00:24,100 --> 00:00:27,509 And a separate data set of 7th period art students and 11 00:00:27,509 --> 00:00:31,330 then to join them and see what's inside the inner section. 12 00:00:31,330 --> 00:00:35,360 So let's start by figuring out which students have fifth period science. 13 00:00:35,360 --> 00:00:38,170 So let's select everything from the student's table. 14 00:00:39,706 --> 00:00:43,000 And then to get which classes they are taking, we're going to need to join to 15 00:00:43,000 --> 00:00:45,368 the schedule table and then to the classes table. 16 00:00:45,368 --> 00:00:50,461 So let's JOIN to the SCHEDULE table 17 00:00:50,461 --> 00:00:57,100 ON STUDENTS.ID = SCHEDULE.STUDENT_ID. 18 00:00:57,100 --> 00:00:59,840 And then if we run this, that gives us the class ID. 19 00:00:59,840 --> 00:01:04,240 And from here we'll be able to join to the classes table. 20 00:01:04,240 --> 00:01:08,630 Which from there will have the subject ID and then we can join to the subject table. 21 00:01:08,630 --> 00:01:10,280 And we can get the rest of the information we need. 22 00:01:11,630 --> 00:01:19,567 So we need to JOIN from here to CLASSES ON CLASSES.ID = SCHEDULE.CLASS_ID. 23 00:01:19,567 --> 00:01:22,988 And remember that gives us the subject ID, 24 00:01:22,988 --> 00:01:27,550 which means we can JOIN to SUBJECTS ON SUBJECTS.ID = and 25 00:01:27,550 --> 00:01:32,128 remember this is from the CLASSES table .SUBJECT_ID. 26 00:01:32,128 --> 00:01:38,419 Then, now that we've got all the information 27 00:01:38,419 --> 00:01:43,747 that we need let's add a WHERE clause. 28 00:01:43,747 --> 00:01:48,021 And say WHERE PERIOD_ID = 5 AND 29 00:01:48,021 --> 00:01:52,299 SUBJECT.NAME = 'Science'. 30 00:01:52,299 --> 00:01:56,807 And if we Run this, we should have 31 00:01:56,807 --> 00:02:01,320 only period 5 and only science. 32 00:02:01,320 --> 00:02:02,500 Perfect. 33 00:02:02,500 --> 00:02:10,260 Now, instead of selecting everything let's only select the Student ID number. 34 00:02:10,260 --> 00:02:12,355 So let's just SELECT STUDENT_ID. 35 00:02:13,550 --> 00:02:15,140 And let's Run this, and perfect. 36 00:02:16,140 --> 00:02:20,850 Now let's change this to be part of a CTE, which stands for common table expression. 37 00:02:20,850 --> 00:02:24,789 So WITH, and let's call this FIFTH_SCIENCE AS that. 38 00:02:24,789 --> 00:02:29,564 And then let's add another parenthesis down here. 39 00:02:29,564 --> 00:02:30,289 Tab these out. 40 00:02:30,289 --> 00:02:35,263 And so those are our fifth period science students. 41 00:02:35,263 --> 00:02:40,992 Then let's copy this, except for we don't need to copy the whole thing. 42 00:02:40,992 --> 00:02:43,907 Let's just copy from the AS. 43 00:02:43,907 --> 00:02:50,754 And when you're doing multiple common table expressions, what you wanna do, 44 00:02:50,754 --> 00:02:56,493 is you add a comma after your last one, then you name your next one. 45 00:02:56,493 --> 00:03:02,020 So SEVENTH_ART and then you add the common table expression. 46 00:03:03,140 --> 00:03:07,160 So for this we want to change the period to 7 and 47 00:03:07,160 --> 00:03:09,380 we want to change the name of the subject to Art. 48 00:03:10,910 --> 00:03:13,680 Finally, once we've got our two common table expressions, 49 00:03:13,680 --> 00:03:14,833 we just need to join them together. 50 00:03:14,833 --> 00:03:23,329 So let's SELECT * FROM FIFTH_SCIENCE and 51 00:03:23,329 --> 00:03:30,690 let's JOIN that to SEVENTH_ART ON. 52 00:03:30,690 --> 00:03:33,530 And since I'd rather not keep writing these out, 53 00:03:33,530 --> 00:03:36,853 I'm going to alias Fifth science as A and Seventh Art as B. 54 00:03:36,853 --> 00:03:43,059 So ON A.STUDENT_ID = B.STUDENT_ID and 55 00:03:43,059 --> 00:03:49,490 if we run this, we get one student back. 56 00:03:49,490 --> 00:03:53,506 So to figure out which student this is, we just need to join to the students table. 57 00:03:53,506 --> 00:03:57,920 So let's JOIN to STUDENTS, and since we've been aliasing this, let's go ahead and 58 00:03:57,920 --> 00:03:59,820 alias this one as C. 59 00:03:59,820 --> 00:04:03,400 So join ON A, and since they have the same ID, 60 00:04:03,400 --> 00:04:08,223 it doesn't matter if we use A or B, .STUDENT_ID = C .ID. 61 00:04:08,223 --> 00:04:13,740 And instead of selecting everything, let's just select what's in the students' table. 62 00:04:13,740 --> 00:04:15,480 So let's SELECT C .*. 63 00:04:16,978 --> 00:04:18,410 And there we go. 64 00:04:18,410 --> 00:04:19,830 It looks like it's Bobby Tables.