Question

Write appropriate PL/SQL statements to create a procedure to check that no section has more students than the capacity of the classroom in which the section is scheduled. Display the course_id, sec_id, semester, year, building, and room_number for the sections that have more students than the capacity of the classroom in which the section is scheduled.   The following are the tables needed:

classroom (building, room_number, capacity)

section (course_id, sec_id, semester, year, building, room_number, time_slot_id)

takes (student_id, course_id, sec_id, semester, year, grade)

Answer 1

CREATE OR REPLACE PROCEDURE capacity
AS
BEGIN

WITH student_count AS
(
SELECT count(student_id) stud_count, sec_id
FROM   takes
GROUP BY course_id, sec_id, semester, year, grade
),
WITH Sec_Cap
(
SELECT sum(a.capacity) room_cap,b.sec_id
FROM   classroom JOIN section
ON (a.room_number = b.room_number
      AND a.building = b.building
)
)

SELECT a.course_id
       , a.sec_id
    , a.semester
    , a.year
    , a.building
    , a.room_number
   FROM section a
   JOIN student_count b ON (a.sec_id = b.sec_id)
   JOIN Sec_Cap c ON (    b.sec_id = a.sec_id
                       AND b.stud_count > c.room_cap )
END;