**Heads up!** To view this whole video, sign in with your Courses account or enroll in your free 7-day trial.
Sign In
Enroll

Start a free Courses trial

to watch this video

Which elective teacher is the most popular?

#### Using the WITH keyword

Want to learn and practice more about using the `WITH`

keyword? Check out our workshop on it: Common Table Expressions Using WITH

Elective teachers query:

```
SELECT DISTINCT TEACHERS.ID, FIRST_NAME, LAST_NAME
FROM TEACHERS
JOIN CLASSES ON TEACHERS.ID = CLASSES.TEACHER_ID
JOIN SUBJECTS ON CLASSES.SUBJECT_ID = SUBJECTS.ID
WHERE GRADE IS NULL;
```

Bobby tables, I guess they'll have to be having words with his parents. 0:00 Any who, getting back to the teacher questions, 0:04 there's two more things the management team would like to know. 0:07 First, they'd like to know which elective teacher is the most popular. 0:10 So if you can find out which elective teacher, 0:14 teaches the most students, that'd be awesome. 0:16 Let's start, by figuring out which teachers are the elective teachers. 0:19 Luckily, we've already written this query. 0:23 So it's in the teacher's notes if you'd like to just copy and 0:25 paste it in, which is what I'm going to do. 0:27 However, let's paste it in as a common table expression. 0:30 So, WITH, 0:32 ELECTIVE TEACHERS AS And 0:36 then I'm gonna tab this out. 0:45 And then just to make sure it worked, let's scroll down here and 0:48 SELECT * FROM ELECTIVE_TEACHERS. 0:52 Perfect, from here, 0:57 we need to figure out which elective teacher teaches the most students. 0:58 So another intermediate step we can take, 1:03 is to figure out how many students each elective teacher teaches. 1:05 So let's do that as another common table expression. 1:10 So, and let's call it STUDENT_COUNTS and AS. 1:14 And then here, let's start by selecting everything FROM ELECTIVE_TEACHERS, 1:23 and then to figure out, how many students each elective teacher teaches, 1:28 we'll just need to join through to the classes table on the teacher ID. 1:33 And then join from the classes table to the schedule. 1:37 Once we're to the schedule, each row will represent a student. 1:41 So we'll just need to count how many rows there are for each elective teacher. 1:44 So let start by joining to the classes table. 1:48 JOIN CLASSES ON, And remember, 1:50 we're selecting the TEACHERS.ID, Up here, 1:55 so we can join on ELECTIVE_TEACHERS.ID. 2:00 Yep, and it'll be named ID. 2:07 And this is going to = CLASSES.TEACHER_ID. 2:11 And then we need to join to the schedule table. 2:17 So JOIN SCHEDULE ON CLASSES.ID = SCHEDULE.CLASS_ID. 2:20 And now that we got one row for each student, 2:25 we just need to group by the teachers. 2:30 So GROUP BY ELECTIVE_TEACHERS.ID. 2:34 And we need to select the ELECTIVE_TEACHERS 2:38 .ID as well as the COUNT. 2:45 And let's put this FROM on a new line. 2:48 And if we select start from STUDENT_COUNTS, 2:51 we should have the counts of how many students go with each elective teacher. 2:54 So let's try that. 2:58 SELECT* FROM STUDENT_COUNTS. 3:00 All right, looks good. 3:04 But, the question was to figure out, which one was teaching the most students? 3:07 So again, let's come down here, and let's SELECT the MAX 3:12 of STUDENT_COUNTS., 3:18 and we wanna select the max of this column, which we have an alias. 3:22 So we're gonna need to do that. 3:26 So let's come up here, and call it CT. 3:28 And then down here, we'll be able to select the max of CT. 3:32 And we wanna select which teacher this is, as well. 3:35 So for that, we're going to need to join to the teacher's table. 3:40 But first, we need to say FROM, and this is STUDENT_COUNTS, 3:43 and let's put that from on a new line actually. 3:48 And then from student counts, 3:50 JOIN to TEACHERS on STUDENT_COUNTS.ID = TEACHERS.ID. 3:55 And so, in addition to selecting the MAX of the STUDENT_COUNTS, 4:03 we also wanna select what teacher it is. 4:07 So let's select the MAX and then, 4:09 TEACHERS.FIRST_NAME, and TEACHERS.LAST_NAME. 4:14 And if we run this, it looks like Janice Ambrose is the elective teacher teaching 4:22 the most students. 4:25

You need to sign up for Treehouse in order to download course files.

Sign up