1 00:00:00,340 --> 00:00:01,382 Great job. 2 00:00:01,382 --> 00:00:04,550 Next they'd like to figure out which subject is the least 3 00:00:04,550 --> 00:00:06,660 popular amongst the students. 4 00:00:06,660 --> 00:00:10,760 Can you find which subject is taken the least and how many students are taking it? 5 00:00:12,300 --> 00:00:16,770 To figure out which subject is the least popular let's start by just figuring out 6 00:00:16,770 --> 00:00:19,010 how many students are taking each subject. 7 00:00:20,100 --> 00:00:24,080 So, let's start by selecting everything from the SUBJECTS table. 8 00:00:26,000 --> 00:00:30,320 Then, let's join from the SUBJECTS table to the CLASSES table. 9 00:00:30,320 --> 00:00:36,598 So JOIN CLASSES ON SUBJECTS.ID = CLASSES.ID, 10 00:00:36,598 --> 00:00:41,143 or rather CLASSES.SUBJECT_ID. 11 00:00:42,310 --> 00:00:47,240 And then, to get the number of students, we'll need to join to the SCHEDULE table. 12 00:00:48,390 --> 00:00:54,041 So JOIN SCHEDULE ON CLASSES.ID 13 00:00:54,041 --> 00:00:58,866 = SCHEDULE.CLASS_ID. 14 00:00:58,866 --> 00:01:01,810 And let's run this to make sure we've got all the syntax right. 15 00:01:01,810 --> 00:01:06,720 And then from here, we can count the rows in each subject 16 00:01:06,720 --> 00:01:10,090 to figure out how many students are in that subject. 17 00:01:10,090 --> 00:01:14,370 So let's change this to be grouping by the subject, so 18 00:01:14,370 --> 00:01:18,270 let's GROUP BY SUBJECT_ID. 19 00:01:18,270 --> 00:01:23,340 And then up here let's select the SUBJECT_ID as well as the COUNT. 20 00:01:25,140 --> 00:01:28,871 And actually rather than selecting the SUBJECT_ID, let's select the SUBJECT_NAME. 21 00:01:28,871 --> 00:01:34,234 So that's gonna be SUBJECTS.NAME. 22 00:01:34,234 --> 00:01:36,634 And then if we look through this, 23 00:01:36,634 --> 00:01:43,210 it looks like the answer's going to be Puppetry down here with 58 students. 24 00:01:43,210 --> 00:01:48,480 So let's see how we can get this into one query that says Puppetry and 58. 25 00:01:48,480 --> 00:01:52,630 So starting with the data set we have here, it's pretty simple. 26 00:01:52,630 --> 00:01:55,270 We just want to take the minimum of this right column. 27 00:01:56,990 --> 00:02:02,200 So I'm going to alias this column as CT standing for count. 28 00:02:02,200 --> 00:02:05,020 And then I'm gonna use a common table expression again. 29 00:02:05,020 --> 00:02:08,860 So WITH SUBJECT_COUNTS AS. 30 00:02:09,880 --> 00:02:13,630 Let's put that down there, add our other parenthesis. 31 00:02:13,630 --> 00:02:14,940 And then let's tab this out. 32 00:02:17,060 --> 00:02:21,101 And then down here, let's select the NAME. 33 00:02:22,140 --> 00:02:26,020 And we're going to select the minimum of what's now the CT column. 34 00:02:26,020 --> 00:02:30,800 And we're going to select this all from the SUBJECT_COUNTS 35 00:02:30,800 --> 00:02:31,760 common table expression. 36 00:02:33,590 --> 00:02:37,830 And if we run this, Puppetry, 58, perfect.