"HTML" was retired on January 6, 2020. You are now viewing the recommended replacement.
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
Which teachers don't have a class during 1st period?
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
Wow, that must keep her pretty busy.
0:00
The last thing I'd like you to figure out,
0:02
is which teachers don't
have a first period class?
0:04
They're having trouble unloading
the buses on time, and
0:07
are wondering if they can get
some of the teachers to help out.
0:10
To figure out which teachers don't
have a class during first period,
0:13
we can actually do
something pretty clever.
0:17
Instead of trying to solve
this question directly,
0:19
we can figure out which teachers do
have a class during first period, and
0:22
just subtract those teachers away
from all the other teachers.
0:26
So let's start by figuring out,
0:30
which teachers do have
a class during first period.
0:31
So let's SELECT* FROM TEACHERS, And
then to get the period information,
0:34
we'll need to join to the classes table.
0:39
So JOIN CLASSES ON TEACHERS.ID
0:41
= CLASSES.TEACHER_ID.
0:46
And let's add a WHERE clause.
0:50
And if we look at the CLASSES table,
we've got a PERIOD_ID column.
0:52
And if you don't have a CLASSES table
over here, you can always just select
0:57
star from classes or
whatever table you need to look into.
1:00
So WHERE PERIOD.ID = 1.
1:03
All right, here's the teachers
that have a class first period.
1:08
Now, all we need to do, is take
a selection of all the teachers, and
1:13
subtract away these teachers.
1:17
So above this,
let's SELECT* FROM TEACHERS.
1:19
And then, between these two queries,
all we need to write is EXCEPT,
1:25
which in some languages
will be the minus keyword.
1:29
Now, if we run the query, we get an error.
1:33
When you're using except, you need to
make sure that you have exactly the same
1:37
columns in your first query and
your second query.
1:41
So instead of selecting
everything from both of these,
1:44
since the second one
includes the classes table,
1:48
let's go ahead and
just select TEACHERS.* and TEACHERS.*.
1:52
And now if we run it, it looks like
we've only got one teacher back,
1:57
Cassandra McGuire.
2:01
Great work figuring out
all of these queries.
2:03
I'm not sure where they'd be without you.
2:06
On that note,
if you've got any questions or
2:08
comments about what you've seen here,
be sure to post about it in the community.
2:11
Until next time.
2:15
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