58, that's not too bad.

But before we get to the next query,

there's something funny going on with the school system.

And I think it has to do with a student who has fifth period science and

seventh period art.

Can you figure out which students have fifth period science and

seventh period art?

To figure out which students have fifth period science and seventh period art,

a good approach would be to get a data set of fifth period science students.

And a separate data set of 7th period art students and

then to join them and see what's inside the inner section.

So let's start by figuring out which students have fifth period science.

So let's select everything from the student's table.

And then to get which classes they are taking, we're going to need to join to

the schedule table and then to the classes table.

So let's JOIN to the SCHEDULE table

ON STUDENTS.ID = SCHEDULE.STUDENT_ID.

And then if we run this, that gives us the class ID.

And from here we'll be able to join to the classes table.

Which from there will have the subject ID and then we can join to the subject table.

And we can get the rest of the information we need.

So we need to JOIN from here to CLASSES ON CLASSES.ID = SCHEDULE.CLASS_ID.

And remember that gives us the subject ID,

which means we can JOIN to SUBJECTS ON SUBJECTS.ID = and

remember this is from the CLASSES table .SUBJECT_ID.

Then, now that we've got all the information

that we need let's add a WHERE clause.

And say WHERE PERIOD_ID = 5 AND

SUBJECT.NAME = 'Science'.

And if we Run this, we should have

only period 5 and only science.

Perfect.

Now, instead of selecting everything let's only select the Student ID number.

So let's just SELECT STUDENT_ID.

And let's Run this, and perfect.

Now let's change this to be part of a CTE, which stands for common table expression.

So WITH, and let's call this FIFTH_SCIENCE AS that.

And then let's add another parenthesis down here.

Tab these out.

And so those are our fifth period science students.

Then let's copy this, except for we don't need to copy the whole thing.

Let's just copy from the AS.

And when you're doing multiple common table expressions, what you wanna do,

is you add a comma after your last one, then you name your next one.

So SEVENTH_ART and then you add the common table expression.

So for this we want to change the period to 7 and

we want to change the name of the subject to Art.

Finally, once we've got our two common table expressions,

we just need to join them together.

So let's SELECT * FROM FIFTH_SCIENCE and

let's JOIN that to SEVENTH_ART ON.

And since I'd rather not keep writing these out,

I'm going to alias Fifth science as A and Seventh Art as B.

So ON A.STUDENT_ID = B.STUDENT_ID and

if we run this, we get one student back.

So to figure out which student this is, we just need to join to the students table.

So let's JOIN to STUDENTS, and since we've been aliasing this, let's go ahead and

alias this one as C.

So join ON A, and since they have the same ID,

it doesn't matter if we use A or B, .STUDENT_ID = C .ID.

And instead of selecting everything, let's just select what's in the students' table.

So let's SELECT C .*.

And there we go.

It looks like it's Bobby Tables.