create a script named final-pkg.sql that contains PL/SQL code to create a packag

ID: 657136 • Letter: C

Question

create a script named final-pkg.sql that contains PL/SQL code to create a package named student_pkg with the following stored program unit.

1. function named CALCULATE_GPA that will calculate and return a student's grade point average. it should accept the ID for the student to be processed as a parameter and use only courses that have a grade entered in the enrollment table for the student ID. the calculation of the GPA is the sum of quality points(credit hours multiplied by grade value) for all graded courses that is divided by the sum of credit hours for all graded courses. The grade value for a letter grade can be determined by referencing the GRADE table. for example, a letter grade of A has a grade value of 4 in the GRADE table. the quality points for a 3 hours class with a grade of A would be 12(3 credit hours multiplied by a grade value of 4). do not hard code the grade values in your function. The GPA should be returned for any students that do not have any graded courses in the ENROLLMENT table.

2. function named CALCULATE_HOURS that will calculate and return the number of hours a student has completed. it should accept the ID for the student to be processed as a parameter. only courses that have a grade entered in the ENROLLMENT table should be counted in the calculation of hours for the student ID. a return value of 0 should be returned for any students that do not have any graded courses in the ENROLLMENT table.

Explanation / Answer

Package Creation:

CREATE or REPLACE PACKAGE student_pkg
   IS | AS

   FUNCTION CALCULATE_HOURS(Student_ID in number)

   RETURN number;

   FUNCTION CALCULATE_GPA(Student_ID in number)

   RETURN number;
END;
/

Package Description:

CREATE or REPLACE PACKAGE student_pkg
IS
   FUNCTION CALCULATE_HOURS(Student_ID in number) return number
       IS
       sum_hours number;
       no_of_graded_courses number;
       BEGIN
           SELECT sum(course_hours) INTO sum_hours, Count(course_grade) INTO no_of_graded_courses
                FROM enrollment WHERE
           studentid = Student_ID AND course_grade IS NOT NULL;

           IF ( no_of_graded_courses = 0) THEN

           sum_hours:=0;

END IF;

RETURN sum_hours;
END;

   FUNCTION CALCULATE_GPA(Student_ID in number) return number
       IS
       GPA number(5,2);
       Sum_of_credit_hours number;
       Sum_of_quality_points number;
       no_of_graded_courses number;
       BEGIN

           SELECT Count(course_grade) INTO no_of_graded_courses
                FROM enrollment WHERE
           studentid = Student_ID AND course_grade IS NOT NULL;

           select SUM(enrollment.credit_hours*Grade.grade_value)
           INTO Sum_of_quality_points
           from enrollment join Grade on enrollment.studentid = Student_ID and
           enrollment.grade = Grade.grade and
           course_grade IS NOT NULL and
            GROUP BY enrollment.credit_hours,Grade.grade_value;

           Sum_of_credit_hours:=CALCULATE_HOURS(Student_ID);

GPA:=Sum_of_quality_points / Sum_of_credit_hours;

           IF ( no_of_graded_courses == 0) THEN

           GPA:=0;

END IF;

           Return GPA;

       END;
END;
/

Navigate

create a script named final-pkg.sql that contains PL/SQL code to create a packag

create a script named final-trg.sql that contains the PL/SQL code to create a tr

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

create a script named final-pkg.sql that contains PL/SQL code to create a packag

Question

Explanation / Answer

Related Questions

Navigate