Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed SQL Reporting by Example!
You have completed SQL Reporting by Example!
Preview
Do they have room for that many 6th graders?
This video doesn't have any notes.
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
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 upYou need to sign up for Treehouse in order to set up Workspace
Sign up