Write a Pl/SQL block which calculates the percentage for each student who has passed in all subjects

❓ Question

✅ Solution

CREATE TABLE STUDENT (
    ROLLNO      NUMBER PRIMARY KEY,
    NAME        VARCHAR2(30),
    M1          NUMBER(3),
    M2          NUMBER(3),
    M3          NUMBER(3),
    PERCENTAGE  NUMBER(5,2)
);
==============================================================================================
INSERT INTO STUDENT VALUES (1, 'Amit', 75, 80, 70, NULL);
INSERT INTO STUDENT VALUES (2, 'Rahul', 35, 60, 55, NULL);
INSERT INTO STUDENT VALUES (3, 'Neha', 88, 90, 92, NULL);
INSERT INTO STUDENT VALUES (4, 'Priya', 40, 45, 42, NULL);
INSERT INTO STUDENT VALUES (5, 'Karan', 90, 85, 88, NULL);

COMMIT;
===============================================================================================
SET SERVEROUTPUT ON;

DECLARE
    v_percentage STUDENT.PERCENTAGE%TYPE;
    v_max_per    STUDENT.PERCENTAGE%TYPE;
BEGIN
    -- Calculate & update percentage for passed students
    FOR rec IN (
        SELECT ROLLNO, M1, M2, M3
        FROM STUDENT
        WHERE M1 >= 40 AND M2 >= 40 AND M3 >= 40
    ) LOOP
        v_percentage := (rec.M1 + rec.M2 + rec.M3) / 3;

        UPDATE STUDENT
        SET PERCENTAGE = v_percentage
        WHERE ROLLNO = rec.ROLLNO;
    END LOOP;

    COMMIT;

    -- Find highest percentage
    SELECT MAX(PERCENTAGE)
    INTO v_max_per
    FROM STUDENT;

    -- Display topper details
    DBMS_OUTPUT.PUT_LINE('Student(s) with Highest Percentage:');

    FOR s IN (
        SELECT ROLLNO, NAME, PERCENTAGE
        FROM STUDENT
        WHERE PERCENTAGE = v_max_per
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(
            'RollNo: ' || s.ROLLNO ||
            ', Name: ' || s.NAME ||
            ', Percentage: ' || s.PERCENTAGE
        );
    END LOOP;

END;
/

💻 Submit Your Solution