Preview
Video Player
00:00
00:00
Busiest Teachers
2:12Which 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