Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.
Start a free Basic trial
to watch this video
Which teachers teach elective courses?
-
0:00
Perfect, it's always nice to show someone they're appreciated.
-
0:04
Lastly, the school is looking for ways to get students to take more electives.
-
0:07
And they'd like to start by getting the opinions of the elective teachers.
-
0:11
Could you generate a list of the elective teachers?
-
0:14
This query is going to be a lot like the previous two queries.
-
0:17
We're going to need to start with the TEACHERS or SUBJECTS table and
-
0:20
use the CLASSES table to join to the other one.
-
0:22
So let's start by selecting DISTINCT.
-
0:27
And it's TEACHERS.ID and FIRST_NAME and LAST_NAME.
-
0:34
FROM TEACHERS JOIN CLASSES ON TEACHERS.ID
-
0:41
= CLASSES.TEACHER_ID.
-
0:45
And JOIN to SUBJECTS on SUBJECTS.ID
-
0:51
= CLASSES.SUBJECT_ID.
-
0:56
Then from here we only wanna select the teachers who are teaching the electives.
-
1:01
So if we look at the SUBJECTS table,
-
1:03
the electives are represented as classes that don't have a grade level.
-
1:08
So, back in the query, let's add WHERE GRADE = NULL and let's run it.
-
1:18
And we get nothing.
-
1:19
And to see what it is, let's take a second and talk about null.
-
1:23
In SQL, nothing can ever equal null, ever.
-
1:27
You can't do it.
-
1:27
You cannot equal null.
-
1:29
Think of null as more of an I don't know.
-
1:33
If we asked two people what they had for breakfast this morning and
-
1:36
they both said I don't know, we can't just conclude that they had the same thing.
-
1:41
In SQL, when you compare two things, It could be true or
-
1:45
false or it can also be unknown.
-
1:49
Let's try a few examples.
-
1:51
I'm going to cut this out, but you should probably leave it.
-
1:55
So, let's SELECT 1 = 1..
-
1:58
And if we run this, we get 1, which means true.
-
2:02
If we SELECTt 1 = 0, we get 0, which means false.
-
2:07
Then if we check if 1 = NULL, we get NULL because we don't know if 1 can equal NULL,
-
2:13
cuz we don't know what NULL is.
-
2:16
And even worse, if we check NULL = NULL, we get null because we just don`t know.
-
2:23
So when you`re checking with null, you want to use IS.
-
2:28
And there we get 1.
-
2:29
Meaning null is null.
-
2:31
Cool.
-
2:32
So, pasting back in the query, Instead of saying
-
2:37
WHERE GRADE = NULL we wanna say WHERE GRADE IS NULL.
-
2:43
And if we run this, we get our list of electives teachers.
-
2:48
Great work on your first day.
-
2:50
It looks like you're really getting the hang of this.
-
2:52
See you tomorrow.
You need to sign up for Treehouse in order to download course files.
Sign up