c) | The following SQL statement generates a list of the open sections in courses that the student with the ID of 214 is not enrolled in. There are many different SQL statements that would produce the same result. Since various in-line views are required, it is important to examine the execution plan to determine which plan will produce the result with the least COST to the database. Run the SQL as follows to generate an SQL plan. -- ch21_1b.sql EXPLAIN PLAN FOR SELECT c.course_no course_no, c.description description, b.section_no section_no, s.section_id section_id, i.first_name first_name, i.last_name last_name FROM course c, instructor i, section s, (SELECT a.course_no course_no, MIN(a.section_no) section_no FROM (SELECT count(*) enrolled, se.CAPACITY capacity, se.course_no course_no, se.section_no section_no, e.section_id section_id FROM section se, enrollment e WHERE se.section_id = e.section_id AND e.student_id <> 214 GROUP BY se.CAPACITY, se.course_no, e.section_id, se.section_no HAVING count(*) < se.CAPACITY) a GROUP BY a.course_no) b WHERE c.course_no = b.course_no AND b.course_no = s.course_no AND s.section_no = b.section_no AND s.instructor_id = i.instructor_id; |