week 5 weds

gistfile1.txt

DROP TABLE student CASCADE CONSTRAINTS ;
DROP TABLE enrollment CASCADE CONSTRAINTS;
-- Did not see any data so gonna make some big assumptions
CREATE TABLE student(
student_name VARCHAR2(40),
student_id NUMBER(10)
);

CREATE TABLE enrollment(
    course_id NUMBER,
    course_name VARCHAR2(30),
    grade VARCHAR2(2),
    student_id NUMBER(10)
);

INSERT INTO student (student_name,student_id) values('bob builder',1);
INSERT INTO student (student_name,student_id) values('lego man',2);
INSERT INTO student (student_name,student_id) values('harry potter',3);
INSERT INTO student (student_name,student_id) values('tiger shark',4);

INSERT INTO enrollment (course_id,course_name,grade,student_id) values(1,'CS BALK', 'a+',1);
INSERT INTO enrollment (course_id,course_name,grade,student_id) values(1,'CS BALK', 'a+',2);
INSERT INTO enrollment (course_id,course_name,grade,student_id) values(1,'CS BALK', 'a+',3);
INSERT INTO enrollment (course_id,course_name,grade,student_id) values(1,'CS BALK', 'a+',4);

INSERT INTO enrollment (course_id,course_name,grade,student_id) values(2,'CS ZZZ', 'c+',1);
INSERT INTO enrollment (course_id,course_name,grade,student_id) values(2,'CS ZZZ', 'c+',3);

INSERT INTO enrollment (course_id,course_name,grade,student_id) values(3,'CS DBA', 'b+',4);
INSERT INTO enrollment (course_id,course_name,grade,student_id) values(3,'CS DBA', 'a+',2);

INSERT INTO enrollment (course_id,course_name,grade,student_id) values(4,'CS COFFEE', 'b+',1);
INSERT INTO enrollment (course_id,course_name,grade,student_id) values(4,'CS COFFEE', 'b+',2);

SELECT * FROM student;
SELECT * FROM enrollment;
SET SERVEROUTPUT ON;
CREATE OR REPLACE PACKAGE calcGPA IS
    --Records
    TYPE student_rec IS RECORD (
        student_name student.student_name%type,
        student_id student.student_id%type
    );
    TYPE enrollment_rec IS RECORD (
        course_id enrollment.course_id%type,
        course_name enrollment.course_name%type,
        grade enrollment.grade%type,
        student_id enrollment.student_id%type   
    );
    
END calcGPA;
/

CREATE OR REPLACE PACKAGE BODY calcGPA IS
 PROCEDURE calculate_gpa(student_rec IN student%rowtype)
    IS
        cursor cursor_student IS SELECT student_name,student_id FROM student;
        cursor cursor_enrollment IS SELECT course_id,course_name,grade,student_id FROM enrollment;

    BEGIN
         dbms_output.put_line('StudentName: ' || student_rec.student_name);
    END;
    
    PROCEDURE process_students_gpa
    IS
        cursor cursor_student IS SELECT student_name,student_id FROM student;
        cursor cursor_enrollment IS SELECT course_id,course_name,grade,student_id FROM enrollment;
        sturec student_rec;
    BEGIN
        FOR cur_student IN cursor_student
        LOOP
            sturec.student_name := cur_student.student_name;
            sturec.student_id := cur_student.student_id;
            calculate_gpa(sturec);
        END LOOP;
    END;

    PROCEDURE GPASTART IS
    BEGIN
        process_students_gpa();
    END;
/*the first procedure reads in the students information and puts it into a programmer
defined record type, validating the student type code

It then passes that record to the second procedure as a parameter and the second
procedure calculates and prints out the students’ GPA for that term.*/
END calcGPA;
/

BEGIN
    CALCGPA.GPASTART();
END;