SRM TRP ENGINEERING COLLEGE

IRUNGALUR
TIRUCHIRAPALLI-621 105

DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

CS3481-DATABASE MANAGEMENT SYSTEMS LABORATORY

(Anna University CHENNAI)

Regulation 2021

LAB MANUAL

ISSUE- B

REVISION – 02

Original Date of Issue: 01/02/2023 Rev. No./Date: 18/02/2025

Copy Number: 01 Master Copy

Prepared by: Reviewed by : Approved by:

[Link] ,
Assistant Professor
HOD/CSE PRINCIPAL
 SRM TRP ENGINEERING COLLEGE

IRUNGALUR, MANACHANALLUR TALUK, TIRUCHIRAPALLI– 621 105

DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

CS3481 – DATABASE MANAGEMENT SYSTEMS LABORATARY

 CONTENTS

Exp. Date Title Page Marks Signature

No. 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 commands.
2 Create a set of tables, add foreign key constraints and
incorporate referentialintegrity
3 Query the database tables using different ‘where’ clause
conditions and alsoimplement aggregate functions.
4 Query the database tables and explore sub-queries and
simple join operations.
5 Query the database tables and explore sub-queries, natural,
eqi and outer joinoperations
6 Write user defined functions and stored procedures in SQL

7 Execute complex transactions and realize DCL and TCL

commands
8 write SQL TRIGGERS for insert, delete, and update
operation in database table
9 Create View and index for database tables with a large
number of records.
10 Create an XML database and validate it using XML schema

11 Create Document, column and graph based data using

NOSQL database tools.
12 Develop a simple GUI based database application and
incorporate all the above mentioned features

CONTENT BEYOND SYLLABUS

13 Implementing a Fire-Store Database for Real-Time
Messaging.
14 Implementing Role Based Access Control(RBAC)in
MYSQL
 SRM TRP Engineering College
Vision of the Institute

To carve the youth as dynamic, competent, valued, and knowledgeable Technocrats through
research, innovation, and entrepreneurial development for accomplishing the global
expectations.
Mission of the Institute

M1: To inculcate academic excellence in engineering education to create talented professionals

M2: To promote research in basic sciences and applied engineering among faculty and
students to fulfill the societal expectations.
M3: To the holistic development of students through meaningful interaction with industry and
academia.
M4: To foster the students on par with sustainable development goals thereby contributing to the
process of nation building.
M5: To nurture and retain conducive lifelong learning environment towards professional
excellence.
Vision of the Department

To be recognized as Centre of Excellence for innovation and research in computer science and
engineering through the futuristic technologies by developing technocrats with ethical values to
serve the society at global level.

Mission of the Department

M1: To develop quality and technically competent computer professionals through excellence in
academics.
M2: To encourage the faculty and students towards research and development with advanced
tools and technologies.
M3: To enhance industry institute interaction to build a strong technical expertise among the
students.
M4: To inculcate leadership skills with ethical behaviors and social consciousness within the
students.
M5: To nurture professional empowerment among students through continuous Learning.
Program Educational Objectives (PEO's)

The graduate of Computer Science and Engineering will have,

PEO1: Ability to analyze and get solutions in the field of Computer Science and Engineering
through application of fundamental knowledge of Mathematics, Science and Electronics
(Preparation).

PEO2: Innovative ideas, methods and techniques thereby rendering expertise to the industrial and
societal needs in an effective manner and will be a competent computer/software engineer (Core
Competency).

PEO3: Good and broad knowledge with interpersonal skills so as to comprehend, analyze,
design and create novel products and solutions for real-time applications (Breadth).

PEO4: Professional with ethical values to develop leadership, effective communication skills and
teamwork to excel in career. (Professionalism)

PEO5: Strive to learn continuously and update their knowledge in the specific fields of computer
science & engineering for societal growth. (Learning environment).
Program Outcomes (PO'S)
PO1: Engineering knowledge: Apply the basic knowledge of science, mathematics, and
engineering fundamentals in the field of Computer Science and Engineering to solve complex
engineering problems.

PO2: Problem analysis: Ability to use basic principles of mathematics, natural sciences, and
engineering sciences to Identify, formulate, review research literature, and analyze Computer
Science and engineering problems.

PO3: Design/development of solutions: Ability to design solutions for complex Computer

Science and engineering problems and basic design system to meet the desired needs within
realistic constraints such as manufacturability, durability, reliability, sustainability and economy
with appropriate consideration for the public health, safety, cultural, societal, and
environmental considerations.
PO4: Conduct investigations of complex problems: Ability to execute the experimental
activities using research-based knowledge and methods including analyze, interpret the data and
results with valid conclusion.
PO5: Modern tool usage: Ability to use state of the art of techniques, skills, and modern
engineering tools necessary for engineering practice to satisfy the needs of the society with an
understanding of the limitations.

PO6: The Engineer and Society: Ability to apply reasoning informed by the contextual
knowledge to assess the impact of Computer Science and engineering solutions in legal, health,
cultural, safety and societal context and the consequent responsibilities relevant to the
professional engineering practice.

PO7: Environment and sustainability: Ability to understand professional responsibility and

accountability to demonstrate the need for sustainable development globally in Computer Science
domain with consideration of environmental effect.

PO8: Ethics: Ability to understand and apply ethical principles and commitment to address the
professional ethical responsibilities of an engineer.

PO9: Individual and team work: Ability to function efficiently as an individual or as a

group member or leader in a team in multidisciplinary environment.

PO10: Communication: Ability to communicate, comprehend and present effectively with

engineering community and the society at large on complex engineering activities by receiving
clear instructions for preparing effective reports, design documentation and presentations.

PO11: Project management and finance: Ability to acquire and demonstrate the knowledge
of contemporary issues related to finance and managerial skills in one’s own work, as a
member and leader in a team, to manage projects and in multidisciplinary environments.
PO12: Life-long learning: Ability to recognize and adapt to the emerging field of application in
engineering and technology by developing self-confidence for lifelong learning process.

Program Specific Outcome (PSO's)

The graduates of Bachelor of Engineering in Computer Science and Engineering Programme will
be able to:

PSO1:Use Data structures, Data management, Networking, System software, Data science
with high end programming skills to design and implement automation in various domains of
emerging technologies.
PSO2: Apply engineering knowledge in project development with the end products and
services in the field of hardware and software platform to accomplish industry expectations.
CS3481 DATABASE MANAGEMENT SYSTEMS LABORATORY L T P C
0 0 3 1.5

COURSE OBJECTIVES:

 To learn and implement important commands in SQL.
 To learn the usage of nested and joint queries.
 To understand functions, procedures and procedural extensions of databases.
 To understand design and implementation of typical database applications.
 To be familiar with the use of a front end tool for GUI based application development.

LIST OF EXPERIMENTS:

1. Create a database table, add constraints (primary key, unique, check, Not null), insert rows, update and delete
rows using SQL DDL and DML commands.
2. Create a set of tables, add foreign key constraints and incorporate referential integrity.
3. Query the database tables using different ‘where’ clause conditions and also implement aggregate functions.
4. Query the database tables and explore sub queries and simple join operations.
5. Query the database tables and explore natural, equi and outer joins.
6. Write user defined functions and stored procedures in SQL.
7. Execute complex transactions and realize DCL and TCL commands.
8. Write SQL Triggers for insert, delete, and update operations in a database table.
9. Create View and index for database tables with a large number of records.
10. Create an XML database and validate it using XML schema.
11. Create Document, column and graph based data using NOSQL database tools.
12. Develop a simple GUI based database application and incorporate all the above-mentioned features
13. Case Study using any of the real life database applications from the following list
a) Inventory Management for a EMart Grocery Shop
b) Society Financial Management
c) Cop Friendly App – Eseva
d) Property Management – eMall
e) Star Small and Medium Banking and Finance
● Build Entity Model diagram. The diagram should align with the business and functional goals stated in the
application.
● Apply Normalization rules in designing the tables in scope.
● Prepared applicable views, triggers (for auditing purposes), functions for enabling enterprise grade features.
● Build PL SQL / Stored Procedures for Complex Functionalities, ex EOD Batch Processing for calculating the
EMI for Gold Loan for each eligible Customer.
● Ability to showcase ACID Properties with sample queries with appropriate settings
COURSE OUTCOMES:
At the end of this course, the students will be able to:
CO1: Create databases with different types of key constraints.
CO2: Construct simple and complex SQL queries using DML and DCL commands.
CO3: Use advanced features such as stored procedures and triggers and incorporate in GUI based application
development.
CO4: Create an XML database and validate with meta-data (XML schema).
CO5: Create and manipulate data using NOSQL database.

