Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed SQL Reporting by Example!
You have completed SQL Reporting by Example!
Preview
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;
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
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 upYou need to sign up for Treehouse in order to set up Workspace
Sign up