1 00:00:00,210 --> 00:00:01,250 Great job. 2 00:00:01,250 --> 00:00:03,248 Also, while you were working on that, 3 00:00:03,248 --> 00:00:06,398 the school got a few calls from parents, upset that their kids 4 00:00:06,398 --> 00:00:10,114 were going to be sweaty all day from having PE first thing in the morning. 5 00:00:10,114 --> 00:00:13,048 Could you figure out how many students have physical education during 6 00:00:13,048 --> 00:00:13,700 first period? 7 00:00:14,920 --> 00:00:17,090 For this query, just like the last query, 8 00:00:17,090 --> 00:00:19,060 we'll need to start with the STUDENTS table. 9 00:00:19,060 --> 00:00:22,340 And then join through to a couple other tables to be able to get all this 10 00:00:22,340 --> 00:00:22,850 information. 11 00:00:24,090 --> 00:00:28,360 So let's start by selecting everything from the STUDENTS table. 12 00:00:29,670 --> 00:00:31,213 And let's join it to the SCHEDULE table. 13 00:00:35,120 --> 00:00:43,105 On STUDENTS.ID = SCHEDULE.STUDENT_ID. 14 00:00:43,105 --> 00:00:47,408 And remember, this gives us a results set of all the students and 15 00:00:47,408 --> 00:00:50,600 all the classes those students are in. 16 00:00:50,600 --> 00:00:54,670 So, for example, if we ordered this, so let's add ORDER BY, 17 00:00:56,290 --> 00:01:00,121 and we order it by a STUDENT.ID, or 18 00:01:00,121 --> 00:01:04,093 rather, STUDENTS.ID. 19 00:01:05,670 --> 00:01:10,273 Then we should see each student listed seven times, which we do. 20 00:01:12,524 --> 00:01:15,494 So now that we have the students and each of their schedules, 21 00:01:15,494 --> 00:01:19,680 we need to get a little more information about which classes these are. 22 00:01:19,680 --> 00:01:22,380 So just like last time, let's use this CLASS_ID 23 00:01:22,380 --> 00:01:26,270 column that we get from the SCHEDULE table to join to the CLASSES table. 24 00:01:26,270 --> 00:01:33,557 JOIN CLASSES on CLASSES.ID = SCHEDULE.CLASS_ID. 25 00:01:37,259 --> 00:01:38,848 And there we go. 26 00:01:38,848 --> 00:01:42,031 And then to get this Physical Education piece of information, 27 00:01:42,031 --> 00:01:45,130 we'll need to join to the SUBJECT table on the SUBJECT_ID. 28 00:01:45,130 --> 00:01:49,170 And we already have which period it is right here as the PERIOD_ID column. 29 00:01:49,170 --> 00:01:53,360 So let's join to the SUBJECT_ID table, or rather, to the SUBJECTS table. 30 00:01:53,360 --> 00:02:00,416 So JOIN SUBJECTS ON, and we'll use SUBJECTS.ID =. 31 00:02:00,416 --> 00:02:04,000 And remember this SUBJECT_ID column is coming from the CLASSES table. 32 00:02:04,000 --> 00:02:09,120 So we'll need to do CLASSES.SUBJECT_ID. 33 00:02:09,120 --> 00:02:13,740 Then in the WHERE clause, we can add that we want the PERIOD_ID. 34 00:02:13,740 --> 00:02:16,659 And I think we should only have one of those, but we might have more than one. 35 00:02:16,659 --> 00:02:19,734 So we'll see if this gives us an error, = 1. 36 00:02:19,734 --> 00:02:24,371 And let's see what that's named in the SUBJECT table, SUBJECTS.NAME. 37 00:02:24,371 --> 00:02:28,251 I don't know if that'll give us a collision error, but let's see. 38 00:02:28,251 --> 00:02:34,645 And NAME =, Physical Education. 39 00:02:34,645 --> 00:02:39,380 And if we run this, looks like it works. 40 00:02:39,380 --> 00:02:41,549 And there's Physical Education. 41 00:02:41,549 --> 00:02:44,453 And we've got a bunch of 1s over here for the PERIOD_ID. 42 00:02:49,582 --> 00:02:54,908 Nice, so from here, all we need to do is add a COUNT. 43 00:02:54,908 --> 00:02:58,960 COUNT, and whatever you wanna count, FROM STUDENTS. 44 00:02:59,980 --> 00:03:03,480 And there you go, 47 students have Physical Education for this period.