CICINCIV

IIIYear I Sem - EEE Fundamentals of Data Base Management Systems: 2024-2025

Laboratory Record

Department of Electrical and Electronics

Engineering
Fundamentals of Data Base Management Systems LAB
COURSE FILE

III B. Tech - II Semester

Sub: Fundamentals of Data Base Management Systems
Laboratory RecordSubject Code :B0556
Academic Year 2024-25
Regulations:MR22

Malla Reddy Engineering College

(Autonomous)
(An UGC Autonomous Institution, Approved by AICTE and Affiliated to JNTUH Hyderabad,
Recognized under 2(f) &12 (B) of UGC Act 1956, Accredited by NAAC with ‘A’ Grade (II Cycle)
Maisammaguda, Dhulapally (Post Via Kompally), Secunderabad-500 100
www.mrec.ac.in E-mail: principal@mrec.ac.in
2024-2025

1
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Record

MALLA REDDY ENGINEERING COLLEGE B.Tech.

(MR-21)
(Autonomous) V Semester
Code: B0563 Fundamentals of Database Management Systems Lab L T P
Credits: 2 (Common for CE, EEE, ME, ECE, MiE) - 1 2

Prerequisites: NIL
Course Objectives: This course enables the students to practice the concepts learnt in the
subject DBMS by developing a database for an example project. The student is expected to
and PL/SQL statements using a sample database.
Sample Database: Textbooks: Railway Reservation System -(Redesigning IRCTC database)
Train (train Number, name, source, destination, start_time, reach_time, travel time, distance,
class, days, type)
Ticket (PNR No, Transaction id, from_station, To_station, date_of_journey, class
date_of_booking, total_ticket_fare, train number)
Passenger (PNR No, Serial no, Name, Age, Reservation_status)
Train_Route(Train_No, route_no, station_code, name, arrival_time, depart_time, distance,
day) Train_Ticket_fare(Train_No, class, base_fare, reservation_charge, superfast_charge,
other_charge, tatkal_charge, service_tax)

List of experiments:

1. SQL Data Definition Language Commands: Create all the tables specified above. Make
underlined columns as primary key. (use number, number(m,n), varchar(n), date, time,
timestamp data types appropriately) Insert at least 5 rows to each table. (Check
www.irctc.co.in website for actual data)

2. SQL Data Manipulation Language Commands:

1. Change the name of the Passenger table to Passenger_Details.
2. List all train details.
3. List all passenger details.
4. Give a list of trains in ascending order of number.
5. List the senior citizen passengers details.
6. List the station names where code starts with 'M'.
7. List the trains details within a range of numbers.
8. Change the super fast charge value in train fare as zero, if it is null.
9. List the passenger names whose tickets are not confirmed.
10. Remove all the rows from Passenger table permanently.

3. Create (Alter table to add constraint) the necessary foreign keys by identifying the
relationships in the table. 1) Add a suitable constraint to train table to always have train no in
the range 10001 to 99999. 2) Add a suitable constraint for the column of station name, so that
does not take duplicates. 3) Change the data type of arrival time, depart time (date ->
timestamp or timestamp to date), and do the necessary process for updating the table with
new values. 4) Add a suitable constraint for the class column that it should take values only
as 3 1A, 2A, 3A, SL, C. 5) Add a not null constraint for the column distance in train_route.

4. Designing Employee Database with MySQL (Create and insert data in EMP table, DEPT
table and SALGRADE table)

2
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC
5. Multi row functions, GROUP ByLaboratory Record clause, ORDER BY clause in SQL on
clause, HAVING
sample database.

6. Use Join Query

1. Find the train names that stop in 'Katpadi'.
2. Find the train names that are superfast and the service tax is zero.
3. Find the Passenger name (and train name) who have booked for the train that starts
from 'Chennai'.
4. Display the trains names, each type of class and the total fare for each type of class.
5. Display all the train details and the ticket details (if booked any).

7. Use Nested Query(in Operators)

1. Find the train names that stop in 'Warangal'.
2. Find the train names that are superfast and the service tax is zero.
3. Find the Passenger name who have booked for the train that starts from
'Secunderabad'.
4. Find the trains names that have all the AC coaches and the base fare is less than 3000
for each case.

8. Create sample Views and practice basic operation

9. Write a PL/SQL procedures to practice Conditional Statements

10. Write a PL/SQL procedures to practice Iterative Statements

11. Implementing simple trigger

12. Implementing simple cursor

Textbooks:
1. Database Management Systems, Raghurama Krishnan, Johannes Gehrke, Tata Mc Graw
Hill 3rd Edition
2. Database System Concepts, Silberschatz, Korth, Mc Graw hill, V edition.

References:
1. Database Systems design, Implementation, and Management, Peter Rob & Carlos Coronel,
7th Edition.
2. SQL The Complete Reference, James R. Groff, Paul N. Weinberg, 3rd Edition,
3. Oracle for Professionals, The X Team, S.Shah and V. Shah, SPD.
4. Database Systems Using Oracle: A Simplified guide to SQL and PL/SQL,Shah,PHI.

Course Outcomes:
At the end of the course, students will be able to

1. Design and implement a database schema for a given problem.

2. Generate queries using SQL DML/DDL/DCL commands on a database.
3. Declare normalization techniques for development of application software to realistic problems.
4. Make use of procedures for various Data Base accessing and manipulations.

