Bus Troubles2:17 with Ben Deitch
Which teachers don't have a class during 1st period?
Wow, that must keep her pretty busy. 0:00 The last thing I'd like you to figure out, 0:02 is which teachers don't have a first period class? 0:04 They're having trouble unloading the buses on time, and 0:07 are wondering if they can get some of the teachers to help out. 0:10 To figure out which teachers don't have a class during first period, 0:13 we can actually do something pretty clever. 0:17 Instead of trying to solve this question directly, 0:19 we can figure out which teachers do have a class during first period, and 0:22 just subtract those teachers away from all the other teachers. 0:26 So let's start by figuring out, 0:30 which teachers do have a class during first period. 0:31 So let's SELECT* FROM TEACHERS, And then to get the period information, 0:34 we'll need to join to the classes table. 0:39 So JOIN CLASSES ON TEACHERS.ID 0:41 = CLASSES.TEACHER_ID. 0:46 And let's add a WHERE clause. 0:50 And if we look at the CLASSES table, we've got a PERIOD_ID column. 0:52 And if you don't have a CLASSES table over here, you can always just select 0:57 star from classes or whatever table you need to look into. 1:00 So WHERE PERIOD.ID = 1. 1:03 All right, here's the teachers that have a class first period. 1:08 Now, all we need to do, is take a selection of all the teachers, and 1:13 subtract away these teachers. 1:17 So above this, let's SELECT* FROM TEACHERS. 1:19 And then, between these two queries, all we need to write is EXCEPT, 1:25 which in some languages will be the minus keyword. 1:29 Now, if we run the query, we get an error. 1:33 When you're using except, you need to make sure that you have exactly the same 1:37 columns in your first query and your second query. 1:41 So instead of selecting everything from both of these, 1:44 since the second one includes the classes table, 1:48 let's go ahead and just select TEACHERS.* and TEACHERS.*. 1:52 And now if we run it, it looks like we've only got one teacher back, 1:57 Cassandra McGuire. 2:01 Great work figuring out all of these queries. 2:03 I'm not sure where they'd be without you. 2:06 On that note, if you've got any questions or 2:08 comments about what you've seen here, be sure to post about it in the community. 2:11 Until next time. 2:15
You need to sign up for Treehouse in order to download course files.Sign up