Database Lab Manual
Academic Year: 2025
DEPARTMENT OF COMPUTER SCIENCE
COMSATS UNIVERSITY ISLAMABAD, SAHIWAL
2
LIST OF EXPERIMENTS
1. Data Definition, Table Creation, Constraints,
2. Insert, Select Commands, Update and Delete Commands.
3. Transaction Control:
4. Nested Queries and Join Queries
5. Views
6. High level programming language extensions (Control structures, Procedures
and Functions).
7. Front end Tools
8. Forms
9. Triggers
10. Menu Design
11. Reports.
12. Database Design and implementation (Mini Project).
3
4
CONTENTS
Page
S.
LIST OF EXPRIEMENTS No.
No
1 Data Definition Language Commands 5
2 Data Manipulation Language Commands 10
Data Control Language, Transfer Control
3 16
Language Commands
4 In Built Functions 19
5 Nested Queries And Join Queries 24
6 Set operators 29
7 Views 31
8 Control Structure 33
9 Procedure and Function 43
10 Trigger 52
11 Front End Tools 56
12 Form 62
13 Menu Design 64
14 Report Generation 66
Database Design And Implementation
15 68
Payroll Processing
Beyond the Syllabus
16 Banking System (mini Project) 72
5
Exercise Number: 3
Title of the Exercise : DATA CONTROL LANGUAGE (DCL),
TRANSACTION CONTROL LANGUAGE (TCL) COMMANDS
Date of the Exercise :
OBJECTIVE (AIM) OF THE EXPERIMENT
To study the various data language commands (DCL, TCL) and implements them on the
database.
FACILITIES REQUIRED AND PROCEDURE
a) Facilities required to do the experiment:
Sl.No. Facilities required Quantity
1 System 1
2 Operating System Windows 10 or above
3 Front end JAVA/Node.js
4 Back end SSMS v19.3
b) Procedure for doing the experiment:
Step
Details of the step
no.
DCL COMMAND
1 The DCL language is used for controlling the access to the table and hence securing the
database. DCL is used to provide certain privileges to a particular user. Privileges are
rights to be allocated.
2 The privilege commands are namely, Grant and Revoke
The various privileges that can be granted or revoked are,
3
Select Insert Delete Update References ExecuteAll
GRANT COMMAND: It is used to create users and grant access to the database. It
4 requires database administrator (DBA) privilege, except that a user can change
their password. A user can grant access to their database objects to other users.
5 REVOKE COMMAND: Using this command , the DBA can revoke the granted database
privileges from the user.
TCL COMMAND
6 COMMIT: command is used to save the Records.
ROLLBACK: command is used to undo the Records.
SAVE POINT command is used to undo the Records in a particular transaction.
c)SQL Commands
DCL Commands
GRANT
COMMAND
Grant < database_priv [database_priv…..] > to <user_name> identified by
<password> [,<password…..];
Grant <object_priv> | All on <object> to <user | public> [ With Grant Option ];
REVOKE COMMAND
Revoke <database_priv> from <user [, user ] >;
Revoke <object_priv> on <object> from < user | public >;
<database_priv> -- Specifies the system level priveleges to be granted to the users or roles.
This includes create / alter / delete any object of the system.
<object_priv> -- Specifies the actions such as alter / delete / insert / references / execute / select
/ update for tables.
<all> -- Indicates all the priveleges.
6
[ With Grant Option ] – Allows the recipient user to give further grants on the objects.
The priveleges can be granted to different users by specifying their names or to all users by
using the “Public” option.
TCL COMMANDS:
Syntax:
SAVEPOINT: SAVEPOINT <SAVE POINT NAME>;
ROLLBACK: ROLL BACK <SAVE POINT NAME>;
COMMIT: Commit;
d) Queries:
Tables Used:
Consider the following tables namely “DEPARTMENTS” and “EMPLOYEES”
Their schemas are as follows ,
Departments ( dept _no , dept_ name , dept_location );
Employees ( emp_id , emp_name , emp_salary );
Q1: Develop a query to grant all privileges of employees table into departments table
Ans:
SQL> Grant all on employees to departments;
Grant succeeded.
Q2: Develop a query to grant some privileges of employees table into departments table
Ans:
SQL> Grant select, update , insert on departments to departments with grant
option; Grant succeeded.
Q3: Develop a query to revoke all privileges of employees table from departments table
Ans:
SQL> Revoke all on employees from departments;
Revoke succeeded.
Q4: Develop a query to revoke some privileges of employees table from departments table
Ans:
SQL> Revoke select, update , insert on departments from departments;
Revoke succeeded.
Q5: Write a query to implement the save point
Ans:
SQL> SAVEPOINT S1;
Savepoint created.
SQL> select * from employees;
rollback to savepoint s1;
SQL> INSERT INTO EMPLOYEES VALUES(14,'Raheela
Shahzadi','Lab Engineer',1,40000); 1 row created.
7
SQL> select * from
employees;
Q6: Write a query to implement the rollback
Ans:
SQL> rollback s1;
Select * from
employees;
Q6: Write a query to implement the commit
Ans:
SQL> COMMIT;
Commit complete.
e)Result
The DCL,TCL commands was performed successfully and executed.
QUESTIONS AND ANSWERS
1. Define DCL?
The DCL language is used for controlling the access to the table and hence securing
the database. DCL is used to provide certain privileges to a particular user. Privileges are
rights to be allocated.
2. List the DCL commands used in data bases
The privilege commands are namely, Grant and Revoke
3. What type of privileges can be granted?
The various privileges that can be granted or revoked are,
Select
8
Insert
Delete
Update
References
Execute
All
4. Write the syntax for grant command
Grant < database_priv [database_priv…..] > to <user_name> identified by <password>
[,<password…..];
Grant <object_priv> | All on <object> to <user | public> [ With Grant Option ];
5. What are TCL commands?
*Commit *Rollback *save point
