"Build a Simple Android App (2014)" was retired on August 31, 2016. You are now viewing the recommended replacement.

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