DBMS Lab Practical Manual

Department of Information Technology

UIET
Panjab University, Chandigarh

August 25, 2025

Contents

0.1 Week 1: Introduction to RDBMS and SQL . . . . . . . . . . . . . . . . . . . . . . . . . . 2

0.2 Week 2: DML commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
0.3 Week 3: Basic SQL Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
0.4 Week 4: Functions and Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
0.5 Week 5: SQL JOINS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

1
 0.1 Week 1: Introduction to RDBMS and SQL
Topics:
Overview of RDBMS, Installing SQL environment, Data types, DDL queries

Practicals
1. Create a database for a Company.

2. Create tables Employees(emp no, birth date, first name, last name, gender,hire date), Depart-
ments(dept no, dept name), Dept emp(emp no, dept no, from date , to date), Dept manager(emp no,
dept no, from date , to date), titles and Salaries(emp no,salary, from date, to date) in Company
database.

3. Alter table Employees to add a column Address, and then drop it.
4. Drop the table Salaries from Company database.
5. Truncate all the records of a Table Dept manager.
6. Rename table Employees to New employees.

Implementation

1
2 -- Create database
3 DROP DATABASE IF EXISTS Company ;
4 CREATE DATABASE IF NOT EXISTS Company ;
5 USE Company ;
6
7 -- Create tables in Company Database
8 CREATE TABLE employees (
9 emp_no VARCHAR (5) NOT NULL ,
10 birth_date DATE NOT NULL ,
11 first_name VARCHAR (14) NOT NULL ,
12 last_name VARCHAR (16) NOT NULL ,
13 gender ENUM ( ’M ’ , ’F ’) NOT NULL ,
14 hire_date DATE NOT NULL ,
15 PRIMARY KEY ( emp_no )
16 );
17
18 CREATE TABLE departments (
19 dept_no CHAR (4) NOT NULL ,
20 dept_name VARCHAR (25) NOT NULL ,
21 PRIMARY KEY ( dept_no ) ,
22 UNIQUE KEY ( dept_name )
23 );
24
25 CREATE TABLE dept_manager (
26 emp_no INT NOT NULL ,
27 dept_no CHAR (4) NOT NULL ,
28 from_date DATE NOT NULL ,
29 to_date DATE NOT NULL ,
30 FOREIGN KEY ( emp_no ) REFERENCES employees ( emp_no ) ON DELETE CASCADE ,
31 FOREIGN KEY ( dept_no ) REFERENCES departments ( dept_no ) ON DELETE CASCADE ,
32 PRIMARY KEY ( emp_no , dept_no )
33 );
34
35 CREATE TABLE dept_emp (
36 emp_no INT NOT NULL ,
37 dept_no CHAR (4) NOT NULL ,
38 from_date DATE NOT NULL ,
39 to_date DATE NOT NULL ,
40 FOREIGN KEY ( emp_no ) REFERENCES employees ( emp_no ) ON DELETE CASCADE ,
41 FOREIGN KEY ( dept_no ) REFERENCES departments ( dept_no ) ON DELETE CASCADE ,
42 PRIMARY KEY ( emp_no , dept_no )
43 );
44

2
45 CREATE TABLE salaries (
46 emp_no INT NOT NULL ,
47 salary INT NOT NULL ,
48 from_date DATE NOT NULL ,
49 to_date DATE NOT NULL ,
50 FOREIGN KEY ( emp_no ) REFERENCES employees ( emp_no ) ON DELETE CASCADE ,
51 PRIMARY KEY ( emp_no , from_date )
52 );
53
54 -- Altering table schema
55 ALTER TABLE employees ADD COLUMN Address CHAR (30) ;
56 ALTER TABLE employees MODIFY COLUMN Address VARCHAR (40) NOT NULL ;
57 ALTER TABLE employees DROP COLUMN Address ;
58
59 -- Deleting the table
60 DROP TABLE salaries ;
61
62 -- Deleting all the records
63 TRUNCATE TABLE dept_manager ;
64
65 -- Renaming table
66 RENAME TABLE employees to New_Employees ;

0.2 Week 2: DML commands

Topics:
Insert, Update and Delete commands

Practicals:
• Inserting records in the table employees, departments, dept emp and salaries.

• Update the name of the employee in employees relation whose emp no=10000
• Delete the records of all the employees who have joined before a particular year.

