Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed SQL Reporting by Example!
You have completed SQL Reporting by Example!
Preview
What class does Janis Ambrose teach during each period? Be sure to include all 7 periods in your report!
This video doesn't have any notes.
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
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
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up