Write a nested cursor where the parent cursor calls information about each section of a course. The child cursor counts the enrollment. The only output is one line for each course with the course name, section number, and the total enrollment.
Answer: Your answer should look similar to the following:
SET SERVEROUTPUT ON
DECLARE
CURSOR c_course IS
SELECT course_no, description
FROM course
WHERE course_no < 120;
CURSOR c_enrollment(p_course_no IN course.course_no%TYPE)
IS
SELECT s.section_no section_no, count(*) count
FROM section s, enrollment e
WHERE s.course_no = p_course_no
AND s.section_id = e.section_id
GROUP BY s.section_no;
BEGIN
FOR r_course IN c_course LOOP
DBMS_OUTPUT.PUT_LINE
(r_course.course_no||' '|| r_course.description);
FOR r_enroll IN c_enrollment(r_course.course_no) LOOP
DBMS_OUTPUT.PUT_LINE
(Chr(9)||'Section: '||r_enroll.section_no||
' has an enrollment of: '||r_enroll.count);
END LOOP;
END LOOP;
END;
Write an anonymous PL/SQL block that finds all the courses that have at least one section that is at its maximum enrollment. If there are no courses that meet that criterion, then pick two courses and create that situation for each.
Answer: Your answer should look similar to the following:
SET SERVEROUTPUT ON
DECLARE
v_instid_min instructor.instructor_id%TYPE;
v_section_id_new section.section_id%TYPE;
v_snumber_recent section.section_no%TYPE := 0;
-- This cursor determines the courses that have at least
-- one section filled to capacity.
CURSOR c_filled IS
SELECT DISTINCT s.course_no
FROM section s
WHERE s.capacity = (SELECT COUNT(section_id)
FROM enrollment e
WHERE e.section_id = s.section_id);
BEGIN
FOR r_filled IN c_filled LOOP
-- For each course in this list, add another section.
-- First, determine the instructor who is teaching the least
-- number of courses. If there are more than one instructor
-- teaching the same number of minimum courses (e.g. if there are
-- three instructors teaching 1 course) use any of those
-- instructors.
SELECT instructor_id
INTO v_instid_min
FROM instructor
WHERE EXISTS (SELECT NULL
FROM section
WHERE section.instructor_id = instructor.instructor_id
GROUP BY instructor_id
HAVING COUNT(*) = (SELECT MIN(COUNT(*))
FROM section
WHERE instructor_id IS NOT NULL
GROUP BY instructor_id)
)
AND ROWNUM = 1;
-- Determine the section_id for the new section
-- Note that this method would not work in a multi-user
-- environment. A sequence should be used instead.
SELECT MAX(section_id) + 1
INTO v_section_id_new
FROM section;
-- Determine the section number for the new section
-- This only needs to be done in the real world if the
-- system specification calls for a sequence in a parent.
-- The sequence in parent here refers to the section_no
-- incrementing within the course_no, and not the section_no
-- incrementing within the section_id.
DECLARE
CURSOR c_snumber_in_parent IS
SELECT section_no
FROM section
WHERE course_no = r_filled.course_no
ORDER BY section_no;
BEGIN
-- Go from the lowest to the highest section_no and find
-- any gaps. If there are no gaps make the new section_no
-- equal to the highest current section_no + 1.
FOR r_snumber_in_parent IN c_snumber_in_parent LOOP
EXIT WHEN r_snumber_in_parent.section_no > v_snumber_recent + 1;
v_snumber_recent := r_snumber_in_parent.section_no + 1;
END LOOP;
-- At this point, v_snumber_recent will be equal either
-- to the value preceeding the gap or to the highest
-- section_no for that course.
END;
-- Do the insert.
INSERT INTO section
(section_id, course_no, section_no, instructor_id)
VALUES
(v_section_id_new, r_filled.course_no, v_snumber_recent,
v_instid_min);
-- COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('An error has occurred');
END;