3
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Record

CO- PO, PSO Mapping

(3/2/1 indicates strengthof correlation) 3-Strong, 2-Medium, 1-Weak
COs
Programme Outcomes (POs) PSOs
PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO12 PSO1 PSO2 PSO3
CO1 2 - -
CO2 - 2 2 1 2
CO3 2 2 2 3 2 2

4
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Record
Week 1 – Introduction to MySQL
MySQL
(Structured Query Language)
SQL is a standard language for storing, manipulating and retrieving data in databases.
SQL in: MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres, and other database systems.

Introduction to SQL
SQL is a standard language for accessing and manipulating databases.

What is SQL?
 SQL stands for Structured Query Language
 SQL lets you access and manipulate databases
 SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the
International Organization for Standardization (ISO) in 1987
What Can SQL do?
 SQL can execute queries against a database
 SQL can retrieve data from a database
 SQL can insert records in a database
 SQL can update records in a database
 SQL can delete records from a database
 SQL can create new databases
 SQL can create new tables in a database
 SQL can create stored procedures in a database
 SQL can create views in a database
 SQL can set permissions on tables, procedures, and views

SQL is a Standard - BUT....

Although SQL is an ANSI/ISO standard, there are different versions of the SQL language.
However, to be compliant with the ANSI standard, they all support at least the major commands (such as
SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.
Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL
standard!
Using SQL in Your Web Site
To build a web site that shows data from a database, you will need:
 An RDBMS database program (i.e. MS Access, SQL Server, MySQL)
 To use a server-side scripting language, like PHP or ASP
 To use SQL to get the data you want
 To use HTML / CSS to style the page

RDBMS
RDBMS stands for Relational Database Management System.
RDBMS is the basis for SQL, and for all modern databasesystems such as MS SQL Server, IBM DB2 , Oracle,
MySQL, and Microsoft Access.
The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries
and it consists of columns and rows.
Look at the "Customers" table:
Example
SELECT * FROM Customers;
Try it Yourself »

5
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Record
Every table is broken up into smaller entities called fields. The fields in the Customers table consist of
CustomerID, CustomerName, ContactName, Address, City, PostalCode and Country. A field is a column in
a table that is designed to maintain specific information about every record in the table.
A record, also called a row, is each individual entry that exists in a table. For example, there are 91 records in
the above Customers table. A record is a horizontal entity in a table.
A column is a vertical entity in a table that contains all information associated with a specific field in a table.

SQL Syntax
Database Tables
A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or
"Orders"). Tables contain records (rows) with data.
In this tutorial we will use the well-known Northwind sample database (included in MS Access and MS SQL
Server).
Below is a selection from the "Customers" table:
Custo- CustomerName ContactName Address City Postal Country
MerID Code

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

2 Ana Trujillo Ana Trujillo Avda. de la México D.F. 05021 Mexico

Emparedadosy helados Constitución 2222

3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico

4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK

5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden

The table above contains five records (one for each customer) and seven columns (CustomerID,
CustomerName, ContactName, Address, City, PostalCode, and Country).

SQL Statements
Most of the actions you need to perform on a database are done with SQL statements.

The following SQL statement selects all the records in the "Customers" table:
Example
SELECT * FROM Customers;
Try it Yourself »
In this tutorial we will teach you all about the different SQL statements.

Keep in Mind That...

 SQL keywords are NOT case sensitive: select is the same as SELECT
In this tutorial we will write all SQL keywords in upper-case.

Semicolon after SQL Statements?

Some database systems require a semicolon at the end of each SQL statement.
Semicolon is the standard way to separate each SQL statement in database systems that allow more than one
SQL statement to be executed in the same call to the server.
In this tutorial, we will use semicolon at the end of each SQL statement.

Some of The Most Important SQL Commands

1. DDL: Data Definition Language (DDL) statements are used to define the database structure or schema.

6
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Record
 CREATE DATABASE - creates a new database
 ALTER DATABASE - modifies a database
 CREATE TABLE - creates a new table
 ALTER TABLE - modifies a table
 DROP TABLE - deletes a table
 CREATE INDEX - creates an index (search key) 
 DROP INDEX - deletes an index
 RENAME - rename an object

2. DML: Data Manipulation Language (DML) statements are used for managing data within schema objects
and to manipulate data of a database objects.
 SELECT - extracts data from a database 
 UPDATE - updates data in a database
 DELETE - deletes data from a database
 INSERT INTO - inserts new data into a database
 TRUNCATE - remove all records from a table, including all spaces allocated for the records are
removed
3. DCL: Data Control Language (DCL) statements are used to create roles, permissions, and referential
integrity as well it is used to control access to database by securing it. To control the data of a database.
 DCL Commands: Grant, Revoke
 GRANT - gives user's access privileges to database
 REVOKE -withdraw access privileges given with the GRANT command

4. TCL: Transaction Control (TCL) statements are used to manage the changes made by DML statements.
It allows statements to be grouped together into logical transactions.
 TCL Commands: Commit, Rollback, Save point
 COMMIT - save work done
 SAVEPOINT - identify a point in a transaction to which you can later roll back
ROLLBACK - restore database to original since the last COMMIT

7
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Record
Experiment 1

