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