Andre Long
CISP 350 Oracle
May 18, 2006
Chapter 4B PSC 

CASE#7
DECLARE
blank		VARCHAR2(1):= ' ';
studentID	number;
gradePoints	number;
gpa			number;
toppNum		number:=0;
bottomNum	number:=0;
topp		number;
bottom		number;


CURSOR	fred IS
SELECT	DISTINCT	s_last, s_first, enrollment.s_id
FROM	student, enrollment
WHERE	enrollment.s_id = student.s_id AND enrollment.grade IS NOT NULL;

pupil fred%ROWTYPE;

CURSOR	barney IS
SELECT	credits, grade
FROM	course,	enrollment, course_section
WHERE	course_section.course_id = course.course_id AND
		course_section.c_sec_id = enrollment.c_sec_id AND
		enrollment.grade IS NOT NULL	AND
		enrollment.s_id = studentID;
		
dino	barney%ROWTYPE;

BEGIN
OPEN	fred;
LOOP
	FETCH fred INTO pupil;
	EXIT WHEN fred%NOTFOUND;
	studentID:= pupil.s_id;
	DBMS_OUTPUT.PUT_LINE('Student:' || blank || pupil.s_first || blank || pupil.s_last);
	DBMS_OUTPUT.PUT_LINE('Student ID = ' || blank || pupil.s_id);
	OPEN	barney;
			LOOP
				FETCH barney INTO dino;
				EXIT WHEN barney%NOTFOUND;
				if dino.grade = 'A' THEN
				gradePoints:= 4;
				elsif dino.grade = 'B' THEN
				gradePoints:= 3;
				elsif dino.grade = 'C' THEN
				gradePoints:= 2;
				elsif dino.grade = 'D' THEN
				gradePoints:= 1;
				else
				gradePoints:=0;
				end if;
				topp:=(dino.credits * gradePoints);
				bottom:=(dino.credits);
				toppNum:= toppNum + topp;
				bottomNum:= bottomNum + bottom;
				gpa:= toppNum / bottomNum;
				END LOOP;
	DBMS_OUTPUT.PUT_LINE('GPA: ' || TO_CHAR(gpa,'9.99'));
	CLOSE barney;
	
END LOOP;
CLOSE fred;
END;