CO-PO matrices
Course code PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO12
CCS352.1 3 3 3 3 - - - - 3 1 3 2
CCS352.2 2 2 3 2 2 - - - 1 2 3 3
CCS352.3 3 3 2 1 1 - - - 1 1 1 3
CCS352.4 1 3 3 3 1 - - - 1 1 3 2
CCS352.5 3 2 1 1 1 - - - 2 2 3 1
CCS352 2 3 2 2 1 - - - 2 1 3 2
CO-PSO matrices
Course code PSO1 PSO2 PSO3
CCS352.1 2 3 2
CCS352.2 2 1 2
CCS352.3 2 3 3
CCS352.4 3 1 3
CCS352.5 3 1 2
CCS352 2 2 2

1 Slight 2 Moderate 3 Substantial

 [Link] 01 Create a database table, add constraints (primary key, unique, check, Not null),
insert rows, update and delete rows using SQL DDL and DML commands.

AIM:
To create a database table, add constraints (primary key, unique, check, Not null), insert
rows, update and delete rows using SQL DDL and DML commands.
TABLE CREATION:

SQL> create table employee(empid number(5),empname varchar2(10),dept varchar2(20)

,dob date,salary number(6));

Table created.

DESCRIBING TABLE:

SQL> desc employee

Name Null? Type

EMPID NUMBER(5)

EMPNAME VARCHAR2(10)

DEPT VARCHAR2(20)

DOB DATE

SALARY NUMBER(6)

ALTERING A TABLE

ADDING A NEW CLOUMN:

SQL> alter table employee add dateofjoining date;

Table altered.

DESCRIBING A TABLE:

SQL> desc employee;

Name Null? Type

EMPID NUMBER(5)
EMPNAME VARCHAR2(10)

DEPT VARCHAR2(20)

DOB DATE

SALARY NUMBER(6)

DATEOFJOINING DATE

MODIFY:

SQL> alter table employee modify empname varchar2(15);

Table altered.

SQL> desc employee;

Name Null? Type

EMPID NUMBER(5)

EMPNAME VARCHAR2(15)

DEPT VARCHAR2(20)

DOB DATE

SALARY NUMBER(6)

DATEOFJOINING DATE

DROPING A CLOUMN:

SQL> alter table employee drop column dateofjoining;

Table altered.

DESCRIBING A TABLE:
SQL> desc employee;

Name Null? Type

EMPID NUMBER(5)

EMPNAME VARCHAR2(15)

DEPT VARCHAR2(20)

DOB DATE
SALARY NUMBER(6)

TRUNCATE:

SQL> truncate table employee;

Table truncated.

RENAME:

SQL> rename employee to emp;

Table renamed.

INSERT:

ONETIME INSERTION:

SQL> insert into emp values(1181,'harini','production','16-jun-1999',30000);

1 row created.

SELECT:

SQL> select * from emp;

EMPID EMPNAME DEPT DOB SALARY

1181 harini production 16-JUN-99 30000

PARTICULAR COLUMN INSERTION:

SQL> insert into emp(empid) values(1021);

1 row created.

SELECT:

SQL> select * from emp;

EMPID EMPNAME DEPT DOB SALARY

1181 harini production 16-JUN-99 30000

1021

RUNTIME INSERTION:

SQL> insert into emp values(&empid,'&empname','&dept','&dob',&salary);

Enter value for empid: 1112

Enter value for empname: krithikaa

Enter value for dept: marketing

Enter value for dob: 20-dec-1999

Enter value for salary: 50000

old 1: insert into emp values(&empid,'&empname','&dept','&dob',&salary)

new 1: insert into emp values(1112,'krithikaa','marketing','20-dec-1999',50000)

1 row created.

SELECT:

SQL>SQL> select * from emp;

EMPID EMPNAME DEPT DOB SALARY

1181 harini production 16-JUN-99 30000

1021

1112 krithikaa marketing 20-DEC-99 50000

UPDATE:

SQL> update emp set salary=50000;

3 rows updated.

SELECT:

SQL> select * from emp;

EMPID EMPNAME DEPT DOB SALARY

1181 harini production 16-JUN-99 50000

1021 50000

1112 krithikaa marketing 20-DEC-99 50000

DELETE:

SQL> delete from emp where empid=1021;

1 row deleted.
SELECT:

SQL> select * from emp;

EMPID EMPNAME DEPT DOB SALARY

1181 harini production 16-JUN-99 50000

1112 krithikaa marketing 20-DEC-99 50000

SQL> insert into emp values(1232,'aruna','design','03-sep-1999',65000);

1 row created.

SQL> select * from emp;

EMPID EMPNAME DEPT DOB SALARY

1181 harini production 16-JUN-99 50000

1232 aruna design 03-SEP-99 65000

1112 krithikaa marketing 20-DEC-99 50000

PRIMARY KEY:

SQL> create table stock3(itemno number(5)primary key ,itemname varchar2(10));

Table created.

SQL> insert into stock3 values('','pendrive');

insert into stock3 values('','pendrive')

ERROR at line 1:

ORA-01400: cannot insert NULL into ("CSE132"."STOCK3"."ITEMNO")

SQL> insert into stock3 values('3','cd');

1 row created.

SQL> insert into stock3 values('3','dvd');

insert into stock3 values('3','dvd')

ERROR at line 1:

ORA-00001: unique constraint (CSE132.SYS_C0012551) violated

FOREIGN KEY:
SQL> create table stock4(itemno number(5)references stock3(itemno),price number(5));

Table created.

SQL> insert into stock4 values(3,100);

1 row created.

SQL> select * from stock4;

ITEMNO PRICE

3 100

SQL> insert into stock4 values(2,100);

insert into stock4 values(2,100)

ERROR at line 1:

ORA-02291: integrity constraint (CSE132.SYS_C0012562) violated - parent key notfound

NOT NULL CONSTRAIN:

SQL> create table stud(rollno number(5) not null,studname varchar2(10),dob date,

joiningyear number(5),dept varchar2(5));

Table created.

SQL> insert into stud values(101,'akash','21-jul-1999',2017,'cse');

1 row created.

SQL> insert into stud values('','ram','22-aug-1999',2017,'eee');

insert into stud values('','ram','22-aug-1999',2017,'eee')

ERROR at line 1:

ORA-01400: cannot insert NULL into ("CSE132"."STUD"."ROLLNO")

DEFAULT CONSTRAIN:

SQL> create table stud3(rollno number(5),studname varchar2(10),joiningyear numbe

r(5)default10,dept varchar2(5));

Table created.

SQL> insert into stud3(rollno,studname,dept)values(108,'divya','ece');

1 row created.

SQL> insert into stud3(rollno,studname,dept)values(117,'ramya','eee');

1 row created.

SQL> select * from stud3;

ROLLNO STUDNAME JOININGYEAR DEPT

108 divya 10 ece

117 ramya 10 eee

RESULT:

Thus a database table with added constraints (primary key, unique, check, Not null), insert rows,
update and delete rows using SQL DDL and DML commands is created successfully.
 Ex no 02 Create a set of tables, add foreign key constraints and incorporate referential
integrity

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

PRIMARY KEY:

SQL> create table stock3(itemno number(5)primary key ,itemname varchar2(10));

Table created.

SQL> insert into stock3 values('','pendrive');

insert into stock3 values('','pendrive')

ERROR at line 1:

ORA-01400: cannot insert NULL into ("CSE132"."STOCK3"."ITEMNO")

SQL> insert into stock3 values('3','cd');

1 row created.

SQL> insert into stock3 values('3','dvd');

insert into stock3 values('3','dvd')

ERROR at line 1:

ORA-00001: unique constraint (CSE132.SYS_C0012551) violated

FOREIGN KEY:

SQL> create table stock4(itemno number(5)references stock3(itemno),price number(5));

Table created.

SQL> insert into stock4 values(3,100);

1 row created.

SQL> select * from stock4;

ITEMNO PRICE
 3 100

SQL> insert into stock4 values(2,100);

insert into stock4 values(2,100)

ERROR at line 1:

ORA-02291: integrity constraint (CSE132.SYS_C0012562) violated - parent key notfound

NOT NULL CONSTRAIN:

SQL> create table stud(rollno number(5) not null,studname varchar2(10),dob date,

joiningyear number(5),dept varchar2(5));

Table created.

