Calculate average grade : Utility Function « Stored Procedure Function « Oracle PL / SQL





Calculate average grade

  
SQL> CREATE TABLE myStudent (
  2    student_id NUMBER(5) NOT NULL,
  3    department CHAR(3)   NOT NULL,
  4    course     NUMBER(3) NOT NULL,
  5    grade      CHAR(1)
  6    );

Table created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10000, 'CS', 102, 'A');

1 row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10002, 'CS', 102, 'B');

1 row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10003, 'CS', 102, 'C');

1 row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10000, 'HIS', 101, 'A');

1 row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10001, 'HIS', 101, 'B');

1 row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10002, 'HIS', 101, 'B');

1 row created.

SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION AverageGrade (p_Department IN VARCHAR2,p_Course IN NUMBER) RETURN VARCHAR2 AS
  2
  3    v_AverageGrade VARCHAR2(1);
  4    v_NumericGrade NUMBER;
  5    v_NumberStudents NUMBER;
  6
  7    CURSOR c_Grades IS
  8      SELECT grade
  9        FROM myStudent
 10        WHERE department = p_Department
 11        AND course = p_Course;
 12  BEGIN
 13    SELECT COUNT(*)
 14      INTO v_NumberStudents
 15      FROM myStudent
 16      WHERE department = p_Department
 17        AND course = p_Course;
 18
 19    IF v_NumberStudents = 0 THEN
 20      RAISE_APPLICATION_ERROR(-20001, 'No students registered for ' ||
 21        p_Department || ' ' || p_Course);
 22    END IF;
 23
 24    SELECT AVG(DECODE(grade, 'A', 5,
 25                             'B', 4,
 26                             'C', 3,
 27                             'D', 2,
 28                             'E', 1))
 29      INTO v_NumericGrade
 30      FROM myStudent
 31      WHERE department = p_Department
 32      AND course = p_Course;
 33
 34    SELECT DECODE(ROUND(v_NumericGrade), 5, 'A',
 35                                         4, 'B',
 36                                         3, 'C',
 37                                         2, 'D',
 38                                         1, 'E')
 39      INTO v_AverageGrade
 40      FROM dual;
 41
 42    RETURN v_AverageGrade;
 43  END AverageGrade;
 44  /

Function created.

SQL>
SQL> select AverageGrade('HIS', 101) from dual;

AVERAGEGRADE('HIS',101)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
B

1 row selected.

SQL>
SQL>
SQL> drop table myStudent;

Table dropped.

SQL>
SQL>

   
  










Related examples in the same category

1.Your own month add method
2.Raise power function
3.Raise pay level
4.Validate date value format
5.Function to update null value
6.Function to count tab
7.Temperature package: convert Celsius to Fahrenheit back, forth