Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How can I calculate a GPA from my tables?

844620Oct 15 2011 — edited Oct 15 2011
I have a DB with the following tables:

STUDENT
CREATE TABLE student
(s_id VARCHAR2(6),
s_last VARCHAR2(30),
s_first VARCHAR2(30),
s_mi CHAR(1),
s_address VARCHAR2(25),
s_city VARCHAR2(20),
s_state CHAR(2),
s_zip VARCHAR2(10),
s_phone VARCHAR2(10),
s_class CHAR(2),
s_dob DATE,
s_pin NUMBER(4),
f_id NUMBER(6),
time_enrolled INTERVAL YEAR TO MONTH,
CONSTRAINT student_s_id_pk PRIMARY KEY (s_id),
CONSTRAINT student_f_id_fk FOREIGN KEY (f_id) REFERENCES faculty(f_id));
ENROLLMENT
CREATE TABLE ENROLLMENT
(s_id VARCHAR2(6),
c_sec_id NUMBER(6),
grade CHAR(1),
CONSTRAINT enrollment_pk PRIMARY KEY (s_id, c_sec_id),
CONSTRAINT enrollment_sid_fk FOREIGN KEY (s_id) REFERENCES student(s_id),
CONSTRAINT enrollment_csecid_fk FOREIGN KEY (c_sec_id) REFERENCES course_section (c_sec_id));
COURSE SECTION
CREATE TABLE COURSE_SECTION
(c_sec_id NUMBER(6),
course_no VARCHAR2(7) CONSTRAINT course_section_courseid_nn NOT NULL,
term_id NUMBER(6) CONSTRAINT course_section_termid_nn NOT NULL,
sec_num NUMBER(2) CONSTRAINT course_section_secnum_nn NOT NULL,
f_id NUMBER(6),
c_sec_day VARCHAR2(10),
c_sec_time DATE,
c_sec_duration INTERVAL DAY TO SECOND,
loc_id NUMBER(6),
max_enrl NUMBER(4) CONSTRAINT course_section_maxenrl_nn NOT NULL,
CONSTRAINT course_section_csec_id_pk PRIMARY KEY (c_sec_id),
CONSTRAINT course_section_cid_fk FOREIGN KEY (course_no) REFERENCES course(course_no), 	
CONSTRAINT course_section_loc_id_fk FOREIGN KEY (loc_id) REFERENCES location(loc_id),
CONSTRAINT course_section_termid_fk FOREIGN KEY (term_id) REFERENCES term(term_id),
CONSTRAINT course_section_fid_fk FOREIGN KEY (f_id) REFERENCES faculty(f_id));
COURSE
CREATE TABLE COURSE
(course_no VARCHAR2(7),
course_name VARCHAR2(25),
credits NUMBER(2),
CONSTRAINT course_course_id_pk PRIMARY KEY(course_no));
I am trying to create a PL/SQL program that will show me a student, their ID and their GPA. I was able to create this so far but am having trouble trying to grab other fields and making a calculation. I think I should create a second explicit cursor that is processed within the loop for the first cursor the retrieves the course information for students and calculates each student's GPA. However I am having trouble doing this. Any help would be appreciated.

Thank You
DECLARE
    current_student_firstName	student.s_first%TYPE;
	current_student_lastName	student.s_last%TYPE;
	current_student_id			student.s_id%TYPE;
	current_student_grade			NUMBER(10) := 0;
	CURSOR student_cursor IS
		SELECT *
		FROM student
		WHERE s_id IN (SELECT s_id
							FROM enrollment
							WHERE grade IS NOT NULL);
	student_info	student_cursor%ROWTYPE;
	
	CURSOR student_grade IS
	    SELECT student.s_id, enrollment.grade
		FROM student INNER JOIN enrollment
			ON student.s_id = enrollment.s_id
		WHERE enrollment.grade IS NOT NULL;
	grade_info		student_grade%ROWTYPE;
BEGIN
	OPEN student_cursor;
	LOOP
		FETCH student_cursor INTO student_info;
		EXIT WHEN student_cursor%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE('***********************************');
		DBMS_OUTPUT.PUT_LINE('Student: ' || student_info.s_first || ' ' || student_info.s_last);
		DBMS_OUTPUT.PUT_LINE('Student ID: ' || student_info.s_id);
	END LOOP;
	CLOSE student_cursor;
END;
This post has been answered by Frank Kulash on Oct 15 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 12 2011
Added on Oct 15 2011
2 comments
1,867 views