VNSGU BCA Sem 2: Core RDBMS (205) Practical Solutions - April 2024 Set F

Paper Details

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

Max Marks: 10

Create the two tables as shown below with the given constraints and insert 5 records in each table.

• Employee (Eid, Ename, DeptId, Designation, Salary, Date_of_joining)
  • Constraints: Eid is Primary key and DeptId is foreign key. Salary should not be less than 10000.
• Department (DeptId, Dname)
  • Constraints: DeptId Primary key and Dname is NOT NULL

1. Department Table Creation

Hint

Create the Department table first as it is the parent table referenced by Employee.

View Solution & Output

-- Create DEPARTMENT table
CREATE TABLE Department (
    DeptId NUMBER(5) PRIMARY KEY,
    Dname VARCHAR2(50) NOT NULL
);

-- Output: Table 'Department' created.

2. Employee Table with Foreign Key and Check Constraint

View Solution & Output

-- Create EMPLOYEE table with Foreign Key and Check constraint
CREATE TABLE Employee (
    Eid NUMBER(5) PRIMARY KEY,
    Ename VARCHAR2(50) NOT NULL,
    DeptId NUMBER(5),
    Designation VARCHAR2(30),
    Salary NUMBER(10,2) CHECK (Salary >= 10000),
    Date_of_joining DATE,
    CONSTRAINT fk_dept_employee 
        FOREIGN KEY (DeptId) 
        REFERENCES Department(DeptId)
);

-- Output: Table 'Employee' created with FK and Check constraints.

3. Inserting Data

View Solution & Output

-- Insert records into DEPARTMENT
INSERT INTO Department VALUES (10, 'IT');
INSERT INTO Department VALUES (20, 'HR');
INSERT INTO Department VALUES (30, 'Sales');
INSERT INTO Department VALUES (40, 'Finance');
INSERT INTO Department VALUES (50, 'Operations');

-- Insert records into EMPLOYEE
INSERT INTO Employee VALUES (101, 'Rahul Sharma', 10, 'Developer', 45000, '15-JAN-2018');
INSERT INTO Employee VALUES (102, 'Priya Patel', 20, 'Manager', 55000, '20-MAR-2017');
INSERT INTO Employee VALUES (103, 'Amit Kumar', 10, 'Senior Developer', 65000, '10-JUN-2016');
INSERT INTO Employee VALUES (104, 'Sneha Gupta', 30, 'Sales Executive', 35000, '05-AUG-2019');
INSERT INTO Employee VALUES (105, 'Vikram Singh', 40, 'Accountant', 40000, '25-NOV-2018');

COMMIT;

-- Verify data
SELECT * FROM Department;
SELECT * FROM Employee;

Q1B: Display Employee with Work Experience

Max Marks: 10

Write a PL/SQL block to display all the employee details along with their work experience in the company till current date.

Hint

Use MONTHS_BETWEEN or SYSDATE to calculate experience. Use cursor to iterate through all employees.

View Solution & Output

SET SERVEROUTPUT ON;

DECLARE
    -- Cursor to fetch all employees with department details
    CURSOR c_employees IS
        SELECT e.Eid, e.Ename, e.Designation, e.Salary,
               e.Date_of_joining, d.Dname
        FROM Employee e
        JOIN Department d ON e.DeptId = d.DeptId;

    v_experience_years NUMBER;
    v_experience_months NUMBER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE DETAILS WITH WORK EXPERIENCE');
    DBMS_OUTPUT.PUT_LINE('========================================');

    FOR rec IN c_employees LOOP
        -- Calculate experience in years and months
        v_experience_months := TRUNC(MONTHS_BETWEEN(SYSDATE, rec.Date_of_joining));
        v_experience_years := TRUNC(v_experience_months / 12);
        v_experience_months := MOD(v_experience_months, 12);

        DBMS_OUTPUT.PUT_LINE('----------------------------------------');
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || rec.Eid);
        DBMS_OUTPUT.PUT_LINE('Name: ' || rec.Ename);
        DBMS_OUTPUT.PUT_LINE('Department: ' || rec.Dname);
        DBMS_OUTPUT.PUT_LINE('Designation: ' || rec.Designation);
        DBMS_OUTPUT.PUT_LINE('Salary: ' || rec.Salary);
        DBMS_OUTPUT.PUT_LINE('Date of Joining: ' || rec.Date_of_joining);
        DBMS_OUTPUT.PUT_LINE('Experience: ' || v_experience_years || ' years ' || 
                          v_experience_months || ' months');
    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_employees IS
        SELECT e.Eid, e.Ename, e.Designation, e.Salary,
               e.Date_of_joining, d.Dname
        FROM Employee e
        JOIN Department d ON e.DeptId = d.DeptId;

    v_eid Employee.Eid%TYPE;
    v_ename Employee.Ename%TYPE;
    v_designation Employee.Designation%TYPE;
    v_salary Employee.Salary%TYPE;
    v_doj Employee.Date_of_joining%TYPE;
    v_dname Department.Dname%TYPE;
    v_total_months NUMBER;
    v_years NUMBER;
    v_months NUMBER;
BEGIN
    OPEN c_employees;

    DBMS_OUTPUT.PUT_LINE('Employee Experience Report:');

    LOOP
        FETCH c_employees INTO v_eid, v_ename, v_designation, v_salary, v_doj, v_dname;
        EXIT WHEN c_employees%NOTFOUND;

        v_total_months := TRUNC(MONTHS_BETWEEN(SYSDATE, v_doj));
        v_years := TRUNC(v_total_months / 12);
        v_months := MOD(v_total_months, 12);

        DBMS_OUTPUT.PUT_LINE(v_ename || ' (' || v_dname || ') - ' ||
                          v_years || ' years ' || v_months || ' months');
    END LOOP;

    CLOSE c_employees;
END;
/

Alternative: Using TRUNC with Date Subtraction

SET SERVEROUTPUT ON;

DECLARE
    CURSOR c_emp IS
        SELECT Eid, Ename, Date_of_joining, Dname
        FROM Employee e JOIN Department d ON e.DeptId = d.DeptId;
BEGIN
    FOR rec IN c_emp LOOP
        -- Alternative calculation using date subtraction
        DBMS_OUTPUT.PUT_LINE(rec.Ename || ' - Joined: ' || rec.Date_of_joining ||
                          ' - Experience: ' || 
                          TRUNC((SYSDATE - rec.Date_of_joining)/365) || ' years');
    END LOOP;
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 Eid, Ename, Date_of_joining, Dname
    FROM Employee e JOIN Department d ON e.DeptId = d.DeptId
) LOOP
    DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.Ename || ' Experience: ' || 
                      TRUNC((SYSDATE - rec.Date_of_joining)/365) || ' years');
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 purpose of MONTHS_BETWEEN function?
   • A: It returns the number of months between two dates.
2. Q: What is the difference between TRUNC and ROUND functions?
   • A: TRUNC removes decimal part, ROUND rounds to nearest integer.
3. Q: What is MOD function used for?
   • A: It returns the remainder after division (used here to get months after years).
4. Q: What is the CHECK constraint on Salary doing?
   • A: Ensures salary values are >= 10000.
5. Q: How do you calculate years from months?
   • A: Divide total months by 12 using TRUNC or FLOOR.

Common Pitfalls

• CHECK Constraint: Salary must be >= 10000, not > 10000.
• Date Arithmetic: Use MONTHS_BETWEEN for accurate month calculation.
• Leap Years: Simple division by 365 may be inaccurate for leap years.

---

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

---

Last Updated: April 2026