Department of Computer Science and Engineering

(CSE)

II B. Tech II Semester

Faculty Name: Mrs G.Prathibha & Ms K.Theja.

Subject Name: DATABASE MANAGEMENT SYSTEMS LAB
Subject Code: C0519
Regulations: MR-22
For the Academic Year: 2023-24
Lab Manual

MALLA REDDY ENGINEERING COLLEGE

(Autonomous)
(An UGC Autonomous Institution, Approved by AICTE and Affiliated to JNTUH, Hyderabad, Accredited by NAAC with
‘A++’ Grade (III Cycle) )
NBA Accredited Programmes – UG (CE, EEE, ME, ECE, & CSE ), PG(CE-SE, EEE,-EPS, ME-TE)
Maisammaguda, Dhulapally (Post ViaKompally), Secunderabad-500 100
MALLA REDDY ENGINEERING COLLEGE (AUTONOMOUS)
MR22 – ACADEMIC REGULATIONS (CBCS)
for B.Tech. (REGULAR) DEGREE PROGRAMME

Applicable for the students of B.Tech. (Regular) programme admitted from the Academic Year 2022
onwards
The B.Tech. Degree of Jawaharlal Nehru Technological University Hyderabad, Hyderabad shall be
conferred on candidates who are admitted to the programme and who fulfill all the requirements for the
award of the Degree.
VISION OF THE INSTITUTE
To be a premier center of professional education and research, offering quality programs in a socio-
economic and ethical ambience.
MISSION OF THE INSTITUTE
 To impart knowledge of advanced technologies using state-of-the-art infrastructuralfacilities.
 To inculcate innovation and best practices in education, training and research.
 To meet changing socio-economic needs in an ethical ambience.

DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING

DEPARTMENT VISION
To attain global standards in Computer Science and Engineering education, training and research to meet the
growing needs of the industry with socio-economic and ethical considerations.
DEPARTMENT MISSION
 To impart quality education and research to undergraduate and postgraduate students in Computer
Science and Engineering..
 To encourage innovation and best practices in Computer Science and Engineering utilizing state-of-
the-art facilities.
 To develop entrepreneurial spirit and knowledge of emerging technologies based on ethical values
and social relevance.
PROGRAMME EDUCATIONAL OBJECTIVES (PEOs)

PEO1: To impart with a sound knowledge in scientific and engineering technologies necessary to
formulate, analyze, design and implement solutions to computer technology related problems.
PEO2: To carry out research in frontier areas of computer science and engineering with the capacity to
learn independently throughout life to develop new technologies.
PEO3: To train to exhibit technical, communication and project management skills in their profession and
follow ethical practices.
PEO4: To possess leadership and team working skills to become a visionary and an inspirational leader and
entrepreneur.
PROGRAMME OUTCOMES (POs)

PO1: Engineering knowledge: Apply the knowledge of mathematics, science, engineering fundamentals,
and an engineering specialization to the solution of complex engineering problems.
PO2: Problem analysis: Identify, formulate, review research literature and analyze complex engineering
problems reaching substantiated conclusions using first principles of mathematics, natural sciences, and
engineering sciences.
PO3: Design/development of solutions: Design solutions for complex engineering problems and design
system components or processes that meet the specified needs with appropriate consideration for the public
health and safety, and the cultural, societal, and environmental considerations.
PO4: Conduct investigations of complex problems: Use research-based knowledge and research methods
including design of experiments, analysis and interpretation of data, and synthesis of the information to
provide valid conclusions.
PO5: Modern tool usage: Create, select, and apply appropriate techniques, resources, and modern
engineering and IT tools including prediction and modeling to complex engineering activities with an
understanding of the limitations.
PO6: The engineer and society: Apply reasoning informed by the contextual knowledge to assess societal,
health, safety, legal and cultural issues and the consequent responsibilities relevant to the professional
engineering practice.
PO7: Environment and sustainability: Understand the impact of the professional engineering solutions in
societal and environmental contexts, and demonstrate the knowledge of, and need for sustainable
development.
PO8: Ethics: Apply ethical principles and commit to professional ethics and responsibilities and norms of
the engineering practice.
PO9: Individual and team work: Function effectively as an individual and as a member or leader in
diverse teams, and in multidisciplinary settings.
PO10: Communication: Communicate effectively on complex engineering activities with the engineering
community and with society at large, such as, being able to comprehend and write effective reports and
design documentation, make effective presentations, and give and receive clear instructions.
PO11: Project management and finance: Demonstrate knowledge and understanding of the engineering
and management principles and apply these to one’s own work, as a member and leader in a team, to
manage projects and in multidisciplinary environments.
PO12: Life-long learning: Recognize the need for, and have the preparation and ability to engage in
independent and life-long learning in the broadest context of technological change.

PROGRAMME SPECIFIC OUTCOMES (PSOs)

PSO1: The ability to identify, analyze real world problems and design their ethical solutions using
computer-based systems of varying complexity.
PSO2: The ability to design and develop the hardware and interfacing software systems for solving real
world engineering problems with socio acceptance.
PSO3: The ability to employ modern computer languages, and platforms in creating innovative career paths
to be an entrepreneur by inculcating in them moral values & ethics.
Database Management Systems Lab

Dept of CSE , MREC

Database Management Systems Lab

Lab Syllabus:
2022-23
MALLA REDDY ENGINEERING COLLEGE B.Tech.
Onwards
(Autonomous) IV Semester
(MR-22)
Code: C0519 Database Management Systems Lab L T P
(Common for CSE, CSE (Cyber Security), CSE (AI and
Credits: 1 ML), CSE (DS), CSE (IOT), AI and IT) - - 2
Co-requisites: “Database Management Systems”

Course Objectives:
1. Introduce ER data model, database design and normalization
2. Learn SQL basics for data definition and data manipulation

Course Outcomes:
1. Design database schema for a given application and apply normalization
2. Acquire skills in using SQL commands for data definition and data manipulation.
3. Develop solutions for database applications using procedures, cursors and triggers

LIST OF EXPERIMENTS:
i) Concept design with E-R Model
ii) Relational Model
iii) Normalization
iv) Practicing DDL commands
v) Practicing DML commands
vi) A. Querying (using ANY, ALL, UNION, INTERSECT, JOIN, Constraints etc.)
B. Nested, Correlated subqueries
vii) Queries using Aggregate functions, GROUP BY, HAVING and Creation and dropping ofViews.
viii) Triggers (Creation of insert trigger, delete trigger, update trigger)
ix) Procedures
x) Usage of Cursors

TEXT BOOKS:
1. Database Management Systems, Raghurama Krishnan, Johannes Gehrke, Tata Mc Graw Hill, 3rdEdition
2. Database System Concepts, Silberschatz, Korth, McGraw Hill, V edition.

REFERENCE BOOKS:
1. Database Systems design, Implementation, and Management, Peter Rob & Carlos Coronel 7thEdition.
2. Fundamentals of Database Systems, Elmasri Navrate, Pearson Education
3. Introduction to Database Systems, C.J. Date, Pearson Education
4. Oracle for Professionals, The X Team, S. Shah and V. Shah, SPD.
5. Database Systems Using Oracle: A Simplified guide to SQL and PL/SQL, Shah, PHI.
6. Fundamentals of Database Management Systems, M. L. Gillenson, Wiley Student Edition.

Dept of CSE , MREC

Database Management Systems Lab

Programme Outcomes (POs) PSOs

PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO12 PSO1 PSO2 PSO3

COs

CO1 2 - -

CO2 - 2 2 1 2

CO3 2 2 2 3 2 2