Implementation

1
2 -- inserting records in a table
3
4 INSERT INTO ‘ employees ‘ VALUES
5 (10001 , ’1953 -09 -02 ’ , ’ Georgi ’ , ’ Facello ’ , ’M ’ , ’1986 -06 -26 ’) ,
6 (10002 , ’1964 -06 -02 ’ , ’ Bezalel ’ , ’ Simmel ’ , ’F ’ , ’1985 -11 -21 ’) ,
7 (10003 , ’1959 -12 -03 ’ , ’ Parto ’ , ’ Bamford ’ , ’M ’ , ’1986 -08 -28 ’) ,
8 (10004 , ’1954 -05 -01 ’ , ’ Chirstian ’ , ’ Koblick ’ , ’M ’ , ’1986 -12 -01 ’) ;
9
10 INSERT INTO ‘ departments ‘ VALUES
11 ( ’ d001 ’ , ’ Marketing ’) ,
12 ( ’ d002 ’ , ’ Finance ’) ,
13 ( ’ d003 ’ , ’ Human Resources ’) ,
14 ( ’ d004 ’ , ’ Production ’) ,
15 ( ’ d005 ’ , ’ Development ’) ,
16 ( ’ d006 ’ , ’ Quality Management ’) ,
17 ( ’ d007 ’ , ’ Sales ’) ,
18 ( ’ d008 ’ , ’ Research ’) ,
19 ( ’ d009 ’ , ’ Customer Service ’) ;
20
21 INSERT INTO ‘ dept_manager ‘ VALUES
22 (110022 , ’ d001 ’ , ’1985 -01 -01 ’ , ’1991 -10 -01 ’) ,
23 (110039 , ’ d001 ’ , ’1991 -10 -01 ’ , ’9999 -01 -01 ’) ,
24 (110085 , ’ d002 ’ , ’1985 -01 -01 ’ , ’1989 -12 -17 ’) ,
25 (110114 , ’ d002 ’ , ’1989 -12 -17 ’ , ’9999 -01 -01 ’) ,
26 (110183 , ’ d003 ’ , ’1985 -01 -01 ’ , ’1992 -03 -21 ’) ,
27 (110228 , ’ d003 ’ , ’1992 -03 -21 ’ , ’9999 -01 -01 ’) ,
28 (111939 , ’ d009 ’ , ’1996 -01 -03 ’ , ’9999 -01 -01 ’) ;
29

3
30 INSERT INTO ‘ dept_emp ‘ VALUES (10001 , ’ d005 ’ , ’1986 -06 -26 ’ , ’9999 -01 -01 ’) ,
31 (10002 , ’ d007 ’ , ’1996 -08 -03 ’ , ’9999 -01 -01 ’) ,
32 (10003 , ’ d004 ’ , ’1995 -12 -03 ’ , ’9999 -01 -01 ’) ,
33 (10004 , ’ d004 ’ , ’1986 -12 -01 ’ , ’9999 -01 -01 ’) ,
34 (10005 , ’ d003 ’ , ’1989 -09 -12 ’ , ’9999 -01 -01 ’) ,
35 (10006 , ’ d005 ’ , ’1990 -08 -05 ’ , ’9999 -01 -01 ’) ,
36 (10007 , ’ d008 ’ , ’1989 -02 -10 ’ , ’9999 -01 -01 ’) ,
37 (10008 , ’ d005 ’ , ’1998 -03 -11 ’ , ’2000 -07 -31 ’) ;
38
39 INSERT INTO ‘ salaries ‘ VALUES (10001 ,60117 , ’1986 -06 -26 ’ , ’1987 -06 -26 ’) ,
40 (10001 ,62102 , ’1987 -06 -26 ’ , ’1988 -06 -25 ’) ,
41 (10001 ,66074 , ’1988 -06 -25 ’ , ’1989 -06 -25 ’) ,
42 (10001 ,66596 , ’1989 -06 -25 ’ , ’1990 -06 -25 ’) ,
43 (10001 ,66961 , ’1990 -06 -25 ’ , ’1991 -06 -25 ’) ,
44 (10001 ,71046 , ’1991 -06 -25 ’ , ’1992 -06 -24 ’) ,
45 (10001 ,74333 , ’1992 -06 -24 ’ , ’1993 -06 -24 ’) ;
46
47 -- View all records in a table
48 SELECT * from employees ;
49 SELECT * from salaries ;
50 SELECT * from titles ;
51 SELECT * from departments ;
52 SELECT * from dept_emp ;
53 SELECT * from dept_manager ;
54
55 -- Updating the first_name of a particular employee
56 UPDATE employees SET first_name = ’ Avinash ’ WHERE emp_no = ’100000 ’;
57
58 -- Deleting a record based on a condition
59 DELETE FROM employees WHERE hire_date < ’1986 -01 -01 ’;

