Solution
RDBMS PRACTICAL ASSIGNMENT Join and PLSQL Block
Subject:
Concepts of Relational Database Management System(RDBMS)
Created by:
Super Admin
Posted:
Feb 15, 2026
SQL Practical – Complete Solution (1–24)
STEP 1 – Create Tables
CREATE TABLE DEPARTMENT (
dept_id NUMBER PRIMARY KEY,
dept_name VARCHAR2(50),
location VARCHAR2(50)
);
CREATE TABLE EMPLOYEE (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50),
salary NUMBER(10,2),
dept_id NUMBER,
FOREIGN KEY (dept_id) REFERENCES DEPARTMENT(dept_id)
);
CREATE TABLE PROJECT (
project_id NUMBER PRIMARY KEY,
project_name VARCHAR2(50),
dept_id NUMBER,
FOREIGN KEY (dept_id) REFERENCES DEPARTMENT(dept_id)
);
STEP 2 – Insert Data
INSERT INTO DEPARTMENT VALUES (1,'IT','Surat'); INSERT INTO DEPARTMENT VALUES (2,'HR','Ahmedabad'); INSERT INTO DEPARTMENT VALUES (3,'Finance','Mumbai'); INSERT INTO DEPARTMENT VALUES (4,'Marketing','Delhi'); INSERT INTO EMPLOYEE VALUES (101,'Amit',35000,1); INSERT INTO EMPLOYEE VALUES (102,'Riya',28000,1); INSERT INTO EMPLOYEE VALUES (103,'Karan',40000,2); INSERT INTO EMPLOYEE VALUES (104,'Neha',25000,3); INSERT INTO EMPLOYEE VALUES (105,'Rahul',50000,NULL); INSERT INTO PROJECT VALUES (201,'Website Development',1); INSERT INTO PROJECT VALUES (202,'Recruitment Drive',2); INSERT INTO PROJECT VALUES (203,'Budget Planning',3);
STEP 3 – Check Tables
SELECT * FROM DEPARTMENT; SELECT * FROM EMPLOYEE; SELECT * FROM PROJECT;
1. Employee name and department name
SELECT e.emp_name, d.dept_name FROM EMPLOYEE e JOIN DEPARTMENT d ON e.dept_id = d.dept_id;
2. Employees working in IT department
SELECT e.* FROM EMPLOYEE e JOIN DEPARTMENT d ON e.dept_id = d.dept_id WHERE d.dept_name = 'IT';
3. Employee details with department location
SELECT e.*, d.location FROM EMPLOYEE e JOIN DEPARTMENT d ON e.dept_id = d.dept_id;
4. Project name with department name
SELECT p.project_name, d.dept_name FROM PROJECT p JOIN DEPARTMENT d ON p.dept_id = d.dept_id;
5. All departments including no employees
SELECT d.dept_id, d.dept_name, d.location FROM DEPARTMENT d LEFT JOIN EMPLOYEE e ON d.dept_id = e.dept_id;
6. All employees even if no department
SELECT e.emp_name, d.dept_name FROM EMPLOYEE e LEFT JOIN DEPARTMENT d ON e.dept_id = d.dept_id;
7. All departments including no projects
SELECT d.dept_name, p.project_name FROM DEPARTMENT d LEFT JOIN PROJECT p ON d.dept_id = p.dept_id;
8. Full Outer Join
SELECT e.emp_name, d.dept_name FROM EMPLOYEE e FULL OUTER JOIN DEPARTMENT d ON e.dept_id = d.dept_id;
10. Employees earning more than 30000 sorted
SELECT * FROM EMPLOYEE WHERE salary > 30000 ORDER BY salary;
11. Department-wise total salary
SELECT dept_id, SUM(salary) AS total_salary FROM EMPLOYEE GROUP BY dept_id;
12. Number of employees in each department
SELECT dept_id, COUNT(*) AS total_employees FROM EMPLOYEE GROUP BY dept_id;
13. Employee name, department name, location
SELECT e.emp_name, d.dept_name, d.location FROM EMPLOYEE e JOIN DEPARTMENT d ON e.dept_id = d.dept_id;
14. Project name with employee names
SELECT p.project_name, e.emp_name FROM PROJECT p JOIN EMPLOYEE e ON p.dept_id = e.dept_id;
15. Employees in Surat department
SELECT e.* FROM EMPLOYEE e JOIN DEPARTMENT d ON e.dept_id = d.dept_id WHERE d.location = 'Surat';
16. Department-wise employee list ordered
SELECT d.dept_name, e.emp_name FROM DEPARTMENT d JOIN EMPLOYEE e ON d.dept_id = e.dept_id ORDER BY d.dept_name, e.emp_name;
17. Employee name and project name
SELECT e.emp_name, p.project_name FROM EMPLOYEE e JOIN PROJECT p ON e.dept_id = p.dept_id;
18. Employees in departments that have projects
SELECT DISTINCT e.* FROM EMPLOYEE e JOIN PROJECT p ON e.dept_id = p.dept_id;
19. Department name and total employees
SELECT d.dept_name, COUNT(e.emp_id) AS total_employees FROM DEPARTMENT d LEFT JOIN EMPLOYEE e ON d.dept_id = e.dept_id GROUP BY d.dept_name;
20. Employees not earning max salary in department
SELECT *
FROM EMPLOYEE e
WHERE salary < (
SELECT MAX(salary)
FROM EMPLOYEE
WHERE dept_id = e.dept_id
);
21. Employees earning more than average salary
SELECT e.emp_name, e.salary, d.dept_name FROM EMPLOYEE e JOIN DEPARTMENT d ON e.dept_id = d.dept_id WHERE e.salary > (SELECT AVG(salary) FROM EMPLOYEE);
22. Department name and project count
SELECT d.dept_name, COUNT(p.project_id) AS project_count FROM DEPARTMENT d LEFT JOIN PROJECT p ON d.dept_id = p.dept_id GROUP BY d.dept_name;
23. Employees whose dept_id exists in PROJECT
SELECT * FROM EMPLOYEE WHERE dept_id IN (SELECT dept_id FROM PROJECT);
24. Projects of departments having more than one employee
SELECT *
FROM PROJECT
WHERE dept_id IN (
SELECT dept_id
FROM EMPLOYEE
GROUP BY dept_id
HAVING COUNT(*) > 1
);
PL/SQL Practical Programs (1–13)
1. Accept salary and display annual salary
DECLARE
v_salary NUMBER;
v_annual NUMBER;
BEGIN
v_salary := &salary;
v_annual := v_salary * 12;
DBMS_OUTPUT.PUT_LINE('Annual Salary: ' || v_annual);
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Invalid Salary Input');
END;
/
2. Check salary > 25000
DECLARE
v_salary NUMBER;
BEGIN
v_salary := &salary;
IF v_salary > 25000 THEN
DBMS_OUTPUT.PUT_LINE('High Salary');
ELSE
DBMS_OUTPUT.PUT_LINE('Low Salary');
END IF;
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Invalid Input');
END;
/
3. Display Grade Based on Salary
DECLARE
v_salary NUMBER;
BEGIN
v_salary := &salary;
IF v_salary >= 50000 THEN
DBMS_OUTPUT.PUT_LINE('Grade A');
ELSIF v_salary >= 30000 THEN
DBMS_OUTPUT.PUT_LINE('Grade B');
ELSE
DBMS_OUTPUT.PUT_LINE('Grade C');
END IF;
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Invalid Salary');
END;
/
4. Print numbers 1 to 10 (LOOP)
DECLARE
i NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(i);
i := i + 1;
EXIT WHEN i > 10;
END LOOP;
END;
/
5. First 10 Even Numbers (WHILE)
DECLARE
i NUMBER := 2;
count_num NUMBER := 1;
BEGIN
WHILE count_num <= 10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
i := i + 2;
count_num := count_num + 1;
END LOOP;
END;
/
6. Count Number of Employees
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM EMPLOYEE;
DBMS_OUTPUT.PUT_LINE('Total Employees: ' || v_count);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No Employees Found');
END;
/
7. Display Employee Names
BEGIN
FOR rec IN (SELECT emp_name FROM EMPLOYEE) LOOP
DBMS_OUTPUT.PUT_LINE(rec.emp_name);
END LOOP;
END;
/
8. Employees earning more than 30000
BEGIN
FOR rec IN (SELECT emp_name, salary FROM EMPLOYEE WHERE salary > 30000) LOOP
DBMS_OUTPUT.PUT_LINE(rec.emp_name || ' - ' || rec.salary);
END LOOP;
END;
/
9. Employee name and department name
BEGIN
FOR rec IN (
SELECT e.emp_name, d.dept_name
FROM EMPLOYEE e
JOIN DEPARTMENT d ON e.dept_id = d.dept_id
) LOOP
DBMS_OUTPUT.PUT_LINE(rec.emp_name || ' - ' || rec.dept_name);
END LOOP;
END;
/
10. Accept dept ID and display employees
DECLARE
v_dept NUMBER;
BEGIN
v_dept := &dept_id;
FOR rec IN (SELECT emp_name FROM EMPLOYEE WHERE dept_id = v_dept) LOOP
DBMS_OUTPUT.PUT_LINE(rec.emp_name);
END LOOP;
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Invalid Department ID');
END;
/
11. Fetch employee name & salary using emp_id
DECLARE
v_id NUMBER;
v_name EMPLOYEE.emp_name%TYPE;
v_salary EMPLOYEE.salary%TYPE;
BEGIN
v_id := &emp_id;
SELECT emp_name, salary
INTO v_name, v_salary
FROM EMPLOYEE
WHERE emp_id = v_id;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee Not Found');
END;
/
12. Handle NO_DATA_FOUND
DECLARE
v_name EMPLOYEE.emp_name%TYPE;
BEGIN
SELECT emp_name INTO v_name
FROM EMPLOYEE
WHERE emp_id = &emp_id;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No such Employee exists');
END;
/
13. Cursor – Display all employee names with salary
DECLARE
CURSOR emp_cursor IS
SELECT emp_name, salary FROM EMPLOYEE;
v_name EMPLOYEE.emp_name%TYPE;
v_salary EMPLOYEE.salary%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_name, v_salary;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name || ' - ' || v_salary);
END LOOP;
CLOSE emp_cursor;
END;
/