Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases SQL Reporting by Example Day 2: Advanced Selecting Predicting the Future

Giuseppe Ardito
Giuseppe Ardito
14,130 Points
Posted by Giuseppe Ardito
Giuseppe Ardito
14,130 Points

Are students using 6 rooms at the same time for 6th grade?

My doubt is of logical nature.

Querying the database, I can understand that 6th grade students are - at any given period - using 6 different 6th grade rooms.

In other words the 6th grade student are being split between 6 rooms depending on their schedule.

So, to understand the total capacity the school has for 6th grade rooms, shouldn't we sum 6th grade room's capacity?

2 Answers

Steven Parker
Steven Parker
229,783 Points
Steven Parker
Steven Parker
229,783 Points

I see what you are suggesting, but I think the issue is that each student must take all classes. So at some point every student would spend one period in the smallest room. So to account for that, the capacity is that of the smallest room that is used for 6th grade classes multiplied by the number of periods in the day.

Your suggestion would work if students were allowed to skip the class given in the smallest room entirely, and be in one of the larger rooms more than once a day.

Giuseppe Ardito
Giuseppe Ardito
14,130 Points
Giuseppe Ardito
Giuseppe Ardito
14,130 Points

I see what you mean. Ok I think I got it now. Thanks for the clarification Steven!

Charlie Krell
Charlie Krell
2,184 Points
Charlie Krell
Charlie Krell
2,184 Points

Ah! I think I was falling in the same hole. I was summing the capacity of 6th grade rooms for each period. I guess the problem with that is, the school can change around which 6th grade rooms are scheduled for each period. Is my logic on the right track?

SELECT PERIOD_ID, SUM(CAPACITY) FROM ROOMS
JOIN CLASSES ON CLASSES.ROOM_ID = ROOMS.ID
JOIN SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID
WHERE GRADE = 6
GROUP BY PERIOD_ID
Mark Chesney
Mark Chesney
11,747 Points

Yes, I'm appreciating this conversation, as it's giving light to an unspoken assumption. My assumption is that, in a hypothetical example, a required Algebra 1 class can happen in room 101 and 102, and a student need only attend one of them. However, in this school's database, 6th grade Math only occurs in Room 13, across periods 1-7. Here's the query I used

SELECT SUBJECTS.GRADE, CLASSES.ROOM_ID
, SUBJECTS.NAME
, ROOMS.CAPACITY
, SUM(ROOMS.CAPACITY)
FROM SUBJECTS
INNER JOIN CLASSES
ON CLASSES.SUBJECT_ID = SUBJECTS.ID
INNER JOIN ROOMS
ON CLASSES.ROOM_ID = ROOMS.ID
WHERE SUBJECTS.GRADE = 6
GROUP BY CLASSES.ROOM_ID
Gavin Schilling
Gavin Schilling
37,904 Points
Gavin Schilling
Gavin Schilling
37,904 Points

-- Do they have room for that many 6th graders?

SELECT DISTINCT(classes.room_id) AS "Room #", subjects.grade AS "Grade", subjects.name AS "Course", periods.id AS "# of Periods", rooms.capacity AS "Room Capacity per Period" FROM CLASSES
  JOIN SUBJECTS ON subjects.id = classes.subject_id
  JOIN ROOMS ON rooms.id = classes.room_id
  JOIN PERIODS ON periods.id = classes.period_id
  GROUP BY subjects.name HAVING subjects.grade = 6
  ORDER BY classes.room_id;


SELECT DISTINCT(classes.room_id) AS "Room #", subjects.grade AS "Grade", subjects.name AS "Course", periods.id AS "# of Periods", rooms.capacity AS "Room Capacity per Period" FROM CLASSES
  JOIN SUBJECTS ON subjects.id = classes.subject_id
  JOIN ROOMS ON rooms.id = classes.room_id
  JOIN PERIODS ON periods.id = classes.period_id
  GROUP BY subjects.name
  ORDER BY classes.room_id;


SELECT 30*4+35*2 AS "Current 6th Grade Capacity per Period";

SELECT "School needs to fund for outside expansion of P.E. & use Room #18 to accommodate next year's in-coming class expansion, use Room #20 (Woodshop Room) for the other 2 periods and/or Room #23 (Puppetry Room) for the other 3 periods as the capacity sizes are much smaller, or go to block scheduling so rooms can be used multi-purpose" AS "Answer";

Seems to be the best solution to me.

Steven Parker
Steven Parker
229,783 Points
Steven Parker
Steven Parker
229,783 Points

This won't give you a correct result. It is adding up the room ID numbers with no concern for capacity!

If you fix that, you still need to account for the smallest room being the limiting factor.