1. SQL Data Definition Language Commands: Create all the tables specified above. Make
underlined columns as primary key. (use number, number(m,n), varchar(n), date, time,
timestamp data types appropriately)
a. Insert at least 5 rows to each table. (Check www.irctc.co.in website for actual data)

SOLUTION:
MYSQL> CREATE DATABASE RailwayReservationSystem;
Use RailwayReservationSystem;

MYSQL> CREATE TABLE Train

(
TrainNumber NUMERIC(6),
TrainName VARCHAR(20),
Source VARCHAR(20),
Destination VARCHAR(20),
start_time TIME,
reach_time TIME,
Travaltime TIME,
distance NUMERIC(10),
Class VARCHAR(4),
days NUMERIC(2),
type VARCHAR(10),
PRIMARY KEY (TrainNumber)
);
MYSQL>
1.INSERT into Train VALUES(10001,'hyderabad exp','hyderabad', 'vizag', 100023,231123, 24, 2456,'A',
3,'AC');

2.INSERT into Train VALUES (12260,'Vijayawada exp','Vijayawada', 'Warangal', 120322,

230222, 8, 2000,'S',1,'General');

3.INSERT into Train VALUES (11111,'Banglore exp','hyderabad', 'banglore',

'140225',062450, 48, 256,'B',2,'AC');
4.INSERT into Train VALUES (12280,'mumbai exp','chennai', 'mumbai',
'154555',202453, 25, 8000,'S',2,'General');

5.INSERT into Train VALUES (12255,'delhi exp','delhi', 'mumbai', 202542,052322, 12,

800,'A',1,'General');
MYSQL> desc Train;

8
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Record

Field Type Null Key Default Extra

TrainNumber NUMERIC(6) YES NULL
TrainName VARCHAR(20) YES NULL

Source VARCHAR(20) YES NULL

Destination VARCHAR(20) YES NULL

start_time TIME YES NULL

reach_time TIME YES NULL

Travaltime TIME YES NULL

Distance NUMERIC(10) YES NULL

Class VARCHAR(20) YES NULL

days NUMERIC(2) YES NULL

type VARCHAR(10) YES NULL

PRIMAR (TrainNumber) YES PRIMAR NULL

KEY KEY

9
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Record
a. Add column designation to the department table.
MYSQL> alter table Train add (contactNo int(30));
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0

MYSQL> select * from Train;

TICKET
MYSQL>CREATE TABLE Ticket
(
PNRNo NUMERIC(20),
Transactionid VARCHAR(20),
from_station VARCHAR(20),
To_station VARCHAR(20),
date_of_journey DATE,
Class VARCHAR(4),
date_of_booking DATE,
total_ticket_fare NUMERIC(10),
TrainNumber NUMERIC(6),

PRIMARY KEY (PNRNo)

);

MYSQL>
INSERT into Ticket VALUES (122500023,123456789,'hyderabad', 'vizag', '20220310','A','20220309',
240, 10001);

INSERT into Ticket VALUES (122500024,1234567810,'vijayawada', 'Warangal', '20220311','S''20220310',

100, 12260);

INSERT into Ticket VALUES (122500025,1234567811,'hyderabad', 'Banglore', '20220312','B','20220311',

1000, 11111);

INSERT into Ticket VALUES (122500026,1234567812,'Chennai', 'Mumbai', '20220313','S','20220312',

1020, 12280);

INSERT into Ticket VALUES (122500027,1234567813,'Delhi', 'Mumbai', '20220314','A','20220313',

2020, 12255);
10
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC
MYSQL > Select * from Ticket; Laboratory Record

--PASSENGER
MYSQL > CREATE TABLE Passenger(
PNRNo NUMERIC(20),
SerialNo VARCHAR(20),
Name VARCHAR(20),
Age NUMERIC(3),
Reservation_Status VARCHAR(20),

PRIMARY KEY (PNRNo,SerialNo) );

MYSQL >
INSERT into Passenger VALUES (122500023,12345,'Allen', '20', 'Waiting_List');

INSERT into Passenger VALUES (122500024,12346,'Alex','28','Waiting_List');

INSERT into Passenger VALUES (122500025,12347,'Bhuvan', '38', 'Confirmed');

INSERT into Passenger VALUES (122500027,12349,'Charls', '88', 'Confirmed');

INSERT into Passenger VALUES (122500028,12310,'keri', '19', 'Waiting_List');

MYSQL > Select * from Passenger;

11
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC
TRAIN ROUTE Laboratory Record

MYSQL > CREATE TABLE TrainRoute

(
TrainNumber NUMERIC(6),
RouteNo NUMERIC(20),
Station_code VARCHAR(20),
Name VARCHAR(10),
arrival_time TIME,
Departure_time TIME,
distance NUMERIC(10),
days NUMERIC(2),
PRIMARY KEY (TrainNumber,RouteNo)
);

MYSQL >
INSERT into TrainRoute VALUES (10001,'2564444','hyd','Allen','111110','231110',2456,3);

INSERT into TrainRoute VALUES (12260,'2564445','bza', 'Alex', '121110','231210', 245,2);

INSERT into TrainRouteVALUES (11111,'2564446','mum', 'Bhuvan', '121120','231420', 55,1);

INSERT into TrainRoute VALUES (12280,'2564447','mum', 'Bhuvana', '122020','241420', 2555,4);

