1 00:00:00,310 --> 00:00:05,440 Okay, so next year there'll probably be around 220 sixth grade students. 2 00:00:05,440 --> 00:00:08,940 It seems like the current sixth grade classrooms are pretty full. 3 00:00:08,940 --> 00:00:11,220 Do they have room for that many sixth graders? 4 00:00:12,330 --> 00:00:17,390 Okay, so we've gotta figure out if we have room for 220 sixth graders. 5 00:00:17,390 --> 00:00:18,410 But, how do we do that? 6 00:00:19,480 --> 00:00:22,280 This is a frequent problem with writing reports. 7 00:00:22,280 --> 00:00:25,240 Sometimes we aren't told exactly how to get the data. 8 00:00:25,240 --> 00:00:27,780 So, we need to figure it out on our own. 9 00:00:27,780 --> 00:00:30,390 For this example, a good approach would be to look at 10 00:00:30,390 --> 00:00:35,250 all of the sixth grade classrooms and see which one has the smallest capacity. 11 00:00:35,250 --> 00:00:37,220 Then we can multiply that capacity by seven, 12 00:00:37,220 --> 00:00:42,520 the number of periods in a day, to get the maximum number of sixth graders. 13 00:00:42,520 --> 00:00:46,219 Since each sixth grader needs to take each sixth grade class, 14 00:00:46,219 --> 00:00:47,720 this is a good approach. 15 00:00:48,740 --> 00:00:53,840 So let's start by getting a list of the rooms that teach sixth grade. 16 00:00:53,840 --> 00:00:56,990 And let's start from the CLASSES table since that has a lot of the information 17 00:00:56,990 --> 00:00:58,690 we're going to need. 18 00:00:58,690 --> 00:01:00,580 And let's go from the CLASSES table and 19 00:01:00,580 --> 00:01:04,740 we're going to join to the SUBJECT table to get which grade the class is. 20 00:01:04,740 --> 00:01:07,750 And we'll join to the ROOMS table to get the capacity 21 00:01:07,750 --> 00:01:09,200 of the room that that class is in. 22 00:01:10,430 --> 00:01:16,672 Okay, getting to the query, let's start by selecting everything from CLASSES. 23 00:01:16,672 --> 00:01:20,281 And then let's join this to the SUBJECTS table so 24 00:01:20,281 --> 00:01:23,190 we can get what grade that class is. 25 00:01:23,190 --> 00:01:29,295 And let's join this on SUBJECTS.ID = CLASSES.SUBJECT_ID. 26 00:01:29,295 --> 00:01:33,430 And from here, I guess we can add our WHERE clause. 27 00:01:33,430 --> 00:01:36,398 So, WHERE GRADE = 6. 28 00:01:36,398 --> 00:01:38,020 And there we go. 29 00:01:39,040 --> 00:01:44,000 And so from here we need to use this ROOM_ID to join out to the ROOMS table. 30 00:01:44,000 --> 00:01:48,850 So, let's join to ROOMS ON, and this is coming from the CLASSES table. 31 00:01:49,890 --> 00:01:53,000 So you can see we got CLASSES table right here. 32 00:01:53,000 --> 00:01:55,940 SUBJECTS table from this ID over to description. 33 00:01:55,940 --> 00:02:03,777 So join ROOMS ON ROOMS.ID = CLASSES.ROOM_ID. 34 00:02:03,777 --> 00:02:07,470 And if we run this, now we've got some capacity numbers. 35 00:02:07,470 --> 00:02:10,150 We need to figure out what the smallest capacity is. 36 00:02:10,150 --> 00:02:15,200 So, let's select the minimum which is MIN of the CAPACITY 37 00:02:16,570 --> 00:02:21,330 from CLASSES and from the sixth grade classes, since these are all sixth grade. 38 00:02:21,330 --> 00:02:25,510 And we can run this and we get 30. 39 00:02:25,510 --> 00:02:28,980 And since that class is going to happen seven times a day, 40 00:02:28,980 --> 00:02:32,990 the maximum that the smallest class can hold is 210. 41 00:02:32,990 --> 00:02:36,880 Which is, unfortunately, less than 220. 42 00:02:36,880 --> 00:02:39,740 >> Well, that's certainly not good news. 43 00:02:39,740 --> 00:02:43,340 But, at least they've got a year to figure what to do about it. 44 00:02:43,340 --> 00:02:45,640 Thanks a ton for helping them figure that out. 45 00:02:45,640 --> 00:02:46,310 See you tomorrow!