Predicting the Future2:47 with Ben Deitch
Do they have room for that many 6th graders?
Okay, so next year there'll probably be around 220 sixth grade students. 0:00 It seems like the current sixth grade classrooms are pretty full. 0:05 Do they have room for that many sixth graders? 0:08 Okay, so we've gotta figure out if we have room for 220 sixth graders. 0:12 But, how do we do that? 0:17 This is a frequent problem with writing reports. 0:19 Sometimes we aren't told exactly how to get the data. 0:22 So, we need to figure it out on our own. 0:25 For this example, a good approach would be to look at 0:27 all of the sixth grade classrooms and see which one has the smallest capacity. 0:30 Then we can multiply that capacity by seven, 0:35 the number of periods in a day, to get the maximum number of sixth graders. 0:37 Since each sixth grader needs to take each sixth grade class, 0:42 this is a good approach. 0:46 So let's start by getting a list of the rooms that teach sixth grade. 0:48 And let's start from the CLASSES table since that has a lot of the information 0:53 we're going to need. 0:56 And let's go from the CLASSES table and 0:58 we're going to join to the SUBJECT table to get which grade the class is. 1:00 And we'll join to the ROOMS table to get the capacity 1:04 of the room that that class is in. 1:07 Okay, getting to the query, let's start by selecting everything from CLASSES. 1:10 And then let's join this to the SUBJECTS table so 1:16 we can get what grade that class is. 1:20 And let's join this on SUBJECTS.ID = CLASSES.SUBJECT_ID. 1:23 And from here, I guess we can add our WHERE clause. 1:29 So, WHERE GRADE = 6. 1:33 And there we go. 1:36 And so from here we need to use this ROOM_ID to join out to the ROOMS table. 1:39 So, let's join to ROOMS ON, and this is coming from the CLASSES table. 1:44 So you can see we got CLASSES table right here. 1:49 SUBJECTS table from this ID over to description. 1:53 So join ROOMS ON ROOMS.ID = CLASSES.ROOM_ID. 1:55 And if we run this, now we've got some capacity numbers. 2:03 We need to figure out what the smallest capacity is. 2:07 So, let's select the minimum which is MIN of the CAPACITY 2:10 from CLASSES and from the sixth grade classes, since these are all sixth grade. 2:16 And we can run this and we get 30. 2:21 And since that class is going to happen seven times a day, 2:25 the maximum that the smallest class can hold is 210. 2:28 Which is, unfortunately, less than 220. 2:32 >> Well, that's certainly not good news. 2:36 But, at least they've got a year to figure what to do about it. 2:39 Thanks a ton for helping them figure that out. 2:43 See you tomorrow! 2:45
You need to sign up for Treehouse in order to download course files.Sign up