INSERT into TrainRoute VALUES (12255,'2564448','delhi', 'Charls', '121440','243020', 555,1);

MYSQL > Select * from TrainRoute;

MYSQL >truncate table TrainRoute;

12
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC
TRAIN TICKET FARE Laboratory Record

MYSQL >
CREATE TABLE TrainTicketFare (
TrainNumber NUMERIC(6),
Class VARCHAR(2),
Base_fare VARCHAR(20),
Reservation_charge VARCHAR(20),
Superfast_charge VARCHAR(10),
Other_charge VARCHAR(10),
Tatkal_charge NUMERIC(3,2),
Service_tax NUMERIC(10),
PRIMARY KEY (TrainNumber, Class));

MYSQL >

INSERT into TrainTicketFare VALUES (10001,'A','200', '200', '300','50', 2.42, 26);

INSERT into TrainTicketFare VALUES (12260,'A','200', '200', '300','50', 2.42, 26);

INSERT into TrainTicketFare VALUES (11111,'A','200', '200', '300','50', 2.42, 26);

INSERT into TrainTicketFare VALUES (12280,'A','200', '200', '300','50', 2.42, 26);

INSERT into TrainTicketFare VALUES (12255,'A','200', '200', '300','50', 2.42, 26);

MYSQL > Select * from TrainTicketFare;

13
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Record
Experiment 2

SQL Data Manipulation Language Commands:

1. Change the name of the Passenger table to Passenger_Details.

2. List all train details.
3. List all passenger details.
4. Give a list of trains in ascending order of number.
5. List the senior citizen passengers details.
6. List the station names where code starts with 'M'.
7. List the trains details within a range of numbers.
8. Change the super fast charge value in train fare as zero, if it is null.
9. List the passenger names whose tickets are not confirmed.
10. Remove all the rows from Passenger table permanently.

1.Use RailwayReservationSystem;

ALTER TABLE Passenger RENAME TO Passenger_Details;

Select * from Passenger;

Select * from Passenger_Details;

********************************
1. select * from Train;

*******************************
2. Select * from Passenger_Details;

********************************
3. SELECT * FROM Train ORDER BY TrainNumber;

*********************************
14
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
4.Select
EeEEMIECIVIOC
Age from Passenger_DetailsLaboratory
where Age>=60;
Record

s
*********************************
5.Select * from TrainRoute where station_code like 'M%';

************************************
6.Select * from Train where TrainNumber Between 1220 and 1300;

************************************
8. CREATE TABLE TrainTicketFare1
(
TrainNumber NUMERIC(4),
Class VARCHAR(2),
Base_fare VARCHAR(20),
Reservation_charge VARCHAR(20),
Superfast_charge VARCHAR(10),
Other_charge VARCHAR(10),
Tatkal_charge NUMERIC(3,2),
Service_tax NUMERIC(10),
PRIMARY KEY (TrainNumber, Class)
);

MYSQL >INSERT into TrainTicketFare1 VALUES (1228,'A','200', '200', '450','50', 4.20, 26);
MYSQL >INSERT into TrainTicketFare1 VALUES (1229,'A','200', '200', '450','50', 4.20, 26);
MYSQL >INSERT into TrainTicketFare1 VALUES (1231,'A','200', '200', NULL,'50', 4.20, 26);
MYSQL >UPDATE TrainTicketFare1
SET Superfast_charge = 0
WHERE Superfast_charge IS NULL;
*************************************

15
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Record

9 Select * from Passenger_Details where Reservation_status like 'Waiting_List';

*************************************
10 Delete from Passenger_Details;

Select * from Passenger_Details;

16
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Record
Experiment 3

3.Create (Alter table to add constraint) the necessary foreign keys by identifying the relationships in the
table.
1) Add a suitable constraint to train table to always have train no in the range 10001 to 99999.
2) Add a suitable constraint for the column of station name, so that does not take duplicates.
3) Change the data type of arrival time, depart time (date -> timestamp or timestamp to date), and do
the necessary process for updating the table with new values.
4) Add a suitable constraint for the class column that it should take values only as 1A, 2A, 3A, SL, C.
5) Add a not null constraint for the column distance in train_route.

1.CREATE DATABASE RailwayReservationSystem;

Use RailwayReservationSystem;

CREATE TABLE Train1

(
TrainNumber NUMERIC(6),
TrainName VARCHAR(20),
Source VARCHAR(20),
Destination VARCHAR(20),
start_time TIME,
reach_time TIME,
Travaltime TIME,
distance NUMERIC(10),
Class VARCHAR(4),
days NUMERIC(2),
type VARCHAR(10),
PRIMARY KEY (TrainNumber)
);

MYSQL > INSERT into Train1 VALUES (10001,'hyderabad exp','hyderabad', 'vizag', 100023,231123,
24, 2456,'A',3,'AC');
MYSQL > INSERT into Train1 VALUES (12260,'Vijayawada exp','Vijayawada', 'Warangal', 120322,230222,
8, 2000,'S',1,'General');
MYSQL >INSERT into Train1 VALUES (11111,'Banglore exp','hyderabad', 'banglore', '140225',062450,
48, 256,'B',2,'AC');
MYSQL >INSERT into Train1 VALUES (12280,'mumbai exp','chennai', 'mumbai', '154555',202453,
25, 8000,'S',2,'General');

