Janis' Schedule4:17 with Ben Deitch
What class does Janis Ambrose teach during each period? Be sure to include all 7 periods in your report!
That makes sense. 0:00 In addition to that, can you figure out her schedule? 0:01 What class does she teach during each period? 0:04 Also, make sure that your report includes all seven periods, so 0:07 we can easily compare schedules between teachers. 0:10 For this query, 0:14 let's start by just getting a list of which classes Janice teaches. 0:14 And to do that, we're going to need to join from the TEACHERS table 0:19 to the CLASSES table. 0:22 So let's select everything from the TEACHERS table, and 0:24 join that to the CLASSES table, ON TEACHERS.ID = CLASSES.TEACHER_ID. 0:28 Perfect. 0:35 Then let's add a WHERE and, 0:36 I guess we can just peek down here, Janice should be down here somewhere. 0:39 Instead of doing where her first name is Janice, 0:44 we can do by the ID, which is a little more robust. 0:46 So, let's do where TEACHERS.ID = 391, 0:48 which is Janice, and there we have Janice and all of her classes. 0:53 So to get which classes these are we're also going to have to join 0:58 to the subjects table. 1:02 So let's add a join here. 1:03 Join to subjects on SUBJECTS.ID 1:06 = CLASSES.SUBJECT_ID. 1:11 And up here, let's go ahead and 1:15 select the PERIOD SUBJECTS.NAME. 1:19 And if we run this, sorry this column is called period ID, we have one, 1:24 two, three, five, six, seven, so to get this to show us the fourth 1:29 period as well we're going to need to use the PERIODs table. 1:34 But before we get to that I'd like to show you a common table expression. 1:39 So before this SELECT, let's type WITH and 1:44 then let's type JANIS_CLASSES and then AS and then a parenthesis, 1:48 and I'm gonna go ahead and, that doesn't work. 1:55 So I'm gonna click and Tab, click and Tab and then add another parenthesis here, 2:00 and what this is, is this is called a common table expression. 2:06 It's basically just a sub query, 2:09 except we get to put it above instead of inside of our query. 2:11 Let me show you how it works. 2:15 So down here, we can type SELECT* FROM JANIS_CLASSES, 2:16 and it's the same thing we just got. 2:24 So using a common table expression which is just this WITH keyword and then AS, 2:26 and the query you want inside parentheses, lets us give a name to our data set. 2:31 So what we wanna do here is we wanna SELECT * FROM PERIODS. 2:37 When we do that, we get which period it is, which gives us this four, and 2:43 that's really important. 2:48 So, we get the periods, and what we want to do down here is a left outer join 2:50 to Janis classes on the period ID. 2:56 So on periods.ID equals, and remember in Janis classes, 3:01 we have this period ID column, so PERIODS.ID equals PERIOD_ID. 3:07 And let's go ahead and 3:14 select only the PERIODS.ID and 3:18 JANIS_CLASSES.NAME. 3:23 And there we go there's, Janis's schedule. 3:26 So what's happening with this left outer join? 3:30 Well, normally, we've been doing interjoins which means that you 3:33 only return a row if there's a match in the table but with a left outer join, 3:37 what you're saying is I want every single row from the periods table and if 3:42 we can join to a row and the Janis classes table let's go ahead and bring that in. 3:48 So we get every row from the PERIODs table and 3:53 if there's data in the JANIS_CLASSES table it'll JOIN that in for us. 3:56 So again if we just make this a JOIN, 4:01 We're not gonna get the fourth period that we need. 4:05 So changing this back to a LEFT OUTER JOIN, it's gonna give us 4:08 everything from the PERIODS table which means that we have all the periods. 4:13
You need to sign up for Treehouse in order to download course files.Sign up