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 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;

0:00
Bobby tables, I guess they'll have to be having words with his parents.

0:04
Any who, getting back to the teacher questions,

0:07
there's two more things the management team would like to know.

0:10
First, they'd like to know which elective teacher is the most popular.

0:14
So if you can find out which elective teacher,

0:16
teaches the most students, that'd be awesome.

0:19
Let's start, by figuring out which teachers are the elective teachers.

0:23
Luckily, we've already written this query.

0:25
So it's in the teacher's notes if you'd like to just copy and

0:27
paste it in, which is what I'm going to do.

0:30
However, let's paste it in as a common table expression.

0:32
So, WITH,

0:36
ELECTIVE TEACHERS AS And

0:45
then I'm gonna tab this out.

0:48
And then just to make sure it worked, let's scroll down here and

0:52
SELECT * FROM ELECTIVE_TEACHERS.

0:57
Perfect, from here,

0:58
we need to figure out which elective teacher teaches the most students.

1:03
So another intermediate step we can take,

1:05
is to figure out how many students each elective teacher teaches.

1:10
So let's do that as another common table expression.

1:14
So, and let's call it STUDENT_COUNTS and AS.

1:23
And then here, let's start by selecting everything FROM ELECTIVE_TEACHERS,

1:28
and then to figure out, how many students each elective teacher teaches,

1:33
we'll just need to join through to the classes table on the teacher ID.

1:37
And then join from the classes table to the schedule.

1:41
Once we're to the schedule, each row will represent a student.

1:44
So we'll just need to count how many rows there are for each elective teacher.

1:48
So let start by joining to the classes table.

1:50
JOIN CLASSES ON, And remember,

1:55
we're selecting the TEACHERS.ID, Up here,

2:00
so we can join on ELECTIVE_TEACHERS.ID.

2:07
Yep, and it'll be named ID.

2:11
And this is going to = CLASSES.TEACHER_ID.

2:17
And then we need to join to the schedule table.

2:20
So JOIN SCHEDULE ON CLASSES.ID = SCHEDULE.CLASS_ID.

2:25
And now that we got one row for each student,

2:30
we just need to group by the teachers.

2:34
So GROUP BY ELECTIVE_TEACHERS.ID.

2:38
And we need to select the ELECTIVE_TEACHERS

2:45
.ID as well as the COUNT.

2:48
And let's put this FROM on a new line.

2:51
And if we select start from STUDENT_COUNTS,

2:54
we should have the counts of how many students go with each elective teacher.

2:58
So let's try that.

3:00
SELECT* FROM STUDENT_COUNTS.

3:04
All right, looks good.

3:07
But, the question was to figure out, which one was teaching the most students?

3:12
So again, let's come down here, and let's SELECT the MAX

3:18
of STUDENT_COUNTS.,

3:22
and we wanna select the max of this column, which we have an alias.

3:26
So we're gonna need to do that.

3:28
So let's come up here, and call it CT.

3:32
And then down here, we'll be able to select the max of CT.

3:35
And we wanna select which teacher this is, as well.

3:40
So for that, we're going to need to join to the teacher's table.

3:43
But first, we need to say FROM, and this is STUDENT_COUNTS,

3:48
and let's put that from on a new line actually.

3:50
And then from student counts,

3:55
JOIN to TEACHERS on STUDENT_COUNTS.ID = TEACHERS.ID.

4:03
And so, in addition to selecting the MAX of the STUDENT_COUNTS,

4:07
we also wanna select what teacher it is.

4:09
So let's select the MAX and then,

4:14
TEACHERS.FIRST_NAME, and TEACHERS.LAST_NAME.

4:22
And if we run this, it looks like Janice Ambrose is the elective teacher teaching

4:25
the most students.
You need to sign up for Treehouse in order to download course files.
Sign up