Busiest Teachers2:12 with Ben Deitch
Which teachers teach a class during all 7 periods?
[SOUND] Day three, you ready to get started? 0:00 The management team's been looking into teacher compensation, and 0:07 they've got a lot of questions they'd like answered. 0:10 First, they'd like to know which teachers are doing the most teaching. 0:13 Could you figure out which teachers teach all seven periods? 0:17 For this query we'll need to start with the teacher's table and 0:21 then join out to the classes table. 0:24 So let's start by selecting everything from the teacher's table and 0:26 then joining to the classes table on teachers.id = classes.teacher ID. 0:32 Then from here we want to count how many entries each teacher has in this dataset. 0:41 So to do that, let's group by the teacher's ID. 0:46 Group by teachers.ID and instead of selecting everything, 0:53 let's select The TEACHERS.ID, and we can go ahead and 0:58 select the FIRST_NAME and LAST_NAMEs too. 1:03 Since this is unique, we shouldn't have any issues with selecting the FIRST_NAMEs 1:08 and LAST_NAMEs after we've grouped. 1:11 Then let's also select a COUNT so 1:16 we can see how many periods each teacher is teaching. 1:18 Cool. 1:22 So, now we've got a list of which teachers teach all seven periods but it still 1:23 includes the teachers that don't teach all seven periods, like Cassandra McGuire. 1:28 Ideally, we'd like this report to not include Cassandra, Janice, 1:34 Nancy and Thomas. 1:39 And to do that we need to use a having clause, 1:42 having is basically where for your group bys. 1:45 And here we can type having and this just means where for 1:50 a group by and we can do count. 1:54 And we want this count to be equal to 7. 1:57 And if we run the query now we only have counts of 7. 2:00 So, now we've got a list of all the teachers that teach all seven periods. 2:07
You need to sign up for Treehouse in order to download course files.Sign up