MYSQL >INSERT into Train1 VALUES (12255,'delhi exp','delhi', 'mumbai', 202542,052322,

12, 800,'A',1,'General');
MYSQL >INSERT into Train1 VALUES (9000,'delhi exp','delhi', 'mumbai', 202542,052322,
12, 800,'A',1,'General');
17
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC
MYSQL >INSERT into Train1 VALUES Laboratory
(200000,'delhi
Record exp','delhi', 'mumbai', 202542,052322,
12, 800,'A',1,'General');
MYSQL > Select * from Train1;

MYSQL > Select * from Train1 where TrainNumber>=10001 and TrainNumber<=99999;

MYSQL >2.alter table TrainRoute add constraint Train_Route_name_unique unique(Station_code);

MYSQL > INSERT into TrainRoute VALUES (1228,2564444,'del', 'Allen', 111110,052322, 1233,1);

MYSQL >3.alter table TrainRoute drop column arrival_time;

MYSQL >alter table TrainRoute drop column Departure_time;

MYSQL >alter table TrainRoute add Departure_time timestamp;

MYSQL >alter table TrainRoute add arrival_time timestamp;

18
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Record

MYSQL >update TrainRoute set arrival_time=timestamp('2018-08-08 22:30:00'),

Departure_time=timestamp('2018-08 -10 07:10:00') where TrainNumber=1228;