1. LAB OBJECTIVES
This DBMS Lab enables the students to practice the concepts learnt in the DBMS
subject by developing a database for an example company named “Roadway Travels”

 The Student is expected to practice the designing, developing and querying a

database in the context of “Roadway Travels”.
 The Student are expected to use “MySQL” Database for Practice.

2. LAB OUTCOMES
Upon successful completion of this Lab the student will be able to:

 ER Model: The Objective of the lab is to analyze the problem carefully and come up
with entities and attributes etc in the Roadway Travels and later buildup the conceptual
design with the E-R Model.

 Relational Model: The Objective of the lab exercise is to represent all the entities in
tabular fashion, representing relationships as tables based on Cardinality, different types
of attributes(composite, Multi-valued and Derived)

 Normalization: The Objective of the lab exercise is to apply the Normalization

techniques like INF,2NF,3NF,BCNF to above Roadway travels database

 Installation of MySQL and Practicing DDL & DML commands: The Objective of the
lab Exercise is to install the MySQL software and Practice DDL & DML Commands and
Practicing the Queries.

 Triggers, Cursors and Procedures: The Objective of the lab exercise is to create
triggers and cursors and writing Procedures on Roadway Travels database

 me system is allotted for students when they do the lab.

Dept of CSE , MREC

Database Management Systems Lab

INDEX
S. No Name of the Experiment Date Page Sign
No
1. Concept design with E-R Model

2. Relational Model
3. Normalization
4. Practicing DDL commands
5. Practicing DML commands
6.A Querying (using ANY, ALL,
UNION, INTERSECT, JOIN,
Constraints etc.)
B. Nested, Correlated subqueries
7. Queries using Aggregate functions,
GROUP BY, HAVING and Creation
and dropping of Views.
8. Triggers (Creation of insert trigger,
delete trigger, update trigger)
9. Procedures

10. Usage of Cursors

Lab Incharge HOD

Dept of CSE , MREC

Database Management Systems Lab

WEEK 1: E-R Model

Aim: Analyze the problem and come with the entities in it. Identify what Data has to be
Persisted in the databases.

Recommended Hardware / Software Requirements:

 Hardware Requirements: Intel Based desktop PC with minimum of 166 MHZ or

faster processor with at least 1GB RAM and 500 MB free disk space.
 MySQL 5.6.1

Prerequisites: Student must know the entities and attributes and representation of
diagrams The Following are the entities:

Identify the primary keys for all the entities. Identify the other keys like candidate keys,
partial keys, if any.

Example:
Entities: 1. BUS
2. Ticket
3. Passenger

Relationships: 1. Reservation
2. Cancellation

Primary Key Attributes: 1. Ticket ID (Ticket Entity)

2. Passport ID (Passenger Entity)
3. Bus_NO (Bus Entity)

Dept of CSE(Data Science) , MREC Page 1

Database Management Systems Lab

E-R Model

Attributes of the following Entities

Bus
- BusNo
- Source
- Destination
- CoachType

SCHEMA

Bus: Bus(BusNo: String, Source: String, Destination: String, CoachType:

String)

Dept of CSE(Data Science) , MREC Page 2

Database Management Systems Lab

Ticket
- TicketNo
- DOJ
- Address
- ContactNo
- BusNo
- SeatNo
- Source
- Destination

SCHEMA

Ticket(TicketNo: string, DOJ: date, Address:string,ContactNo: string, BusNo:String

,SeatNo:Integer,Source: String, Destination: String)

Dept of CSE(Data Science) , MREC Page 3

Database Management Systems Lab

Passenger
- PassportID
- TicketNo
- Name
- ContactNo
- Age
- Sex
- Address

SCHEMA

Passenger(PassportID: String, TicketNo:string,Name: String, ContactNo:string,Age: integer,

Sex: character, Address: String)

Dept of CSE(Data Science) , MREC Page 4

Database Management Systems Lab

Reservation
- PNRNo
- DOJ
- No_of_seats
- Address
- ContactNo
- BusNo
- SeatNo

SCHEMA
Reservation(PNRNo: String, DOJ: Date, NoofSeats: integer , Address: String ,ContactNo:
String, , BusNo: String,SeatNo:Integer)

Dept of CSE(Data Science) , MREC Page 5

Database Management Systems Lab

Cancellation
- PNRNo
- DOJ
- SeatNo
- ContactNo
- Status

SCHEMA

Cancellation(PNRNo: String,DOJ: Date, SeatNo: integer,ContactNo: String,Status:

String)The

Conclusion: The Student is able to Identify the Entities and Attributes of the
Roadway Travels

Viva-Voce:

1. What is SQL?
2. what is entity and relationship?
3. What is DBMS?
4. What is a Database system?
5. Advantages of DBMS?
6.How many types of database languages are available?

Dept of CSE(Data Science) , MREC Page 6

Database Management Systems Lab

CONCEPT DESIGN WITH E-R MODEL

Aim: To Relate the entities appropriately. Apply cardinalities for each relationship.
Identify strong and weak entities. Indicate the type of relationships (total/partial).
Incorporate generalization, aggregation and specialization etc wherever required.

Recommended Hardware / Software Requirements:

 Hardware Requirements: Intel Based desktop PC with minimum of 166 MHZ or

faster processor with at least 1GB RAM and 500 MB free disk space.
 MySQL 5.6.1

Prerequisites: Student must know the entities and attributes and relationship among entities

Conclusion: The Student is able design the concept design of Road-way Travels

Dept of CSE(Data Science) , MREC Page 7

Database Management Systems Lab

Viva-Vice:

1. Describe the three levels of data abstraction?

2. Define the "integrity rules"

3. What are the usage of SQL?

4. What is the use of SELECT?

Dept of CSE(Data Science) , MREC Page 8

Database Management Systems Lab

WEEK 2 : RELATIONAL MODEL

Aim: To Represent all the entities (Strong, Weak) in tabular fashion. Represent
relationships in a tabular fashion.

Recommended Hardware / Software Requirements:

 Hardware Requirements: Intel Based desktop PC with minimum of 166 MHZ or

faster processor with at least 1GB RAM and 500 MB free disk space.
 MySQL 5.6.1

Prerequisites: Student must know about the Relational Model

1. Bus: Bus(BusNo: String, Source: String, Destination: String, CoachType: String)

ColumnName Datatype Constraints Type of

Attributes
BusNo Varchar(10) Primary Single-value
key
Source Varchar(20) Single-value
Destination Varchar(20) Simple
CoachType Varchar(10) Simple

Mysql>create table Bus(BusNo varchar(10),source varchar(20),Destination

varchar(20),coachType varchar(10),primary key(BusNo));

Mysql>desc Bus;

Dept of CSE(Data Science) , MREC Page 9

Database Management Systems Lab

Ticket:

Ticket(TicketNo: string, DOJ: date, Address:string,ContactNo: string, BusNo:String

,SeatNo:Integer,Source: String, Destination: String)

ColumnName Datatype Constraints Type of Attributes

TicketNo Varchar(20) Primary Key Single-valued
DOJ Date Single-valued
Address Varchar(20) Composite
ContactNo Integer Multi-valued
BusNo Varchar(10) Foreign Key Single-valued
SeatNo Integer Simple
Source Varchar(10) Simple
Destination Varchar(10) Simple

Mysql>create table Ticket(TicketNo varchar(20),DOJ date,Address

varchar(20),ContactNo varchar(15)BusNo varchar(10),SeatNo int,Source
varchar(10),primary key(TicketNo,BusNo),foreign key(BusNo) references Bus(BusNo));

Mysql>desc Ticket;

Dept of CSE(Data Science) , MREC Page 10

