Busiest Teachers

Name: Busiest Teachers
Uploaded: 2017-07-12T11:29:20Z
Duration: 132 s
Description: Which teachers teach a class during all 7 periods?

2: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.