Consider the following table to answer any two of the following: 14
student(stud_ID, subl, sub2, sub3, percentage, grade)
(1) Write a procedure to display the details of students who have scored
more than 70%
(2) Create a cursor to update the marks of all students by 5 numbers who
have got less than 45 marks.
(3) Create a stored procedure that will take student id as an input and display
result information

1) Procedure to display details of students who have scored more than 70%

You can write a stored procedure to retrieve the details of students whose percentage is greater than 70%. Here’s the SQL procedure:

CREATE OR REPLACE PROCEDURE Get_Students_Above_70 AS
BEGIN
    -- Select query to get details of students with percentage > 70
    FOR student IN
        (SELECT stud_ID, sub1, sub2, sub3, percentage, grade
         FROM student
         WHERE percentage > 70)
    LOOP
        -- Display each student's details
        DBMS_OUTPUT.PUT_LINE('Student ID: ' || student.stud_ID || 
                             ' Subject 1: ' || student.sub1 || 
                             ' Subject 2: ' || student.sub2 || 
                             ' Subject 3: ' || student.sub3 || 
                             ' Percentage: ' || student.percentage || 
                             ' Grade: ' || student.grade);
    END LOOP;
END;
/

2) Create a cursor to update the marks of all students by 5 marks who have got less than 45 marks

For this, we need to create a cursor that fetches students who have a percentage of less than 45% and then updates their marks by increasing each subject’s marks by 5. Here is an example of the cursor and update operation:

DECLARE
    CURSOR low_score_cursor IS
        SELECT stud_ID, sub1, sub2, sub3
        FROM student
        WHERE percentage < 45;
    
    -- Declare variables to hold the marks of each student
    v_sub1 NUMBER;
    v_sub2 NUMBER;
    v_sub3 NUMBER;
    
BEGIN
    -- Open the cursor and loop through the students with low marks
    FOR student IN low_score_cursor LOOP
        -- Fetch the current marks for the student
        v_sub1 := student.sub1;
        v_sub2 := student.sub2;
        v_sub3 := student.sub3;
        
        -- Update the student's marks by adding 5 to each subject
        UPDATE student
        SET sub1 = v_sub1 + 5, sub2 = v_sub2 + 5, sub3 = v_sub3 + 5
        WHERE stud_ID = student.stud_ID;
        
        -- Optionally, print the updated result (if using DBMS_OUTPUT)
        DBMS_OUTPUT.PUT_LINE('Updated marks for Student ID: ' || student.stud_ID);
    END LOOP;
    
    -- Commit the changes to the database
    COMMIT;
END;
/

3) Create a stored procedure that will take student ID as input and display result information

This stored procedure will take a stud_ID as input and return the student’s information such as subjects, percentage, and grade. Here’s an example of the procedure:

CREATE OR REPLACE PROCEDURE Get_Student_Result (p_stud_ID IN NUMBER) AS
    v_sub1 NUMBER;
    v_sub2 NUMBER;
    v_sub3 NUMBER;
    v_percentage NUMBER;
    v_grade CHAR(1);
BEGIN
    -- Select the student's details based on the input student ID
    SELECT sub1, sub2, sub3, percentage, grade
    INTO v_sub1, v_sub2, v_sub3, v_percentage, v_grade
    FROM student
    WHERE stud_ID = p_stud_ID;

    -- Display the result for the given student ID
    DBMS_OUTPUT.PUT_LINE('Student ID: ' || p_stud_ID);
    DBMS_OUTPUT.PUT_LINE('Subject 1: ' || v_sub1);
    DBMS_OUTPUT.PUT_LINE('Subject 2: ' || v_sub2);
    DBMS_OUTPUT.PUT_LINE('Subject 3: ' || v_sub3);
    DBMS_OUTPUT.PUT_LINE('Percentage: ' || v_percentage);
    DBMS_OUTPUT.PUT_LINE('Grade: ' || v_grade);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No student found with the given ID.');
END;
/