PL/SQL Question #12: You want to write a PL/SQL program that has a cursor that b
ID: 3773501 • Letter: P
Question
PL/SQL Question #12: You want to write a PL/SQL program that has a cursor that brings in all students with a CI major and a gpa of 3.2 or higher and numcredits > 45 and who have submitted their intent to graduate form.. Then you want to process the records in the cursor to determine the level of honors. Lets say 3.2 to 3.49 is Cum Laude, Magna Cum Laude is 3.5 to 3.79 and Summa Cum Laude is 3.8 or higher. You want to update the table (gradstatus) with that information. Student (studentid, studentname, majorcode, optioncode, numcredits, formsub, GPA, gradstatus)
Question #13: Run the program you wrote in question #12
Question #14: You want to write a PL/SQL program that uses two cursors. The first cursor should hold the records from the major table. The second cursor should hold the records from the student table. The program should process the students one majorcode at a time. It should count the number of students in the major, and the numcredits earned by students in that major. That information should be written to a separate table. Input tables: Major (majorcode, majorname, advisor) Student(studentid, studentname, majorcode, optioncode, numcredits, formsub, GPA, gradstatus) Output table: Statistics (majorcode, numstudents, numcredits)
Question #15: Run the program you wrote in question #14.
Explanation / Answer
12) & 13)
DECLARE
c_id student.studentid%type;
c_GPA student.GPA%type;
l_gradstatus varchar2(50);
CURSOR c_students is
SELECT studentid,gpa FROM student where majorcode='C1' and GPA > 3.2 and numcredits > 45 and formsub='Y';
BEGIN
OPEN c_students;
LOOP
FETCH c_students into c_id , c_GPA ;
EXIT WHEN c_students%notfound;
IF c_GPA >= 3.2 and c_GPA < 3.49 then
l_gradstatus:= 'Cum Laude';
ELSIF c_GPA >= 3.5 and c_GPA < 3.79 then
l_gradstatus:= 'Magna Cum Laude';
ELSE
l_gradstatus:= 'Summa Cum Laude';
END IF;
update student set gradstatus=l_gradstatus where studentid =c_id ;
END LOOP;
CLOSE c_students;
END;
/
14) & 15)
DECLARE
c_majorcode major.majorcode%type;
c_credits student.numcredits%type;
l_count number(5);
CURSOR c_major is
SELECT majorcode FROM major;
CURSOR c_students(in_majorcode varchar) is
SELECT sum(numcredits),count(*) from student where majorcode=in_majorcode group by majorcode ;
BEGIN
OPEN c_major;
LOOP
FETCH c_major into c_majorcode ;
EXIT WHEN c_major%notfound;
OPEN c_students(c_majorcode);
LOOP
FETCH c_students into c_credits,l_count ;
EXIT WHEN c_students%notfound;
insert into statistics values (c_majorcode,c_credits,l_count);
END LOOP;
END LOOP;
commit;
CLOSE c_students;
CLOSE c_major;
END;
/
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.