VNSGU BCA Sem 2: Core RDBMS (205) Practical Solutions - April 2025 Set E

Paper Details

• Subject: Core of Relational Database Management System (CRDMS)
• Subject Code: 205
• Set: E
• Semester: 2
• Month/Year: April 2025
• Max Marks: 25
• Time Recommendation: 45 Minutes
• Paper: View Paper | Download PDF

---

Questions & Solutions

All questions are compulsory

Q1A: Student & Result Schema

Max Marks: 10

Create following tables with appropriate constraints with primary key and foreign key. Insert minimum 5 records in each table.

• Stud (roll_no, name, gender, mobileNo)
• stud_result (roll_no, mark1, mark2, mark3, mark4)

1. Stud Table Creation

Hint

Start with the Stud table as it is the "Parent" table referenced by the stud_result table.

View Solution & Output

-- Create STUD table
CREATE TABLE Stud (
    roll_no NUMBER(5) PRIMARY KEY,
    name VARCHAR2(50) NOT NULL,
    gender VARCHAR2(10) CHECK (gender IN ('M', 'F', 'Male', 'Female')),
    mobileNo VARCHAR2(15)
);

-- Output: Table 'Stud' created.

2. stud_result Table with Foreign Key

View Solution & Output

-- Create STUD_RESULT table with Foreign Key
CREATE TABLE stud_result (
    roll_no NUMBER(5) PRIMARY KEY,
    mark1 NUMBER(5) CHECK (mark1 BETWEEN 0 AND 100),
    mark2 NUMBER(5) CHECK (mark2 BETWEEN 0 AND 100),
    mark3 NUMBER(5) CHECK (mark3 BETWEEN 0 AND 100),
    mark4 NUMBER(5) CHECK (mark4 BETWEEN 0 AND 100),
    CONSTRAINT fk_stud_result 
        FOREIGN KEY (roll_no) 
        REFERENCES Stud(roll_no)
);

-- Output: Table 'stud_result' created with FK to 'Stud'.

3. Inserting Data

View Solution & Output

-- Insert records into STUD
INSERT INTO Stud VALUES (101, 'Rahul Sharma', 'M', '9876543210');
INSERT INTO Stud VALUES (102, 'Priya Patel', 'F', '9876543211');
INSERT INTO Stud VALUES (103, 'Amit Kumar', 'M', '9876543212');
INSERT INTO Stud VALUES (104, 'Sita Verma', 'F', '9876543213');
INSERT INTO Stud VALUES (105, 'Ram Gupta', 'M', '9876543214');

-- Insert records into STUD_RESULT
INSERT INTO stud_result VALUES (101, 85, 90, 78, 88);
INSERT INTO stud_result VALUES (102, 75, 82, 80, 85);
INSERT INTO stud_result VALUES (103, 90, 85, 92, 88);
INSERT INTO stud_result VALUES (104, 70, 75, 72, 78);
INSERT INTO stud_result VALUES (105, 88, 92, 85, 90);

COMMIT;

-- Verify data
SELECT * FROM Stud;
SELECT * FROM stud_result;

Q1B: Student Mark Sheet PL/SQL

Max Marks: 10

Write a PL/SQL block that accept Roll no as input, calculate total marks, and percentage of that student. Display student mark sheet.

Hint

Join Stud and stud_result tables on roll_no. Calculate total and percentage (out of 400 marks).

View Solution & Output

SET SERVEROUTPUT ON;

DECLARE
    v_roll_no NUMBER := &roll_no;
    v_name Stud.name%TYPE;
    v_gender Stud.gender%TYPE;
    v_mobile Stud.mobileNo%TYPE;
    v_mark1 stud_result.mark1%TYPE;
    v_mark2 stud_result.mark2%TYPE;
    v_mark3 stud_result.mark3%TYPE;
    v_mark4 stud_result.mark4%TYPE;
    v_total NUMBER;
    v_percentage NUMBER(5,2);
    v_grade VARCHAR2(2);
