Skip to content

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

Paper Details

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

Max Marks: 10

Create following tables and apply appropriate constraints and primary key. Insert at least 5 records in each table. - Work (Worker_Id, Name, salary, Department) - Bonus (Worker_Id, Bonus_Amount)

1. Work Table Creation

Hint

Worker_Id in Work table will be the primary key and referenced by Bonus table.

View Solution & Output 
-- Create WORK table
CREATE TABLE Work (
    Worker_Id NUMBER(5) PRIMARY KEY,
    Name VARCHAR2(50) NOT NULL,
    salary NUMBER(10,2),
    Department VARCHAR2(30)
);

-- Output: Table 'Work' created.

2. Bonus Table with Foreign Key

View Solution & Output 
-- Create BONUS table with Foreign Key
CREATE TABLE Bonus (
    Worker_Id NUMBER(5),
    Bonus_Amount NUMBER(10,2),
    CONSTRAINT pk_bonus PRIMARY KEY (Worker_Id),
    CONSTRAINT fk_work_bonus 
        FOREIGN KEY (Worker_Id) 
        REFERENCES Work(Worker_Id)
);

-- Output: Table 'Bonus' created with FK to 'Work'.

3. Inserting Data

View Solution & Output 
-- Insert records into WORK
INSERT INTO Work VALUES (101, 'Rahul Sharma', 45000, 'IT');
INSERT INTO Work VALUES (102, 'Priya Patel', 50000, 'HR');
INSERT INTO Work VALUES (103, 'Amit Kumar', 55000, 'IT');
INSERT INTO Work VALUES (104, 'Sneha Gupta', 48000, 'Sales');
INSERT INTO Work VALUES (105, 'Vikram Singh', 60000, 'IT');

-- Insert records into BONUS
INSERT INTO Bonus VALUES (101, 5000);
INSERT INTO Bonus VALUES (102, 4000);
INSERT INTO Bonus VALUES (103, 6000);
INSERT INTO Bonus VALUES (104, 3500);
INSERT INTO Bonus VALUES (105, 7000);

COMMIT;

-- Verify data
SELECT * FROM Work;
SELECT * FROM Bonus;

Q1B: IT Department Salary Increase

Max Marks: 10

Write a PL/SQL block to increase salary of worker in department of IT by 10% of current salary.

Hint

Use cursor to iterate through IT department workers and update salary with 10% increment.

View Solution & Output 
SET SERVEROUTPUT ON;

DECLARE
    -- Cursor for IT department workers
    CURSOR c_it_workers IS
        SELECT Worker_Id, Name, salary
        FROM Work
        WHERE UPPER(Department) = 'IT';

    v_worker_id Work.Worker_Id%TYPE;
    v_name Work.Name%TYPE;
    v_salary Work.salary%TYPE;
    v_new_salary Work.salary%TYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('IT DEPARTMENT SALARY INCREASE - 10%');
    DBMS_OUTPUT.PUT_LINE('========================================');

    FOR rec IN c_it_workers LOOP
        v_new_salary := rec.salary * 1.10;

        UPDATE Work
        SET salary = v_new_salary
        WHERE Worker_Id = rec.Worker_Id;

        DBMS_OUTPUT.PUT_LINE('Worker: ' || rec.Name || 
                          ' | Old Salary: ' || rec.salary ||
                          ' | New Salary: ' || v_new_salary);
    END LOOP;

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('Salary update completed for IT department.');

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

You can also use explicit cursor operations for more control:

SET SERVEROUTPUT ON;

DECLARE
    CURSOR c_it_workers IS
        SELECT Worker_Id, Name, salary
        FROM Work
        WHERE UPPER(Department) = 'IT';

    v_worker_id Work.Worker_Id%TYPE;
    v_name Work.Name%TYPE;
    v_salary Work.salary%TYPE;
    v_new_salary Work.salary%TYPE;
    v_count NUMBER := 0;
BEGIN
    OPEN c_it_workers;

    LOOP
        FETCH c_it_workers INTO v_worker_id, v_name, v_salary;
        EXIT WHEN c_it_workers%NOTFOUND;

        v_new_salary := v_salary * 1.10;

        UPDATE Work
        SET salary = v_new_salary
        WHERE Worker_Id = v_worker_id;

        DBMS_OUTPUT.PUT_LINE('Updated: ' || v_name || ' - New Salary: ' || v_new_salary);
        v_count := v_count + 1;
    END LOOP;

    CLOSE c_it_workers;

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Total workers updated: ' || v_count);
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 Worker_Id, Name, salary
    FROM Work
    WHERE UPPER(Department) = 'IT'
) LOOP
    DBMS_OUTPUT.PUT_LINE('Worker: ' || rec.Name);
END LOOP;

This is the recommended approach for simple cursor iteration as it automatically handles OPEN, FETCH, and CLOSE.

Q2: Viva Preparation

Max Marks: 5

Potential Viva Questions
  1. Q: What is the difference between %NOTFOUND and %FOUND?
  2. A: %NOTFOUND returns TRUE when no row is fetched, %FOUND returns TRUE when a row is fetched.
  3. Q: What is the purpose of ROLLBACK?
  4. A: To undo all changes made in the current transaction.
  5. Q: Can we update rows while iterating through a cursor?
  6. A: Yes, but it's recommended to use FOR UPDATE clause for concurrent access scenarios.
  7. Q: What is the difference between implicit and explicit cursor?
  8. A: Implicit cursors are automatically created for DML, explicit cursors are user-defined.
  9. Q: What is the purpose of FOR LOOP with cursor?
  10. A: It simplifies cursor handling by automatically opening, fetching, and closing the cursor.

Common Pitfalls

  • Case Sensitivity: Use UPPER(Department) = 'IT' to handle case variations.
  • COMMIT: Always commit after updates or changes won't be saved.
  • Percentage Calculation: Use 1.10 for 10% increase, not 0.10.

Quick Navigation

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

Last Updated: April 2024