Question

Create the SQL statements for displaying the results for each of the following scenarios in the Academic Database. Save these statements in a script called M07_LastNameFirstName_Functions (where LastName is your last name and FirstName is your first name). Run/execute these commands in Oracle Application Express. Based on data in the Academic Database, complete the following:
1. Display the number of students that are eligible for exams.
2. Display the number of students that had a grade of 70 or above.
3. Display the highest exam score that Nina received.
4. Display the least number of days off.
5. Display the average exam grade received by each student.
6. Display the average exam grade for the OOAD course.

Answer 1

Answer:

CREATE FUNCTION exam_eligible_students

   RETURN NUMBER AS

   num_students NUMBER(15);

BEGIN

   SELECT COUNT(STUDENT_ID)

   INTO num_students

   FROM STUDENT_ATTENDANCE

   WHERE ELIGIBILITY_FOR_EXAMS = 'Y';

   

   RETURN (num_students);

END;

Explanation:

exam_eligible_students is a made of name for the FUNCTION to be called.

num_students is a made up name for the RETURN to be called. The RETURN name is referenced in the INTO statement and as the name of the the return in the RETURN line in ().