Preview
Video Player
00:00
00:00
Predicting the Future
2:47Do 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