SQL> insert into stud values(101,'akash','21-jul-1999',2017,'cse');

1 row created.

SQL> insert into stud values('','ram','22-aug-1999',2017,'eee');

insert into stud values('','ram','22-aug-1999',2017,'eee')

ERROR at line 1:

ORA-01400: cannot insert NULL into ("CSE132"."STUD"."ROLLNO")

DEFAULT CONSTRAIN:

SQL> create table stud3(rollno number(5),studname varchar2(10),joiningyear numbe

r(5)default10,dept varchar2(5));

Table created.

SQL> insert into stud3(rollno,studname,dept)values(108,'divya','ece');

1 row created.

SQL> insert into stud3(rollno,studname,dept)values(117,'ramya','eee');

1 row created.

SQL> select * from stud3;

ROLLNO STUDNAME JOININGYEAR DEPT

108 divya 10 ece

 117 ramya 10 eee

CHECK CONSTRAIN:

SQL> create table stud4(rollno number(5) not null,studname varchar2(10),joiningyear number(5)default 10,dept
varchar2(5),age number(5)check (age>17));

Table created.

SQL> insert into stud4(rollno,studname,dept,age)values(170,'sivani','eee',17);

SQL> insert into stud4(rollno,studname,dept,age)values(140,'shruthi','eee',19);

insert into stud4(rollno,studname,dept,age)values(140,'shruthi','eee',16)

ERROR at line 1:

ORA-02290: check constraint (CSE132.SYS_C0012437) violated

UNIQUE CONSTRAIN:

SQL> create table stock1(itemno number(5)unique,itemname varchar2(10)not null,qu

quantity number(5));

Table created.

SQL> insert into stock1 values(111,'pendrive',15);

1 row created.

SQL> select * from stock1;

ITEMNO ITEMNAME QUANTITY

111 pendrive 15

SQL> insert into stock1 values(111,'cd',20);

insert into stock1 values(111,'cd',20)

ERROR at line 1:

ORA-00001: unique constraint (CSE132.SYS_C0012501) violated

RESULT:

Thus a set of tables with added foreign key constraints and incorporate referential integrity is
created successfully.
 [Link] 3 Query the database tables using different ‘where’ clause conditions and also
implement aggregate functions.

AIM:

To Query the database tables using different ‘where’ clause conditions and also implement aggregate functions.

SQL Aggregate Functions

 SQL aggregation function is used to perform the calculations on multiple rows of a single column of a
table. It returns a single value.
 It is also used to summarize the data.

Types of SQL Aggregation Function

1. COUNT
FUNCTION

COUNT function is used to Count the number of rows in a database table. It can work on both numeric and non-
numeric data types.

COUNT function uses the COUNT(*) that returns the count of all the rows in a specified table. COUNT(*)
considers duplicate and Null.

Syntax

COUNT(*) or

COUNT( [ALL|DISTINCT] expression )

Sample table: PRODUCT_MAST

PRODUCT COMPANY QTY RATE COST

Item1 Com1 2 10 20

Item2 Com2 3 25 75

Item3 Com1 2 30 60

Item4 Com3 5 10 50

Item5 Com2 2 20 40

Item6 Com1 3 25 75

Item7 Com1 5 30 150

Item8 Com1 3 10 30

Item9 Com2 2 25 50

Item10 Com3 4 30 120

Example: COUNT()

SELECT COUNT(*) FROM PRODUCT_MAST;

Output:

10

Example: COUNT with WHERE

SELECT COUNT(*) FROM PRODUCT_MAST WHERE RATE>=20;

Output:

Example: COUNT() with DISTINCT

SELECT COUNT(DISTINCT COMPANY) FROM PRODUCT_MAST;

Output:

Example: COUNT() with GROUP BY

SELECT COMPANY, COUNT(*) FROM PRODUCT_MAST GROUP BY COMPANY;

Output:

Com1 5

Com2 3

Com3 2

Example: COUNT() with HAVING

SELECT COMPANY, COUNT(*) FROM PRODUCT_MAST GROUP BY COMPANY HAVING

COUNT(*)>2;

Output:

Com1 5

Com2 3

2. SUM FUNCTION

Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.

Syntax

SUM()

or

SUM( [ALL|DISTINCT] expression )

Example: SUM()

SELECT SUM(COST) FROM PRODUCT_MAST;

Output:

670

Example: SUM() with WHERE

SELECT SUM(COST) FROM PRODUCT_MAST WHERE QTY>3;

Output:

320

Example: SUM() with GROUP BY

SELECT SUM(COST) FROM PRODUCT_MAST WHERE QTY>3 GROUP BY COMPANY;

Output:

Com1 150
Com2 170

Example: SUM() with HAVING

SELECT COMPANY, SUM(COST) FROM PRODUCT_MAST GROUP BY COMPANY

HAVING SUM(COST)>=170;

Output:

Com1 335

Com3 170

3. AVG Function

The AVG function is used to calculate the average value of the numeric type. AVG function returns the average of
all non-Null values.

Syntax

AVG() or

AVG( [ALL|DISTINCT] expression )

Example:

SELECT AVG(COST) FROM PRODUCT_MAST;

Output:

67.00

4. MAX FUNCTION

MAX function is used to find the maximum value of a certain column. This function determines the largest value
of all selected values of a column.

Syntax

MAX() or

MAX( [ALL|DISTINCT] expression )

Example:

SELECT MAX(RATE) FROM PRODUCT_MAST;

30

5. MIN FUNCTION
MIN function is used to find the minimum value of a certain column. This function determines the smallest value
of all selected values of a column.

Syntax

MIN() or

MIN( [ALL|DISTINCT] expression )

Example:

SELECT MIN(RATE) FROM PRODUCT_MAST;

Output:

10

RESULT:

Thus Query the database tables using different ‘where’ clause conditions and also
implement aggregate functions.
[Link] 4 Query the database tables and explore sub-queries and simple join operations.

AIM:

To Query the database tables and explore sub-queries and simple join operations.

SQL JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Let's look at a selection from the "Orders" table:

OrderID CustomerID OrderDate

10308 2 1996-09-18

10309 37 1996-09-19

10310 77 1996-09-20

Then, look at a selection from the "Customers" table:

CustomerID CustomerName ContactName Country

1 Alfreds Futterkiste Maria Anders Germany

2 Ana Trujillo Emparedados Ana Trujillo Mexico

3 Antonio Moreno Taquería Antonio Moreno Mexico

Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table.
The relationship between the two tables above is the "CustomerID" column.

Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have
matching values in both tables:

Example
SELECT [Link], [Link], [Link] FROM Orders INNER JOIN Customers
ON [Link]=[Link];
CustomerName OrderDate
OrderID
10308 Ana Trujillo Emparedados 9/18/1996
10365 Antonio Moreno Taquería 11/27/1996
10383 Around the Horn 12/16/1996
10355 Around the Horn 11/15/1996
10278 Berglunds snabbköp 8/12/1996

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:
(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

SQL INNER JOIN Keyword

The INNER JOIN keyword selects records that have matching values in both tables.

INNER JOIN Syntax

SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

Demo Database
 In this tutorial we will use the well-known Northwind sample database.
 Below is a selection from the "Orders" table:

OrderID CustomerID EmployeeID OrderDate ShipperID

10308 2 7 1996-09-18 3

10309 37 3 1996-09-19 1

10310 77 8 1996-09-20 2

And a selection from the "Customers" table:

CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Maria Anders Obere Str.57 Berlin 12209 Germany

2 Ana Trujillo Emparedados Ana Trujillo [Link] la 05021 Mexico

3 Antonio Moreno Taquería Antonio Moreno Mataderos 05023 Mexico

SQL INNER JOIN

The following SQL statement selects all orders with customer information:

Example

SELECT [Link], [Link] FROM Orders INNER JOIN Customers ON

[Link] = [Link];

JOIN Three Tables

The following SQL statement selects all orders with customer and shipper information:

Example

SELECT [Link], [Link], [Link] FROM ((Orders

INNER JOIN Customers ON [Link] = [Link]) INNER JOIN Shippers ON

[Link] = [Link]);

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right
table (table2). The result is 0 records from the right side, if there is no match.

LEFT JOIN Syntax

SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Customers" table:

CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Maria Anders Obere Str. 12209 Germany

2 Ana Trujillo Emparedados Ana Trujillo Avda. 05021 Mexico

3 Antonio Moreno Taquería Antonio Moreno Mataderos 05023 Mexico

And a selection from the "Orders" table:

OrderID CustomerID EmployeeID OrderDate ShipperID

10308 2 7 1996-09-18 3

10309 37 3 1996-09-19 1

10310 77 8 1996-09-20 2

SQL LEFT JOIN

The following SQL statement will select all customers, and any orders they might have:

Example

SELECT [Link], [Link] FROM Customers LEFT JOIN Orders ON

[Link] = [Link] ORDER BY [Link];

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left
table (table1). The result is 0 records from the left side, if there is no match.

RIGHT JOIN Syntax

SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Orders" table:

OrderID CustomerID EmployeeID OrderDate ShipperID

10308 2 7 1996-09-18 3

10309 37 3 1996-09-19 1

10310 77 8 1996-09-20 2

And a selection from the "Employees" table:

EmployeeID LastName FirstName BirthDate Photo

1 Davolio Nancy 12/8/1968 [Link]

2 Fuller Andrew 2/19/1952 [Link]

3 Leverling Janet 8/30/1963 [Link]

SQL RIGHT JOIN

The following SQL statement will return all employees, and any orders they might have placed:

Example

SELECT [Link], [Link], [Link] FROM Orders RIGHT JOIN Employees

ON [Link] = [Link] ORDER BY [Link];

SQL FULL OUTER JOIN Keyword

The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table
records.

Tip: FULL OUTER JOIN and FULL JOIN are the same.

FULL OUTER JOIN Syntax

SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name =

table2.column_name WHERE condition;

Note: FULL OUTER JOIN can potentially return very large result-sets!

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Customers" table:

CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Maria Anders Obere Berlin 12209 Germany

2 Ana Trujillo Emparedados Ana Trujillo Avda. 05021 Mexico

3 Antonio Moreno Taquería Antonio Moreno Mataderos 05023 Mexico

And a selection from the "Orders" table:

OrderID CustomerID EmployeeID OrderDate ShipperID

10308 2 7 1996-09-18 3

10309 37 3 1996-09-19 1

10310 77 8 1996-09-20 2

SQL FULL OUTER JOIN Example

The following SQL statement selects all customers, and all orders:

SELECT [Link], [Link] FROM Customers FULL OUTER JOIN Orders ON

[Link]=[Link] ORDER BY [Link];

A selection from the result set may look like this:

CustomerName OrderID

Null 10309

Null 10310

Alfreds Futterkiste Null

Ana Trujillo Emparedados 10308

Antonio Moreno Taquería Null

RESULT:

Thus the database tables and explore sub-queries and simple join operations are queried
successfully.
[Link]:- 5 Query the database tables and explore sub-queries , natural, eqi and outer join
operations.

AIM:

To Query the database tables and explore sub-queries , natural, eqi and outer join operations.

SET OPERATORS

Table1: STUDENT

ROLLNO NAME M1 M2 M3

1 XXX 90 60 90

2 AAA 80 70 50

3 BBB 90 80 50

Table2: STUD

ROLLNO NAME M1 M2 M3

1 XXX 90 60 90

2 AAA 80 70 50

4 CCC 80 80 60

SQL>select * from student union select * from stud;

1. UNION

OUTPUT

ROLLNO

NAME

M1

M2

M3

1 XXX 90 60 90

2 AAA 80 70 50
3 BBB 90 80 50

4 CCC 80 80 60

2. UNION ALL

SQL>select * from student unionall select * from stud;

OUTPUT

ROLLNO NAME M1 M2 M3

1 XXX 90 60 90

2 AAA 80 70 50

3 BBB 90 80 50

1 XXX 90 60 90

2 AAA 80 70 50

4 CCC 80 80 60

3. INTERSECT

SQL>select * from student intersect select * from stud;

OUTPUT

ROLLNO NAMEM1 M2 M3

1 XXX 90 60 90

2 AAA 80 70 50

4. MINUS

SQL>select * from student minus select * from stud;

OUTPUT

ROLLNO NAME M1 M2 M3

3 BBB 90 80 50

NESTD SUBQUERIE IN:

SYNTAX:

select * from table1 where in(select attribute from table2);

DESCRIPTION:

The IN connective test for set membership where set is a collection of values produced by a select clause.

NOT IN:

SYNTAX:

select * from table1 where notin(select attribute from table2);

DESCRIPTION:

The NOTIN connective tests for set membership where set is a collection of values produced by a select clause.

ANY/SOME:

SYNTAX:

select * from table1 where attribute > some/any(select attribute from table name(condition));

DESCRIPTION:

The some/any keyword require that atleast one pair compiler, if denotes that the select condition TRUE if the
comparision is TRUE for atlest one of the values that is required.

ALL:

SYNTAX:

select * from table1 where attribute > ALL(select attribute from table where(condition));

DESCRIPTION:

The ALL keyword require that all values from the subselect comply with the comparison [Link] ALL
keyword specifies that the select condition TRUE if the comparision is TRUE for everyvalues that the sub query
returns.

Examples:

SQL> select * from emp where eno in(select eno from sal);

ENO ENAME DEPT

1 ram cse

2 rajan ece

4 hari cse
SQL> select * from emp where eno not in(select eno from sal where dept='cse');

ENO ENAME DEPT

2 rajan ece

3 balu ece

4 hari cse

SQL> select * from sal where salary>any(select salary from sal where dept='cse');

ENO DEPT SALARY

4 eee 8000

5 cse 10000

SQL> select * from sal where salary>some(select salary from sal where dept='cse');

ENO DEPT SALARY

4 eee 8000

5 cse 10000

SQL> select * from sal where salary>all(select salary from sal where dept='cse'); no rows selected

SQL> select * from sal where salary<all(select salary from sal where dept='cse'); ENO DEPT SALARY

2 ece 1000

EQUIJOIN:

SYNTAX:

Select attribute 1, 2, …., from table name1,table name2 where t1=t2 attribute;

DESCRIPITION:

A equi join is a SQL join.

NON EQUI JOIN:

SYNTAX:

Select attribute1, attribute2. from table 1, table2 where table1 attribute <> table2 attribute

DESCRIPTION:

A non equi join is a SQL join whose condition is established using all comparison operator except the equal (=)
operations like <=,>=,<,>

SELF JOIN SYNTAX:

Select distinct t1 attributes _name1 t1 attributes name from table name t1 where attribute name1=t2.attribute_name
and t1.attribute_name in<> t2.attribute_name in

DESCRIPTION:

A self join is a type of SQL join which is used to join a table to itself particularly when the used table has a foreign
key the references its own primary key. It compares values with the column of a single table.

INNER JOIN:

SYNTAX:

Select attribute1, attribute 2. from table 1 left inner join table2 on condition.

DESCRIPTION:

Inner join returns the matching rows from the tables that are being joined.

OUTER JOIN:

1. Left outer join SYNTAX:

Select attribute1, attribute 2. from table 1 left outer join table2 on condition.

DESCRIPTION:

Left outer join returns the matching rows from the tables that are being joined and also non matching rows from
the left table in the result and the place NULL values in the attribute that came from the right table.

2. Right outer join SYNTAX:

Select attribute1, attribute 2. from table 1 right outer join table2 on condition.

DESCRIPTION:

Right outer join returns the matching rows from the tables that are being joined and also non matching rows from
the right table in the result and the place NULL values in the attribute that came from the left table.

3. Full outer join SYNTAX:

Select attribute1, attribute 2. from table 1 full outer join table2 on condition.
DESCRIPTION:

The full outer join returns the matching rows from the tables that are being joined and also non matching rows
from the left and right table in the result and the place NULL values in the attribute that came from the right & left
table.

Example:

SQL> create table eem(eno number(3),ename varchar2(10),dept varchar2(5)); Table created.

SQL> create table sal(eno number(3),sal number(6),dept varchar(10)); Table created.

SQL> select * from eem;

ENO ENAME DEPT

1 aaa cse

2 bbb it

3 ccc cse

SQL> select * from sal;

em, sal where [Link]=[Link];

ENO ENAME SAL

1 aaa 30000

2 bbb 50000

Non equi join:

SQL> select [Link],ename,sal from eem,sal where [Link]<>[Link];

ENO ENAME SAL

1 aaa 50000

1 aaa 60000

2 bbb 30000

2 bbb 60000

3 ccc 30000
3 ccc 50000

3 ccc 60000

7 rows selected.

SQL> select * from eem,sal where [Link]<>[Link];

ENO ENAME DEPT ENO SAL DEPT

1 aaa cse 2 50000 it

1 aaa cse 5 60000 cse

2 bbb it 1 30000 cse

2 bbb it 5 60000 cse

3 ccc cse 1 30000 cse

3 ccc cse 2 50000 it

3 ccc cse 5 60000 cse

7 rows selected.

Inner join:

SQL> select [Link],ename,sal from eem inner join sal on [Link]=[Link];

ENO ENAME SAL

1 aaa 30000

2 bbb 50000

Outer join:

1. Left outer join

SQL> select [Link],ename,sal from eem left join sal on [Link]=[Link];

ENO ENAME SAL

1 aaa 30000

2 bbb 50000

3 ccc

2. Right outer join

SQL> select [Link],ename,sal from eem right join sal on [Link]=[Link];

ENO ENAME SAL

1 aaa 30000

2 bbb 50000

60000

3. Full outer join

SQL> select [Link],ename,sal from eem full join sal on [Link]=[Link];

ENO ENAME SAL

1 aaa 30000

2 bbb 50000

3 ccc 60000

RESULT:

Thus the simple SQL queries, set operations, nested queries, sub queries, different
‘where’ clause conditions, aggregate functions and Join operations using clauses such as
GROUP BY, ORDER BY, etc. and retrieving information by joining tables has been verified
and executed successfully.
 [Link]:- 6 Write user defined functions and stored procedures in SQL

AIM:

To solve the given problem statements using procedures and function.

PL/SQL PROCEDURES:

An Oracle stored procedure is a program stored in an Oracle database. The following are the advantages of
using procedures. Better performance: Oracle stored procedures load once into the shared pool and remain there
unless they become paged out. Subsequent executions of the Oracle stored procedure are far faster than executions
of external code.

Coupling of data with behaviour: DBAs can use naming conventions to couple relational tables with the
behaviors associated with a table by using Oracle stored procedures as "methods". If all behaviors associated with
the employee table are prefixed with the table [Link], employee.give_raise, for example--the data
dictionary can be queries to list all behaviors associated with a table (select * from dba_objects where owner =
'EMPLOYEE'), and it's easy to identify and reuse code via stored procedures.

Isolation of code: Since all SQL is moved out of the external programs and into the Oracle stored
procedures, the application programs become nothing more than calls to Oracle stored procedures. As such, it
becomes very simple to swap out one database and swap in another one.

EXECUTION:

SETTING SERVEROUTPUT ON:

SQL> SET SERVEROUTPUT ON

1. PROCEDURE USING POSITIONAL PARAMETERS

SQL> SET SERVEROUTPUT ON

SQL> CREATE OR REPLACE PROCEDURE PROC1 AS 2 BEGIN

3 DBMS_OUTPUT.PUT_LINE ('Hello from procedure...'); 4 END;

5/

OUTPUT:

Procedure created.

SQL> EXECUTE PROC1

Hello from procedure...

PL/SQL procedure successfully completed.

2. PROCEDURE USING NOTATIONAL PARAMETERS

SQL> CREATE OR REPLACE PROCEDURE PROC2

2 (N1 IN NUMBER,N2 IN NUMBER,TOT OUT NUMBER) IS

3 BEGIN

4 TOT: = N1 + N2;

5 END;

6/

OUTPUT:

Procedure created.

SQL> VARIABLE T NUMBER SQL> EXEC PROC2 (33, 66: T)

PL/SQL procedure successfully completed. SQL> PRINT T

99

3. PROCEDURE FOR GCD NUMBERS

SQL> create or replace procedure pro is

a number(3); b number(3); c number(3); d number(3); begin

a:=&a;

b:=&b; if(a>b) then c:=mod(a,b); if(c=0) then

dbms_output.put_line('GCD is'); dbms_output.put_line(b);

else dbms_output.put_line('GCD is'); dbms_output.put_line(c);

end if; else

d:=mod(b,a); if(d=0) then

dbms_output.put_line('GCD is'); dbms_output.put_line(a);

else dbms_output.put_line('GCD is');

dbms_output.put_line(d); end if;

end if; end;

INPUT:

Enter value for a: 8 old 8: a:=&a;

new 8: a:=8;

Enter value for b: 16 old 9: b:=&b;

new 9: b:=16;

OUTPUT:

Procedure created.

SQL> set serveroutput on;

SQL> execute pro;

GCD is 8

PL/SQL procedure successfully completed.

2. PROCEDURE USING NOTATIONAL PARAMETERS

CREATE [OR REPLACE] PROCEDURE procedure_name (parameters list) IS

<declaration_section> BEGIN

<executable_section> EXCEPTION

<exception_section> END;

Addition of Two Numbers

1. Write a PL/SQL Program for Addition of Two Numbers

PROCEDURE

1. Start.
2. Initialize the necessary variables.

3. Develop the set of statements with the essential operational parameters.

4. Specify the Individual operation to be carried out.

5. Execute the statements.

6. Stop.

PL/ SQL General Syntax

SQL> DECLARE

<VARIABLE DECLARATION>; BEGIN

<EXECUTABLE STATEMENT >; END;

PL/SQL CODING FOR ADDITION OF TWO NUMBERS

SQL> declare a number;

b number; c number; begin a:=&a;

b:=&b; c:=a+b;

dbms_output.put_line('sum of'||a||'and'||b||'is'||c); end;

INPUT:

Enter value for a: 23 old 6: a:=&a;

new 6: a:=23;

Enter value for b: 12 old 7: b:=&b;

new 7: b:=12;

OUTPUT:

sum of23and12is35

PL/SQL procedure successfully completed.

2. Write a PL/SQL Program using if condition PROCEDURE

STEP 1: Start

STEP 2: Initialize the necessary variables. STEP 3: invoke the if condition.

STEP 4: Execute the statements. STEP 5: Stop.

PL/ SQL GENERAL SYNTAX FOR IF CONDITION

SQL> DECLARE

<VARIABLE DECLARATION>; BEGIN

IF (CONDITION) THEN

<EXECUTABLE STATEMENT >; END;

Coding for If Statement:

DECLARE

b number; c number; BEGIN B:=10;

C:=20;

if(C>B) THEN

dbms_output.put_line('C is maximum'); end if;

end;

OUTPUT:

C is maximum

PL/SQL procedure successfully completed.

3. PL/ SQL GENERAL SYNTAX FOR IF AND ELSECONDITION

SQL> DECLARE

<VARIABLE DECLARATION>; BEGIN

IF (TEST CONDITION) THEN

<STATEMENTS>;
ELSE

<STATEMENTS>;

ENDIF;

END;

Less then or Greater Using IF ELSE

SQL> declare n number; begin

dbms_output.put_line('enter a number'); n:=&number;

if n<5 then

dbms_output.put_line('entered number is less than 5'); else

dbms_output.put_line('entered number is greater than 5'); end if;

end;

INPUT:

Enter value for number: 2 old 5: n:=&number;

new 5: n:=2;

OUTPUT:

entered number is less than 5

PL/SQL procedure successfully completed.

4. PL/ SQL GENERAL SYNTAX FOR NESTED IF

SQL> DECLARE

<VARIABLE DECLARATION>; BEGIN

IF (TEST CONDITION) THEN

<STATEMENTS>;

ELSEIF (TEST CONDITION) THEN

 <STATEMENTS>;

ELSE

<STATEMENTS>;

ENDIF;

END;

GREATEST OF THREE NUMBERS USING IF ELSEIF

SQL> declare a number;

b number; c number; d number; begin a:=&a;

b:=&b;

c:=&b; if(a>b)and(a>c) then

dbms_output.put_line('A is maximum'); elsif(b>a)and(b>c)then dbms_output.put_line('B is maximum');

else

dbms_output.put_line('C is maximum'); end if;

end;

INPUT:

Enter value for a: 21 old 7: a:=&a;

new 7: a:=21;

Enter value for b: 12 old 8: b:=&b;

new 8: b:=12;

Enter value for b: 45 old 9: c:=&b;

new 9: c:=45;

OUTPUT:

C is maximum
PL/SQL procedure successfully completed.

5. PL/ SQL GENERAL SYNTAX FOR LOOPING STATEMENT

SQL> DECLARE

<VARIABLE DECLARATION>; BEGIN

LOOP

<STATEMENT>; END LOOP;

<EXECUTAVLE STATEMENT>;

END;

SUMMATION OF ODD NUMBERS USING FOR LOOP

SQL> declare n number;

sum1 number default 0; endvalue number;

begin endvalue:=&endvalue; n:=1;

for n in 1..endvalue loop

if mod(n,2)=1 then sum1:=sum1+n; end if;

end loop;

dbms_output.put_line('sum ='||sum1); end;

INPUT:

Enter value for endvalue: 4 old 6: endvalue:=&endvalue;

new 6: endvalue:=4;

OUTPUT:

sum =4

PL/SQL procedure successfully completed.

6. PL/ SQL GENERAL SYNTAX FOR WHILE LOOPING STATEMENT

SQL> DECLARE
<VARIABLE DECLARATION>; BEGIN

WHILE <condition> LOOP

<STATEMENT>; END LOOP;

<EXECUTAVLE STATEMENT>; END;

SUMMATION OF ODD NUMBERS USING WHILE LOOP

SQL> declare n number;

sum1 number default 0; endvalue number; begin endvalue:=&endvalue; n:=1;

while(n<endvalue) loop sum1:=sum1+n;

n:=n+2;

end loop;

dbms_output.put_line('sum of odd no. bt 1 and' ||endvalue||'is'||sum1); end;

INPUT:

Enter value for endvalue: 4 old 6: endvalue:=&endvalue;

new 6: endvalue:=4;

OUTPUT:

sum of odd no. bt 1 and4is4

PL/SQL procedure successfully completed.

PL/SQL FUNCTION:

PROCEDURE

1. Start.

2. Create the table with essential attributes.

3. Initialize the Function to carry out the searching procedure.

4. Frame the searching procedure for both positive and negative searching.

5. Execute the Function for both positive and negative result.

 6. Stop.

EXECUTION:

SETTING SERVEROUTPUT ON:

SQL> SET SERVEROUTPUT ON

1. IMPLEMENTATION OF FACTORIAL USING FUNCTION

SQL>create function fnfact (n number) return number is

b number; begin b:=1;

for i in 1..n loop b:=b*i; end loop; return b; end;

/ SQL>Declare

n number:=&n; y number; begin y:=fnfact(n);

dbms_output.put_line(y); end;

Function created.

Enter value for n: 5

old 2: n number:=&n; new 2: n number:=5; 120

PL/SQL procedure successfully completed.

2. PROGRAM

SQL> create table phonebook (phone_no number (6) primary key,username varchar2(30),doorno
varchar2(10),street varchar2(30),place varchar2(30),pincode char(6));

Table created.

SQL> insert into phonebook values(20312,'vijay','120/5D','bharathi street','NGOcolony','629002'); 1 row

created.

SQL> insert into phonebook values(29467,'vasanth','39D4','RK bhavan','sarakkal vilai','629002'); 1 row

created.
 SQL> select * from phonebook;

PHONE_NO USERNAME DOORNO STREET PLACE PINCODE

20312 vijay 120/5D bharathi street NGO colony 629002

29467 vasanth 39D4 RK bhavansarakkalvilai 629002

SQL> create or replace function findAddress(phone in number) return varchar2 as address varchar2(100);

begin

select username||','||doorno ||','||street ||','||place||','||pincode into address from phonebook

wherephone_no=phone;

return address; exception

whenno_data_found then return 'address not found'; end;

Function created.

SQL>declare

2 address varchar2(100);

3 begin

4 address:=findaddress(20312);

5 dbms_output.put_line(address);

6 end;

7/

OUTPUT 1:

Vijay,120/5D,bharathi street,NGO colony,629002 PL/SQL procedure successfully completed.

SQL> declare

2 address varchar2(100);

3 begin
4 address:=findaddress(23556);

5 dbms_output.put_line(address);

6 end;

7/

OUTPUT2:

Address not found

PL/SQL procedure successfully completed.

RESULT:

Thus PL/SQL Procedures and function were created to solve the given problem statements
[Link].:07 Execute complex transactions and realize DCL and TCL commands

AIM:
To execute complex transaction and realize DCL and TCL commands.

TCL COMMANDS:

COMMANDS OVERVIEW

 COMMIT - save work done & it is visible to other users.

 SAVEPOINT - identify a point in a transaction to which you can later roll back
 ROLLBACK - restore database to original since the last COMMIT
 SET TRANSACTION - Change transaction options like what rollback segment to use

SAVEPOINT
Syntax:
savepoint
username;Input:

SQL> savepoint emp;

Output:
savepoint created;
COMMIT
Syntax: commit;

Input:
SQL> commit;
Output:
Commit complete.
ROLLBACK
Syntax: rollback to
savepoint_text_identifier;Input
:
 SQL> rollback to
emp;Output:

Rollback complete.

DCL COMMANDS:

DCL provides users with privilege commands the owner of database objects (tables), has the
soul authority ollas them. The owner (data base administrators) can allow other data base
users to access the objects as per their requirement

1. GRANT: The GRANT command allows granting various privileges to other users and
allowing them to perform operations within their privileges.

Example, if a uses is granted as ‘SELECT’ privilege then he/she can only view data but
cannot perform any other DML operations on the data base object GRANTED privileges
can also be withdrawn by the DBA at any time

Syntax:

SQL>GRANT PRIVILEGES_NAME on object_name To user_name;

Example:

SQL>GRANT SELECT, UPDATE on emp To hemanth;

2. REVOKE: To with draw the privileges that has been GRANTED to a uses, we use the
REVOKE
command
 Syntax:
SQL>REVOKE PRIVILEGES_NAME ON object-name FROM user_name;

Example:

SQL>REVOKE SELECT, UPDATE ON emp FROM ravi;

RESULT:

Thus the Execute complex transactions and realize DCL and TCL commands is completed and
verified successfully.
[Link]: 08 Write SQL TRIGGERS for insert,delete,and update operation in database table

AIM
To write a PL/SQL query to create a trigger.

TRIGGERS:
A trigger is a statement that the system automatically as a side effect of a modification

SYNTAX FOR CREATING A TRIGGER:

Create or replace trigger<trigger_name>
{Before/After}
{Delete Insert
Update}
On<Table
name>
[Referencing {OLD[AS] OLD new [AS]
new}] [For each row]
When
(condition)
Declare
<declaration
section> Begin
<executable statement>
End;
Component of Trigger
Triggering SQL statement : SQL DML (INSERT, UPDATE and DELETE)
statement that execute and implicitly called trigger to execute.

Trigger Action : When the triggering SQL statement is execute, trigger

automatically call and PL/SQL trigger block execute.
Trigger Restriction : We can specify the condition inside trigger to when trigger is fire.

Type of Triggers
1. BEFORE Trigger : BEFORE trigger execute before the triggering
DML statement (INSERT, UPDATE, DELETE) execute. Triggering
SQL statement is may or may not execute, depending on the
BEFORE trigger conditions block.
 2. AFTER Trigger : AFTER trigger execute after the triggering DML statement

(INSERT, UPDATE, DELETE) executed. Triggering SQL statement is

execute as soon as followed bythe code of trigger before performing Database
operation.
3. ROW Trigger : ROW trigger fire for each and every record which are performing
INSERT, UPDATE, DELETE from the database table. If row deleting is define as
trigger event, when trigger file, deletes the five rows each times from the table.
4. Statement Trigger : Statement trigger fire only once for each statement. If

row deleting isdefine as trigger event, when trigger file, deletes the five rows
at once from the table.
5. Combination Trigger : Combination trigger are combination of two trigger type,
1. Before Statement Trigger : Trigger fire only once for each
statement before the triggering DML statement.
2. Before Row Trigger : Trigger fire for each and every record before
the triggering DML statement.
3. After Statement Trigger : Trigger fire only once for each
statement after the triggering DML statement executing.
4. After Row Trigger : Trigger fire for each and every record after the
triggering DML statement executing.

PL/SQL
Triggers
Inserting
Trigger
SQL> CREATE or REPLACE TRIGGER trg1
2 BEFORE
3 INSERT ON customer
4 FOR EACH ROW
5 BEGIN
6 :[Link] := upper(:[Link]);
7 END;
8/

Trigger created.

SQL> select * from customer;

NAME CID ADDRESS SALARY

---------- --------- ---------- ---------
mani 200 erode 30000
abi 201 namakkal 40000
rani 301 sale 40000
m

SQL> insert into customer

values('&name',&cid,'&address',&salary); Enter value for
name: logu
Enter value for cid: 306
Enter value for address:
salem Enter value for
salary: 40000
old 1: insert into customer
values('&name',&cid,'&address',&salary) new 1: insert
into customer values('logu',306,'salem',40000)

1 row [Link]> select * from customer;

NAME CID ADDRESS SALARY

LOGU 306 salem 40000

mani 200 erode 30000
abi 201 namakkal 40000
rani 301 salem 40000

This trigger execute BEFORE to convert name field lowercase to uppercase.

CREATION OF FIRST TABLE

SQL> create table student(regno number(10),name varchar2(10),dept

varchar2(10),percentage number(10));

Table created

SQL> select * from student;

REGN NAM DEPT PERCENTA
O E GE
1001 kani cse 70
1002 madhi cse 80
1003 sharan it 85
1004 sathya it 90

CREATION OF SECOND TABLE

SQL> create table bckupstu2(regno number(10),name varchar2(10),dept

varchar2(10),percentage number(10));
Table created.

TRIGGER -1:
SQL> create or replace trigger t1 after delete on student
for each row 2 begin
3 insert into bckupstu2 values(:[Link],:[Link],:[Link],:[Link]);
4 end
;
5/

Trigger created.

SQL> select * from

bckupstu2; no rows

selected

SQL> delete from student where

regno=1001; 1 row deleted.

SQL> select * from bckupstu2;

REGNO NAME DEPT PERCENTAGE

1001 kani cse 70

SQL> select * from student;

REGN NAM DEPT PERCENTA
O E GE
1002 madhi cse 80
1003 sharan it 85
1004 sathya it 90
TRIGGER-2
SQL> create or replace trigger t2 after update of regno on student
for each row 2 begin
3 insert into bckupstu2 values(:[Link],:[Link],:[Link],:[Link]);
4 end
;
5/
Trigger created.

SQL> update student set name='mozhi' where

regno=1002; 1 row updated.
SQL> select * from student;
REGNO NAME DEPT PERCENTAGE
1002 mozhi cse 80
1003 sharan it 85
1004 sathya it 90

SQL> select * from bckupstu2;

REGNO NAME DEPT PERCENTAGE

1001 kani cse 70

SQL> update student set regno=1005 where

regno=1002; 1 row updated.

SQL> select * from student;

REGNO NAME DEPT PERCENTAGE

1005 mozhi cse 80

1003 sharan it 85
1004 sathya it 90

SQL> select * from bckupstu2;

REGNO NAME DEPT PERCENTAGE

100 kani cse 70

1
100 mozhi cse 80
2

TRIGGER-3
SQL> create or replace trigger t3 before insert or update of name on student for each row

2 begin
3 :[Link]:=upper(:[Link]);
4 end
;
5/

Trigger created.

SQL> insert into student

values(1006,'anu','ece',91); 1 row created.
SQL> select * from student;

REGNO NAME DEPT PERCENTAGE

1005 mozh cse 80

 i
1003 shara it 85
n
1004 sathy it 90
a
1006 anu ec 91
e

SQL> select * from bckupstu2;

REGN NAM DEPT PERCENTA
O E GE
1001 kani cse 70
1002 mozhi cse 80

CREATION OF THIRD TABLE:

SQL> create table voter_master(voterid number(5),name varchar2(10),wardno
number(4),dob date,address varchar2(15),primary key(voterid,wardno));
Table created.

SQL> select * from voter_master;

VOTERID NAME WARDNO DOB ADDRESS

1000 Saran 5 03-JAN-91 Neru street erode

1001 Madhi 12 15-MAY- Gandhi nagar
89 erode
2000 Priya 6 05-FEB-84 Yal street erode

CREATION OF FOURTH TABLE:

SQL> create table new_list(voterid number(5),wardno number(4),name

char(20),description char(20);
Table created.

SQL> select * from new_list;

VOTERID WARDNO NAME DESCRIPTION

1000 5 Saran Neru street erode

1001 12 Mad Gandhi nagar
hi erode
5000 21 Roja Ashok street
erode

TRIGGER-4
SQL> create or replace trigger vot_trig after delete on voter_master for
each row declare
2 v_id number(5);
3 w_id number(4);
4 begin
5 v_id:=:[Link];
6 update new_list set description='shifted' where voterid=v_id;
7 end
;
8/
Trigger created.
SQL> delete from voter_master where
voterid=1000; 1 row deleted.

SQL> select * from voter_master;

VOTERID NAME WARDNO DOB ADDRESS

100 Madhi 12 15-MAY- Gandhi nagar

1 89 erode
200 Priya 6 05-FEB-84 Yal street erode
0

SQL> select * from new_list;

VOTERID WARDNO NAME DESCRIPTION

1000 5 Saran shifted

1001 12 Madhi Gandhi nagar
erode
5000 21 Roja Ashok street
erode

SQL> desc phonebook;

Name Null? Type

PHONE_NO NOT NULL NUMBER (6)
USERNAME VARCHAR2
(13)
DOORNO VARCHAR2
(5)
STREET VARCHAR2
(13)
PLACE VARCHAR2
(13)
PINCODE CHAR (6)

SQL> select * from phonebook;

PHONE_NO USERNAME DOORN STREET PLACE PINCOD
 25301 priya 390 main street
SIT 659002
colony
25401 murthy 39D9 MS bhavan sai nagar 689002
25701 karthi 7 jay nagar chennai 600002

SQL> create or replace function findAddress(phone in number) return varchar2 as

address varchar2(100);
2 begin
3 select username||','||door no ||','||street ||','||place||','||Pincode into address from phonebook
4 where phone_no=phone;
5 return
address;
6 exceptions
7 when no_data_found then return 'address not found';
8 end;
9/
Function created.

SQL> declare
2 address varchar2 (100);
3 begin

4 address:=find address(25301);
5 dbms_output.put_line (address);
6 end;
Priya,390,main street,SIT colony,659002

PL/SQL procedure
successfully [Link]> declare
2 address varchar2(100);
3 begin
4 address:=findaddress(25601);
5 dbms_output.put_line(address);
6 end;
Address not found
PL/SQL procedure successfully completed.

RESULT:
Thus the PL/SQL block for trigger with different controls are verified and executed.
[Link] 09 Create View and index for database tables with a large number of records.

AIM:
Create View and index for database tables with a large number of records.
OVERVIEW:
VIEW: A view is simply the representation of a SQL statement that is stored
in memory so that itcan easily be re-used. It‟s also referred as logical table.

SQL COMMANDS

Creating views:

Syntax:
Create view<view name>;
Description:
This command is used to create view by combining two tables.
Viewing single row of table:

Syntax:

Create view<view name> as select from <table name>;

Description:
This command is used to view a single row from a particular table.
Viewing all columns from a single table:

Syntax:

Create view<view name> as select * from <table name>;

Description :
This is used to create view which displays all columns from a single table.
View specified column from a single table:
Syntax:
Create view<view table name> as select column1,column2 from <tablename>;
Description:
This command is used to create view which displays on a specified
from a single table.
View specified column from a muliple table:
Syntax:

Create view<view table name> as select column1,column2,….columnn where

„condition‟;
Description:
This is used to create view to display specified columns from multiple tables.
View all column from a muliple table:
Syntax:

Create view<view table name> as select * from <table name> where „condition‟;
Description:
This is used to create view which displays all the columns of a table.
Inserting into views:

Syntax:

Insert into <view name> values <‟data1‟,‟data2‟,……>;

Description:
This is used to do inserting of information or data into values.
Updating in view:

Syntax:

Alter table <table name> add constraint;

Description:
This is used to do updation of values by specifying the constraints.
Deleting a view:

Syntax:
 Drop view <view name>;
SYNTAX:

CREATE OR REPLACE VIEW <view name > AS < select statement >
SYNONYM: A synonym is an alias or alternate name for a table, view,
sequence, or other schema object. They are used mainly to make it easy for
users to access database objects owned by other users.

SYNTAX:

CREATE OR REPLACE SYNONYM <synonym_name> FOR <object_name>

SEQUENCE: A sequence is a database object from which multiple users

may generate unique integers. User can use sequences to automatically
generate primary key values.
INDEXES: Database system uses indexes to avoid the need for large-table,
full-table scans and disk sorts, which are required when the SQL optimizer
cannot find an efficient way to service the SQL query.

Result:

Thus the problem statements based on views, synonym, sequence and

indexes are executed successfully.
EX:NO:10 Create an XML database and validate it using XML schema.

AIM:

To create an XML database and validate it using XML schema.

ALGORITHM:

1. Import the necessary libraries for XML processing in your chosen programming
language.
2. Define the data that will be stored in the XML database, including thestructure of the
data and any associated attributes.
3. Write a function that creates the XML document based on the defined datastructure.
4. Validate the XML document using an XML schema to ensure that it conforms to the
expected structure.
5. Write a function that reads and parses the XML document to retrieve and manipulate
the data as needed.
6. Implement error handling to catch and respond to any errors that may occur during
the creation or validation of the XML document.
7. Test the program by creating an XML database, validating it using the XML schema,
and reading and parsing the data to ensure that it is correctly stored and retrieved.
8. Refine the program as needed to improve functionality and efficiency, and document
the code to make it easier for others to understand and use.

PROGRAM:
Example XML Document:
<?xml version="1.0" encoding="UTF-8"?>
<employees>
<employee>
<id>1</id>
<name>John Doe</name>
<department>IT</department>
<salary>60000</salary>
</employee>
<employee>
 <id>2</id>
<name>Jane Smith</name>
<department>HR</department>
<salary>50000</salary>
</employee>
</employees>
Example XML Schema:
<?xml version="1.0"?>
<xs:schema xmlns:xs="[Link]
<xs:element name="employees">
<xs:complexType>
<xs:sequence>
<xs:element name="employee" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="id" type="xs:integer"/>
<xs:element name="name" type="xs:string"/>
<xs:element name="department" type="xs:string"/>
<xs:element name="salary" type="xs:decimal"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
OUTPUT:
Validation successful.
RESULT:

Thus the program to Create an XML database and validate it using XML
schema is executed and verified successfully.
 EX NO :11 Create Document, column and graph based data using NOSQL database
tools.

AIM:

To Create Document, column and graph based data using NOSQL database tools.

ALGORITHM:
1. Import the necessary libraries for NoSQL database tools in your chosen programming
language.
2. Connect to the desired NoSQL database using the appropriate client library.
3. Create the desired data structure, such as a document, column family, orgraph.
4. Insert data into the NoSQL database using the appropriate method orquery.
5. Retrieve and manipulate data from the NoSQL database as needed using the appropriate
method or query.
6. Implement error handling to catch and respond to any errors that may occur during data
insertion, retrieval, or manipulation.
7. Test the program by creating and manipulating data in the NoSQL database, and ensure that
the data is correctly stored and retrieved.
PROGRAM:

// Import necessary libraries

import [Link];
import [Link];

import [Link];
import [Link];

import [Link];

import [Link];

import [Link];

import [Link];
import [Link];

import [Link];
import [Link];

import [Link];

import [Link];

import [Link];
import [Link];

import [Link];

import [Link];

import [Link];

import [Link];

public class NoSQLDatabaseExample {

public static void main(String[] args) {

// MongoDB example

MongoClient mongoClient =
[Link]("mongodb://localhost:27017"); MongoDatabase

database = [Link]("mydb");

MongoCollection<Document> collection =
[Link]("mycollection");

// Create a document

Document document = new Document("name", "John Doe")

.append("age", 35)

.append("email", "johndoe@[Link]");
[Link](document);

// Create a column family

 Cluster cluster = [Link]().addContactPoint("localhost").build();
Session session = [Link]("mykeyspace");

String createTableQuery = "CREATE TABLE mycolumnfamily (id int PRIMARY

KEY, name text, age int);";

[Link](createTableQuery);

String insertQuery = "INSERT INTO mycolumnfamily (id, name, age) VALUES (1,
'John Doe', 35);";

[Link](insertQuery);

// Create a graph

ArangoDB arangoDB = new [Link]().build();

ArangoCollection collection =
[Link]("mydb").createCollection("mycollection", new
CollectionCreateOptions().type([Link]));
DocumentEntity documentEntity = [Link](new
BaseDocument("name", "John Doe"));

ArangoCollection edgeCollection =
[Link]("mydb").createCollection("myedges", new
CollectionCreateOptions().type([Link]));

EdgeDefinition edgeDefinition = new

EdgeDefinition().collection("myedges").from("mycollection").to("mycollection");

GraphEntity graph = [Link]("mydb").createGraph("mygraph",

[Link](edgeDefinition), null);

VertexEntity vertex = [Link]("mycollection").insertVertex(new

BaseDocument("name", "John Doe"));

[Link]("myedges").insertEdge(new
BaseEdgeDocument([Link](), [Link](), new BaseDocument())); }
RESULT:

Document, column and graph based data using NOSQL database tools are Created
Successfully
EX NO :12 Develop a simple GUI based database application and
incorporate all the above mentioned features

AIM:

To Develop a simple GUI based database application and incorporate allthe above
mentioned features.

ALGORITHM:
1. Import the required libraries (tkinter for GUI and sqlite3 for databaseoperations).
2. Define functions for adding records to the database and displaying records.
3. Create a new database and table (if they don't exist).
4. Create the main window for the GUI application.
5. Create input fields (labels and entry widgets) for name and age.
6. Create buttons for adding records and displaying records, and associatethem with their
respective functions.
7. Create a text area to display the records.
8. Start the application by calling [Link]().
Close the database connection when the application is closed

PROGRAM:

import tkinter as tk

import sqlite3

# Function to create a new record in the databasedef

add_record():

name = name_entry.get()

age = age_entry.get()
[Link]("INSERT INTO records (name, age) VALUES (?, ?)", (name, age))

[Link]()
 display_records()

# Function to display all records from the database

def display_records():

cursor = [Link]("SELECT * FROM

records") records = [Link]()

record_text.delete(1.0, [Link])

for record in records:

record_text.insert([Link], f"Name: {record[0]}\nAge: {record[1]}\n\n")

# Create a new database and table

conn = [Link]("[Link]")

[Link]("CREATE TABLE IF NOT EXISTS records (name TEXT, age INTEGER)")

# Create the main window

window = [Link]()

[Link]("Database Application")
# Create input fields

name_label = [Link](window, text="Name:")

name_label.pack()

name_entry = [Link](window)

name_entry.pack()

age_label = [Link](window,
text="Age:") age_label.pack()

age_entry = [Link](window)
age_entry.pack()
# Create buttons

add_button = [Link](window, text="Add Record", command=add_record)

add_button.pack()

display_button = [Link](window, text="Display Records",

command=display_records)

display_button.pack()

# Create text area to display records

record_text = [Link](window)

record_text.pack()

# Start the application

[Link]()

# Close the database connection

[Link]()

RESULT:

Thus program to develop a simple GUI based database application andincorporate

all the above mentioned features is done successfully.
CONTENT BEYOND SYLLABUS
[Link]. 13 Implementing a Fire Store Database for Real-Time Messaging

AIM:
To implement a fire store database for real-time messaging.

PROGRAM:
Step 1: Create a fire store Collection
Navigate to Firebase Console → Firestore Database → Create Collection messages.

Step 2: Add a Sample Message (Python)

import firebase_admin
from firebase_admin import credentials, firestore
cred = [Link]("path_to_your_serviceAccountKey.json")
firebase_admin.initialize_app(cred)
db = [Link]()
[Link]("messages").add({
"sender": "Alice",
"receiver": "Bob",
"message": "Hey Bob! How are you?",
"timestamp": firestore. SERVER_TIMESTAMP
})

Step 3: Retrieve Messages

messages = [Link]("messages").order_by("timestamp").stream()
for msg in messages:
print(msg.to_dict())
Expected Output:

{
"sender": "Alice",
"receiver": "Bob",
"message": "Hey Bob! How are you?",
"timestamp": "2024-02-24T10:30:00Z"
}

Result:
Thus the Program to implement the fire store database for real-time messaging is successfully
completed.
[Link]. 14 Implementing Role-Based Access Control (RBAC) in MySQL

AIM:
To implement role based access control in MySQL
PROGRAM:
Step 1: Create Users and Roles
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin123';
CREATE USER 'staff'@'localhost' IDENTIFIED BY 'staff123';
CREATE USER 'customer'@'localhost' IDENTIFIED BY 'customer123';

Step 2: Grant Permissions

GRANT ALL PRIVILEGES ON Store.* TO 'admin'@'localhost';
GRANT SELECT, INSERT ON [Link] TO 'staff'@'localhost';
GRANT SELECT ON [Link] TO 'customer'@'localhost';

Step 3: Check User Privileges

SHOW GRANTS FOR 'staff'@'localhost';

Expected Output:
+------------------------------------------------------------+
| Grants for staff@localhost |
+------------------------------------------------------------+
| GRANT SELECT, INSERT ON [Link] TO 'staff'@'localhost' |
+------------------------------------------------------------+

Result:
Thus the Program to implement role based access control in MySQL is successfully
completed.