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

Paper Details

• Subject: Core of Relational Database Management System (CRDMS)
• Subject Code: 205
• Set: D
• 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: Distributor & Item Schema

Max Marks: 10

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

• Distributor (Dno, D_Name, phno)
• Item (Item_no, Dno, item_name, qty, rate)

1. Distributor Table Creation

Hint

Start with the Distributor table as it is the "Parent" table referenced by the Item table.

View Solution & Output

-- Create DISTRIBUTOR table
CREATE TABLE Distributor (
    Dno NUMBER(5) PRIMARY KEY,
    D_Name VARCHAR2(50) NOT NULL,
    phno VARCHAR2(15)
);

-- Output: Table 'Distributor' created.

2. Item Table with Foreign Key

View Solution & Output

-- Create ITEM table with Foreign Key
CREATE TABLE Item (
    Item_no NUMBER(5) PRIMARY KEY,
    Dno NUMBER(5),
    item_name VARCHAR2(50) NOT NULL,
    qty NUMBER(5),
    rate NUMBER(10,2),
    CONSTRAINT fk_distributor_item 
        FOREIGN KEY (Dno) 
        REFERENCES Distributor(Dno)
);

-- Output: Table 'Item' created with FK to 'Distributor'.

3. Inserting Data

View Solution & Output

-- Insert records into DISTRIBUTOR
INSERT INTO Distributor VALUES (101, 'ABC Distributors', '9876543210');
INSERT INTO Distributor VALUES (102, 'XYZ Traders', '9876543211');
INSERT INTO Distributor VALUES (103, 'PQR Suppliers', '9876543212');
INSERT INTO Distributor VALUES (104, 'LMN Vendors', '9876543213');
INSERT INTO Distributor VALUES (105, 'DEF Enterprises', '9876543214');

-- Insert records into ITEM
INSERT INTO Item VALUES (1, 101, 'Laptop', 50, 45000.00);
INSERT INTO Item VALUES (2, 101, 'Mouse', 200, 500.00);
INSERT INTO Item VALUES (3, 102, 'Keyboard', 150, 800.00);
INSERT INTO Item VALUES (4, 103, 'Monitor', 75, 12000.00);
INSERT INTO Item VALUES (5, 102, 'Headphones', 100, 1500.00);

COMMIT;

-- Verify data
SELECT * FROM Distributor;
SELECT * FROM Item;

Q1B: Display Distributor with Item Details

Max Marks: 10

Write a PL/SQL block that accept distributor no and display its details with item details he distributes.

Hint

Use JOIN to combine Distributor and Item tables on Dno. Use &dno for user input.

View Solution & Output

SET SERVEROUTPUT ON;

DECLARE
    v_dno NUMBER := &dno;
    v_dname Distributor.D_Name%TYPE;
    v_phno Distributor.phno%TYPE;