MYSQL >update TrainRoute set arrival_time=timestamp('2018-08-08

22:30:00'),Departure_time=timestamp('2018-08
-10 07:10:00') where TrainNumber=10001;

MYSQL >update TrainRoute set arrival_time=timestamp('2018-08-08

22:30:00'),Departure_time=timestamp('2018-08 -10 07:10:00') where TrainNumber=11111;

MYSQL >update TrainRoute set arrival_time=timestamp('2018-08-08

22:30:00'),Departure_time=timestamp('2018-08 -10 07:10:00') where TrainNumber=12255;

MYSQL > update TrainRoute set arrival_time=timestamp('2018-08-0822:30:00'), 3

Departure_time=timestamp('2018-08-10 07:10:00') where TrainNumber=12260;

MYSQL >update TrainRoute set arrival_time=timestamp('2018-08-08

22:30:00'),Departure_time=timestamp('2018-08 -10 07:10:00') where TrainNumber=12280;

MYSQL >4.ALTER TABLE TrainTicketFare ADD CONSTRAINT CHECKCLASS CHECK(Class='1A' and

Class= '2A' and Class='3A' and Class='SL' and Class='C');

MYSQL >5.ALTER TABLE TrainRoute MODIFY distance int NOT NULL

19
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Record
Experiment 4

4.Designing Employee Database with MySQL (Create and insert data in EMP table, DEPT table
and SALGRADE table)

CREATE TABLE emp (

empno NUMERIC(4),
ename VARCHAR(10),
job VARCHAR(9),
mgr NUMERIC(4),
hiredate DATE,
sal NUMERIC(7),
comm NUMERIC(7),
deptno NUMERIC(2),
PRIMARY KEY (empno)
);

MYSQL >INSERT INTO emp VALUES(7838,'KING', 'PRESIDENT',null,'19811117',5000, null,10 );

MYSQL >INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'19810510',2850,null, 30);

MYSQL >INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'19810609',2450, null,10);

MYSQL >INSERT INTO emp VALUES(7566,'JONES', 'MANAGER',7839,'19810402',2975,null, 20);

MYSQL >INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'19811203',3000, null, 20);

MYSQL >INSERT INTO emp VALUES(7839,'KING','PRESIDENT',null,'19811117',5000, null,10);

CREATE TABLE dept (

deptno NUMERIC(2),
dname VARCHAR(14),
loc VARCHAR(13),
PRIMARY KEY (deptno)
);

20
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Record

MYSQL >INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK');

MYSQL >INSERT INTO dept VALUES(20, 'RESEARCH', 'DALLAS');
MYSQL >INSERT INTO dept VALUES(30, 'SALES', 'CHICAGO');
MYSQL >INSERT INTO dept VALUES(40, 'OPERATIONS', 'BOSTON');
MYSQL > INSERT INTO dept VALUES(50, 'DEVELOPEMENT', 'BOSTON');

CREATE TABLE salgrade (

grade NUMERIC(20),
min_sal NUMERIC(20),
max_sal NUMERIC(20),
empno Numeric(10),
job varchar(20),
Primary key (empno)
);
MYSQL > INSERT INTO salgrade VALUES (1, 700, 1200, 7838,'PRESIDENT');
MYSQL >INSERT INTO salgrade VALUES (2, 1201, 1400,7698,'MANAGER');
MYSQL > INSERT INTO salgrade VALUES (3, 1401, 2000,7782,'MANAGER');
MYSQL > INSERT INTO salgrade VALUES (4, 2001, 3000,7566,'MANAGER');
MYSQL >INSERT INTO salgrade VALUES (5, 3001, 9999,7902,'ANALYST');

21
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Record
Experiment 5
5.Multi row functions, GROUP By clause, HAVING clause, ORDER BY clause in SQL on sample
database.
GROUPBY clause
---------------
SELECT deptno "Department Code",
COUNT(*) "empno"
FROM emp
GROUP BY deptno;

*************************************
GROUP BY with SUM() function
-----------------------------
SELECT deptno, SUM(sal)
FROM emp GROUP BY deptno;

***********************************
SQL GROUP BY with COUNT() and SUM() function
---------------------------------------------
SELECT deptno "Department Code",
COUNT(*) "empno",
SUM(sal) "Total Salary"
FROM emp
GROUP BY deptno;

****************************************
SQL GROUP BY on more than one columns
-------------------------------------

SELECT deptno "Department Code", ename,

SUM(sal) "Total Salary"
FROM emp
GROUP BY deptno,ename;

22
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Record

********************************************
SQL GROUP BY with WHERE clause
-------------------------------
SELECT deptno"Department Code",
SUM(sal) "Total Salary"
FROM emp
WHERE mgr = 7566
GROUP BY deptno;

********************************************
SQL GROUP BY with HAVING clause
-------------------------------
SELECT deptno, count(*) "empno"
FROM emp
GROUP BY deptno
HAVING count(*)>2;

*********************************************
SQL HAVING using where
--------------------------
SELECT job,SUM(empno),
AVG(min_sal),MAX(max_sal)
FROM emp
WHERE grade=2
GROUP BY Job
HAVING AVG(min_sal)>500;

******************************************
SQL ORDER BY clause - Sorting on column names
---------------------------------------------
SELECT * FROM emp ORDER BY deptno;

*********************************************

23
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
Usage
EeEEMIECIVIOC
of the ORDER BY clause withLaboratory the DESCRecord
argument
----------------------------------------------------
SELECT * FROM emp ORDER BY deptno DESC;

**********************************************
Ordering by more than one columns
--------------------------------------
SELECT * FROM emp ORDER BY empno,deptno;

***********************************************
Ordering by more than one columns in ascending or descending order
------------------------------------------------------------------
SELECT * FROM emp ORDER BY sal DESC,empno ASC;

24
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Record
Experiment 6
Use Join Query
1. Find the train names that stop in 'Katpadi'.
2. Find the Passenger name (and train name) who have booked for the train that starts from 'Chennai'.
3. Display all the train details and the ticket details (if booked any).

6 (1)
CREATE TABLE Train
(
TrainNumber NUMERIC(6),
TrainName VARCHAR(20),
Source VARCHAR(20),
Destination VARCHAR(20),
start_time TIME,
reach_time TIME,
Travaltime TIME,
distance NUMERIC(10),
Class VARCHAR(4),
days NUMERIC(2),
type VARCHAR(10)
);

MYSQL >INSERT into Train VALUES (10001,'hyderabad exp','hyderabad', 'vizag', 100023,231123, 24, 2456
,'A',3,'AC');

MYSQL >INSERT into Train VALUES (12260,'Vijayawada exp','Vijayawada', 'Warangal', 120322,230222,

8, 2000,,'S',1,'General');

MYSQL > INSERT into TrainVALUES (11111,'banglore exp','hyderabad', 'katpadi', '140225',062450, 48, 256,
'B',2,'AC');

25
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
CREATE TABLE stops
EeEEMIECIVIOC Laboratory Record
(
TrainNumber NUMERIC(6),
TrainName VARCHAR(20),
Source VARCHAR(20),
Destination VARCHAR(20),
stop1 Varchar(20),
stop2 varchar(20)
);

MYSQL >INSERT into stops VALUES (1227,'banglore exp','banglore','Warangal','hyderabad','katpadi');

MYSQL >SELECT Train.Trainnumber,Train.TrainName,stops.Trainname, Train.Destination ,stops.stop2 FROM

Train,stops WHERE Train.Trainname =stops.Trainname AND Train.Destination=stops.stop2;

6(2)
Use RailwayReservationSystem;

CREATE TABLE Train5

(
TrainNumber NUMERIC(6),
TrainName VARCHAR(20),
Source VARCHAR(20),
Destination VARCHAR(20),
start_time TIME,
reach_time TIME,
Travaltime TIME,
distance NUMERIC(10),
Class VARCHAR(4),
days NUMERIC(2),
type VARCHAR(10),
PRIMARY KEY (TrainNumber)
);
MYSQL > INSERT into Train5 VALUES (10001,'hyderabad exp','hyderabad', 'vizag', 100023,231123,
24, 2456,'A',3,'AC');

MYSQL > INSERT into Train5 VALUES (12260,'Vijayawada exp','Vijayawada', 'Warangal', 120322,230222,
8, 2000,'S',1,'General');
26
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC
MYSQL >INSERT into Train5 VALUES
Laboratory
(12280,'mumbai
Record exp','chennai', 'mumbai', '154555',202453, 25,
8000,'S',2,'General');
MYSQL >SELECT * FROM Train5;

MYSQL >SELECT * FROM Train WHERE Source LIKE 'Chennai';

6(3)
MYSQL >Use RailwayReservationSystem;
MYSQL >CREATE TABLE Train6
(
TrainNumber NUMERIC(6),
TrainName VARCHAR(20),
Source VARCHAR(20),
Destination VARCHAR(20),
start_time TIME,
reach_time TIME,
Travaltime TIME,
distance NUMERIC(10),
Class VARCHAR(4),
days NUMERIC(2),
type VARCHAR(10),
PRIMARY KEY (TrainNumber)
);
MYSQL > INSERT into Train6 VALUES (10001,'hyderabad exp','hyderabad', 'vizag', 100023,231123,
24, 2456,'A',3,'AC');

MYSQL >INSERT into Train6 VALUES (12260,'Vijayawada exp','Vijayawada', 'Warangal', 120322,230222,

8,2000,'S',1,'General');
MYSQL >INSERT into Train6 VALUES (11111,'Banglore exp','hyderabad', 'banglore', '140225',062450, 48, 256,
'B', 2,'AC');

CREATE TABLE Ticket3

(
PNRNo NUMERIC(20),
Transactionid VARCHAR(20),
from_station VARCHAR(20),
To_station VARCHAR(20),
date_of_journey DATE,
Class VARCHAR(4),
date_of_booking DATE,
total_ticket_fare NUMERIC(10),
TrainNumber NUMERIC(6),
PRIMARY KEY (PNRNo));
27
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Record

MYSQL >INSERT into Ticket3 VALUES (122500023,123456789,'hyderabad', 'vizag', '20220310','A',

'20220309', 240, 10001);

MYSQL >INSERT into Ticket3 VALUES (122500024,1234567810,'vijayawada', 'Warangal', '20220311','S',

'20220310',100, 12260);

MYSQL >SELECT * FROM Train6, Ticket2;

28
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC
Viva-Voce Laboratory Record
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
7. What is the difference between SUM and COUNT ?
8. What will you get when you use MIN ?
9. What will you get when you use MAX ?
10. What is VIEW ? and What will you get when you use VIEW
11. What is difference between DROP table and DELETE?
12. What will you get when you use AVG?

29
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Record
Experiment 7
Use Nested Query(in Operators)
1. Find the train names that stop in 'Warangal'.

Use RailwayReservationSystem;
CREATE TABLE stops
(
TrainNumber NUMERIC(6),
TrainName VARCHAR(20),
Source VARCHAR(20),
Destination VARCHAR(20),
stop1 Varchar(20),
stop2 varchar(20)

);
MYSQL > INSERT into Stops VALUES (1227,'banglore exp','banglore','Warangal','hyderabad','katpadi');

MYSQL >INSERT into stops VALUES (1228,'mumbai exp','mumbai','Nalgonda','hyderabad','choutuppal');

MYSQL >SELECT * FROM stops;

MYSQL >SELECT * FROM stops WHERE Destination='Warangal';

30
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory RecordExperiment 8

8) Create sample Views and practice basic operation