Database Management Systems Lab

Passenger:

Passenger(PassportID: String, TicketNo:string,Name: String, ContactNo:string,Age:

integer, Sex: character, Address: String);

Type of
ColumnName Datatype Constraints Attributes
PassportID Varchar(15) Primary Key Single-valued
TicketNo Varchar(20) Foreign Key Single-valued
Name Varchar(20) Composite
ContactNo Varchar(20) Multi-valued
Age Integer Single-valued
Sex character Simple
Address Varchar(20) Composite

Mysql> Create table passenger(passportID varchar(15) ,TicketNo varchar(15),Name

varchar(15),ContactNo varchar(20),Age integer, sex char(2),address varchar(20), primary
key(passportID,TicketNo),foreign key(TicketNo) references Ticket(TicketNo));

Mysql> desc passenger;

Dept of CSE(Data Science) , MREC Page 11

Database Management Systems Lab

Reservation:

Reservation(PNRNo: String, DOJ: Date, NoofSeats: integer , Address: String ,ContactNo:

String, , BusNo: String,SeatNo:Integer)

ColumnName Datatype Constraints Type of Attributes

PNRNo Varchar(20) Primary Single-valued
Key
DOJ date Single-valued
No_of_Seats Integer Simple
Address Varchar(20) Composite
ContactNo Varchar(10) Multi-valued
BusNo Varchar(10) Foreign Single-valued
Key
SeatNo Integer Simple

Mysql> Create table Resevation(PNRNo varchar(20),DOJ date,NoofSeates

integer,Address varchar(20),ContactNo varchar(20),BusNo varchar(20),SeatNo integer,
primary key(PNRNo,BusNo),foreign key(BusNo) references Bus(BusNo));

Mysql> desc reservation;

Dept of CSE(Data Science) , MREC Page 12

Database Management Systems Lab

Cancellation:

Cancellation(PNRNo: String,DOJ: Date, SeatNo: integer,ContactNo: String,Status:

String)

ColumnName Datatype Constraints Type of Attributes

PNRNo Varchar(10) Primary Key Single-valued
DOJ date Single-valued
SeatNo Integer Simple
ContactNo Varchar(15) Multi-valued
Status Varchar(10) Simple

Mysql> create table cancellation(PNRNo varchar(10),DOJ date,SeatNo integer, ContactNo

varchar(15),Status varchar(10), primary key(PNRNo), foreign key(PNRNo) references
reservation(PNRNo));

Mysql> desc cancellation;

Conclusion: The Student is able draw the tabular representation of the relations of
Roadway travels.

Viva-Voce:

1. What is the difference between SUM and COUNT ?

2. What is VIEW ? and What will you get when you use VIEW

3. What is difference between TRUNCATE and DELETE?

Dept of CSE(Data Science) , MREC Page 13

Database Management Systems Lab

WEEK 3 : NORMALIZATION
Aim: Apply the database Normalization techniques for designing relational database
tables to minimize duplication of information like 1NF, 2NF, 3NF, BCNF.

Recommended Hardware / Software Requirements:

 Hardware Requirements: Intel Based desktop PC with minimum of 166 MHZ or

faster processor with at least 1GB RAM and 500 MB free disk space.
 MySQL 5.6.1

Prerequisites: Student must know about the Relational database(SQL)

Normalization is a process of converting a relation to be standard form by decomposition a

larger relation into smaller efficient relation that depicts a good database design.

1NF: A Relation scheme is said to be in 1NF if the attribute values in the relation are
atomic.i.e., Mutli –valued attributes are not permitted.

2NF: A Relation scheme is said to be in 2NF,iff and every Non-key attribute is fully
functionally dependent on primary Key.

3NF: A Relation scheme is said to be in 3NF,iff and does not have transitivity
dependencies. A Relation is said to be 3NF if every determinant is a key for each & every
functional dependency.

BCNF: A Relation scheme is said to be BCNF if the following statements are true for eacg
FD P->Q in set F of FDs that holds for each FD. P->Q in set F of FD’s that holds over R.
Here P is the subset of attributes of R & Q is a single attribute of R.

xi) The given FD is a trival

xii) P is a super key.

Dept of CSE(Data Science) , MREC Page 14

Database Management Systems Lab

Normalized tables are:-

Mysql> create table Bus2(BusNo varchar(20) primary key,Source varchar(20),Destination

varchar(20));

Mysql>Create table passenger4(PPN varchar(15) Primary key,Name varchar(20),Age

integer,Sex char,Address varchar(20));

Mysql> Create table PassengerTicket(PPN varchar(15) Primary key,TicketNo integer);

Mysql> Create table Reservation2(PNRNO integer Primary key, JourneyDate

DateTime,NoofSeats int,Address varchar(20),ContactNo Integer);

Mysql> create table Cancellation2(PNRNO Integer primary key,JourneyDate

DateTime,NoofSeats Integer,Address varchar(20),ContactNo Integer,foreign
key(PNRNO) references Reservation2(PNRNO));

Mysql> Create table Ticket2(TicketNo Integer Primary key,JourneyDate DateTime, Age

Int(4),Sex char(2),Source varchar(20),Destination varchar(20),DeptTime varchar(2));

Conclusion: The Student is able to Normalize the tables applying

3NF Viva-Voce:

3. Define Normalization?

4. What is 1 NF (Normal Form)?

5. What is Fully Functional dependency?

6. What is 2NF?

7. What is 3NF?

8. What is BCNF (Boyce-Codd Normal Form)?

Dept of CSE(Data Science) , MREC Page 15

Database Management Systems Lab

Week 4 : PRACTICING DDL COMMANDS

Aim: Installation of MySQL. Creating database tables, altering the database, dropping
tables and truncate commands

Recommended Hardware / Software Requirements:

 Hardware Requirements: Intel Based desktop PC with minimum of 166 MHZ or

faster processor with at least 1GB RAM and 500 MB free disk space.
 MySQL 5.6.1

Installation Steps for MySQL:

1. Steps for installing MySQL

Step1
Make sure you already downloaded the MySQL essential 5.0.45 win32.msi file. Double click
on the .msi file.
Step2
This is MySQL Server 5.0 setup wizard. The setup wizard will install MySQL Server 5.0 release
5.0.45 on your computer. To continue, click next.

Dept of CSE(Data Science) , MREC Page 16

Dept of CSE(Data Science) , MREC Database Management Systems Lab

Step 3
Choose the setup type that best suits your needs. For common program features select Typical
and it’s recommended for general use. To continue, click next.

Step 4
This wizard is ready to begin installation. Destination folder will be in C:\Program Files\
MySQL\MySQL Server 5.0\. To continue, click next

Dept of CSE(Data Science) , MREC Page 17

Database Management Systems Lab

Step5
The program features you selected are being installed. Please wait while the setup wizard
installs MySQL 5.0. This may take several minutes.

Step6
To continue, click next.

Dept of CSE(Data Science) , MREC Page 18

Database Management Systems Lab

Step7
To continue, click next

Step8
Wizard Completed. Setup has finished installing MySQL 5.0. Check the configure the MySQL
server now to continue. Click Finish to exit the wizard

Dept of CSE(Data Science) , MREC Page 19

Database Management Systems Lab

Step9
The configuration wizard will allow you to configure the MySQL Server 5.0 server instance

Step10
Select a standard configuration and this will use a general purpose configuration for the
server that can be tuned manually. To continue, click next

Dept of CSE(Data Science) , MREC Page 20

Database Management Systems Lab

Step 11
Check on the install as windows service and include bin directory in windows path. To
continue, click next.

