Preview
Video Player
00:00
00:00
Janis' Schedule
4:17What class does Janis Ambrose teach during each period? Be sure to include all 7 periods in your report!
That makes sense.
0:00
In addition to that,
can you figure out her schedule?
0:01
What class does she teach
during each period?
0:04
Also, make sure that your report
includes all seven periods, so
0:07
we can easily compare
schedules between teachers.
0:10
For this query,
0:14
let's start by just getting a list
of which classes Janice teaches.
0:14
And to do that, we're going to need
to join from the TEACHERS table
0:19
to the CLASSES table.
0:22
So let's select everything
from the TEACHERS table, and
0:24
join that to the CLASSES table,
ON TEACHERS.ID = CLASSES.TEACHER_ID.
0:28
Perfect.
0:35
Then let's add a WHERE and,
0:36
I guess we can just peek down here,
Janice should be down here somewhere.
0:39
Instead of doing where
her first name is Janice,
0:44
we can do by the ID,
which is a little more robust.
0:46
So, let's do where TEACHERS.ID = 391,
0:48
which is Janice, and there we have
Janice and all of her classes.
0:53
So to get which classes these
are we're also going to have to join
0:58
to the subjects table.
1:02
So let's add a join here.
1:03
Join to subjects on SUBJECTS.ID
1:06
= CLASSES.SUBJECT_ID.
1:11
And up here, let's go ahead and
1:15
select the PERIOD SUBJECTS.NAME.
1:19
And if we run this, sorry this column
is called period ID, we have one,
1:24
two, three, five, six, seven, so
to get this to show us the fourth
1:29
period as well we're going to
need to use the PERIODs table.
1:34
But before we get to that I'd like to
show you a common table expression.
1:39
So before this SELECT, let's type WITH and
1:44
then let's type JANIS_CLASSES and
then AS and then a parenthesis,
1:48
and I'm gonna go ahead and,
that doesn't work.
1:55
So I'm gonna click and Tab, click and
Tab and then add another parenthesis here,
2:00
and what this is, is this is
called a common table expression.
2:06
It's basically just a sub query,
2:09
except we get to put it above
instead of inside of our query.
2:11
Let me show you how it works.
2:15
So down here,
we can type SELECT* FROM JANIS_CLASSES,
2:16
and it's the same thing we just got.
2:24
So using a common table expression which
is just this WITH keyword and then AS,
2:26
and the query you want inside parentheses,
lets us give a name to our data set.
2:31
So what we wanna do here is we
wanna SELECT * FROM PERIODS.
2:37
When we do that, we get which period
it is, which gives us this four, and
2:43
that's really important.
2:48
So, we get the periods, and what we want
to do down here is a left outer join
2:50
to Janis classes on the period ID.
2:56
So on periods.ID equals, and
remember in Janis classes,
3:01
we have this period ID column,
so PERIODS.ID equals PERIOD_ID.
3:07
And let's go ahead and
3:14
select only the PERIODS.ID and
3:18
JANIS_CLASSES.NAME.
3:23
And there we go there's, Janis's schedule.
3:26
So what's happening with
this left outer join?
3:30
Well, normally, we've been doing
interjoins which means that you
3:33
only return a row if there's a match in
the table but with a left outer join,
3:37
what you're saying is I want every
single row from the periods table and if
3:42
we can join to a row and the Janis classes
table let's go ahead and bring that in.
3:48
So we get every row from
the PERIODs table and
3:53
if there's data in the JANIS_CLASSES
table it'll JOIN that in for us.
3:56
So again if we just make this a JOIN,
4:01
We're not gonna get the fourth
period that we need.
4:05
So changing this back to
a LEFT OUTER JOIN, it's gonna give us
4:08
everything from the PERIODS table which
means that we have all the periods.
4:13