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