Step12
Please set the security options by entering the root password and confirm retype the password.
To continue, click next.

Dept of CSE(Data Science) , MREC Page 21

Database Management Systems Lab

Step13
Ready to execute? Clicks execute to continue.

Step14
Processing configuration in progress.

Dept of CSE(Data Science) , MREC Page 22

Database Management Systems Lab

Step 15

Configuration file created. Windows service MySQL5 installed. Press finish to close the wizard.

Dept of CSE(Data Science) , MREC Page 23

Database Management Systems Lab

Creating Tables and altering the Tables

Mysql>Create table passenger2(passportId Integer Primary Key,Name varchar(10) Not

Null,Age Integer Not Null,Sex char,Address varchar(20) Not Null);

Mysql> desc passenger2;

USING ALTER COMMAND

Adding Extra column to Existing Table

Mysql>Alter table passenger3 add column TicketNo varchar(10);

Dept of CSE(Data Science) , MREC Page 24

Database Management Systems Lab

Mysql>Alter Table passenger3 add Foreign key(TicketNo) references Ticket(TicketNo);

Mysql>Alter Table passenger3 Modify column Name varchar(20);

Dept of CSE(Data Science) , MREC Page 25

Database Management Systems Lab

Mysql>Alter table passenger drop foreign key fk1;

Mysql> Alter table passenger2 Drop column TicketNo;

Viva Voce:

1. What is DDL (Data Definition Language)?

2. What is VDL (View Definition Language)?
3. What is SDL (Storage Definition Language)?
4. What is DML (Data Manipulation Language)?
5. What is DML Compiler?
6. What is PL/SQL?

Dept of CSE(Data Science) , MREC Page 26

Database Management Systems Lab

WEEK 5: PRACTICING DML COMMANDS

Aim : Create a DML Commands are used to manage data within the scheme

objects. SELECT- retrieve data from the database

INSERT- insert data into a table
UPDATE- Updates existing data within a
table DELETE-delete all records from a table.

Recommended Hardware / Software Requirements:

 Hardware Requirements: Intel Based desktop PC with minimum of 166 MHZ or

faster processor with at least 1GB RAM and 500 MB free disk space.
 MySQL 5.6.1

Prerequisites: Student must know about the RDBMS SQL

DML Commands:

INSERT COMMAND ON BUS2 & PASSENGER2 RELATIONS

mysql> select * from Bus2;

Empty set (0.00 sec)

mysql> insert into Bus2

values(1234,'Hyderabad','Tirupathi'); Query OK, 1 row

affected (0.03 sec)

mysql> insert into Bus2 values(2345,'Hyderabad','Banglore');

Query OK, 1 row affected (0.01 sec)

mysql> insert into Bus2 values(23,'Hyderabad','Kolkata');

Query OK, 1 row affected (0.03 sec)

mysql> insert into Bus2 values(45,'Tirupathi','Banglore');

Query OK, 1 row affected (0.03 sec)

mysql> insert into Bus2 values(34,'Hyderabad','Chennai');

Query OK, 1 row affected (0.03 sec)

Dept of CSE(Data Science) , MREC Page 27

Database Management Systems Lab

mysql> select * from Bus2;

Dept of CSE(Data Science) , MREC Page 28

Database Management Systems Lab

mysql> select * from Passenger2;

Empty set (0.00 sec)

mysql> insert into Passenger2 values(145,'Ramesh',45,'M','abc123');

Query OK, 1 row affected (0.05 sec)

mysql> insert into Passenger2 values(278,'Geetha',36,'F','abc124');

Query OK, 1 row affected (0.02 sec)

mysql> insert into Passenger2 values(4590,'Ram',30,'M','abc12');

Query OK, 1 row affected (0.03 sec)

mysql> insert into Passenger2 values(6789,'Ravi',50,'M','abc14');

Query OK, 1 row affected (0.03 sec)

mysql> insert into Passenger2 values(5622,'Seetha',32,'F','abc55');

Query OK, 1 row affected (0.03 sec)

mysql> select * from Passenger2;

Dept of CSE(Data Science) , MREC Page 29

Database Management Systems Lab

UPDATE COMMAND ON BUS2 RELATION

UPDATE Selected Rows & Multiple Rows

mysql> Update Bus2 SET Source='Secundrabad' where BusNo=1234;

