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