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

Ken Stone
Ken Stone
22,734 Points

I see some sixth grade classes with a max capacity of 15.

I see some sixth grade classes with a max capacity of 15. Using this query:

select count(*), room_id, rooms.capacity, period_id
from rooms
join classes on classes.room_id = rooms.id
join schedule on schedule.class_id = classes.id
join students on students.id = schedule.student_id
where students.grade = 6
group by room_id, period_id
order by capacity asc;

What is not right with this query?

1 Answer

Ben Deitch
STAFF
Ben Deitch
Treehouse Teacher

Hey Ken!

To get which rooms teach 6th grade classes we join to the SUBJECTS table and use the GRADE column. You're using the STUDENTS.GRADE column, which means that any class that contains 6th graders will be included. So in your result set, you end up including the elective classes which shouldn't be counted as they have a GRADE of NULL in the SUBJECTS table.

Hope that helps!

Ken Stone
Ken Stone
22,734 Points

Thanks Ben, crystal clear on that now! Actually that was my thought process to find any class that has sixth graders in it. OK thanks again!