Query OK, 1 row affected (0.05 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Dept of CSE(Data Science) , MREC Page 30

Database Management Systems Lab

mysql> Update Bus2 SET Source='Secundrabad' where Source=’Hyderabad’;

Query OK, 1 row affected (0.05 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Dept of CSE(Data Science) , MREC Page 31

Database Management Systems Lab

DELETE COMMAND ON BUS2 RELATION

DELETES Selected Rows and Multiple Rows

mysql> Delete from Bus2 where BusNo=1234;

Query OK, 1 row affected (0.05 sec)

mysql> select * from Bus2;

Dept of CSE(Data Science) , MREC Page 32

Database Management Systems Lab

mysql> Delete from Bus2 where Source=’Secundrabad’;

Query OK, 1 row affected (0.05 sec)

mysql> select * from Bus2;

Conclusion: The Student is able perform DML Commands like Insert, Update, Delete
and Select

Viva-Voce

1. What are DML commands?

2. Write the syntax for insert command?
3. What is the syntax for update command?
4. Write the syntax for delete command?
5. Write the syntax for select command?
6. Difference between DCL and TCL

Dept of CSE(Data Science) , MREC Page 33

Database Management Systems Lab

WEEK 6A: Querying (using ANY, ALL, UNION, INTERSECT, JOIN,

Constraints etc.)

Aim: Practice the following Queries:

1. Display unique PNR_NO of all passengers

2. Display all the names of male passengers.
3. Display the ticket numbers and names of all the passengers.
4. Find the ticket numbers of the passengers whose name start with ‘r’ and ends with ‘h’.
5. Find the names of Passengers whose age is between 30 and 45.
6. Display all the passengers names beginning with ‘A’.
7. Display the sorted list of Passengers names.

Recommended Hardware / Software Requirements:

 Hardware Requirements: Intel Based desktop PC with minimum of 166 MHZ or

faster processor with at least 64MB RAM and 100 MB free disk space.
 MySQL 5.6.1

Prerequisites: Student must know about the RDBMS

Dept of CSE(Data Science) , MREC Page 34

Database Management Systems Lab

mysql> insert into passenger2 values(82302,'Smith',23,'M','Hyderabad');

Query OK, 1 row affected (0.02 sec)

mysql> insert into passenger2 values(82303,'Neha',23,'F','Hyderabad');

Query OK, 1 row affected (0.01 sec)

mysql> insert into passenger2 values(82304,'Neha',35,'F','Hyderabad');

Query OK, 1 row affected (0.03 sec)

mysql> insert into passenger2 values(82306,'Ramu',40,'M','Hyderabad');

Query OK, 1 row affected (0.02 sec)

mysql> insert into passenger2 values(82308,'Aakash',40,'M','Hyderabad');

Query OK, 1 row affected (0.02 sec)

mysql> insert into passenger2 values(82402,'Aravind',42,'M','Hyderabad');

Query OK, 1 row affected (0.02 sec)

mysql> insert into passenger2 values(82403,'Avinash',42,'M','Hyderabad');

Query OK, 1 row affected (0.02 sec)

mysql> insert into passenger2 values(82502,'Ramesh',23,'M','Hyderabad');

Query OK, 1 row affected (0.02 sec)

mysql> insert into passenger2 values(82602,'Rajesh',23,'M','Hyderabad');

Query OK, 1 row affected (0.02 sec)

RESERVATION2

mysql> insert into reservation2 values(10201,'2012-02-20 10:20:25',05,'HYD',9654

235242);
Query OK, 1 row affected (0.03 sec)

mysql> insert into reservation2 values(10202,'2012-02-22 10:22:25',05,'HYD',9654

232451);
Query OK, 1 row affected (0.02 sec)

mysql> insert into reservation2 values(10203,'2012-03-22 10:30:25',05,'DELHI',96

54587960);
Query OK, 1 row affected (0.01 sec)

mysql> insert into reservation2 values(10204,'2013-03-22 11:30:25',05,'CHENNAI',

9845761254);
Query OK, 1 row affected (0.02 sec)

Dept of CSE(Data Science) , MREC Page 35

Database Management Systems Lab

1. Display unique PNR_NO of all reservation

Mysql>Select DISTINCT PNR_NO from

Reservation;

PNR_No
10201
10202
10203
10204

2. Display all the names of male passengers.

mysql> Select p.name from passenger2 p

where p.passportid IN (select p2.passportid from passenger2 p2
where p2.sex='M');

Dept of CSE(Data Science) , MREC Page 36

Database Management Systems Lab

Dept of CSE(Data Science) , MREC Page 37

Database Management Systems Lab

Dept of CSE(Data Science) , MREC Page 38

Database Management Systems Lab

3. Display the ticket numbers and names of all the passengers.

mysql> select t.ticketno,p.name from passengerticket t,passenger2 p

where t.passportid = p.passportid;

Dept of CSE(Data Science) , MREC Page 39

Database Management Systems Lab

4. Find the ticket numbers of the passengers whose name start with ‘r’ and ends with ‘h’.

MySQL> SELECT Name FROM Passenger WHERE name LIKE ‘R%H’

Name
Rajesh
Ramesh
Ramesh

Dept of CSE(Data Science) , MREC Page 40

Database Management Systems Lab

5. Find the names of Passengers whose age is between 30 and 45.

MySQL> SELECT Name FROM PASSENGER WHERE AGE BETWEEN 30 AND 45

Dept of CSE(Data Science) , MREC Page 41

Database Management Systems Lab

6. Display all the passengers names beginning with ‘A’.

MySQL> SELECT * FROM PASSENGER WHERE NAME LIKE ‘A%’;

Name
Akash
Arivind
Avinash

Dept of CSE(Data Science) , MREC Page 42

Database Management Systems Lab

7. Display the sorted list of Passengers names

MySQL> SELECT NAME FROM PASSENGER ORDER BY NAME;

8. JOINS
Create a table EMP with the following structure.

COLUMN Name DATA Type

- -
EMPNO INTEGER(6)
ENAME VARCHAR2(20)
JOB VARCHAR2(10)
MGR INTEGER (4)
DEPTNO INTEGER (3)
SAL INTEGER (7)

Dept of CSE(Data Science) , MREC Page 43

Database Management Systems Lab

1. Creating Table Emp:

Inserting the values into the table:

Create dept table with the following structure.

COLUMN Name DATA Type

DEPTNO INTEGER (2)

DNAME VARCHAR2(10)
LOC VARCHAR2(10)
DEPTNO as the primary key
2. Creating Table Dept:

Inserting the values into the table:

Dept of CSE(Data Science) , MREC Page 44

Database Management Systems Lab

Queries:

Display all the employees and the departments implementing a left outer join.

Display the employee name and department name in which they are working implementing a full outer join.
// MySQL does not support full outer join out of the box, unlike other databases.

SELECT emame,dname FROM emp

LEFT JOIN dept ON emp.deptno = dept.deptno
UNION ALL
SELECT ename,dname FROM emp
RIGHT JOIN dept ON emp.deptno =
dept.deptnoWHERE emp.deptno IS NULL;

Find the third highest salary of an employee.

Dept of CSE(Data Science) , MREC Page 45

Database Management Systems Lab

Display all employee names and salary whose salary is greater than minimum salary of the
companyand job titlestarts with ‘M’.

Insert the Manger record and display the details:

Write a query to display information about employees who earn more than any employee in dept 30.
Inserting more values:

Query:

Dept of CSE(Data Science) , MREC Page 46

Database Management Systems Lab

Write a query to create and drop

ViewTo create a view:

To drop a view:

Constraints
SQL constraints are used to specify rules for the data in a table.

Constraints can be column level or table level. Column level constraints apply to a column, and
table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

 NOT NULL - Ensures that a column cannot have a NULL value

 UNIQUE - Ensures that all values in a column are different
 PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in
a table
 FOREIGN KEY - Prevents actions that would destroy links between tables
 CHECK - Ensures that the values in a column satisfies a specific condition
 DEFAULT - Sets a default value for a column if no value is specified
 CREATE INDEX - Used to create and retrieve data from the database very quickly

Dept of CSE(Data Science) , MREC Page 47

Database Management Systems Lab

SQL NOT NULL on CREATE TABLE

The following SQL ensures that the "ID", "LastName", and "FirstName" columns will NOT accept
NULL values when the "Persons" table is created:

CREATE TABLE Persons

( ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);

SQL NOT NULL on ALTER TABLE

To create a NOT NULL constraint on the "Age" column when the "Persons" table is already created,
use the following SQL:

My SQL / Oracle (prior version 10G):

ALTER TABLE Persons

MODIFY COLUMN Age int NOT NULL;

SQL UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different. The following SQL
creates a UNIQUE constraint on the "ID" column when the "Persons" table is created:

MySQL:

CREATE TABLE Persons

( ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID)
);

To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the
following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons

( ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT UC_Person UNIQUE (ID,LastName)
);

Dept of CSE(Data Science) , MREC Page 48

Database Management Systems Lab

SQL UNIQUE Constraint on ALTER TABLE

To create a UNIQUE constraint on the "ID" column when the table is already created, use the
following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons

ADD UNIQUE (ID);

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table.

Primary keys must contain UNIQUE values, and cannot contain NULL values.

SQL PRIMARY KEY on CREATE TABLE

The following SQL creates a PRIMARY KEY on the "ID" column when the "Persons" table is
created:

MySQL:

CREATE TABLE Persons

( ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);

SQL FOREIGN KEY Constraint

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY
KEY in another table.

Persons Table

PersonID LastName FirstName Age

1 Hansen Ola 30

2 Svendson Tove 23

3 Pettersen Kari 20

Dept of CSE(Data Science) , MREC Page 48

Database Management Systems Lab

Orders Table

OrderID OrderNumber PersonID

1 77895 3

2 44678 3

3 22456 2

4 24562 1

Notice that the "PersonID" column in the "Orders" table points to the "PersonID" column in the
"Persons" table.

The "PersonID" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.

The "PersonID" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key
column, because it has to be one of the values contained in the parent table.

SQL FOREIGN KEY on CREATE TABLE

The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders" table
is created:

MySQL:

CREATE TABLE Orders (

OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

SQL CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column.

If you define a CHECK constraint on a column it will allow only certain values for this column.

SQL CHECK on CREATE TABLE

The following SQL creates a CHECK constraint on the "Age" column when the "Persons" table is

Dept of CSE(Data Science) , MREC Page 49

Database Management Systems Lab

created. The CHECK constraint ensures that the age of a person must be 18, or older:

MySQL:

CREATE TABLE Persons

( ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
);

SQL DEFAULT Constraint

The DEFAULT constraint is used to set a default value for a column.

The default value will be added to all new records, if no other value is specified.

SQL DEFAULT on CREATE TABLE

The following SQL sets a DEFAULT value for the "City" column when the "Persons" table is
created:

My SQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons

( ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes'
);

The DEFAULT constraint can also be used to insert system values, by using functions
like GETDATE():

CREATE TABLE Orders (

ID int NOT NULL,
OrderNumber int NOT NULL,
OrderDate date DEFAULT GETDATE()
);

Dept of CSE(Data Science) , MREC Page 50

Database Management Systems Lab

SQL CREATE INDEX Statement

The CREATE INDEX statement is used to create indexes in tables.

Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot
see the indexes, they are just used to speed up searches/queries.

CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name

ON table_name (column1, column2, ...);

CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name

ON table_name (column1, column2, ...);

CREATE INDEX Example

The SQL statement below creates an index named "idx_lastname" on the "LastName" column in
the "Persons" table:

CREATE INDEX idx_lastname

ON Persons (LastName);

If you want to create an index on a combination of columns, you can list the column names within
the parentheses, separated by commas:

CREATE INDEX idx_pname

ON Persons (LastName, FirstName);

Conclusion: The Student is able execute the Queries from above database.

Viva-Vice:
1. What is the result of String functions?

2. What is the result of Date functions?

3. What is the result of conversion function?

4. What is Concatenation?

5. What is the difference between LTRIM and RTRIM?

Dept of CSE(Data Science) , MREC Page 51

Database Management Systems Lab

WEEK 6B: Nested, Correlated subqueries

Nested Queries

A query embedded in a query. This type of relation is termed as Nested Query and the Embedded
Query is termed as a subquery.

Example

To find the names of employee who are department Id 103.

SELECT E. ename FROM Employee EWHERE E.id IN (SELECTD.id FROM Department D

WHERE D.i d = 10 3)
Correlated subqueries are used for row-by-row processing. Each subquery is executed once for every row of the outer
query.

SQL_Correlated_Subqueries

A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement
can be a SELECT, UPDATE, or DELETE statement.

SELECT column1, column2, ....

FROM table1 outer
WHERE column1 operator
(SELECT column1, column2
FROM table2
WHERE expr1 =
outer.expr2);

Dept of CSE(Data Science) , MREC Page 52

Database Management Systems Lab

A correlated subquery is one way of reading every row in a table and comparing values in each row
against related data. It is used whenever a subquery must return a different result or set of results for each
candidate row considered by the main query. In other words, you can use a correlated subquery to answer a
multipart question whose answer depends on the value in each row processed by the parent statement.

Nested Subqueries Versus Correlated Subqueries :

With a normal nested subquery, the inner SELECT query runs first and executes once, returning values to
be used by the main query. A correlated subquery, however, executes once for each candidate row
considered by the outer query. In other words, the inner query is driven by the outer query.
NOTE: You can also use the ANY and ALL operator in a correlated subquery. EXAMPLE of
Correlated Subqueries : Find all the employees who earn more than the average salary in their department.

SELECT last_name, salary, department_id

FROM employees outer
WHERE salary >
(SELECT AVG(salary)
FROM employees
WHERE department_id =
outer.department_id group by department_id);
Other use of correlation is in UPDATE and DELETE
CORRELATED UPDATE :
UPDATE table1 alias1
SET column = (SELECT expression
FROM table2 alias2
WHERE alias1.column =
alias2.column);

Use a correlated subquery to update rows in one table based on rows from another table.
CORRELATED DELETE :
DELETE FROM table1 alias1
WHERE column1 operator
(SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);
Use a correlated subquery to delete rows in one table based on the rows from another table.

Dept of CSE(Data Science) , MREC Page 53

Database Management Systems Lab

Using the EXISTS Operator :

The EXISTS operator tests for existence of rows in the results set of the subquery. If a subquery row value is
found the condition is flagged TRUE and the search does not continue in the inner query, and if it is not found
then the condition is flagged FALSE and the search continues in the inner query.
EXAMPLE of using EXIST operator :
Find employees who have at least one person reporting to them.

SELECT employee_id, last_name, job_id, department_id

FROM employees outer
WHERE EXISTS ( SELECT ’X’
FROM employees
WHERE manager_id =
outer.employee_id);
OUTPUT :

EXAMPLE of using NOT EXIST operator :

Find all departments that do not have any employees.

SELECT department_id, department_name

FROM departments d
WHERE NOT EXISTS (SELECT ’X’
FROM employees
WHERE department_id
= d.department_id);

Dept of CSE(Data Science) , MREC Page 54

Database Management Systems Lab

OUTPUT :

Correlated Nested Queries

These types of queries are nested queries which are independent or depend only on the same row of an
outer query being an embedded query.

Example

To find the names of employee who are department Id 103.

SELECT E.enameFROM Employee EWHERE EXISTS (SELECT *x FROM Department D

WHERE D.id = 103 AND D.id = E.id)

Set Comparison Operators

There are different types of set comparison operators like EXISTS, IN and UNIQUE. SQL also
supports op ANY and op ALL, where op means arithmetic comparison operators such
as <, <=, =, <>, >=, >. SOME are also one of the set comparison operators but it is similar to ANY.

Example

Find Employees whose salary is greater than an employee named Shivam.

SELECT E.idFROM Employee EWHERE E.salary>ANY (SELECTE2.salary FROM Employee

E2 WHERE E2.ename = 'Shivam')

Dept of CSE , MREC Page 55

Database Management Systems Lab

WEEK 7: Querying Aggregate Functions(COUNT,SUM,AVG,MAX and MIN),

GROUP BY, HAVING and Creation and dropping of Views.

Aim: To Practice Queries using Aggregate functions, group by, having and dropping views for

the following

1. Write a Query to display the information present in the passenger and cancellation
tables
2. Display the number of days in a week on which the AP123 bus is available
3. Find number of tickets booked for each PNR_No using GROUP BY CLAUSE
4. Find the distinct PNR Numbers that are present.

Recommended Hardware / Software Requirements:

 Hardware Requirements: Intel Based desktop PC with minimum of 166 MHZ or

faster processor with at least 1GB RAM and 500 MB free disk space.
 MySQL 5.6.1

Prerequisites: Student must know about the RDBMS-SQL

1. Write a Query to display the information present in the passenger and cancellation
tables

MYSQL> CREATE TABLE CANCELLATION2(PNRNO INT PRIMARY

KEY,JOURNEYDATE DATETIME, NOOFSEATS INT,ADDRESS
VARCHAR(20),CONTACTNO INT,STATUS VARCHAR(10),FOREIGN
KEY(PNRNO) REFERENCES RESERVATION2(PNRNO));

mysql> INSERT INTO CANCELLATION2 VALUES(10201,'2012-02-20

10:20:25',2,'HYD',9654235242,'CONFIRM');

mysql> INSERT INTO CANCELLATION2 VALUES(10202,'2012-02-22

10:22:25',2,'HYD',9654232451,'CONFIRM');

mysql> INSERT INTO CANCELLATION2 VALUES(10203,'2012-03-22

10:30:25',2,'DELHI',9654587960,'CONFIRM');

Dept of CSE , MREC Page 56

Database Management Systems Lab

MySQL> SELECT * FROM RESERVATION

UNION

SELECT * FROM CANCELLATION;

2. Display the Minimum age of the Passenger

MySQL> SELECT MIN(AGE) as MINAGE FROM PASSENGER;

Dept of CSE , MREC Page 57

Database Management Systems Lab

3. Find number of tickets booked for each PNR_No using GROUP BY CLAUSE

MySQL> SELECT PNRNO,SUM(No_of_SEATS) AS SUM_OF_SEATS FROM

RESERVATION2 GROUP BY PNRNO;

4. Find the distinct PNR Numbers that are present.

MySQL> SELECT DISTINCT PNR_NO FROM RESERVATION2;

Dept of CSE , MREC Page 58

Database Management Systems Lab

5.Mysql> select sum(Noofseats) from Cancellation2;

1. Find the number of tickets booked by a passenger where the number of seats is
greater than 1.
2. Find the total number of cancelled seats.

Recommended Hardware / Software Requirements:

 Hardware Requirements: Intel Based desktop PC with minimum of 166 MHZ or

faster processor with at least 1GB RAM and 500 MB free disk space.
 MySQL 5.6.1

Prerequisites: Student must know about the RDBMS-SQL

Find the number of tickets booked by a passenger where the number of seats is
greater than 1.

MySQL> select sum(noofseats) from reservation2 GROUP BY PNRNO HAVING

Noofseats >1;

Dept of CSE , MREC Page 59

Database Management Systems Lab

Find the total number of cancelled seats.

MySQL> select sum(noofseats) as canceled_seats from cancellation2;

Creation and Droping of Views

mysql> create table students(sid int primary key,name varchar(15),login varchar(15), age int,gpa real);

mysql> create table Enrolled(sid int,cid int,grade varchar(5),primary key(sid,cid),

foreign key(sid) references students(sid));

Dept of CSE , MREC Page 60

Database Management Systems Lab

mysql>create view BStudents(name,sid,course) AS SELECT s.name,s.sid,E.cid from

students s,enrolled E where s.sid=e.sid AND E.grade='B';

mysql> create view Goodstudents(sid,gpa) AS select s.sid,s.gpa from students s where gpa > 3.0;

Syntax: Drop view viewname;

Mysql> Drop view Bstudents;

Mysql> Drop view Goodstudents;

Dept of CSE , MREC Page 61

Database Management Systems Lab

Viva-Voce:

1. What is the difference between SUM and COUNT ?

2. What will you get when you use MIN ?
3. What will you get when you use MAX ?
4. What is VIEW ? and What will you get when you use VIEW
5. What is difference between DROP table and DELETE?
6. What will you get when you use AVG?

Dept of CSE , MREC Page 62

Database Management Systems Lab

WEEK 8: Triggers (Creation of insert trigger, delete trigger, update trigger)

Aim: Creation of insert trigger, delete trigger and update trigger.

Recommended Hardware / Software Requirements:

 Hardware Requirements: Intel Based desktop PC with minimum of 166 MHZ or faster
processor with at least 64MB RAM and 100 MB free disk space.
 MySQL 5.6.1

Prerequisites: Student must know about the Relational Database SQL-Triggers.

MySQL>CREATE TABLE BUS(BUSNO VARCHAR(10) NOT NULL,

SOURCE VARCHAR(10), DESTINATION VARCHAR(10),

CAPACITY INT(2), PRIMARY KEY(BUSNO));

MySQL>INSERT INTO BUS VALUES('AP123','HYD','CHENNAI','40');

Dept of CSE , MREC Page 63

Database Management Systems Lab

i) CREATE TABLE BUS_AUDIT1(ID INT NOT NULL AUTO_INCREMENT,

SOURCE VARCHAR(10) NOT NULL, CHANGEDON DATETIME DEFAULT NULL,

ACTION VARCHAR(10) DEFAULT NULL, PRIMARY KEY(ID));

Dept of CSE , MREC Page 64

Database Management Systems Lab

DELIMITER $$

CREATE TRIGGER BEFORE_BUS_UPDATE

BEFORE UPDATE ON BUS

FOR EACH ROW

BEGIN

INSERT INTO BUS_AUDIT1

SET action='update',

source=OLD.source,

changedon=NOW();

END$$

Dept of CSE , MREC Page 65

Database Management Systems Lab

i)UPDATE :

MySQL>UPDATE BUS SET SOURCE='KERALA' WHERE BUSNO='AP123'$$

SNo Source Changedon Action

1 Banglore 2014:03:23 12:51:00 Insert
2 Kerela 2014:03:25:12:56:00 Update
3 Mumbai 2014:04:26:12:59:02 Delete

Dept of CSE , MREC Page 66

Database Management Systems Lab

ii) INSERT:

CREATE TRIGGER

BEFORE_BUS_INSERT BEFORE INSERT

ON BUS

FOR EACH ROW

BEGIN

INSERT INTO BUS_AUDIT1

SET action='Insert',

source=NEW.source,

changedon=NOW();

END$$

MYSQL>INSERT INTO BUS VALUES('AP789','VIZAG','HYDERABAD',30)$$

SNo Source Changedon Action

1 Banglore 2014:03:23 12:51:00 Insert
2 Kerela 2014:03:25:12:56:00 Update
3 Mumbai 2014:04:26:12:59:02 Delete

Dept of CSE , MREC Page 67

Database Management Systems Lab

Dept of CSE , MREC Page 68

Database Management Systems Lab

iii)

CREATE TRIGGER BEFORE_BUS_DELETE

BEFORE DELETE ON BUS

FOR EACH ROW

BEGIN

DELETE FROM BUS_AUDIT1

SET action='Insert',

source=NEW.source,

changedon=NOW();

END$$

DELETE FROM BUS WHERE SOURCE=’HYDERABAD’$$

SNo Source Changedon Action

1 Banglore 2014:03:23 12:51:00 Insert
2 Kerela 2014:03:25:12:56:00 Update
3 Mumbai 2014:04:26:12:59:02 Delete

Dept of CSE , MREC Page 69

Database Management Systems Lab

Examples

CREATE TRIGGER updcheck1 BEFORE UPDATE ON passengerticket

FOR EACH ROW

BEGIN

IF NEW.TicketNO > 60 THEN

SET New.TicketNo = New.TicketNo;

ELSE

SET New.TicketNo = 0;

END IF;

END;

Dept of CSE , MREC Page 70

Database Management Systems Lab

Dept of CSE , MREC Page 71

Database Management Systems Lab

Conclusion: The Student is able to work on Triggers and create active

database. Viva-Vice:

1. What is TRIGGER?

2. What is BEFORE Trigger?

3. What is AFTER Trigger?

4. What is the difference between BEFORE and AFTER?

5. What is ROW and Statement Triggers?

6. What is INSTEAD Triggers?

7. What are the types on Triggers?

8. It is possible to create Trigger on Views?

Dept of CSE , MREC Page 72

Database Management Systems Lab

WEEK 9: Procedures
Aim: Creation of stored Procedures and Execution of Procedures and Modification of
Procedures.

Recommended Hardware / Software Requirements:

 Hardware Requirements: Intel Based desktop PC with minimum of 166 MHZ or

faster processor with at least 64MB RAM and 100 MB free disk space.
 MySQL 5.6.1

Prerequisites: Student must know about the Relational Database SQL-Procedures

Ex1:

CREATE PROCEDURE BUS_PROC1()

BEGIN

SELECT * FROM BUS;

END$$

CALL BUS_PROC1()$$

Dept of CSE , MREC Page 73

Database Management Systems Lab

Ex2:

CREATE PROCEDURE SAMPLE2()

BEGIN

DECLARE X

INT(3); SET X=10;

SELECT X;

END$$

Mysql> CALL SAMPLE2()$$

Dept of CSE , MREC Page 74

Database Management Systems Lab

Ex3: CREATE PROCEDURE SIMPLE_PROC(OUT PARAM1 INT)

BEGIN

SELECT COUNT(*) INTO PARAM1 FROM BUS;

END$$

Mysql> CALL

SIMPLE_PROC(@a)$$Mysql> select

@a;

Viva Voce:

1. What is a stored procedure?

2. When would you use stored procedure or functions ?
3. What are external procedures?
4. What is input parameter?
5. How to use Stored Procedures.

Dept of CSE , MREC Page 75

Database Management Systems Lab

WEEK 10: Cursors

Aim: Declare a cursor that defines a result set. Open the cursor to establish the result set.
Fetch the data into local variables as needed from the cursor, one row at a time. Close the
cursor when done.

Recommended Hardware / Software Requirements:

 Hardware Requirements: Intel Based desktop PC with minimum of 166 MHZ or

faster processor with at least 64MB RAM and 100 MB free disk space.
 MySQL 5.6.1

Prerequisites: Student must know about the Relational SQL-Cursors

Cursors
In MySQL, a cursor allows row-by-row processing of the result sets. A cursor is used for the
result set and returned from a query. By using a cursor, you can iterate, or by step through the
results of a query and perform certain operations on each row. The cursor allows you to iterate
through the result set and then perform the additional processing only on the rows that require it.

In a cursor contains the data in a loop. Cursors may be different from SQL commands that
operate on all the rows in the returned by a query at one time.

There are some steps we have to follow, given below :

 Declare a cursor

 Open a cursor statement

 Fetch the cursor

 Close the cursor

Dept of CSE , MREC Page 76

Database Management Systems Lab

1 . Declaration of Cursor : To declare a cursor you must use the DECLARE statement. With
the help of the variables, conditions and handlers we need to declare a cursor before we can use
it. first of all we will give the cursor a name, this is how we will refer to it later in the procedure.
We can have more than one cursor in a single procedure so its necessary to give it a name that
will in some way tell us what its doing. We then need to specify the select statement we want to
associate with the cursor. The SQL statement can be any valid SQL statement and it is possible
to use a dynamic where clause using variable or parameters as we have seen previously.

Syntax : DECLARE cursor_name CURSOR FOR select_statement;

2 . Open a cursor statement : For open a cursor we must use the open statement.If we want
to fetch rows from it you must open the cursor.

Syntax : OPEN cursor_name;

3 . Cursor fetch statement : When we have to retrieve the next row from the cursor and
move the cursor to next row then you need to fetch the cursor.

Synatx : FETCH cursor_name INTO var_name;

If any row exists, then the above statement fetches the next row and cursor pointer moves ahead
to the next row.

4 . Cursor close statement : By this statement closed the open cursor.

Syntax: CLOSE_name;

By this statement we can close the previously opened cursor. If it is not closed explicitly then a
cursor is closed at the end of compound statement in which that was declared.

Dept of CSE , MREC Page 77

Database Management Systems Lab

Delimiter $$

Create procedure p1(in_customer_id int)

begin
declare v_id int;
declare v_name varchar(20);
declare v_finished integer default 0;
declare c1 cursor for select sid,sname from students where sid=in_customer_id;
declare continue handler for NOT FOUND set v_finished=1;
open c1;
std:LOOP
fetch c1 into v_id,v_name;
if v_finished=1 then
leave std;
end if;
select concat(v_id,v_name);
end LOOP std;
close c1;
end;

Dept of CSE , MREC Page 78

Database Management Systems Lab

Dept of CSE , MREC Page 79

Database Management Systems Lab

Conclusion: The Student is able to work on Cursors.

Viva Voce:

1. What is a cursor?

2. What are the types of cursor?

3. What is the use of parameterized cursor?
4. What is the use of cursor variable?
5. What is a normal cursor?
6. What are Explicit cursor attributes?

Dept of CSE , MREC Page 80

Database Management Systems Lab

ADDITIONAL PROGRAMMS

EMPLOYEES TABLE

mysql> create table Employees(ssn varchar(15),name varchar(20),lot int,PRIMARY KEY(ssn));

mysql> insert into Employees values('123-22-3666','Attishoo',48);

mysql> insert into Employees values('321-31-5368','Smiley',22);

mysql> insert into Employees values('131-24-3650','Smethurst',35);

Dept of CSE , MREC Page 81

Database Management Systems Lab

Dept of CSE , MREC Page 82

Database Management Systems Lab

DEPARTMENT TABLE

mysql> create table Departments(did int,dname varchar(10),budget real, PRIMARY KEY(did));

mysql> insert into Departments values(05,'CSE',500000);

mysql> insert into Departments values(04,'ECE',400000);

mysql> insert into Departments values(03,'ME',300000);

mysql> insert into Departments values(01,'CE',100000);

Dept of CSE , MREC Page 83

Database Management Systems Lab

Sailors , Reserves , Boats Tables

Mysql> Create table Sailors(Sid integer PRIMARY KEY,sname varchar(15), rating int,age real);

Mysql>Create table Reserves(Sid int,Bid int,Day Date);

Mysql>Create table Boats(Bid int,Bname varchar(15),Color varchar(15);

Dept of CSE , MREC Page 84

Database Management Systems Lab

mysql> select S.sname from sailors S, reserves R where S.sid=R.sid AND R.bid=103;

mysql> select sname from sailors s,Reserves R where S.sid=R.sid AND bid=103;

mysql> select R.sid from Boats B,Reserves R where B.bid=R.bid AND B.color='red';

mysql> select S.sname from sailors S,reserves R,Boats B where S.sid=R.sid AND R.bid=B.bid

AND B.color='red';

mysql> select B.color from Sailors S,Reserves R,Boats B where S.sid=R.sid AND R.bid=B.bid

AND S.sname='Lubber';

Dept of CSE , MREC Page 85

Database Management Systems Lab

mysql> select S.sname,S.rating+1 AS rating from Sailors S,Reserves R1,Reserves R2 where

S.sid=R1.sid AND S.sid=R2.sid AND R1.day=R2.day AND R1.bid<>R2.bid;

mysql> select S1.sname AS name1,S2.sname AS name2 from sailors S1,sailors S2

where 2*S1.rating=S2.rating-1;

Dept of CSE , MREC Page 86

Database Management Systems Lab

Dept of CSE , MREC Page 87

Database Management Systems Lab

USING UNION , INTERSECT , AND EXCEPT

1).Find the names of sailors who have reserved a red or a green boat.

OR

Dept of CSE , MREC Page 88

Database Management Systems Lab

2). Find the names of sailors who have reserved both a red and a green boat.

