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

Start a free Courses trial

to watch this video

Which 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

You need to sign up for Treehouse in order to download course files.

Sign up