Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
Which students have 5th period science and 7th period art?
This video doesn't have any notes.
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
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
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up