Preview
Video Player
00:00
00:00
Least Popular Subject
2:38Which subject is the least popular, and how many students are taking it?
Great job.
0:00
Next they'd like to figure out
which subject is the least
0:01
popular amongst the students.
0:04
Can you find which subject is taken the
least and how many students are taking it?
0:06
To figure out which subject is the least
popular let's start by just figuring out
0:12
how many students are taking each subject.
0:16
So, let's start by selecting
everything from the SUBJECTS table.
0:20
Then, let's join from the SUBJECTS
table to the CLASSES table.
0:26
So JOIN CLASSES ON SUBJECTS.ID
= CLASSES.ID,
0:30
or rather CLASSES.SUBJECT_ID.
0:36
And then, to get the number of students,
we'll need to join to the SCHEDULE table.
0:42
So JOIN SCHEDULE ON CLASSES.ID
0:48
= SCHEDULE.CLASS_ID.
0:54
And let's run this to make sure
we've got all the syntax right.
0:58
And then from here,
we can count the rows in each subject
1:01
to figure out how many
students are in that subject.
1:06
So let's change this to be
grouping by the subject, so
1:10
let's GROUP BY SUBJECT_ID.
1:14
And then up here let's select
the SUBJECT_ID as well as the COUNT.
1:18
And actually rather than selecting the
SUBJECT_ID, let's select the SUBJECT_NAME.
1:25
So that's gonna be SUBJECTS.NAME.
1:28
And then if we look through this,
1:34
it looks like the answer's going to be
Puppetry down here with 58 students.
1:36
So let's see how we can get this into
one query that says Puppetry and 58.
1:43
So starting with the data set we
have here, it's pretty simple.
1:48
We just want to take the minimum
of this right column.
1:52
So I'm going to alias this
column as CT standing for count.
1:56
And then I'm gonna use a common
table expression again.
2:02
So WITH SUBJECT_COUNTS AS.
2:05
Let's put that down there,
add our other parenthesis.
2:09
And then let's tab this out.
2:13
And then down here, let's select the NAME.
2:17
And we're going to select the minimum
of what's now the CT column.
2:22
And we're going to select this
all from the SUBJECT_COUNTS
2:26
common table expression.
2:30
And if we run this, Puppetry, 58, perfect.
2:33