Use RailwayReservationSystem;
CREATE TABLE Train
(
TrainNumber NUMERIC(6),
TrainName VARCHAR(20),
Source VARCHAR(20),
Destination VARCHAR(20),
start_time TIME,
reach_time TIME,
Travaltime TIME,
distance NUMERIC(10),
Class VARCHAR(4),
days NUMERIC(2),
type VARCHAR(10),
PRIMARY KEY (TrainNumber)
);

MYSQL > INSERT into Train VALUES (10001,'hyderabad exp','hyderabad', 'vizag', 100023,231123, 24, 2456,'
A',3,'AC');

MYSQL > INSERT into Train VALUES (12260,'Vijayawada exp','Vijayawada', 'Warangal', 120322,230222,
8, 2000,'S',1,'General');

MYSQL >INSERT into Train VALUES (11111,'Banglore exp','hyderabad', 'banglore', '140225',062450, 48,
256,'B',2,'AC');

MYSQL >INSERT into Train VALUES (12280,'mumbai exp','chennai', 'mumbai', '154555',202453, 25, 8000,
'S',2,'General');

MYSQL >INSERT into Train VALUES (12255,'delhi exp','delhi', 'mumbai', 202542,052322,

12, 800,'A',1,'General');

31
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Record

MYSQL >CREATE VIEW Trainview

AS SELECT * FROM Train;

32
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Record
Experiment 9

9) Write a PL/SQL procedures to practice Conditional Statements

USE Railwayreservationsystem;
MYSQL >CREATE TABLE emp (
empno NUMERIC(4),
ename VARCHAR(10),
job VARCHAR(9),
mgr NUMERIC(4),
hiredate DATE,
sal NUMERIC(7),
comm NUMERIC(7),
deptno NUMERIC(2),
PRIMARY KEY (empno)
);

MYSQL > INSERT INTO emp VALUES( 7838, 'KING', 'PRESIDENT', null,'19811117', 5000, null,10 );

MYSQL > INSERT INTO emp VALUES( 7698, 'BLAKE', 'MANAGER', 7839, '19810510', 2850, null, 30);

MYSQL > INSERT INTO emP VALUES(7782, 'CLARK', 'MANAGER', 7839, '19810609', 2450, null,10);

MYSQL > INSERT INTO emp VALUES( 7566,'JONES', 'MANAGER', 7839, '19810402', 2975, null, 20);

SELECT empno, ename, deptno, sal, CASE deptno WHEN 10 THEN 1.5*sal, ELSE salEND "REVISED sal"
FROM emp;

33
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Experiment
Record 10

10) USE Railwayreservationsystem;

CREATE TABLE emp (
empno NUMERIC(4),
ename VARCHAR(10),
job VARCHAR(9),
mgr NUMERIC(4),
hiredate DATE,
sal NUMERIC(7),
comm NUMERIC(7),
deptno NUMERIC(2),
PRIMARY KEY (empno)
);

