A Smelly Issue3:04 with Ben Deitch
Which students have Physical Education during first period?
Great job. 0:00 Also, while you were working on that, 0:01 the school got a few calls from parents, upset that their kids 0:03 were going to be sweaty all day from having PE first thing in the morning. 0:06 Could you figure out how many students have physical education during 0:10 first period? 0:13 For this query, just like the last query, 0:14 we'll need to start with the STUDENTS table. 0:17 And then join through to a couple other tables to be able to get all this 0:19 information. 0:22 So let's start by selecting everything from the STUDENTS table. 0:24 And let's join it to the SCHEDULE table. 0:29 On STUDENTS.ID = SCHEDULE.STUDENT_ID. 0:35 And remember, this gives us a results set of all the students and 0:43 all the classes those students are in. 0:47 So, for example, if we ordered this, so let's add ORDER BY, 0:50 and we order it by a STUDENT.ID, or 0:56 rather, STUDENTS.ID. 1:00 Then we should see each student listed seven times, which we do. 1:05 So now that we have the students and each of their schedules, 1:12 we need to get a little more information about which classes these are. 1:15 So just like last time, let's use this CLASS_ID 1:19 column that we get from the SCHEDULE table to join to the CLASSES table. 1:22 JOIN CLASSES on CLASSES.ID = SCHEDULE.CLASS_ID. 1:26 And there we go. 1:37 And then to get this Physical Education piece of information, 1:38 we'll need to join to the SUBJECT table on the SUBJECT_ID. 1:42 And we already have which period it is right here as the PERIOD_ID column. 1:45 So let's join to the SUBJECT_ID table, or rather, to the SUBJECTS table. 1:49 So JOIN SUBJECTS ON, and we'll use SUBJECTS.ID =. 1:53 And remember this SUBJECT_ID column is coming from the CLASSES table. 2:00 So we'll need to do CLASSES.SUBJECT_ID. 2:04 Then in the WHERE clause, we can add that we want the PERIOD_ID. 2:09 And I think we should only have one of those, but we might have more than one. 2:13 So we'll see if this gives us an error, = 1. 2:16 And let's see what that's named in the SUBJECT table, SUBJECTS.NAME. 2:19 I don't know if that'll give us a collision error, but let's see. 2:24 And NAME =, Physical Education. 2:28 And if we run this, looks like it works. 2:34 And there's Physical Education. 2:39 And we've got a bunch of 1s over here for the PERIOD_ID. 2:41 Nice, so from here, all we need to do is add a COUNT. 2:49 COUNT, and whatever you wanna count, FROM STUDENTS. 2:54 And there you go, 47 students have Physical Education for this period. 2:59
You need to sign up for Treehouse in order to download course files.Sign up