Oracle PL/SQL by Example, Fifth Edition (2015)

In this lab, you will make use of previously introduced concepts to both extend the packages you have created and create new ones. Only by completing extensive exercises will you become more comfortable with programming in PL/SQL. It is very important when writing your PL/SQL code that you carefully take into consideration all aspects of the business requirements. A good rule of thumb is to think ahead and write your code in reusable components so that it will be easy to extend and maintain that PL/SQL code.

Extending the Package with Additional Procedures

This section provides more examples of writing packages by working through a complex package with various complex functions and procedures. It is always a best practice to build up large packages one step at a time and to test each section you create to ensure that it works properly and does not contain any syntax errors. The following set of examples show you how to build a package step by step.

Creating the Manage_Grades Package Specification

The following script creates a new package specification called manage_grades. This package will perform a number of calculations on grades and will need two package cursors. The first step is to create a cursor called c_grade_Type that has an IN parameter of a section ID and provides a list of all grade types for a given section; this information is necessary to calculate a student’s grade in that section. The return items from the cursor will be (1) the grade type code; (2) the number of that grade type for this section; (3) the percentage of the final grade; and (4) the drop lowest indicator (a flag).

The first thing you should always do when building a package cursor is to write the SELECT statement and test it on a known result set. In other words, you hard-code a value for the variable—for example, a student_id and section_id—and then replace the hard-coded values with the appropriate variables. You continue to build the package one step at a time in this manner. Try to build each component of the package with the smallest testable unit of code. Once that unit of code is returning the correct result and the syntax is free of errors, you can then turn to building the next unit.

The following example contains only the SQL SELECT statement. You are well advised to write the SQL SELECT statement first and then test it for a known value. In this case, the student_id is 145 and the section_id is 106.

For Example ch21_12.sql

SELECT GRADE_TYPE_CODE,
NUMBER_PER_SECTION,
PERCENT_OF_FINAL_GRADE,
DROP_LOWEST
FROM grade_Type_weight
WHERE section_id = 106
AND section_id IN (SELECT section_id
FROM grade
WHERE student_id = 145)

This SELECT statement is now put into the package:

For Example ch21_13.sql

CREATE OR REPLACE PACKAGE MANAGE_GRADES AS
-- Cursor to loop through all grade types for a given section
CURSOR c_grade_type
(pc_section_id section.section_id%TYPE,
PC_student_ID student.student_id%TYPE)
IS
SELECT GRADE_TYPE_CODE,
NUMBER_PER_SECTION,
PERCENT_OF_FINAL_GRADE,
DROP_LOWEST
FROM grade_Type_weight
WHERE section_id = pc_section_id
AND section_id IN (SELECT section_id
FROM grade
WHERE student_id = pc_student_id);
END MANAGE_GRADES;

Creating the c_grade Cursor

The next example shows the expansion of the manage_grades package through the addition of a section cursor called c_grades. This cursor will take a grade type code, a student ID, and a section ID and return all the grades for that student for that section of that grade type. For example, if Alice was enrolled in the Introduction to Java section, this cursor could be used to gather all of her quiz grades.

For Example ch21_14.sql

CREATE OR REPLACE PACKAGE MANAGE_GRADES AS
-- Cursor to loop through all grade types for a given section.
CURSOR c_grade_type
(pc_section_id section.section_id%TYPE,
PC_student_ID student.student_id%TYPE)
IS
SELECT GRADE_TYPE_CODE,
NUMBER_PER_SECTION,
PERCENT_OF_FINAL_GRADE,
DROP_LOWEST
FROM grade_Type_weight
WHERE section_id = pc_section_id
AND section_id IN (SELECT section_id
FROM grade
WHERE student_id = pc_student_id);
-- Cursor to loop through all grades for a given student
-- in a given section.
CURSOR c_grades
(p_grade_type_code
grade_Type_weight.grade_type_code%TYPE,
pc_student_id student.student_id%TYPE,
pc_section_id section.section_id%TYPE) IS
SELECT grade_type_code,grade_code_occurrence,
numeric_grade
FROM grade
WHERE student_id = pc_student_id
AND section_id = pc_section_id
AND grade_type_code = p_grade_type_code;
END MANAGE_GRADES;

Creating the Function final_grade

The next step is to add a function to this package specification called final_grade. This function will have two IN parameters: the student ID and the section ID. It will return a number—that student’s final grade in that section—plus an exit code. The reason you add an exit code instead of raise exceptions is because this approach makes the procedure more flexible and allows the calling program to choose how to proceed depending on the specific error code generated.

