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