SELECT S.SNAME
FROM SAILORS S,RESERVES R,BOATS B
WHERE S.SID=R.SID AND R.BID=B.BID AND B.COLOR='red'
INTERSECT
SELECT S2.SNAME
FROM SAILORS S2,RESERVES R2,BOATS B2
WHERE S2.SID=R2.SID AND R2.BID=B2.BID AND B2.COLOR='green';

NESTED QUERIES

Find the Names of sailors who have reserved boat 103

Find the names of Sailors who have reserved a red Boat

Dept of CSE , MREC Page 89

Database Management Systems Lab

Find the names of Sailors who have NOT reserved a red Boat

Correlated Nested Queries:

Find the names of Sailors who have reserved a red Boat

Set Comparison Operators:

Find sailors whose rating is better than some sailor called Horatio

Dept of CSE , MREC Page 90

Database Management Systems Lab

Find the sailors with the highest rating.

mysql> SELECT S.sid FORM Sailors WHERE S.rating>=ALL(SELECT S2.rating FROM

Sailors S2);

The GROUP BY and HAVING Clauses:

Find the age of the youngest sailor for each rating level.

Find the age of the youngest sailor who is eligible to vote for each rating level with at least two
such sailors

Dept of CSE , MREC Page 91

Database Management Systems Lab

For each red boat , find the number of reservations for this boat

Find the average age of sailors for each rating level that has at least two sailors

Dept of CSE , MREC Page 92