1 00:00:00,330 --> 00:00:04,590 Bobby tables, I guess they'll have to be having words with his parents. 2 00:00:04,590 --> 00:00:07,060 Any who, getting back to the teacher questions, 3 00:00:07,060 --> 00:00:10,270 there's two more things the management team would like to know. 4 00:00:10,270 --> 00:00:14,210 First, they'd like to know which elective teacher is the most popular. 5 00:00:14,210 --> 00:00:16,270 So if you can find out which elective teacher, 6 00:00:16,270 --> 00:00:18,700 teaches the most students, that'd be awesome. 7 00:00:19,700 --> 00:00:23,410 Let's start, by figuring out which teachers are the elective teachers. 8 00:00:23,410 --> 00:00:25,240 Luckily, we've already written this query. 9 00:00:25,240 --> 00:00:27,300 So it's in the teacher's notes if you'd like to just copy and 10 00:00:27,300 --> 00:00:30,090 paste it in, which is what I'm going to do. 11 00:00:30,090 --> 00:00:32,880 However, let's paste it in as a common table expression. 12 00:00:32,880 --> 00:00:34,191 So, WITH, 13 00:00:36,456 --> 00:00:45,339 ELECTIVE TEACHERS AS And 14 00:00:45,339 --> 00:00:46,670 then I'm gonna tab this out. 15 00:00:48,818 --> 00:00:52,828 And then just to make sure it worked, let's scroll down here and 16 00:00:52,828 --> 00:00:55,213 SELECT * FROM ELECTIVE_TEACHERS. 17 00:00:57,779 --> 00:00:58,824 Perfect, from here, 18 00:00:58,824 --> 00:01:02,190 we need to figure out which elective teacher teaches the most students. 19 00:01:03,553 --> 00:01:05,647 So another intermediate step we can take, 20 00:01:05,647 --> 00:01:08,915 is to figure out how many students each elective teacher teaches. 21 00:01:10,090 --> 00:01:12,410 So let's do that as another common table expression. 22 00:01:14,540 --> 00:01:21,085 So, and let's call it STUDENT_COUNTS and AS. 23 00:01:23,431 --> 00:01:28,628 And then here, let's start by selecting everything FROM ELECTIVE_TEACHERS, 24 00:01:28,628 --> 00:01:33,371 and then to figure out, how many students each elective teacher teaches, 25 00:01:33,371 --> 00:01:37,910 we'll just need to join through to the classes table on the teacher ID. 26 00:01:37,910 --> 00:01:41,460 And then join from the classes table to the schedule. 27 00:01:41,460 --> 00:01:44,990 Once we're to the schedule, each row will represent a student. 28 00:01:44,990 --> 00:01:48,770 So we'll just need to count how many rows there are for each elective teacher. 29 00:01:48,770 --> 00:01:50,758 So let start by joining to the classes table. 30 00:01:50,758 --> 00:01:55,635 JOIN CLASSES ON, And remember, 31 00:01:55,635 --> 00:02:00,463 we're selecting the TEACHERS.ID, Up here, 32 00:02:00,463 --> 00:02:04,936 so we can join on ELECTIVE_TEACHERS.ID. 33 00:02:07,758 --> 00:02:08,822 Yep, and it'll be named ID. 34 00:02:11,512 --> 00:02:17,360 And this is going to = CLASSES.TEACHER_ID. 35 00:02:17,360 --> 00:02:19,320 And then we need to join to the schedule table. 36 00:02:20,450 --> 00:02:25,623 So JOIN SCHEDULE ON CLASSES.ID = SCHEDULE.CLASS_ID. 37 00:02:25,623 --> 00:02:30,162 And now that we got one row for each student, 38 00:02:30,162 --> 00:02:34,360 we just need to group by the teachers. 39 00:02:34,360 --> 00:02:37,507 So GROUP BY ELECTIVE_TEACHERS.ID. 40 00:02:38,810 --> 00:02:42,868 And we need to select the ELECTIVE_TEACHERS 41 00:02:45,019 --> 00:02:46,710 .ID as well as the COUNT. 42 00:02:48,570 --> 00:02:51,170 And let's put this FROM on a new line. 43 00:02:51,170 --> 00:02:54,360 And if we select start from STUDENT_COUNTS, 44 00:02:54,360 --> 00:02:58,970 we should have the counts of how many students go with each elective teacher. 45 00:02:58,970 --> 00:03:00,310 So let's try that. 46 00:03:00,310 --> 00:03:01,690 SELECT* FROM STUDENT_COUNTS. 47 00:03:04,620 --> 00:03:07,030 All right, looks good. 48 00:03:07,030 --> 00:03:12,570 But, the question was to figure out, which one was teaching the most students? 49 00:03:12,570 --> 00:03:18,005 So again, let's come down here, and let's SELECT the MAX 50 00:03:18,005 --> 00:03:22,407 of STUDENT_COUNTS., 51 00:03:22,407 --> 00:03:26,920 and we wanna select the max of this column, which we have an alias. 52 00:03:26,920 --> 00:03:28,330 So we're gonna need to do that. 53 00:03:28,330 --> 00:03:31,150 So let's come up here, and call it CT. 54 00:03:32,520 --> 00:03:35,336 And then down here, we'll be able to select the max of CT. 55 00:03:35,336 --> 00:03:39,390 And we wanna select which teacher this is, as well. 56 00:03:40,510 --> 00:03:43,150 So for that, we're going to need to join to the teacher's table. 57 00:03:43,150 --> 00:03:48,810 But first, we need to say FROM, and this is STUDENT_COUNTS, 58 00:03:48,810 --> 00:03:50,970 and let's put that from on a new line actually. 59 00:03:50,970 --> 00:03:55,221 And then from student counts, 60 00:03:55,221 --> 00:04:03,571 JOIN to TEACHERS on STUDENT_COUNTS.ID = TEACHERS.ID. 61 00:04:03,571 --> 00:04:07,171 And so, in addition to selecting the MAX of the STUDENT_COUNTS, 62 00:04:07,171 --> 00:04:09,930 we also wanna select what teacher it is. 63 00:04:09,930 --> 00:04:14,416 So let's select the MAX and then, 64 00:04:14,416 --> 00:04:20,010 TEACHERS.FIRST_NAME, and TEACHERS.LAST_NAME. 65 00:04:22,290 --> 00:04:25,826 And if we run this, it looks like Janice Ambrose is the elective teacher teaching 66 00:04:25,826 --> 00:04:26,719 the most students.