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