QUESTION 17A)
CREATE TABLE students13 (stu_id number(10), stu_name varchar(25),grade
NUMBER);
Table created.
SQL> INSERT INTO students13 VALUES (1, 'Alice', 85);
1 row created.
SQL> INSERT INTO students13 VALUES (2, 'Bobby', 90);
1 row created.
SQL> INSERT INTO students13 VALUES (3, 'Charlie', 78);
1 row created.
SQL> INSERT INTO students13) VALUES (4, 'David', 88);
1 row created.
SQL> INSERT INTO students13 VALUES (5, 'John', 95);
1 row created.
SQL> set serveroutput on size 30000;
SQL> CREATE OR REPLACE FUNCTION get_average_grade
2 RETURN NUMBER
3 IS
4 avg_grade NUMBER;
5 BEGIN
6 SELECT AVG(grade)
7 INTO avg_grade
8 FROM students13;
9
10 RETURN NVL(avg_grade, 0);
11 END get_average_grade;
12 /
Function created.
SQL> CREATE OR REPLACE PROCEDURE display_student_details
2 IS
3 avg_grade NUMBER;
4 BEGIN
5 avg_grade := get_average_grade();
6
7 DBMS_OUTPUT.PUT_LINE('Student Details:');
8 DBMS_OUTPUT.PUT_LINE('--------------------------------------');
9
10 FOR student IN (SELECT stu_id, stu_name, grade FROM students13)
LOOP
11 DBMS_OUTPUT.PUT_LINE('ID: ' || student.stu_id || ', Name: ' ||
student.stu_name ||
12 ', Grade: ' || student.grade);
13 END LOOP;
14
15 DBMS_OUTPUT.PUT_LINE('--------------------------------------');
16 DBMS_OUTPUT.PUT_LINE('Average Grade: ' || avg_grade);
17 END display_student_details;
18 /
Procedure created.
SQL> BEGIN
2 display_student_details();
3 END;
4 /
Output:
Student Details:
--------------------------------------
ID: 1, Name: Alice, Grade: 85
ID: 2, Name: Bobby, Grade: 90
ID: 3, Name: Charlie, Grade: 78
ID: 4, Name: David, Grade: 88
ID: 5, Name: John , Grade: 95
--------------------------------------
Average Grade: 87.2
PL/SQL procedure successfully completed.