0.3 Week 3: Basic SQL Queries

Topics:
SELECT queries, WHERE clause, ORDER BY, DISTINCT

Practicals:
• List all records from salaries table

• select all the employeess from the employees table with unique date of births
• list all the employeess in the descending order of their salaries from salaries.
• List all the emp no with salary less than 45000

• List all the departments whose name ends with ’e’

• – list all the departments name from the departments table whose departments no lies between
d004 & d007.
• list all the departments name from the departments table whose departments no is d001, d003 or
d008

Implementation

1
2 -- General SQL SELECT Syntax
3
4 SELECT [ ALL | DISTINCT | TOP n | TOP n PERCENT ]
5 column1 , column2 , ... | *
6 [ AS alias_name ]
7 FROM table_name [ AS table_alias ]
8 [ JOIN table2 ON condition ]
9 [ JOIN table3 USING ( column ) ]
10 WHERE condition

4
11 GROUP BY column1 , column2 , ...
12 HAVING g ro up_ co nd i ti on
13 ORDER BY column1 [ ASC | DESC ] , column2 [ ASC | DESC ]
14 LIMIT number OFFSET start_row ;
15 FETCH FIRST n ROWS ONLY ;
16
17
18 -- List all records from salaries table
19 select * from salaries ;
20
21 -- select all the employeess from the employees table with unique date of births
22
23 SELECT DISTINCT birth_date , emp_no , first_name , last_name , gender , hire_date
24 FROM employees ;
25
26 -- List all the emp_no with salary < 45000
27 SELECT emp_no , salary from salaries where salary < 45000;
28
29 -- list all the employeess in the descending order of their salaries from salaries
30 SELECT * FROM salaries
31 ORDER BY salary DESC ;
32
33 -- List all the departments whose name ends with ’e ’
34 SELECT * from departments where dept_name like ’%e ’;
35
36 -- list all the departments name from the departments table whose departments no lies
between d004 & d007
37 SELECT dept_no , dept_name
38 FROM departments
39 WHERE dept_no between ’ d004 ’ and ’ d007 ’;
40
41 -- list all the departments name from the departments table whose departments no is d001
, d003 or d008
42 SELECT dept_no , dept_name
43 FROM departments
44 WHERE dept_no in ( ’ d001 ’ , ’ d003 ’ , ’ d008 ’) ;

0.4 Week 4: Functions and Expressions

Topics:
String, Numeric, Date, Aggregate Functions

Practicals:
• Apply Numeric functions(pi(),abs(), exp(), power(), sqrt(),round(), trunc())

• Apply upper(), lower(), reverse(), length() and concat() functions on string datavalues in a table.
• Calculate the age of each employee record using current date() and year() function.
• List all the Managers who have served for more than 5 years in a department

• Increment the salary of each employee by 10% of average previous salary

• Find total number of records in a table
• Calculate the min, max and Average salary of employees from salaries table.
• Count the number of entries of each employee in salaries table.

• Count the number of entries of each employee in salaries table and order the result from lowest to
highest.
• list all the female employeess whose age is greater than 50
• Finding all the employees who are ’Eligible’ or ’Not eligible’ based on a condition (whose age is
less than 50 years as ’Not eligibile’)

5
 Implementation

