[MUSIC]

Welcome back.

So far, things are going a lot smoother today.

The fire department's not around, and the all the teachers seem a bit more relaxed.

The only issue at the moment is a student who was sick yesterday and

wasn't able to pick up his schedule.

Could you print off a schedule for Rex Rios?

To figure out Rex's schedule, we're gonna need to use the schedule table.

But first, let's select just Rex himself from the students table.

So SELECT *

FROM STUDENTS WHERE FIRST_NAME = Rex AND

LAST_NAME = Rios.

Then once we've got Rex, let's look at the schedule table and

it looks like we can join on the STUDENT_ID to get his schedule.

So let's add a JOIN on the schedule table and

let's draw ON STUDENTS.ID

= SCHEDULE.STUDENT_ID.

And if we run this, now we have Rex's schedule, and let's see,

we've got one, two, three, four, five, six, seven classes.

But we don't know what classes those are, or even what period those classes are.

To get that information, we are going to have to join to the class table and

then from the classes table, to the subjects table.

So, let's add a JOIN, and let's join

two CLASSES ON CLASSES.ID = SCHEDULE.CLASS_ID.

And if we run it now,

we should have rows off to the right here which will be the classes table.

And there we go.

So here's the class IDs, the subjects, the periods, the teacher and

what room those are in.

So to get which subject it is, we need to join to the subject table using this

subject ID column which comes from the classes table.

So JOIN on SUBJECTS ON CLASSES.SUBJECT_ID and

actually since we've been doing this ID first,

let's do SUBJECTS.ID = CLASSES.SUBJECT_ID.

And if we run it now, here's the subject information we need.

So to get this into a nice schedule for Rex let's add an ORDER BY and let's do

it by PERIOD_ID ascending as well as let's just select a little less information.

So let's select the PERIOD then the SUBJECTS.NAME so

the NAME of the subject and then let's also select what grade it is.

SUBJECTS.GRADE which actually is name.

Yeah I think we don't need SUBJECTS there and

doesn't look like we need it here either.

And if we run this, right,

it is PERIOD_ID.

Unfortunately grade is an ambiguous column, so

let's do SUBJECTS.GRADE, and there we go.

That's Rex's schedule.