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

Preview

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