For Example ch21_15.sql

CREATE OR REPLACE PACKAGE MANAGE_GRADES AS
-- Cursor to loop through all grade types for a given section.
CURSOR c_grade_type
(pc_section_id section.section_id%TYPE,
PC_student_ID student.student_id%TYPE)
IS
SELECT GRADE_TYPE_CODE,
NUMBER_PER_SECTION,
PERCENT_OF_FINAL_GRADE,
DROP_LOWEST
FROM grade_Type_weight
WHERE section_id = pc_section_id
AND section_id IN (SELECT section_id
FROM grade
WHERE student_id = pc_student_id);
-- Cursor to loop through all grades for a given student
-- in a given section.
CURSOR c_grades
(p_grade_type_code
grade_Type_weight.grade_type_code%TYPE,
pc_student_id student.student_id%TYPE,
pc_section_id section.section_id%TYPE) IS
SELECT grade_type_code,grade_code_occurrence,
numeric_grade
FROM grade
WHERE student_id = pc_student_id
AND section_id = pc_section_id
AND grade_type_code = p_grade_type_code;
-- Function to calculate a student's final grade
-- in one section
Procedure final_grade
(P_student_id IN student.student_id%type,
P_section_id IN section.section_id%TYPE,
P_Final_grade OUT enrollment.final_grade%TYPE,
P_Exit_Code OUT CHAR);
END MANAGE_GRADES;

The next step is to add the function to the package body. To perform this calculation, you will need a number of variables to hold values as the calculation is carried out.

This exercise is also a very good review of the data relationships among the student tables. Before you read through the next step, review Appendix B, which has an entity–relationship diagram (ERD) of the STUDENT schema and descriptions of the tables and their columns.

When calculating the final grade, there are many things that you must keep in mind:

Each student is enrolled in a course, and this information is captured in the enrollment table.

The table holds the final grade only for each student enrolled in one section.

Each section has its own set of elements that are evaluated to calculate the final grade.

All grades for these elements (which have been entered, meaning there is no NULL value in the database) are in the grade table.

Every grade has a grade type code. These codes represent the grade type. For example, the grade type QZ stands for quiz. The description of each GRADE_TYPE comes from the GRADE_TYPE table.

The GRADE_TYPE_WEIGHT table holds key information for this calculation. There is one entry in this table for each grade type that is utilized in a given section (not all grade types exist for each section).

In the GRADE_TYPE_WEIGHT table, the NUMBER_PER_SECTION column lists how many times a grade type should be entered to compute the final grade for a particular student in a particular section of a particular course. This helps you determine whether all grades for a given grade type have been entered and whether too many grades for a given grade type have been entered.

You must take into consideration the drop_lowest flag. The drop_lowest flag can hold a value of Y or N. If the drop lowest flag is Y [Y = Yes, N = No], then you must drop the lowest grade from the grade type when calculating the final grade. ThePERCENT_OF_FINAL_GRADE column refers to all the grades for a given grade type. If the homework element represents 20 percent of the final grade, and there are five homework assignments and a drop_lowest flag, then each remaining homework is worth 5 percent. When calculating the final grade, you divide the PERCENT_OF_FINAL_GRADE by the NUMBER_PER_SECTION (note that would be NUMBER_PER_SECTION – 1 if drop_lowest = Y).

Exit codes should be defined as one of the following five:

S = Success, the final grade has been computed. If the grade cannot be computed, then the final grade will be NULL and the exit code will be one of the other four options.

I = Incomplete; not all the required grades have been entered for this student in this section.

T = Too many grades exist for this student. For example, there should be only four homework grades, but instead there are six.

N = No grades have been entered for this student in this section.

E = There was a general computation error (exception When_others). This kind of exit code allows the procedure to compute final grades when it can; if an Oracle error is somehow raised by some of them, the calling program can still proceed with the grades that have been computed.

To calculate the final grade, you will need a number of variables to hold temporary values during the calculation. Initially the code will create all the variables for the procedure final_grade, but then it will leave the main block with just the statement NULL; this allows you to compile the procedure and check all of the syntax for the variable declaration one step at a time.

