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.

Finding the Troublemaker

4:20 with Ben Deitch

Which students have 5th period science and 7th period art?

58, that's not too bad. 0:00
But before we get to the next query, 0:02
there's something funny going on with the school system. 0:04
And I think it has to do with a student who has fifth period science and 0:06
seventh period art. 0:10
Can you figure out which students have fifth period science and 0:11
seventh period art? 0:14
To figure out which students have fifth period science and seventh period art, 0:16
a good approach would be to get a data set of fifth period science students. 0:20
And a separate data set of 7th period art students and 0:24
then to join them and see what's inside the inner section. 0:27
So let's start by figuring out which students have fifth period science. 0:31
So let's select everything from the student's table. 0:35
And then to get which classes they are taking, we're going to need to join to 0:39
the schedule table and then to the classes table. 0:43
So let's JOIN to the SCHEDULE table 0:45
ON STUDENTS.ID = SCHEDULE.STUDENT_ID. 0:50
And then if we run this, that gives us the class ID. 0:57
And from here we'll be able to join to the classes table. 0:59
Which from there will have the subject ID and then we can join to the subject table. 1:04
And we can get the rest of the information we need. 1:08
So we need to JOIN from here to CLASSES ON CLASSES.ID = SCHEDULE.CLASS_ID. 1:11
And remember that gives us the subject ID, 1:19
which means we can JOIN to SUBJECTS ON SUBJECTS.ID = and 1:22
remember this is from the CLASSES table .SUBJECT_ID. 1:27
Then, now that we've got all the information 1:32
that we need let's add a WHERE clause. 1:38
And say WHERE PERIOD_ID = 5 AND 1:43
SUBJECT.NAME = 'Science'. 1:48
And if we Run this, we should have 1:52
only period 5 and only science. 1:56
Perfect. 2:01
Now, instead of selecting everything let's only select the Student ID number. 2:02
So let's just SELECT STUDENT_ID. 2:10
And let's Run this, and perfect. 2:13
Now let's change this to be part of a CTE, which stands for common table expression. 2:16
So WITH, and let's call this FIFTH_SCIENCE AS that. 2:20
And then let's add another parenthesis down here. 2:24
Tab these out. 2:29
And so those are our fifth period science students. 2:30
Then let's copy this, except for we don't need to copy the whole thing. 2:35
Let's just copy from the AS. 2:40
And when you're doing multiple common table expressions, what you wanna do, 2:43
is you add a comma after your last one, then you name your next one. 2:50
So SEVENTH_ART and then you add the common table expression. 2:56
So for this we want to change the period to 7 and 3:03
we want to change the name of the subject to Art. 3:07
Finally, once we've got our two common table expressions, 3:10
we just need to join them together. 3:13
So let's SELECT * FROM FIFTH_SCIENCE and 3:14
let's JOIN that to SEVENTH_ART ON. 3:23
And since I'd rather not keep writing these out, 3:30
I'm going to alias Fifth science as A and Seventh Art as B. 3:33
So ON A.STUDENT_ID = B.STUDENT_ID and 3:36
if we run this, we get one student back. 3:43
So to figure out which student this is, we just need to join to the students table. 3:49
So let's JOIN to STUDENTS, and since we've been aliasing this, let's go ahead and 3:53
alias this one as C. 3:57
So join ON A, and since they have the same ID, 3:59
it doesn't matter if we use A or B, .STUDENT_ID = C .ID. 4:03
And instead of selecting everything, let's just select what's in the students' table. 4:08
So let's SELECT C .*. 4:13
And there we go. 4:16
It looks like it's Bobby Tables. 4:18