1 -- SQL Functions
2 -- Numeric functions ( pi () , abs () , exp () , power () , sqrt () , round () , trunc () )
3
4 SELECT pi () from dual ;
5 SELECT abs ( -211.258) FROM dual ;
6 SELECT exp (1) FROM dual ;
7 SELECT power (2 ,10) FROM dual ;
8 SELECT sqrt (625) FROM dual ;
9 SELECT round ( pi () ,4) FROM dual ;
10 SELECT truncate ( pi () ,4) from dual ;
11
12 -- String functions
13 SELECT UPPER ( dept_name ) FROM departments ;
14 SELECT REVERSE ( dept_name ) FROM departments ;
15 SELECT LENGTH ( dept_name ) FROM departments ;
16 SELECT CONCAT ( first_name , ’ ’ , last_name ) AS Name FROM employees ;
17
18 -- Selecting multiple columns from a Table
19 SELECT emp_no , CONCAT ( first_name , ’ ’ , last_name ) AS Name , YEAR ( birth_date ) AS
Birth_Year FROM employees ;
20
21 -- Calculate the age of each employee from date of birth
22 SELECT emp_no , YEAR ( current_date () ) - YEAR ( birth_date ) As Age from employees ;
23
24 -- List all the Managers who have served for more than 5 years in a department
25 SELECT *
26 FROM dept_manager
27 where year ( to_date ) - year ( from_date ) > 5;
28
29 -- increment the salary of each employee by 10% of average previous salary
30 SELECT emp_no , avg ( salary ) , ( avg ( salary ) *1.10) as Up dated_Sa lary FROM salaries group by
emp_no ;
31
32 -- Aggregate Functions :
33
34 -- Finding Total Number of Records in a table
35
36 SELECT COUNT (*) FROM employees ;
37
38 -- Finding Max , Min , Average salaries of employees
39
40 SELECT SUM ( salary ) FROM salaries ;
41
42 SELECT MIN ( salary ) FROM salaries ;
43
44 SELECT MAX ( salary ) FROM salaries ;
45
46 SELECT AVG ( salary ) FROM salaries ;
47
48 -- Count the number of entries of each employee in salaries table
49
50 SELECT emp_no , Count (*) N u m b e r _ o f _ E n t r i e s
51 FROM salaries
52 GROUP BY emp_no ;
53
54 -- Count the number of entries of each employee in salaries table and order the result
from lowest to highest
55
56 SELECT emp_no , Count (*) N u m b e r _ o f _ E n t r i e s
57 FROM salaries
58 GROUP BY emp_no
59 ORDER BY N u m b e r _ o f _ E n t r i e s ASC ;
60
61 -- Unlike the WHERE clause , HAVING can be used with aggregate functions .
62
63 SELECT emp_no , Count (*) N u m b e r _ o f _ E n t r i e s
64 FROM salaries
65 GROUP BY emp_no
66 HAVING N u m b e r _ o f _ E n t r i e s > 4
67 ORDER BY N u m b e r _ o f _ E n t r i e s ASC ;
68

6
69 -- list all the female employeess whose age is greater than 50
70 SELECT *
71 FROM employees
72 WHERE gender = ’F ’ and year ( current_date () ) - year ( birth_date ) >50;
73
74 -- Finding all the employees who are ’ Eligible ’ or ’ Not eligible ’ based on a condition (
whose age is less than 50 years as ’ Not eligibile ’)
75
76 SELECT emp_no , first_name , year ( current_date () ) - year ( birth_date ) as age , CASE
77 WHEN year ( current_date () ) - year ( birth_date ) <50 THEN ’ NOT ELLIGIBLE ’
78 ELSE ’ ELLIGIBLE ’
79 END Elligibility
80 FROM employees ;

0.5 Week 5: SQL JOINS

Topics:
Inner Joins, Left outer Joins, Right outer join, Full Outer Join, Self Join

Practical
JOIN is a method to combine (join) information from two tables. Write complex queries using joins,
subqueries, and aggregation:

1. Implementing Inner Join, left outer join, right outer join, full outer join and self join on example
tables.
2. Find the salarary of each employee in company database
3. Finding job title of all the employees
4. Finding the birthdate and hiredate of all the dept managers
5. (Joining Multiple Tables) List all the employees along with their department names
6. (Self Join) Create a table Managed By and List the names of All the Managers Assigned to Em-
ployees

Implementation

1 -- CREATE TABLE Left ( Roll_no , name ) and Right ( Roll_no , Department )

2
3 INSERT INTO Left VALUES
4 (1 , ’A ’) ,
5 (2 , ’B ’) ,
6 (3 , ’C ’) ,
7 (4 , ’A ’) ,
8 (5 , ’D ’) ,
9 (6 , ’E ’) ,
10 (7 , ’B ’) ;
11
12 INSERT INTO Right VALUES
13 (3 , ’CSE ’) ,
14 (4 , ’IT ’) ,
15 (5 , ’ Mech ’) ,
16 (9 , ’ECE ’) ;
17
18 -- cross join
19 SELECT *
20 FROM Left
21 JOIN Right ;
22
23 -- Equi join
24
25 SELECT Left . roll_no , Right . department
26 FROM Left

