[SOUND] Day three, you ready to get started?

The management team's been looking into teacher compensation, and

they've got a lot of questions they'd like answered.

First, they'd like to know which teachers are doing the most teaching.

Could you figure out which teachers teach all seven periods?

For this query we'll need to start with the teacher's table and

then join out to the classes table.

So let's start by selecting everything from the teacher's table and

then joining to the classes table on teachers.id = classes.teacher ID.

Then from here we want to count how many entries each teacher has in this dataset.

So to do that, let's group by the teacher's ID.

Group by teachers.ID and instead of selecting everything,

let's select The TEACHERS.ID, and we can go ahead and

select the FIRST_NAME and LAST_NAMEs too.

Since this is unique, we shouldn't have any issues with selecting the FIRST_NAMEs

and LAST_NAMEs after we've grouped.

Then let's also select a COUNT so

we can see how many periods each teacher is teaching.

Cool.

So, now we've got a list of which teachers teach all seven periods but it still

includes the teachers that don't teach all seven periods, like Cassandra McGuire.

Ideally, we'd like this report to not include Cassandra, Janice,

Nancy and Thomas.

And to do that we need to use a having clause,

having is basically where for your group bys.

And here we can type having and this just means where for

a group by and we can do count.

And we want this count to be equal to 7.

And if we run the query now we only have counts of 7.

So, now we've got a list of all the teachers that teach all seven periods.