MYSQL > INSERT INTO emp VALUES( 7838, 'KING', 'PRESIDENT', null,'19811117', 5000, null,10 );

MYSQL > INSERT INTO emp VALUES( 7698, 'BLAKE', 'MANAGER', 7839, '19810510', 2850, null, 10);

MYSQL > INSERT INTO emP VALUES(7782, 'CLARK', 'MANAGER', 7839, '19810609', 2450, null,10);

MYSQL > INSERT INTO emp VALUES( 7566,'JONES', 'MANAGER', 7839, '19810402', 2975, null, 20);
MYSQL >INSERT INTO emp VALUES( 7838,'KING', 'PRESIDENT', null,'19811117', 5000, null, 10 );
MYSQL >INSERT INTO emp VALUES( 7698, 'BLAKE', 'MANAGER', 7839, '19810510', 2850, null, 30);

MYSQL >INSERT INTO emp VALUES( 7782, 'CLARK', 'MANAGER', 7839, '19810609', 2450,null,10);

MYSQL >INSERT INTO emp VALUES(7566, 'JONES', 'MANAGER', 7839,'19810402', 2975, null, 20);

34
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC
MYSQL >SELECT empno, ename, deptno,
Laboratory
REPEAT(ename,
Record 2)
FROM emp;

35
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Record
Experiment 11
11) USE Railwayreservationsystem;
CREATE TABLE emp (
ename VARCHAR(10),
job VARCHAR(9),
mgr NUMERIC(4),
hiredate DATE,
sal NUMERIC(7),
comm NUMERIC(7),
deptno NUMERIC(2),
PRIMARY KEY (empno)
);

MYSQL >INSERT INTO emp VALUES( 7838, 'KING', 'PRESIDENT', null,'19811117', 5000, null, 10 );
MYSQL >INSERT INTO emp VALUES( 7698, 'BLAKE', 'MANAGER', 7839, '19810510', 2850, null, 30);
MYSQL >INSERT INTO emp VALUES(7782, 'CLARK', 'MANAGER', 7839, '19810609',2450, null, 10);
MYSQL >INSERT INTO emp VALUES( 7566, 'JONES', 'MANAGER', 7839, '19810402', 2975, null, 20);

MYSQL > DELETE FROM emp WHERE deptno = 10;

36
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Record
Experiment 12
12) Implementing simple cursor
 DECLARE statements - Declare variables used in the code block
 SET\SELECT statements - Initialize the variables to a specific value
 DECLARE CURSOR statement - Populate the cursor with values that will be evaluated
 OPEN statement - Open the cursor to begin data processing
 FETCH NEXT statements - Assign the specific values from the cursor to the variables to match the
DECLARE CURSOR FOR and SELECT statement
 WHILE statement - Condition to begin and continue data processing
 BEGIN...END statement - Start and end of the code block
 Data processing - In this example, this logic is to backup a database to a specific path and file name, but this
could be just about any DML or administrative logic
 CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened
 DEALLOCATE statement - Destroys the cursor
Outputs
6(1)
TrainNumber TrainName TrainName Destination Stops2
11111 banglore exp banglore exp katpadi katpadi
6(2)

mysql> SELECT * FROM Train WHERE Source LIKE 'Chennai';

+-------------+------------+---------+-------------+------------+------------+-------
-----+----------+-------+------+---------+
|TrainNumber | TrainName | Source | Destination | start_time | reach_time|
Travaltime | distance | Class | days | type |
+-------------+------------+---------+-------------+------------+------------+---------
--+----------+-------+------+---------+
| 1228 | mumbai exp | chennai | mumbai | 03:00 PM | 10:00 AM |
25 | 8000 | S | 2 | General |
+-------------+------------+---------+-------------+------------+------

6(3)

You just club the two tables(Train & Ticket)(club the rows only which I gave you in the syntax) with only 3
rows in train & 2 rows in ticket, once check trainnumbers in the syntax.

7
mysql> SELECT * FROM stops WHERE Destination='Warangal';
+-------------+--------------+----------+-------------+-----------+-----+
| TrainNumber | TrainName | Source | Destination | Stop1 | Stop2 |
+-------------+--------------+----------+-------------+-----------+---------+
| 1227 | banglore exp | banglore | Warangal | hyderabad | katpadi |

8
Just draw train table output from 1st experiment..just check trainnumber once

9
+-------+-------+--------+------+-------------+
| empno | ename | deptno | sal | REVISED sal |
+-------+-------+--------+------+-------------+
| 7566 | JONES | 20 | 2975 | 2975 |
7698 | BLAKE | 30 | 2850 | 2850 |
37
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC +-------+-------+--------+------+-------------+
Laboratory Record

10

+-------+-------+--------+------+-------------+
| empno | ename | deptno | sal | REVISED sal |
+-------+-------+--------+------+-------------+
| 7566 | JONES | 20 | 2975 | 2975 |
| 7698 | BLAKE | 30 | 2850 | 2850 |
+-------+-------+--------+------+-------------+

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 cursors attributes?

38
F
IIIYear I Sem - Fundamental of Data Base Management Systems: 2024-2025
EeEEMIECIVIOC Laboratory Record

39