SQL & PL/SQL DATABASE LABORATORY
MANUAL
Complete Solutions and Implementation Guide
Ex 1: Installation of SQL
The implementation of these exercises uses standard enterprise relational database structures (Oracle / MySQL
environments). To set up the environment, follow standard installation steps including configuring environment
paths, initializing schemas, and ensuring correct user privileges (e.g., SYSTEM/SCOTT or root accounts).
SQL & PL/SQL Complete Reference Manual 1
Ex 2: Basic SQL Queries
Table Creation and Constraints Setup
-- Creating Department Table
CREATE TABLE dept (
deptno NUMBER(2) PRIMARY KEY,
depname VARCHAR2(14),
location VARCHAR2(13)
);
-- Creating Employee Table
CREATE TABLE employee (
empno NUMBER(4) PRIMARY KEY,
empname VARCHAR2(10),
job VARCHAR2(9),
manager NUMBER(4),
hiredate DATE,
salary NUMBER(7,2),
commission NUMBER(7,2),
deptno NUMBER(2) REFERENCES dept(deptno)
);
-- Mock Data Insertion
INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO employee VALUES (7839, 'KING', 'PRESIDENT', NULL, TO_DATE('17-11-1981','DD-MM-
YYYY'), 5000, NULL, 10);
INSERT INTO employee VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('01-05-1981','DD-MM-
YYYY'), 2850, NULL, 30);
INSERT INTO employee VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('09-06-1981','DD-MM-
YYYY'), 2450, NULL, 10);
INSERT INTO employee VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('02-04-1981','DD-MM-
YYYY'), 2975, NULL, 20);
INSERT INTO employee VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('19-04-1987','DD-MM-
YYYY'), 3000, NULL, 20);
INSERT INTO employee VALUES (7902, 'FORD', 'ANALYST', 7566, TO_DATE('03-12-1981','DD-MM-
YYYY'), 3000, NULL, 20);
INSERT INTO employee VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-12-1980','DD-MM-
YYYY'), 800, NULL, 20);
INSERT INTO employee VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-02-1981','DD-MM-
YYYY'), 1600, 300, 30);
Queries (1 - 10)
1. List all records in the employee table.
SQL & PL/SQL Complete Reference Manual 2
SELECT * FROM employee;
2. List department name and location from department table.
SELECT depname, location FROM dept;
3. List all employees belonging to dep "10".
SELECT * FROM employee WHERE deptno = 10;
4. List all the names of clerk and analysts working in dep "20".
SELECT empname FROM employee WHERE deptno = 20 AND job IN ('CLERK', 'ANALYST');
5. List the names and salary of employees whose salary is more than 2000.
SELECT empname, salary FROM employee WHERE salary > 2000;
6. List the employee whose salary are more than 2000 and less than 5000.
SELECT * FROM employee WHERE salary > 2000 AND salary < 5000;
7. List the details of employees who joined before september 1981.
SELECT * FROM employee WHERE hiredate < TO_DATE('01-09-1981', 'DD-MM-YYYY');
8. List the employees who are not managers.
SELECT * FROM employee WHERE job <> 'MANAGER';
9. List the employees who do not belong to department 10 and 20.
SELECT * FROM employee WHERE deptno NOT IN (10, 20);
10. List the employees who joined before june 1991 and after december 1981.
SELECT * FROM employee WHERE hiredate < TO_DATE('01-06-1991', 'DD-MM-YYYY') AND hiredate >
TO_DATE('31-12-1981', 'DD-MM-YYYY');
Ex 3: Advanced Select & Functions
11. List the different jobs available in employee table.
SELECT DISTINCT job FROM employee;
SQL & PL/SQL Complete Reference Manual 3
12. List the employees who are not eligible for the commission.
SELECT * FROM employee WHERE commission IS NULL OR commission = 0;
13. List the employee's name and designation who doesn't report to anybody.
SELECT empname, job FROM employee WHERE manager IS NULL;
14. List the employee names which starts with 'S'.
SELECT empname FROM employee WHERE empname LIKE 'S%';
15. List the employee names having exactly 5 characters.
SELECT empname FROM employee WHERE LENGTH(empname) = 5;
16. List employee names having 'i' as second character.
SELECT empname FROM employee WHERE UPPER(empname) LIKE '_I%';
17. List the average salary of employees in each department.
SELECT deptno, AVG(salary) FROM employee GROUP BY deptno;
18. Find the maximum salary in each department.
SELECT deptno, MAX(salary) FROM employee GROUP BY deptno;
19. Find the maximum salary in each department having maximum salary of 2000.
SELECT deptno, MAX(salary) FROM employee GROUP BY deptno HAVING MAX(salary) = 2000;
20. List the number of employees.
SELECT COUNT(*) FROM employee;
21. List the total salary payable to employee jobwise.
SELECT job, SUM(salary) FROM employee GROUP BY job;
22. List maximum and minimum of salary.
SELECT MAX(salary), MIN(salary) FROM employee;
23. List the employee name, hra, pf and gross salary.
SQL & PL/SQL Complete Reference Manual 4
SELECT empname,
salary * 0.12 AS HRA,
salary * 0.05 AS PF,
(salary + (salary * 0.12)) - (salary * 0.05) AS GROSS_SALARY
FROM employee;
24. List the total and average salary of employee jobwise for department number 20 and also display only
those rows having average>2000.
SELECT job, SUM(salary), AVG(salary)
FROM employee
WHERE deptno = 20
GROUP BY job
HAVING AVG(salary) > 2000;
25. List the employee name using the following functions.
SELECT
UPPER(empname),
LOWER(empname),
INITCAP(empname),
LENGTH(empname),
LPAD(empname, 10, '*'),
RPAD(empname, 10, '*'),
TRANSLATE(empname, 'A', '1'),
INSTR(empname, 'I'),
SUBSTR(empname, 2, 3)
FROM employee;
26. Use the following data functions.
SELECT
SYSDATE,
LAST_DAY(hiredate),
NEXT_DAY(SYSDATE, 'FRIDAY'),
ADD_MONTHS(hiredate, 5),
MONTHS_BETWEEN(hiredate, SYSDATE)
FROM employee;
27. List the total salary amount payable to employees.
SELECT SUM(salary) FROM employee;
Joins and Subqueries
1. List the employee where salary is > Smith.
SQL & PL/SQL Complete Reference Manual 5
SELECT * FROM employee WHERE salary > (SELECT salary FROM employee WHERE UPPER(empname) =
'SMITH');
2. Display the employee details where job id is same as that of employee 7499.
SELECT * FROM employee WHERE job = (SELECT job FROM employee WHERE empno = 7499);
3. Display the employee details where job id is same as that of employee 7499 and whose salary is same or
> employee 7782.
SELECT * FROM employee WHERE job = (SELECT job FROM employee WHERE empno = 7499)
AND salary >= (SELECT salary FROM employee WHERE empno = 7782);
4. Display the name, job and salary of the employee who gets minimum salary.
SELECT empname, job, salary FROM employee WHERE salary = (SELECT MIN(salary) FROM
employee);
5. Display the dept. and minimum salary of department having minimum salary > department 20.
SELECT deptno, MIN(salary) FROM employee GROUP BY deptno
HAVING MIN(salary) > (SELECT MIN(salary) FROM employee WHERE deptno = 20);
6. List the employees who get the minimum salary in each department.
SELECT * FROM employee WHERE (deptno, salary) IN (SELECT deptno, MIN(salary) FROM employee
GROUP BY deptno);
7. List the employee details whose salary is less than that of analyst.
SELECT * FROM employee WHERE salary < ANY (SELECT salary FROM employee WHERE job =
'ANALYST');
8 & 9. List the employee and their department name / details.
SELECT [Link], [Link], [Link] FROM employee e JOIN dept d ON [Link] = [Link];
11. List the employees working under other employees.
SELECT * FROM employee WHERE manager IS NOT NULL;
12. List the names of employees with their grade.
SELECT [Link], g.grade_level FROM employee1 e JOIN job_grades g ON [Link] BETWEEN
g.low_sal AND g.high_sal;
SQL & PL/SQL Complete Reference Manual 6
13. List the employee with second minimum salary.
SELECT * FROM employee WHERE salary = (SELECT MIN(salary) FROM employee WHERE salary >
(SELECT MIN(salary) FROM employee));
14. List the employees belonging to department of miller.
SELECT * FROM employee WHERE deptno = (SELECT deptno FROM employee WHERE UPPER(empname) =
'MILLER');
15. Show dno, loc, ename, job titles and salary of employees who work in location 1800.
SELECT [Link], [Link], [Link], [Link], [Link]
FROM employee e
JOIN dept d ON [Link] = [Link]
JOIN location l ON [Link] = [Link] WHERE l.loc_id = 1800;
16. How many employees have a name that ends with ‘n’.
SELECT COUNT(*) FROM employee WHERE empname LIKE '%n' OR empname LIKE '%N%';
17. Show the names and location for all department and number of employees working in each.
SELECT [Link], [Link], COUNT([Link])
FROM dept d LEFT JOIN employee e ON [Link] = [Link] GROUP BY [Link], [Link];
18. Which jobs are found in administration and executive department and how many employees do those
jobs?
SELECT [Link], COUNT(*) FROM employee1 e
JOIN depart d ON e.dept_id = d.dep_id
WHERE d.dep_name IN ('Administration', 'Executive') GROUP BY [Link];
19. Show department name and number of employee and average salary of all department together with
names, salaries and jobs.
SELECT [Link], COUNT([Link]) OVER(PARTITION BY [Link]), AVG([Link]) OVER(PARTITION
BY [Link]), [Link], [Link], [Link]
FROM dept d JOIN employee e ON [Link] = [Link];
20. Show the department number and lowest salary of department with the highest average salary.
SELECT deptno, MIN(salary) FROM employee GROUP BY deptno
HAVING AVG(salary) = (SELECT MAX(AVG(salary)) FROM employee GROUP BY deptno);
21. Show the department number, name and location of department where no sales representative work.
SQL & PL/SQL Complete Reference Manual 7
SELECT deptno, depname, location FROM dept WHERE deptno NOT IN
(SELECT DISTINCT deptno FROM employee WHERE job = 'SALESMAN' AND deptno IS NOT NULL);
22. Show the department number, name and number of employees working in each department that has
fewer than 3 employees.
SELECT [Link], [Link], COUNT([Link]) FROM dept d LEFT JOIN employee e ON [Link] =
[Link]
GROUP BY [Link], [Link] HAVING COUNT([Link]) < 3;
23. Show the department number, name and number of employees working in each department that has
highest number of employees.
SELECT [Link], [Link], COUNT([Link]) FROM dept d JOIN employee e ON [Link] =
[Link]
GROUP BY [Link], [Link] HAVING COUNT([Link]) = (SELECT MAX(COUNT(*)) FROM employee
GROUP BY deptno);
24. Show the employee number, name, salary department number, average salary in the department for all
employees.
SELECT empno, empname, salary, deptno, AVG(salary) OVER(PARTITION BY deptno) AS
dept_avg_sal FROM employee;
SQL & PL/SQL Complete Reference Manual 8
Index, Sequence and Synonyms
-- 1. Creating sequence
CREATE SEQUENCE dep START WITH 5 INCREMENT BY 5 MAXVALUE 100 NOCYCLE;
-- 2. Confirming the sequences
SELECT * FROM user_sequences WHERE sequence_name = 'DEP';
-- 3. Inserting into tables
INSERT INTO dept(deptno, depname, location) VALUES ([Link], 'sports', 'dc');
INSERT INTO dept(deptno, depname, location) VALUES ([Link], 'sales', 'newjersy');
INSERT INTO dept(deptno, depname, location) VALUES ([Link], 'accounts', 'newyork');
-- 4. Displaying current value of sequence
SELECT [Link] FROM dual;
-- 5. Altering the sequence
ALTER SEQUENCE dep INCREMENT BY 2;
-- 6. Deleting the sequence
DROP SEQUENCE dep;
-- 7. Creating indexes
CREATE INDEX dep_in ON dept(location);
-- 8. Confirming the index
SELECT index_name FROM user_indexes WHERE table_name = 'DEPT';
-- 9. Creating Synonym
CREATE SYNONYM syn1 FOR dept;
-- 10. Selecting using synonym
SELECT * FROM syn1;
-- 11. Deleting synonym
DROP SYNONYM syn1;
Set Operators and Views
1. Display the current and previous job details of all employees. Display each employee only once.
SELECT emp_id, job_id FROM employee1 UNION SELECT emp_id, job_id FROM job_history;
2. Display the current and previous job details of all employees. Include all duplications.
SELECT emp_id, job_id FROM employee1 UNION ALL SELECT emp_id, job_id FROM job_history;
SQL & PL/SQL Complete Reference Manual 9
3. Display the employee id and job id of all employees who currently have a job title that they held before.
SELECT emp_id, job_id FROM employee1 INTERSECT SELECT emp_id, job_id FROM job_history;
4. Display the employees id of those employees who have not changed their jobs even once.
SELECT emp_id FROM employee1 MINUS SELECT emp_id FROM job_history;
PL/SQL Simple Programs
1. DML Operations using PL/SQL
BEGIN
INSERT INTO dept VALUES(50, 'MARKETING', 'SAN JOSE');
UPDATE dept SET location = 'SILICON VALLEY' WHERE deptno = 50;
COMMIT;
END;
/
2. Factorial of a Given Number
DECLARE
num NUMBER := 5;
fact NUMBER := 1;
BEGIN
FOR i IN 1..num LOOP
fact := fact * i;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Factorial: ' || fact);
END;
/
SQL & PL/SQL Complete Reference Manual 10
3. Fibonacci Series
DECLARE
n NUMBER := 10;
a NUMBER := 0;
b NUMBER := 1;
c NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(a);
DBMS_OUTPUT.PUT_LINE(b);
FOR i IN 3..n LOOP
c := a + b;
DBMS_OUTPUT.PUT_LINE(c);
a := b;
b := c;
END LOOP;
END;
/
PL/SQL Cursors, Exceptions, Packages & Triggers
Simple Cursor Example
DECLARE
CURSOR emp_cur IS SELECT empname, salary FROM employee;
v_name [Link]%TYPE;
v_sal [Link]%TYPE;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO v_name, v_sal;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name || ' earns ' || v_sal);
END LOOP;
CLOSE emp_cur;
END;
/
SQL & PL/SQL Complete Reference Manual 11
Trigger to Prevent Updates on Sunday
CREATE OR REPLACE TRIGGER trg_check_sun
BEFORE INSERT OR UPDATE OR DELETE ON employee
BEGIN
IF TO_CHAR(SYSDATE, 'DY') = 'SUN' THEN
RAISE_APPLICATION_ERROR(-20001, 'DML Operations are restricted on Sundays.');
END IF;
END;
/
SQL & PL/SQL Complete Reference Manual 12