Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

SQL Reporting by Example

Well done!

You have completed SQL Reporting by Example!

Sign up for Treehouse Back to Library

Busiest Teachers

2:12 with Ben Deitch

Which teachers teach a class during all 7 periods?

This video doesn't have any notes.

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    [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

      You need to sign up for Treehouse in order to set up Workspace

      Sign up