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 2025
• Max Marks: 25
• Time Recommendation: 45 Minutes
• Paper: View Paper | Download PDF

---

Questions & Solutions

All questions are compulsory

Q1A: Department & Employee Schema

Max Marks: 10

Create following tables with appropriate constraints with primary key and foreign key. Insert minimum 5 records in each table. - dept (dept_no, dept_name) - emp (emp_no, dept_no, emp_name, emp_salary)

1. Department Table Creation

Create the dept table with a primary key.

Hint

Always clear existing data or use IF NOT EXISTS logic where possible. In standard Oracle/SQL, simply use CREATE TABLE.

View Solution & Output

-- Create DEPT table
CREATE TABLE dept (
    dept_no NUMBER(3) PRIMARY KEY,
    dept_name VARCHAR2(30) NOT NULL
);

print "Table 'dept' created.";

Step-by-Step Explanation: 1. Initialization: Define the table name and specify columns with appropriate data types and sizes. 2. Logic Flow: Apply constraints like PRIMARY KEY, NOT NULL, or CHECK to ensure data validity and uniqueness. 3. Completion: Finalize the table definition in the database schema for future data operations.

2. Employee Table with Foreign Key

Create the emp table linked to dept.

flowchart TD
D[Dept Table]
E[Emp Table]
D --> E

View Solution & Output

-- Create EMP table with Foreign Key
CREATE TABLE emp (
    emp_no NUMBER(4) PRIMARY KEY,
    dept_no NUMBER(3),
    emp_name VARCHAR2(30) NOT NULL,
    emp_salary NUMBER(10,2),
    CONSTRAINT fk_dept_emp 
        FOREIGN KEY (dept_no) 
        REFERENCES dept(dept_no)
);

print "Table 'emp' created with FK to 'dept'.";

Step-by-Step Explanation: 1. Initialization: Define the table structure with various data types (NUMBER, VARCHAR2, DATE) and constraints. 2. Logic Flow: Assign a PRIMARY KEY for uniqueness and establish a relationship using a FOREIGN KEY linked to the master table. 3. Completion: Execute the statement to create the table with full relational integrity.

3. Inserting Sample Records

Insert minimum 5 records in each table.

View Solution & Output

-- Insert records into DEPT
INSERT INTO dept VALUES (10, 'Sales');
INSERT INTO dept VALUES (20, 'Marketing');
INSERT INTO dept VALUES (30, 'IT');
INSERT INTO dept VALUES (40, 'HR');
INSERT INTO dept VALUES (50, 'Finance');

-- Insert records into EMP
INSERT INTO emp VALUES (1001, 10, 'Rahul Sharma', 45000);
INSERT INTO emp VALUES (1002, 20, 'Priya Patel', 50000);
INSERT INTO emp VALUES (1003, 30, 'Amit Kumar', 60000);
INSERT INTO emp VALUES (1004, 10, 'Sneha Gupta', 55000);
INSERT INTO emp VALUES (1005, 30, 'Vikram Singh', 65000);

COMMIT;

-- Verify data
SELECT * FROM dept;
SELECT * FROM emp;

Step-by-Step Explanation: 1. Initialization: Prepare a series of INSERT statements containing sample data that satisfies all table constraints. 2. Logic Flow: Insert records into the master table first, followed by the detail table to maintain referential integrity. 3. Completion: Use the COMMIT command to save all changes permanently and verify the data using SELECT statements.

Q1B: Max Salary PL/SQL

Max Marks: (Misc)

Write a PL/SQL block that take input of department name from user and display name of employee whose salary is maximum in that department.

Hint

You need to join emp and dept OR use a subquery to find the MAX(emp_salary) for the specific dept_no. Use &variable for user input.

flowchart TD
input[Input Name]
get_dept[Find Dept ID]
get_max[Find Max Salary]
output[Print Success]

input --> get_dept
get_dept --> get_max
get_max --> output

View Solution & Output

SET SERVEROUTPUT ON;

DECLARE
    v_dept_name VARCHAR2(30) := '&department_name';
    v_dept_no NUMBER;
    v_emp_name emp.emp_name%TYPE;
    v_max_salary emp.emp_salary%TYPE;
BEGIN
    -- [1] Get department number
    SELECT dept_no INTO v_dept_no
    FROM dept
    WHERE dept_name = v_dept_name;

    -- [2] Find employee with maximum salary in that department
    SELECT emp_name, emp_salary 
    INTO v_emp_name, v_max_salary
    FROM emp
    WHERE dept_no = v_dept_no
    AND emp_salary = (
        SELECT MAX(emp_salary) 
        FROM emp 
        WHERE dept_no = v_dept_no
    );

    DBMS_OUTPUT.PUT_LINE('Department: ' || v_dept_name);
    DBMS_OUTPUT.PUT_LINE('Employee with Maximum Salary: ' || v_emp_name);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_max_salary);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employees found in department: ' || v_dept_name);
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Multiple employees have the same maximum salary.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Step-by-Step Explanation: 1. Initialization: Declare variables and/or constants to hold input values and query results within the PL/SQL block. 2. Logic Flow: Execute procedural logic or SQL queries (using JOINS or Subqueries) to retrieve and process the required information. 3. Completion: Output the results using DBMS_OUTPUT and handle potential errors like NO_DATA_FOUND in the EXCEPTION section.

Concept Deep Dive: Aggregate Subqueries

A subquery like (SELECT MAX(emp_salary) ...) returns a single value which the main query uses for comparison. This is the standard way to find the record holding the "maximum" or "minimum" value in a set.

Q2: Viva & Final Assessment

Max Marks: 5

Potential Viva Questions

1. Q: What is a Foreign Key?
2. A: A field in one table that uniquely identifies a row of another table, establishing a link between them.
3. Q: Why do we use subqueries in PL/SQL?
4. A: To perform complex filtering or comparisons (like finding the Max salary) that a simple SELECT cannot do alone.
5. Q: What is the purpose of TOO_MANY_ROWS exception?
6. A: It occurs when a SELECT INTO statement returns more than one row, which is not allowed for single-variable storage.
7. Q: What is the difference between DDL and DML?
8. A: DDL (Data Definition Language) handles table structure (CREATE, ALTER, DROP), while DML (Data Manipulation Language) handles table data (INSERT, UPDATE, DELETE).
9. Q: How does the & operator work in SQL*Plus?
10. A: It defines a substitution variable that prompts the user for a value at runtime.
11. Q: What is Referential Integrity?
12. A: A rule that ensures the relationship between tables remains consistent, usually enforced via Foreign Keys.

Common Pitfalls

• Exact Matches: Sales and SALES might be different depending on database settings.
• Empty Departments: If a department exists but has no employees, v_dept_no will be found but the second query will throw an exception. Use proper error handling!

---

Quick Navigation

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

Related Solutions

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

---

Last Updated: April 2025