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