BEGIN
    -- Get student and marks data
    SELECT s.name, s.gender, s.mobileNo,
           r.mark1, r.mark2, r.mark3, r.mark4
    INTO v_name, v_gender, v_mobile,
         v_mark1, v_mark2, v_mark3, v_mark4
    FROM Stud s
    JOIN stud_result r ON s.roll_no = r.roll_no
    WHERE s.roll_no = v_roll_no;

    -- Calculate total and percentage
    v_total := v_mark1 + v_mark2 + v_mark3 + v_mark4;
    v_percentage := (v_total / 400) * 100;

    -- Determine grade
    IF v_percentage >= 80 THEN
        v_grade := 'A';
    ELSIF v_percentage >= 60 THEN
        v_grade := 'B';
    ELSIF v_percentage >= 40 THEN
        v_grade := 'C';
    ELSE
        v_grade := 'F';
    END IF;

    -- Display Mark Sheet
    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('         STUDENT MARK SHEET');
    DBMS_OUTPUT.PUT_LINE('========================================');
    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('Mobile: ' || v_mobile);
    DBMS_OUTPUT.PUT_LINE('----------------------------------------');
    DBMS_OUTPUT.PUT_LINE('Subject 1: ' || v_mark1);
    DBMS_OUTPUT.PUT_LINE('Subject 2: ' || v_mark2);
    DBMS_OUTPUT.PUT_LINE('Subject 3: ' || v_mark3);
    DBMS_OUTPUT.PUT_LINE('Subject 4: ' || v_mark4);
    DBMS_OUTPUT.PUT_LINE('----------------------------------------');
    DBMS_OUTPUT.PUT_LINE('Total Marks: ' || v_total || '/400');
    DBMS_OUTPUT.PUT_LINE('Percentage: ' || v_percentage || '%');
    DBMS_OUTPUT.PUT_LINE('Grade: ' || v_grade);
    DBMS_OUTPUT.PUT_LINE('========================================');

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: Student not found.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

One Step Further: The Cursor FOR Loop

If you use a Cursor FOR Loop, you don't even need to declare the %ROWTYPE variables or use OPEN/FETCH/CLOSE. PL/SQL handles the record declaration implicitly:

FOR rec IN (
    SELECT s.name, s.gender, s.mobileNo,
           r.mark1, r.mark2, r.mark3, r.mark4
    FROM Stud s
    JOIN stud_result r ON s.roll_no = r.roll_no
    WHERE s.roll_no = v_roll_no
) LOOP
    DBMS_OUTPUT.PUT_LINE('Student: ' || rec.name);
END LOOP;

Q2: Viva Preparation

Max Marks: 5

Potential Viva Questions

1. Q: What is a PRIMARY KEY constraint?
   • A: It uniquely identifies each record in a table and automatically enforces both NOT NULL and UNIQUE.
2. Q: Why use SET SERVEROUTPUT ON?
   • A: It enables the display of output generated by DBMS_OUTPUT.PUT_LINE in the SQL client.
3. Q: Difference between VARCHAR2 and CHAR?
   • A: VARCHAR2 is variable-length (saves space); CHAR is fixed-length (pads with spaces).
4. Q: What is the ROWNUM pseudo-column?
   • A: It assigns a unique number to each row in the result set based on the order they are retrieved.
5. Q: What is an Exception in PL/SQL?
   • A: An error condition during program execution. It can be internally defined by the system or explicitly defined by the user.
6. Q: How do you handle multiple doctors for the same disease?
   • A: By using a CURSOR to iterate through all matching rows instead of a SELECT INTO which only handles one row.
7. Q: What is a CHECK constraint?
   • A: A constraint that ensures values meet specific conditions (e.g., marks BETWEEN 0 AND 100).
8. Q: How is percentage calculated?
   • A: (Total Marks / Maximum Marks) × 100 = (Total / 400) × 100
9. Q: What is the difference between Stud and stud_result?
   • A: Stud stores student personal info, stud_result stores academic marks.
10. Q: Can we store multiple results for one student?

• A: No, with current schema (PK on roll_no). Would need composite key for multiple.

Common Pitfalls

• Mark Range: Ensure marks are 0-100 using CHECK constraint.
• Insert Order: Insert into Stud first, then stud_result.

---

Quick Navigation

• ← Back to CRDMS Papers
• ← Back to Semester 2
• ← Back to All Past Papers

Related Solutions

Set	Link
Set A	Solutions
Set B	Solutions
Set C	Solutions
Set D	Solutions
Set E	Current Page
Set F	Solutions

---

Last Updated: April 2026