7
27 JOIN Right
28 ON Left . roll_no = Right . roll_no ;
29
30 -- left inner join
31
32 SELECT l . roll_no , r . dept
33 from Left l
34 LEFT JOIN Right r
35 on l . roll_no = r . roll_no ;
36
37 -- right inner join
38
39 SELECT l . roll_no , r . dept
40 from Left l
41 RIGHT JOIN Right r
42 on l . roll_no = r . roll_no ;
43
44 -- left outer join
45
46 SELECT *
47 FROM Left l
48 LEFT OUTER JOIN Right r
49 ON l . roll_no = r . roll_no ;
50
51 -- right outer join
52
53 SELECT *
54 FROM Left
55 RIGHT OUTER JOIN Right r
56 ON l . roll_no = r . roll_no ;
57
58 -- full outer join
59
60 SELECT *
61 FROM Left l
62 LEFT OUTER JOIN right r
63 ON l . roll_no = r . roll_no
64 UNION
65 SELECT *
66 FROM Left l
67 RIGHT OUTER JOIN Right r
68 ON l . roll_no = r . roll_no ;
69
70 -- SELF JOIN : Find the Names of all the group_leaders in student table .
71 -- Create a Table student ( Rollno , Name , group_leader )
72
73 INSERT INTO student VALUES
74 (1 , ’A ’ , 9) ,
75 (2 , ’B ’ , 9) ,
76 (3 , ’C ’ , 9) ,
77 (4 , ’A ’ , 6) ,
78 (5 , ’B ’ , 6) ,
79 (6 , ’D ’ , 6) ,
80 (7 , ’E ’ , 1) ,
81 (8 , ’F ’ , 1) ,
82 (9 , ’G ’ , 1) ;
83
84 SELECT DISTINCT A . name
85 FROM student AS A
86 JOIN student AS B
87 ON A . rollno = B . group_leader ;
88
89 -- alternative to self_join using nested query
90 SELECT name
91 FROM student
92 WHERE rollno IN ( SELECT DISTINCT group_leader FROM student ) ;
93
94 -- JOIN Queries from company database
95
96 -- 1. Find the salarary of each employee in company database
97 SELECT emp_no , first_name , last_name , salary , from_date , to_date
98 FROM employees
99 JOIN salaries

8
100 USING ( emp_no ) ;
101
102 -- 2. Finding the birthdate and hiredate of all the dept_managers
103
104 SELECT d . emp_no , e . birth_date , e . hire_date
105 FROM dept_manager d
106 JOIN employees e
107 USING ( emp_no ) ;
108
109 -- 3. Finding job Title of all the employees .
110
111 SELECT e . emp_no , t . title , concat ( e . first_name , ’ ’,e . last_name ) name , t . from_date , t . to_date
112 FROM titles t
113 JOIN employees e
114 USING ( emp_no ) ;
115
116 -- 4. ( Joining Multiple Tables ) List all the employees along with their department names
.
117
118 SELECT e . emp_no , concat ( e . first_name , ’ ’,e . last_name ) name , d . dept_name , de . from_date , de .
to_date
119 FROM employees e
120 JOIN dept_emp de
121 on de . emp_no = e . emp_no
122 JOIN departments d
123 on d . dept_no = de . dept_no
124 ORDER BY e . emp_no ;
125 );
126
127 -- 5 ( Self Join ) List the names of All the Managers Assigned to Employees .
128
129 -- Create a table Managed_by
130
131 CREATE TABLE ‘ company ‘. ‘ managed_by ‘ (
132 ‘ Emp_ID ‘ INT NOT NULL ,
133 ‘ Name ‘ VARCHAR (45) NOT NULL ,
134 ‘ Manag_Assigned ‘ VARCHAR (45) NULL ,
135 PRIMARY KEY ( ‘ Emp_ID ‘) ) ;
136
137 SELECT DISTINCT a . Name
138 FROM managed_by a JOIN managed_by b
139 ON a . Emp_id = b . Manag_A ssigned
140 ORDER By a . Name ;