The student_id, section_id, and grade_type_code will be values carried from one part of the program to another—which is why you created a variable for each of them. Each instance of a grade will be computed to determine what percentage of the final grade it represents. A counter is needed while processing each individual grade to ensure there are enough grades for the given grade count. A lowest grade variable holds each grade during the examination to see whether it is the lowest. In the end, once the lowest grade is known for a given grade type, it can be removed from the final grade. Additionally, two variables are used as row counters to determine whether the cursor was opened.

The next example shows the package body in a stub format; that is, this example includes all of the necessary variables but no actual processing code has been written. The reason you start with this step when writing the package body is to ensure that all of the syntax is correct. Once this stub compiles without errors, you can then work on the rest of the code for the package body.

For Example ch21_16.sql

CREATE OR REPLACE PACKAGE BODY MANAGE_GRADES AS
Procedure final_grade
(P_student_id IN student.student_id%type,
P_section_id IN section.section_id%TYPE,
P_Final_grade OUT enrollment.final_grade%TYPE,
P_Exit_Code OUT CHAR)
IS
v_student_id student.student_id%TYPE;
v_section_id section.section_id%TYPE;
v_grade_type_code grade_type_weight.grade_type_code%TYPE;
v_grade_percent NUMBER;
v_final_grade NUMBER;
v_grade_count NUMBER;
v_lowest_grade NUMBER;
v_exit_code CHAR(1) := 'S';
v_no_rows1 CHAR(1) := 'N';
v_no_rows2 CHAR(1) := 'N';
e_no_grade EXCEPTION;
BEGIN
NULL;
END;
END MANAGE_GRADES;

The full package body is provided in the next example. Comments have been placed inside the code to explain what is being done at each step. It is a good idea to include comments within your code to help the next person who has to make changes to the package.

For Example ch21_17.sql

CREATE OR REPLACE PACKAGE BODY MANAGE_GRADES AS
Procedure final_grade
(P_student_id IN student.student_id%type,
P_section_id IN section.section_id%TYPE,
P_Final_grade OUT enrollment.final_grade%TYPE,
P_Exit_Code OUT CHAR)
IS
v_student_id student.student_id%TYPE;
v_section_id section.section_id%TYPE;
v_grade_type_code grade_type_weight.grade_type_code%TYPE;
v_grade_percent NUMBER;
v_final_grade NUMBER;
v_grade_count NUMBER;
v_lowest_grade NUMBER;
v_exit_code CHAR(1) := 'S';
v_no_rows1 CHAR(1) := 'N';
v_no_rows2 CHAR(1) := 'N';
e_no_grade EXCEPTION;
BEGIN
v_section_id := p_section_id;
v_student_id := p_student_id;
-- Start loop of grade types for the section.
FOR r_grade in c_grade_type(v_section_id, v_student_id)
LOOP
-- Since cursor is open it has a result
-- set; change indicator.
v_no_rows1 := 'Y';
-- To hold the number of grades per section,
-- reset to 0 before detailed cursor loops.
v_grade_count := 0;
v_grade_type_code := r_grade.GRADE_TYPE_CODE;
-- Variable to hold the lowest grade.
-- 500 will not be the lowest grade.
v_lowest_grade := 500;
-- Determine what to multiply a grade by to
-- compute final grade; must take into consideration
-- if the drop lowest grade indicator is Y.
SELECT (r_grade.percent_of_final_grade /
DECODE(r_grade.drop_lowest, 'Y',
(r_grade.number_per_section - 1),
r_grade.number_per_section
))* 0.01
INTO v_grade_percent
FROM dual;
-- Open cursor of detailed grade for a student in a
-- given section.
FOR r_detail in c_grades(v_grade_type_code,
v_student_id, v_section_id) LOOP
-- Since cursor is open it has a result
-- set; change indicator.
v_no_rows2 := 'Y';
v_grade_count := v_grade_count + 1;
-- Handle the situation where there are more
-- entries for grades of a given grade type
-- than there should be for that section.
If v_grade_count > r_grade.number_per_section THEN
v_exit_code := 'T';
raise e_no_grade;
END IF;
-- If drop lowest flag is Y determine which is lowest
-- grade to drop.
IF r_grade.drop_lowest = 'Y' THEN
IF nvl(v_lowest_grade, 0) >=
r_detail.numeric_grade
THEN
v_lowest_grade := r_detail.numeric_grade;
END IF;
END IF;
-- Increment the final grade with percentage of current
-- grade in the detail loop.
v_final_grade := nvl(v_final_grade, 0) +
(r_detail.numeric_grade * v_grade_percent);
END LOOP;
-- Once detailed loop is finished, if the number of grades
-- for a given student for a given grade type and section
-- is less than the required amount, raise an exception.
IF v_grade_count < r_grade.NUMBER_PER_SECTION THEN
v_exit_code := 'I';
raise e_no_grade;
END IF;
-- If the drop lowest flag was Y, then you need to take
-- the lowest grade out of the final grade; it was not
-- known when it was added which was the lowest grade
-- to drop until all grades were examined.
IF r_grade.drop_lowest = 'Y' THEN
v_final_grade := nvl(v_final_grade, 0) -
(v_lowest_grade * v_grade_percent);
END IF;
END LOOP;
-- If either cursor had no rows then there is an error.
IF v_no_rows1 = 'N' OR v_no_rows2 = 'N' THEN
v_exit_code := 'N';
raise e_no_grade;
END IF;
P_final_grade := v_final_grade;
P_exit_code := v_exit_code;
EXCEPTION
WHEN e_no_grade THEN
P_final_grade := null;
P_exit_code := v_exit_code;
WHEN OTHERS THEN
P_final_grade := null;
P_exit_code := 'E';
END final_grade;
END MANAGE_GRADES;

