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
Preview
Video Player
00:00
00:00
00:00
  1. 2x 2x
  2. 1.75x 1.75x
  3. 1.5x 1.5x
  4. 1.25x 1.25x
  5. 1.1x 1.1x
  6. 1x 1x
  7. 0.75x 0.75x
  8. 0.5x 0.5x
Use Up/Down Arrow keys to increase or decrease volume.

8th Grade Teachers

2:47 with Ben Deitch

Which teachers teach only students in 8th grade?

Well, that sounds about right. 0:00
Now before you head out for the day, 0:01
there's a few questions about teachers they're hoping you can help them answer. 0:03
First, the principal wants to do something nice for the eighth grade teachers. 0:07
If you could get them a list of which teachers teach eighth grade, 0:11
that would be awesome. 0:14
To figure out which teachers teach only students in eighth grade, 0:16
let's start by looking at the teachers table. 0:20
Unfortunately, it doesn't look like we have any great information here. 0:23
So we're going to have to join out to some other tables to get that information. 0:27
Looking at the classes table, 0:31
this would be a good table to join to with our teacher ID. 0:33
And it also gives us access to the subject ID of the class. 0:36
And in the subjects table, that's where we have a grade column. 0:39
So, let's start by selecting everything from the teachers table. 0:43
Then let's JOIN from here to the CLASSES table 0:49
on TEACHERS.ID = CLASSES.TEACHER_ID. 0:54
And if we run it at this point, we can see that we've got all the teachers and 1:00
classes, and we've got this subject ID column. 1:04
Let's use this subject ID column to join out to the subject table. 1:08
JOIN SUBJECTS ON SUBJECTS.ID 1:11
= CLASSES.SUBJECT_ID. 1:16
And if we run this, 1:23
now we've got the grade information that we were looking for. 1:25
Last but not least, let's filter this to only show eighth grade. 1:28
WHERE GRADE, and since there's only one column in this set that's called grade, 1:32
we don't need to specify it with any table. 1:38
So WHERE GRADE = 8. 1:40
And here we have all the teachers that are teaching eight grade. 1:44
However, since we joined through to the classes table, we're getting one row for 1:48
each class. 1:52
So we're going to need to use the distinct keyword again. 1:53
Also, we don't need to return all of this data, 1:57
just the teacher's name would probably suffice. 2:00
So let's change from selecting everything, 2:03
to just selecting FIRST_NAME, and LAST_NAME. 2:06
And let's add a DISTINCT on FIRST_NAME. 2:09
And there we go, those are the teachers that teach eighth grade. 2:15
However, while this does work in this example, if we had two teachers with 2:18
the same first name, we'd only be getting one of them here. 2:23
So a better idea is to add the DISTINCT onto the ID. 2:26
But ID is a column that's in, well, all of these tables. 2:32
So we need to specify which one. 2:37
Let's use the teachers one. 2:41
And there we go. 2:43
All of the teachers that teach eighth grade. 2:44