A). Create SQL table with primary key and foreign key with insert 5 records. Stud : ( roll_no,name,gender,mobile) stud_result : ( roll_no,mark1,mark2,mark3,mark4 )

B). Pl/sql block that accept Roll no as input , calculate total marks and percentage of that student. Display student marksheet

A) Create Tables and Insert Records

1. Create Stud Table (Primary Key)

CREATE TABLE Stud (
    roll_no NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    gender VARCHAR2(10),
    mobile VARCHAR2(15)
);

2. Create stud_result Table (Foreign Key)

CREATE TABLE stud_result (
    roll_no NUMBER,
    mark1 NUMBER,
    mark2 NUMBER,
    mark3 NUMBER,
    mark4 NUMBER,
    CONSTRAINT fk_roll FOREIGN KEY (roll_no)
    REFERENCES Stud(roll_no)
);

3. Insert Records into Stud

INSERT INTO Stud VALUES (1, 'Amit', 'Male', '9876543210');
INSERT INTO Stud VALUES (2, 'Neha', 'Female', '9123456780');
INSERT INTO Stud VALUES (3, 'Rahul', 'Male', '9988776655');
INSERT INTO Stud VALUES (4, 'Priya', 'Female', '9012345678');
INSERT INTO Stud VALUES (5, 'Karan', 'Male', '9090909090');

4. Insert Records into stud_result

INSERT INTO stud_result VALUES (1, 78, 85, 80, 75);
INSERT INTO stud_result VALUES (2, 88, 92, 79, 85);
INSERT INTO stud_result VALUES (3, 67, 70, 72, 68);
INSERT INTO stud_result VALUES (4, 90, 95, 93, 89);
INSERT INTO stud_result VALUES (5, 55, 60, 58, 62);

B) PL/SQL Block (Marksheet Display)

SET SERVEROUTPUT ON;
DECLARE
    v_roll_no Stud.roll_no%TYPE := &roll_no;    v_name Stud.name%TYPE;
    v_gender Stud.gender%TYPE;    m1 NUMBER;
    m2 NUMBER;
    m3 NUMBER;
    m4 NUMBER;    total NUMBER;
    percentage NUMBER;
BEGIN
    -- Fetch student details
    SELECT name, gender INTO v_name, v_gender
    FROM Stud
    WHERE roll_no = v_roll_no;    -- Fetch marks
    SELECT mark1, mark2, mark3, mark4
    INTO m1, m2, m3, m4
    FROM stud_result
    WHERE roll_no = v_roll_no;    -- Calculate total and percentage
    total := m1 + m2 + m3 + m4;
    percentage := total / 4;    -- Display Marksheet
    DBMS_OUTPUT.PUT_LINE('----- STUDENT MARKSHEET -----');
    DBMS_OUTPUT.PUT_LINE('Roll No   : ' || v_roll_no);
    DBMS_OUTPUT.PUT_LINE('Name      : ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Gender    : ' || v_gender);    DBMS_OUTPUT.PUT_LINE('Marks: ');
    DBMS_OUTPUT.PUT_LINE('Mark1     : ' || m1);
    DBMS_OUTPUT.PUT_LINE('Mark2     : ' || m2);
    DBMS_OUTPUT.PUT_LINE('Mark3     : ' || m3);
    DBMS_OUTPUT.PUT_LINE('Mark4     : ' || m4);    DBMS_OUTPUT.PUT_LINE('Total     : ' || total);
    DBMS_OUTPUT.PUT_LINE('Percentage: ' || percentage || '%');EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Student not found!');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/