The following example is an anonymous block to test the final_grade procedure. The block asks for a student_id and a section_id and returns the final grade and an exit code.

It is often a good idea to review the parameter order for the procedure before you write the anonymous block to run the code. In SQL*Plus, this can be done by running a describe command on a procedure.

SQL> desc manage_grades
PROCEDURE FINAL_GRADE

Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_STUDENT_ID NUMBER(8) IN
P_SECTION_ID NUMBER(8) IN
P_FINAL_GRADE NUMBER(3) OUT
P_EXIT_CODE CHAR OUT

In SQL Developer, you can expand the node for packages and hover your cursor over the procedure to obtain more details. By doing so, you can see both what has been declared in the package header and what is compiled in the package body. This is illustrated in Figure 21.1.

Figure 21.1 Manage_Grades Package as Seen in SQL Developer

The following example is an anonymous block that can be used to run the package manage_grades.

For Example ch21_18.sql

SET SERVEROUTPUT ON

DECLARE
v_student_id student.student_id%TYPE := &sv_student_id;
v_section_id section.section_id%TYPE := &sv_section_id;
v_final_grade enrollment.final_grade%TYPE;
v_exit_code CHAR;
BEGIN
manage_grades.final_grade(v_student_id, v_section_id,
v_final_grade, v_exit_code);
DBMS_OUTPUT.PUT_LINE('The Final Grade is '||v_final_grade);
DBMS_OUTPUT.PUT_LINE('The Exit Code is '||v_exit_code);
END;

If you were to run this script for a student_id of 102 and a section_id of 89, you would get the following result in SQL*Plus. In SQL Developer, you would see the full code as you ran it and with the variables substituted for 102 and 89. Both outputs have the same final lines that appear after the anonymous block completes.

Enter value for sv_student_id: 102
old 2: v_student_id student.student_id%TYPE := &sv_student_id;
new 2: v_student_id student.student_id%TYPE := 102;
Enter value for sv_section_id: 86
old 3: v_section_id section.section_id%TYPE := &sv_section_id;
new 3: v_section_id section.section_id%TYPE := 86;
The Final Grade is 89
The Exit Code is S
PL/SQL procedure successfully completed.

The next step is to add a function to the manage_grades package specification called median_grade that takes in a course number (p_cource_number), a section number (p_section_number), and a grade type (p_grade_type) and returns awork_grade.grade%TYPE. Cursors that will be used by this function also need to be added as well as any types that will be required by the function.

For Example ch21_19.sql

