Preview
Video Player
00:00
00:00
Finding the Troublemaker
4:20Which 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