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;