CREATE OR REPLACE PACKAGE MANAGE_GRADES AS
-- Cursor to loop through all grade types for a given section.
CURSOR c_grade_type
(pc_section_id section.section_id%TYPE,
PC_student_ID student.student_id%TYPE)
IS
SELECT GRADE_TYPE_CODE,
NUMBER_PER_SECTION,
PERCENT_OF_FINAL_GRADE,
DROP_LOWEST
FROM grade_Type_weight
WHERE section_id = pc_section_id
AND section_id IN (SELECT section_id
FROM grade
WHERE student_id = pc_student_id);
-- Cursor to loop through all grades for a given student
-- in a given section.
CURSOR c_grades
(p_grade_type_code
grade_Type_weight.grade_type_code%TYPE,
pc_student_id student.student_id%TYPE,
pc_section_id section.section_id%TYPE) IS
SELECT grade_type_code,grade_code_occurrence,
numeric_grade
FROM grade
WHERE student_id = pc_student_id
AND section_id = pc_section_id
AND grade_type_code = p_grade_type_code;
-- Function to calculate a student's final grade
-- in one section.
Procedure final_grade
(P_student_id IN student.student_id%type,
P_section_id IN section.section_id%TYPE,
P_Final_grade OUT enrollment.final_grade%TYPE,
P_Exit_Code OUT CHAR);
-- ---------------------------------------------------------
-- Function to calculate the median grade .
FUNCTION median_grade
(p_course_number section.course_no%TYPE,
p_section_number section.section_no%TYPE,
p_grade_type grade.grade_type_code%TYPE)
RETURN grade.numeric_grade%TYPE;
CURSOR c_work_grade
(p_course_no section.course_no%TYPE,
p_section_no section.section_no%TYPE,
p_grade_type_code grade.grade_type_code%TYPE
)IS
SELECT distinct numeric_grade
FROM grade
WHERE section_id = (SELECT section_id
FROM section
WHERE course_no= p_course_no
AND section_no = p_section_no)
AND grade_type_code = p_grade_type_code
ORDER BY numeric_grade;
TYPE t_grade_type IS TABLE OF c_work_grade%ROWTYPE
INDEX BY BINARY_INTEGER;
t_grade t_grade_type;
END MANAGE_GRADES;

The next step is to add a function to the manage_grades package specification called median_grade that takes in a course number (p_cnumber), a section number (p_snumber), and a grade type (p_grade_type). This function will return the median grade (work_grade.grade%TYPE data type) based on those three components. For example, one might use this function to answer the question, “What is the median grade of homework assignments in Introduction to Java section 2?” A true median can contain two values. Because this function can return only one value, if the median is made of two values, then the function will return the average of the two.

For Example ch21_20.sql

CREATE OR REPLACE PACKAGE MANAGE_GRADES AS
CREATE OR REPLACE PACKAGE BODY MANAGE_GRADES AS
Procedure final_grade
(P_student_id IN student.student_id%type,
P_section_id IN section.section_id%TYPE,
P_Final_grade OUT enrollment.final_grade%TYPE,
P_Exit_Code OUT CHAR)
IS
v_student_id student.student_id%TYPE;
v_section_id section.section_id%TYPE;
v_grade_type_code grade_type_weight.grade_type_code%TYPE;
v_grade_percent NUMBER;
v_final_grade NUMBER;
v_grade_count NUMBER;
v_lowest_grade NUMBER;
v_exit_code CHAR(1) := 'S';
-- Next two variables are used to calculate whether a cursor
-- has no result set.
v_no_rows1 CHAR(1) := 'N';
v_no_rows2 CHAR(1) := 'N';
e_no_grade EXCEPTION;
BEGIN
v_section_id := p_section_id;
v_student_id := p_student_id;
-- Start loop of grade types for the section.
FOR r_grade in c_grade_type(v_section_id, v_student_id)
LOOP
-- Since cursor is open it has a result
-- set; change indicator.
v_no_rows1 := 'Y';
-- To hold the number of grades per section,
-- reset to 0 before detailed cursor loops.
v_grade_count := 0;
v_grade_type_code := r_grade.GRADE_TYPE_CODE;
-- Variable to hold the lowest grade.
-- 500 will not be the lowest grade.
v_lowest_grade := 500;
-- Determine what to multiply a grade by to
-- compute final grade; must take into consideration
-- if the drop lowest grade indicator is Y.
SELECT (r_grade.percent_of_final_grade /
DECODE(r_grade.drop_lowest, 'Y',
(r_grade.number_per_section - 1),
r_grade.number_per_section
))* 0.01
INTO v_grade_percent
FROM dual;
-- Open cursor of detailed grade for a student in a
-- given section.
FOR r_detail in c_grades(v_grade_type_code,
v_student_id, v_section_id) LOOP
-- Since cursor is open it has a result
-- set; change indicator.
v_no_rows2 := 'Y';
v_grade_count := v_grade_count + 1;
-- Handle the situation where there are more
-- entries for grades of a given grade type
-- than there should be for that section.
If v_grade_count > r_grade.number_per_section THEN
v_exit_code := 'T';
raise e_no_grade;
END IF;
-- If drop lowest flag is Y determine which is lowest
-- grade to drop.
IF r_grade.drop_lowest = 'Y' THEN
IF nvl(v_lowest_grade, 0) >=
r_detail.numeric_grade
THEN
v_lowest_grade := r_detail.numeric_grade;
END IF;
END IF;
-- Increment the final grade with percentage of current
-- grade in the detail loop.
v_final_grade := nvl(v_final_grade, 0) +
(r_detail.numeric_grade * v_grade_percent);
END LOOP;
-- Once detailed loop is finished, if the number of grades
-- for a given student for a given grade type and section
-- is less than the required amount, raise an exception.
IF v_grade_count < r_grade.NUMBER_PER_SECTION THEN
v_exit_code := 'I';
raise e_no_grade;
END IF;
-- If the drop lowest flag was Y then you need to take
-- the lowest grade out of the final grade. It was not
-- known when it was added which was the lowest grade
-- to drop until all grades were examined.
IF r_grade.drop_lowest = 'Y' THEN
v_final_grade := nvl(v_final_grade, 0) -
(v_lowest_grade * v_grade_percent);
END IF;
END LOOP;
-- If either cursor had no rows then there is an error.
IF v_no_rows1 = 'N' OR v_no_rows2 = 'N' THEN
v_exit_code := 'N';
raise e_no_grade;
END IF;
P_final_grade := v_final_grade;
P_exit_code := v_exit_code;
EXCEPTION
WHEN e_no_grade THEN
P_final_grade := null;
P_exit_code := v_exit_code;
WHEN OTHERS THEN
P_final_grade := null;
P_exit_code := 'E';
END final_grade;

