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
Generate a schedule for Rex Rios.
[MUSIC]
0:00
Welcome back.
0:02
So far,
things are going a lot smoother today.
0:06
The fire department's not around, and the
all the teachers seem a bit more relaxed.
0:08
The only issue at the moment is
a student who was sick yesterday and
0:12
wasn't able to pick up his schedule.
0:16
Could you print off a schedule for
Rex Rios?
0:18
To figure out Rex's schedule, we're
gonna need to use the schedule table.
0:20
But first, let's select just Rex
himself from the students table.
0:24
So SELECT *
0:29
FROM STUDENTS WHERE FIRST_NAME = Rex AND
0:31
LAST_NAME = Rios.
0:40
Then once we've got Rex,
let's look at the schedule table and
0:46
it looks like we can join on
the STUDENT_ID to get his schedule.
0:50
So let's add a JOIN on
the schedule table and
0:54
let's draw ON STUDENTS.ID
1:01
= SCHEDULE.STUDENT_ID.
1:06
And if we run this,
now we have Rex's schedule, and let's see,
1:10
we've got one, two, three,
four, five, six, seven classes.
1:15
But we don't know what classes those are,
or even what period those classes are.
1:20
To get that information, we are going
to have to join to the class table and
1:25
then from the classes table,
to the subjects table.
1:29
So, let's add a JOIN, and let's join
1:33
two CLASSES ON CLASSES.ID
= SCHEDULE.CLASS_ID.
1:38
And if we run it now,
1:45
we should have rows off to the right
here which will be the classes table.
1:47
And there we go.
1:54
So here's the class IDs, the subjects,
the periods, the teacher and
1:54
what room those are in.
1:58
So to get which subject it is, we need
to join to the subject table using this
2:00
subject ID column which comes
from the classes table.
2:04
So JOIN on
SUBJECTS ON CLASSES.SUBJECT_ID and
2:06
actually since we've
been doing this ID first,
2:12
let's do SUBJECTS.ID = CLASSES.SUBJECT_ID.
2:18
And if we run it now,
here's the subject information we need.
2:24
So to get this into a nice schedule for
Rex let's add an ORDER BY and let's do
2:27
it by PERIOD_ID ascending as well as let's
just select a little less information.
2:33
So let's select the PERIOD
then the SUBJECTS.NAME so
2:41
the NAME of the subject and
then let's also select what grade it is.
2:47
SUBJECTS.GRADE which actually is name.
2:54
Yeah I think we don't
need SUBJECTS there and
2:58
doesn't look like we need it here either.
3:01
And if we run this, right,
3:06
it is PERIOD_ID.
3:11
Unfortunately grade is
an ambiguous column, so
3:15
let's do SUBJECTS.GRADE, and there we go.
3:18
That's Rex's schedule.
3:22
You need to sign up for Treehouse in order to download course files.
Sign up