BEGIN
    -- Display distributor details
    SELECT D_Name, phno INTO v_dname, v_phno
    FROM Distributor
    WHERE Dno = v_dno;

    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('DISTRIBUTOR DETAILS');
    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('Distributor No: ' || v_dno);
    DBMS_OUTPUT.PUT_LINE('Name: ' || v_dname);
    DBMS_OUTPUT.PUT_LINE('Phone: ' || v_phno);
    DBMS_OUTPUT.PUT_LINE('----------------------------------------');
    DBMS_OUTPUT.PUT_LINE('ITEMS DISTRIBUTED:');
    DBMS_OUTPUT.PUT_LINE('----------------------------------------');

    -- Display items distributed by this distributor
    FOR rec IN (
        SELECT Item_no, item_name, qty, rate 
        FROM Item 
        WHERE Dno = v_dno
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('Item No: ' || rec.Item_no || 
                            ' | Name: ' || rec.item_name ||
                            ' | Qty: ' || rec.qty ||
                            ' | Rate: ' || rec.rate);
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('========================================');

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

Alternative: Using Explicit Cursor

You can also solve this using an explicit cursor instead of a FOR loop:

SET SERVEROUTPUT ON;

DECLARE
    v_dno NUMBER := &dno;
    v_dname Distributor.D_Name%TYPE;
    v_phno Distributor.phno%TYPE;

    -- Explicit cursor for items
    CURSOR c_items IS
        SELECT Item_no, item_name, qty, rate 
        FROM Item 
        WHERE Dno = v_dno;

    v_item_no Item.Item_no%TYPE;
    v_item_name Item.item_name%TYPE;
    v_qty Item.qty%TYPE;
    v_rate Item.rate%TYPE;
BEGIN
    -- Get distributor details
    SELECT D_Name, phno INTO v_dname, v_phno
    FROM Distributor
    WHERE Dno = v_dno;

    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('DISTRIBUTOR DETAILS');
    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('Distributor No: ' || v_dno);
    DBMS_OUTPUT.PUT_LINE('Name: ' || v_dname);
    DBMS_OUTPUT.PUT_LINE('Phone: ' || v_phno);
    DBMS_OUTPUT.PUT_LINE('----------------------------------------');
    DBMS_OUTPUT.PUT_LINE('ITEMS DISTRIBUTED (Using Cursor):');
    DBMS_OUTPUT.PUT_LINE('----------------------------------------');

    -- Open and fetch from cursor
    OPEN c_items;
    LOOP
        FETCH c_items INTO v_item_no, v_item_name, v_qty, v_rate;
        EXIT WHEN c_items%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Item No: ' || v_item_no || 
                            ' | Name: ' || v_item_name ||
                            ' | Qty: ' || v_qty ||
                            ' | Rate: ' || v_rate);
    END LOOP;
    CLOSE c_items;

    DBMS_OUTPUT.PUT_LINE('========================================');

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: Distributor 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 r_item IN (
    SELECT Item_no, item_name, qty, rate 
    FROM Item 
    WHERE Dno = v_dno
) LOOP
    DBMS_OUTPUT.PUT_LINE('Name: ' || r_item.item_name);
END LOOP;

This is the recommended approach for simple cursor iteration as it:

• Automatically declares the record variable
• Handles OPEN, FETCH, and CLOSE automatically
• Is more concise and less error-prone

Q2: Viva Preparation

Max Marks: 5

Potential Viva Questions

1. Q: What is a PRIMARY KEY?
   • A: A field that uniquely identifies each record and cannot contain NULL values.
2. Q: Difference between BETWEEN and IN?
   • A: BETWEEN checks for a range of values (incl. endpoints); IN checks for membership in a specific list of values.
3. Q: When is ROLLBACK used?
   • A: To undo any changes made in the current transaction that haven't been COMMITTED yet.
4. Q: What is a Unique Key? How is it different from Primary Key?
   • A: A Unique Key also ensures uniqueness but it can accept one NULL value, whereas a Primary Key cannot.
5. Q: What is the purpose of the DESC keyword in ORDER BY?
   • A: It sorts the result set in descending order (largest to smallest).
6. Q: What is an Autonomous Transaction?
   • A: An independent transaction that can be committed or rolled back without affecting the main transaction.
7. Q: What is a FOREIGN KEY?
   • A: A field that establishes a link between two tables, ensuring referential integrity.
8. Q: What is the difference between qty and rate?
   • A: qty is quantity (NUMBER(5)), rate is price per unit (NUMBER(10,2) with decimals).
9. Q: What is the purpose of FOR loop in PL/SQL?
   • A: To iterate through multiple records returned by a cursor/query.
10. Q: What is a Cursor?

• A: A pointer to a memory area that holds the result set of a SQL query.

Common Pitfalls

• Insert Order: Insert into Distributor first, then Item (FK constraint).
• Dno Match: Ensure Item.Dno exists in Distributor table.

---

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	Current Page
Set E	Solutions
Set F	Solutions

---

Last Updated: April 2026