FUNCTION median_grade
(p_course_number section.course_no%TYPE,
p_section_number section.section_no%TYPE,
p_grade_type grade.grade_type_code%TYPE)
RETURN grade.numeric_grade%TYPE
IS
BEGIN
FOR r_work_grade
IN c_work_grade(p_course_number, p_section_number, p_grade_type)
LOOP
t_grade(NVL(t_grade.COUNT,0) + 1).numeric_grade := r_work_grade.numeric_grade;
END LOOP;
IF t_grade.COUNT = 0
THEN
RETURN NULL;
ELSE
IF MOD(t_grade.COUNT, 2) = 0
THEN
-- There is an even number of work grades. Find the middle
-- two and average them.
RETURN (t_grade(t_grade.COUNT / 2).numeric_grade +
t_grade((t_grade.COUNT / 2) + 1).numeric_grade
) / 2;
ELSE
-- There is an odd number of grades. Return the one in the middle.
RETURN t_grade(TRUNC(t_grade.COUNT / 2, 0) + 1).numeric_grade;
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END median_grade;
END MANAGE_GRADES;

The following example is a SELECT statement that makes use of the function median_grade and shows the median grade for all grade types in sections 1 and 2 of course 25.

For Example ch21_21.sql

SELECT COURSE_NO,
COURSE_NAME,
SECTION_NO,
GRADE_TYPE,
manage_grades.median_grade
(COURSE_NO,
SECTION_NO,
GRADE_TYPE)
median_grade
FROM
(SELECT DISTINCT
C.COURSE_NO COURSE_NO,
C.DESCRIPTION COURSE_NAME,
S.SECTION_NO SECTION_NO,
G.GRADE_TYPE_CODE GRADE_TYPE
FROM SECTION S, COURSE C, ENROLLMENT E, GRADE G
WHERE C.course_no = s.course_no
AND s.section_id = e.section_id
AND e.student_id = g.student_id
AND c.course_no = 25
AND s.section_no between 1 and 2
ORDER BY 1, 4, 3) grade_source

The results of the SELECT statement using the median_grade function for all grade types in sections 1 and 2 of course 25 would be as follows:

COURSE_NO COURSE_NAME SECTION_NO GRADE_TYPE MEDIAN_GRADE
---------- ------------------------------------ ------------
25 Intro to Programming 1 FI 98
25 Intro to Programming 2 FI 71
25 Intro to Programming 1 HM 76
25 Intro to Programming 2 HM 83
25 Intro to Programming 1 MT 86
25 Intro to Programming 2 MT 89
25 Intro to Programming 1 PA 91
25 Intro to Programming 2 PA 97
25 Intro to Programming 1 QZ 71
25 Intro to Programming 2 QZ 78

10 rows selected.

Lab 21.4: Package Instantiation and Initialization