Lab 9.2 Exercise Answers

9.2.1 Answers

a)

Write a PL/SQL block that will reduce the cost of all courses by 5% for courses having an enrollment of eight students or more. Use a cursor FOR loop that will update the course table.

A1:

Answer: Your block should look like this: -- ch09_10a.sql DECLARE CURSOR c_group_discount IS SELECT DISTINCT s.course_no FROM section s, enrollment e WHERE s.section_id = e.section_id GROUP BY s.course_no, e.section_id, s.section_id HAVING COUNT(*)>=8; BEGIN FOR r_group_discount IN c_group_discount LOOP UPDATE course SET cost = cost * .95 WHERE course_no = r_group_discount.course_no; END LOOP; COMMIT; END; The cursor c_group_discount is declared in the declarative section. The proper SQL is used to generate the select statement to answer the question given. The cursor is processed in a FOR loop ”in each iteration of the loop the SQL update statement will be executed. This means it does not have to be opened, fetched , and closed. Also, it means that a cursor attribute does not have to be used to create an exit condition for the loop that is processing the cursor.

9.2.2 Answers

a)

Write a PL/SQL block with two cursor FOR loops . The parent cursor will call the student_id, first_name , and last_name from the student table for students with a student_id less than 110 and output one line with this information. For each student, the child cursor will loop through all the courses that the student is enrolled in, outputting the course_no and the description.

A1:

Answer: Your block should look be similar to this: -- ch09_11a.sql DECLARE v_sid student.student_id%TYPE; CURSOR c_student IS SELECT student_id, first_name, last_name FROM student WHERE student_id < 110; CURSOR c_course IS SELECT c.course_no, c.description FROM course c, section s, enrollment e WHERE c.course_no = s.course_no AND s.section_id = e.section_id AND e.student_id = v_sid; BEGIN FOR r_student IN c_student LOOP v_sid := r_student.student_id; DBMS_OUTPUT.PUT_LINE(chr(10)); DBMS_OUTPUT.PUT_LINE(' The Student ' r_student.student_id' ' r_student.first_name' ' r_student.last_name); DBMS_OUTPUT.PUT_LINE(' is enrolled in the ' 'following courses: '); FOR r_course IN c_course LOOP DBMS_OUTPUT.PUT_LINE(r_course.course_no ' 'r_course.description); END LOOP; END LOOP; END; The select statements for the two cursors are defined in the declarative section of the PL/SQL block. A variable to store the student_id from the parent cursor is also declared. The course cursor is the child cursor, and, since it makes use of the variable v_sid , the variable must be declared first. Both cursors are processed with a FOR loop, which eliminates the need for OPEN , FETCH, and CLOSE. When the parent student loop is processed, the first step is to initialize the variable v_sid , and the value is then used when the child loop is processed. DBMS_OUTPUT is used so that display is generated for each cursor loop. The parent cursor will display the student name once, and the child cursor will display the name of each course in which the student is enrolled.

b)	Before you run the preceding code, analyze what it is doing and determine what you think the result would be. Explain what is happening in each phase of the PL/SQL block and what is happening to the variables as control is passing through parent and child cursor.
A1:	Answer: The declaration section contains a declaration for two variables. The first is v_amount of the datatype matching that of the cost in the course table; the second is the v_instructor_id of the datatype matching the instructor_id in the instructor table. There are also two declarations for two cursors. The first is for c_inst , which is comprised of the first_name , last_name , and instructor_id for an instructor from the instructor table. The second cursor, c_cost , will produce a result set of the cost of the course taken for each student enrolled in a course by the instructor that matches the variable v_instructor_id . These two cursors will be run in nested fashion. First, the cursor c_inst is opened in a FOR loop. The value of the variable v_instructor_id is initialized to match the instructor_id of the current row of the c_inst cursor. The variable v_amount is initialized to 0. The second cursor is open within the loop for the first cursor. This means that for each iteration of the cursor c_inst , the second cursor will be opened, fetched, and closed. The second cursor will loop through all the cost generated by each student enrolled in a course for the instructor, which is current of the c_inst cursor. Each time the nest loop iterates, it will increase the variable v_amount by adding the current cost in the c_cost loop. Prior to opening the c_cost loop, there is a DBMS_OUTPUT to display the instructor name. After the c_cost cursor loop is closed, it will display the total amount generated by all the enrollments of the current instructor.
c)	Run the code and see what the result is. Is it what you expected? Explain the difference.
A2:	Answer: The result set would be as follows : Generated by instructor Fernand Hanks $16,915 Generated by instructor Tom Wojick $18,504 Generated by instructor Nina Schorin $30,137 Generated by instructor Gary Pertez $24,044 Generated by instructor Anita Morris $13,389 Generated by instructor Todd Smythe $14,940 Generated by instructor Rick Chow $0 Generated by instructor Charles Lowry $12,175 Generated by instructor Marilyn Frantzen $13,224 PL/SQL procedure successfully completed. In this example, the nested cursor is tied to the current row of the outer cursor by means of the variable v_instructor_id . A more common way of doing this is to pass a parameter to a cursor. You will learn more about how to achieve this in Chapter 15, " Advanced Cursors ."