Skip to content

VNSGU BCA Sem 2: Core RDBMS (205) Practical Solutions - Set C

Paper Details

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

Questions & Solutions

All questions are compulsory

Q1A: Custmast & Billmast Schema

Max Marks: 10

Create following table with appropriate constraints and primary key. Insert at least 10 records in each table. - Custmast (custID, cname, city, phoneno) - Billmast (billno, custID, bill_date, bill_amt)

1. Custmast Table Creation

Hint

Create the customer master table first. custID will be referenced by Billmast.

View Solution & Output 
-- Create CUSTMAST table
CREATE TABLE Custmast (
    custID NUMBER(5) PRIMARY KEY,
    cname VARCHAR2(50) NOT NULL,
    city VARCHAR2(30),
    phoneno VARCHAR2(15)
);

-- Output: Table 'Custmast' created.

2. Billmast Table with Foreign Key

View Solution & Output 
-- Create BILLMAST table with Foreign Key
CREATE TABLE Billmast (
    billno NUMBER(5) PRIMARY KEY,
    custID NUMBER(5),
    bill_date DATE,
    bill_amt NUMBER(10,2),
    CONSTRAINT fk_cust_bill 
        FOREIGN KEY (custID) 
        REFERENCES Custmast(custID)
);

-- Output: Table 'Billmast' created with FK to 'Custmast'.

3. Inserting Data

View Solution & Output 
-- Insert records into CUSTMAST
INSERT INTO Custmast VALUES (101, 'Rahul Sharma', 'Surat', '9876543210');
INSERT INTO Custmast VALUES (102, 'Priya Patel', 'Mumbai', '9876543211');
INSERT INTO Custmast VALUES (103, 'Amit Kumar', 'Delhi', '9876543212');
INSERT INTO Custmast VALUES (104, 'Sita Verma', 'Ahmedabad', '9876543213');
INSERT INTO Custmast VALUES (105, 'Ram Gupta', 'Bangalore', '9876543214');
INSERT INTO Custmast VALUES (106, 'Neha Shah', 'Pune', '9876543215');
INSERT INTO Custmast VALUES (107, 'Vikram Mehta', 'Chennai', '9876543216');
INSERT INTO Custmast VALUES (108, 'Anita Desai', 'Kolkata', '9876543217');
INSERT INTO Custmast VALUES (109, 'Sunil Joshi', 'Hyderabad', '9876543218');
INSERT INTO Custmast VALUES (110, 'Mona Patel', 'Jaipur', '9876543219');

-- Insert records into BILLMAST
INSERT INTO Billmast VALUES (1, 101, '15-JAN-2024', 5000);
INSERT INTO Billmast VALUES (2, 102, '20-JAN-2024', 3500);
INSERT INTO Billmast VALUES (3, 103, '25-JAN-2024', 4200);
INSERT INTO Billmast VALUES (4, 101, '10-FEB-2024', 2800);
INSERT INTO Billmast VALUES (5, 104, '15-FEB-2024', 5500);
INSERT INTO Billmast VALUES (6, 105, '05-MAR-2024', 3200);
INSERT INTO Billmast VALUES (7, 106, '12-MAR-2024', 4800);
INSERT INTO Billmast VALUES (8, 107, '18-MAR-2024', 2100);
INSERT INTO Billmast VALUES (9, 108, '22-MAR-2024', 6000);
INSERT INTO Billmast VALUES (10, 109, '28-MAR-2024', 3900);

COMMIT;

-- Verify data
SELECT * FROM Custmast;
SELECT * FROM Billmast;

Q1B: Bill Discount PL/SQL

Max Marks: 10

Write a PL/SQL block to give 20% discount on bill amount more than Rs. 3200. Else give 5% discount and display the details.

Hint

Use cursor or loop to process each bill. Calculate discount based on bill_amt threshold of 3200.

View Solution & Output 
SET SERVEROUTPUT ON;

DECLARE
    -- Cursor to fetch all bills with customer details
    CURSOR c_bills IS
        SELECT b.billno, c.cname, b.bill_amt
        FROM Billmast b
        JOIN Custmast c ON b.custID = c.custID;

    v_discount NUMBER(10,2);
    v_final_amt NUMBER(10,2);
BEGIN
    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('BILL DISCOUNT CALCULATION');
    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('Bill No | Customer | Bill Amt | Discount | Final Amt');
    DBMS_OUTPUT.PUT_LINE('----------------------------------------');

    FOR rec IN c_bills LOOP
        -- Calculate discount based on bill amount
        IF rec.bill_amt > 3200 THEN
            v_discount := rec.bill_amt * 0.20;  -- 20% discount
        ELSE
            v_discount := rec.bill_amt * 0.05;  -- 5% discount
        END IF;

        v_final_amt := rec.bill_amt - v_discount;

        DBMS_OUTPUT.PUT_LINE(rec.billno || ' | ' || 
                          RPAD(rec.cname, 10) || ' | ' ||
                          rec.bill_amt || ' | ' ||
                          v_discount || ' | ' ||
                          v_final_amt);
    END LOOP;

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

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
Alternative: Using Explicit Cursor with OPEN/FETCH/CLOSE 
SET SERVEROUTPUT ON;

DECLARE
    CURSOR c_bills IS
        SELECT b.billno, c.cname, b.bill_amt
        FROM Billmast b
        JOIN Custmast c ON b.custID = c.custID;

    v_billno Billmast.billno%TYPE;
    v_cname Custmast.cname%TYPE;
    v_bill_amt Billmast.bill_amt%TYPE;
    v_discount NUMBER(10,2);
    v_final_amt NUMBER(10,2);
BEGIN
    OPEN c_bills;

    LOOP
        FETCH c_bills INTO v_billno, v_cname, v_bill_amt;
        EXIT WHEN c_bills%NOTFOUND;

        IF v_bill_amt > 3200 THEN
            v_discount := v_bill_amt * 0.20;
        ELSE
            v_discount := v_bill_amt * 0.05;
        END IF;

        v_final_amt := v_bill_amt - v_discount;

        DBMS_OUTPUT.PUT_LINE('Bill: ' || v_billno || 
                          ' Customer: ' || v_cname ||
                          ' Discount: ' || v_discount ||
                          ' Final: ' || v_final_amt);
    END LOOP;

    CLOSE c_bills;
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 b.billno, c.cname, b.bill_amt
    FROM Billmast b
    JOIN Custmast c ON b.custID = c.custID
) LOOP
    DBMS_OUTPUT.PUT_LINE('Bill: ' || rec.billno || ' Customer: ' || rec.cname);
END LOOP;

Q2: Viva Preparation

Max Marks: 5

Potential Viva Questions
  1. Q: What is the difference between IF-ELSE and CASE statement?
  2. A: IF-ELSE evaluates conditions sequentially. CASE is better for multiple discrete values.
  3. Q: How do you calculate percentage in SQL?
  4. A: Use multiplication by decimal (0.20 for 20%, 0.05 for 5%).
  5. Q: What is RPAD function used for?
  6. A: It pads the right side of a string with spaces to make it a fixed width.
  7. Q: What is the purpose of DATE data type?
  8. A: To store date values including day, month, and year.
  9. Q: Can we use cursor without loop?
  10. A: Yes, using OPEN/FETCH/CLOSE, but it's more verbose.

Common Pitfalls

  • Discount Logic: Check > 3200 for 20%, not >= 3200.
  • Date Format: Use proper Oracle date format when inserting.
  • Final Amount: Subtract discount from original amount, not add.

Quick Navigation

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

Last Updated: April 2024