THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

Railway Management System

Department of Computer Engineering (COE)

Database Management System

BE – 4th Semester

PROJECT REPORT

By

Garvish Manan
(102303083)

Rachit Mahajan
(102303495)

Lakshya Swar
(102303086)

GROUP NO. – 2C13

DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY ,
Patiala – 147001

SUBMITTED TO – Dr. Garima Singh

1|Page
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

INDEX

Abstract……………………………………………………………………3

Introduction…………………………………………………….……….…4

Project Description…………………………………………………………5

List of Entities and Attributes………………………………………………6

ER Diagram…………………………………………………………………8

Schema Diagram………………………..………………………………..…9

Creating Commands………………………………………………………..10

1. Creating and displaying main tables………………………………..11

2. Creating and displaying Relation Tables…………….……………..16
3. Relational Queries ……………………...…………………………..21

2|Page
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

Abstract

This project presents the design and implementation of a Railway Database

Management System (RDMS) aimed at streamlining and automating various
operations within the railway network. The primary objective is to develop a
centralized database system that efficiently manages train schedules, ticket
bookings, passenger information, and route management, thereby reducing
manual errors and enhancing operational efficiency.

The RDMS is structured using Relational Database Management System

(RDBMS) principles, employing SQL for data retrieval and manipulation. Key
modules include user authentication, train details, ticket reservations,
cancellations, fare calculations, and report generation. The system ensures real-
time availability of train schedules, enabling passengers to check train timings,
seat availability, and book tickets online. Additionally, it facilitates staff
management, locomotive tracking, and reservation system automation.

By implementing this RDMS, the project addresses challenges such as data

volume and scalability, data consistency and integrity, and security and privacy
concerns. The system is designed to handle massive amounts of data, maintain
accurate and up-to-date information across multiple users and operations, and
protect sensitive passenger information.

3|Page
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

Introduction

Database is an organized collection of data. The data is typically organized to

model aspects of reality in a way that supports processes requiring information.
A DBMS makes it possible for end-users to create, read, update and delete data
in a database. The DBMS essentially serves as an interface between the
database and end-users or application programs, ensuring that data is
consistently organized and remains easily accessible. The DBMS manages three
important things: the data, the database engine that allows data to be accessed.
locked and modified and the database schema, which defines the database's
logical structure. These three foundational elements help provide concurrency,
security, data integrity, and uniform administration procedures. The DBMS can
offer both logical and physical data independence Which means it can protect
users and applications from needing to know where data is stored or having to
be concerned about changes to the physical structure of data.

The main purpose of maintaining database for Railway Reservation System is to

reduce the manual errors involved in the booking and canceling of tickets and
make it convenient for the customers and providers to maintain the data about
their customers and also about the seats available at them. Due to automation
many loopholes that exist in the manual maintenance of the records can be
removed. The speed of obtaining and processing the data will be fast. For future
expansion the proposed system can be web enabled so that clients can make
various enquiries about trains between stations. Due to this, sometimes a lot of
problems occur and they are facing many disputes with customers. To solve the
above problem, we design a data base which includes customer details.
availability of seats in trains. no of trains and their details.

4|Page
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

Project Description

This Project is about creating the database about Railway Reservation System

The railway reservation system facilitates the passengers to enquire about the
trains available on the basis of destination, booking and cancellation of tickets,
enquire about the status of the booked ticket, etc. The aim of case study is to
design and develop a database maintaining the records of different trains, train
status, and users. The record of train includes its number, name, description,
capacity, time, destination.

Users can book their tickets for the train in which seats are available. For this,
user has to provide the desired train number and the date for which ticket is to
be booked. Before booking a ticket for a user, the validity of train number is
checked. Once the train number and time are validated, it is checked whether
the seat is available. If yes, the ticket is booked with confirm status and
corresponding ticket ID is generated which is stored along with other details of
the user. The ticket once booked can be cancelled at any time. For this, the
passenger has to provide the ticket ID (the unique Key). User can do the
payment by different modes (UPI,Cash,Card).

5|Page
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

List of Entities and Attributes

ENTITIES ATTRIBUTES
User ID
First_Name
Middle_Name
Last _Name
Age
Gender
City
Pin_code
State
Mobile_no.

Train Train_No
Name
Capacity
Destination
Train_Desc
Arrival_Time

Station Station_No
Arrival_Time
Hault
Departure
Name
Train_No

Ticket Ticket_id
No. Of Passenger
First_Name
Ticket_Price
PNR_No
Train_No
ID

6|Page
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

Ticket Status Ticket_id

Confirmed
RAC
Waiting
ID

Payment ID
Ticket_Id
Paid_Amt
Pay_Date
Pay_Desc

7|Page
THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

ER Diagram

8|Page
THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

Schema Diagram

9|Page
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

Creating Commands

Create table User( ID int primary key, First_Name varchar(50) , Middle_name varchar(50),
Last_name varchar(50), Gender char, Age int, Mobile_No. varchar(50), City varchar(50),
State varchar(50), Pin_Code varchar(20));

Create table Train( Train_No. int primary key, Train_Name varchar(50), Capacity int,
Train_Desc. Varchar(50), Destination varchar(50); Arrival_Time varchar(50) );

Create table Station( Train_No. int, Station_No. primary key varchar(50), Arrival_Time
varchar(50); Hault varchar(50), Departure varchar(50) );

Create table Ticket ( Ticket_Id int primary key, First_Name varchar(50), Ticket_Price
varchar(50), PNR_No. int , Train_No. int, ID int );

Create table Ticket_Status ( Ticket_Id int primary key, Confirmed varchar(50), RAC
varchar(50), Waiting varchar(50) , ID int);

Create table Payment ( Pay_date int, Ticket_Id int, Paid_Amt. int, ID int, Pay_Desc.
Varchar(50) );

Create table Travels _In ( ID int foreign key, Train_No. Int foreign key, Constraint foreign
key(ID) references User(ID), Constraint foreign key(Train_No.) references Train(Train_No.) );

Create table Books & Cancel ( ID int ,First_Name varchar(50), Constraint foreign key (ID)
references User(ID), Constraint foreign key (ID) references Ticket(ID) );

Create table Reaches ( Train_No. int , Arrival_time varchar(50), Constraint foreign

key(train_no.) references Train(train_no.) );

Create table exist Start (Train_No. int , Arrival_time varchar(50), Constraint foreign
key(train_no.) references Train(train_no.) );

Create table Shows (Ticket_Id int, ID int, Constraint foreign key(Ticket_Id) references
Ticket(Ticket_Id), Constraint foreign key(Ticket_Id ) references Ticket_Status( Ticket_Id) );

10 | P a g e
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

1. CREATING AND DISPLAYING MAIN TABLES

CREATE TABLE 'User'(ID int primary key, First_Name varchar(50) , Middle_Name

varchar(50), Last_Name varchar(50), Gender char, Age int, Mobile_No varchar(50), City
varchar(50), State varchar(50), Pin_Code varchar(20));

Insert into 'User' ('ID','First_Name','Middle_Name','Last_Name',

'Gender','Age','Mobile_no','City','State','Pin_Code') VALUES

(1, 'Ritika', 'Devi', 'Verma', 'F', 20, '9890888666', 'Badarpur', 'Delhi', '110044'),
(2, 'Sneha', 'Rani', 'Yadav', 'F', 19, '9022336760', 'Roshanara', 'Delhi', '110007'),
(3, 'Nitya', 'Kumari', 'Chauhan', 'F', 19, '9022336563', 'Rohini', 'Delhi', '110087'),
(4, 'Pooja', 'Devi', 'Thakur', 'F', 22, '9024566760', 'Shahadra', 'Haryana', '11054'),
(5, 'Tanvi', 'Rani', 'Mehra', 'F', 21, '9992336760', 'Sarita Vihar', 'Haryana', '156007'),
(6, 'Simran', 'Sharma', 'Kapoor', 'F', 20, '9023676760', 'Dwarka', 'Punjab', '113407'),
(7, 'Ishita', 'Patel', 'Desai', 'F', 34, '9022336444', 'Lajpat Nagar', 'Punjab', '114507'),
(8, 'Rohan', 'Kumar', 'Bisht', 'M', 24, '9342336760', 'Nehru place', 'Delhi', '112307'),
(9, 'Devansh', 'Rana', 'Rawat', 'M', 20, '8042336760', 'Sangam Vihar', 'Haryana', '101007'),
(10, 'Aryan', 'Kumar', 'Tiwari', 'M', 19, '9022356112', 'Dwarka', 'Delhi', '110067'),
(11, 'Kunal', 'Narayan', 'Malhotra', 'M', 30, '9022311883', 'Bareilly', 'UP', '110627'),
(12, 'Nikhil', 'Singh', 'Bhardwaj', 'M', 23, '8944336760', 'Bareilly', 'UP', '158007');

SELECT * FROM User;

11 | P a g e
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

CREATE TABLE 'Payment' ( Pay_date varchar(50), Ticket_Id int, Paid_Amt int, ID int,
Pay_Desc Varchar(50) );

INSERT INTO 'Payment' ( 'Pay_date' , 'Ticket_Id' , 'Paid_Amt' , 'ID' , 'Pay_Desc' ) VALUES

(TO_DATE('14/11/2020', 'DD/MM/YYYY'), 109900, 200, 1, 'UPI'),

(TO_DATE('07/01/2021', 'DD/MM/YYYY'), 109834, 300, 2, 'BY cash'),
(TO_DATE('03/03/2022', 'DD/MM/YYYY'), 106734, 156, 3, 'Net Banking'),
(TO_DATE('22/10/2019', 'DD/MM/YYYY'), 109823, 799, 4, 'UPI'),
(TO_DATE('19/07/2020', 'DD/MM/YYYY'), 109992, 425, 5, 'BY cash'),
(TO_DATE('25/12/2021', 'DD/MM/YYYY'), 107843, 677, 6, 'UPI'),
(TO_DATE('16/09/2022', 'DD/MM/YYYY'), 102235, 500, 7, 'Net Banking'),
(TO_DATE('05/05/2021', 'DD/MM/YYYY'), 109978, 200, 8, 'Net Banking'),
(TO_DATE('01/01/2020', 'DD/MM/YYYY'), 105588, 600, 9, 'UPI'),
(TO_DATE('10/06/2022', 'DD/MM/YYYY'), 107823, 300, 10, 'UPI'),
(TO_DATE('30/10/2021', 'DD/MM/YYYY'), 106721, 800, 11, 'BY cash'),
(TO_DATE('17/08/2022', 'DD/MM/YYYY'), 102344, 500, 12, 'UPI');

SELECT * FROM Payment;

12 | P a g e
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

CREATE TABLE 'Train'(Train_Name varchar(50),

Train_No int primary key,Train_Desc varchar(50),
Destination varchar(50), Arrival_Time varchar(50),Capacity int);

INSERT INTO 'Train' ('Train_Name','Train_No', 'Train_Desc', 'Destination',

'Arrival_Time', 'Capacity') VALUES

('Frontier Express', 4563, 'Second Class', 'Rajasthan', '16:00:00', 800),

('Delhi Jaipur Double Decker', 1234, 'Third Class', 'Jaipur', '22:45:00', 800),
('Rajdhani Express', 3232, 'Second Class', 'Mumbai', '09:00:00', 2300),
('Chandigarh Delhi Shatabdi', 1276, 'First Class', 'Jammu Kashmir', '14:00:00', 1200),
('Ashram Express', 1021, 'Third Class', 'Kolkata', '21:30:00', 700),
('Jaipur Delhi Double Decker', 1453, 'Third Class', 'Patna', '09:30:00', 800),
('Harijan Express', 1290, 'Second Class', 'Madhya Pradesh', '13:00:00', 3600),
('Frontier Express', 5678, 'First Class', 'Delhi', '10:00:00', 800),
('Ashram Express', 3121, 'Third Class', 'Lucknow', '05:15:00', 700),
('Delhi Chandigarh Shatabdi', 1678, 'Second Class', 'Chandigarh', '20:30:00', 1200),
('Jammu Mail Express', 1345, 'Third Class', 'Madras', '22:00:00', 2500),
('Shatabdi Express', 1089, 'First Class', 'Allahabad', '16:30:00', 600);

SELECT * FROM Train;

13 | P a g e
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

CREATE TABLE 'Station'(Train_No int, Station_No varchar(50) primary key, Name

varchar(50),

Arrival_Time varchar(50), Hault varchar(50), Departure varchar(50));

INSERT INTO 'Station' ('Train_No', 'Station_No','Name', 'Arrival_Time', 'Hault', 'Departure')
VALUES

( 1678 , 101, 'Chandigarh','10:00:00', '00:00:30', '22:05:00'),

( 5678 , 120, 'Delhi', '20:40:00', '00:00:10' , '18:00:00'),
( 1234 , 371, 'Jaipur','01:00:00','00:00:25' ,'10:00:00'),
( 3121 , 230, 'Lucknow','18:00:00', '00:00:35','03:05:00'),
( 3232 , 123, 'Mumbai','10:00:00', '00:00:28' ,'18:00:00'),
( 1089 , 110, 'Allahbad', '10:35:00','00:00:35' ,'22:18:00'),
( 1021, 126, 'Kolkata', '10:05:00','00:00:20' ,'18:55:00'),
(1453, 119, 'Patna','15:00:00', '00:00:10','20:00:00'),
( 1345, 220, 'Madras','18:00:00', '00:00:30' ,'18:00:00'),
( 1276 ,189, 'Jammu Kashmir','19:55:00', '00:00:10','11:20:00'),
( 4563 ,773, 'Rajasthan','01:45:00', '00:00:25' ,'12:00:00'),
( 1290 ,171, 'Madhya Pradesh','15:00:00','00:00:15' ,'10:00:00');

SELECT * FROM Station;

14 | P a g e
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

CREATE TABLE 'Ticket' ( Ticket_Id int primary key, Name varchar(50), Ticket_Price
varchar(50), PNR_No int , Train_No int, ID int );

INSERT INTO Ticket (Ticket_Id, Name, Ticket_Price, PNR_No, Train_No, ID) VALUES
(109900, 'Chandigarh', 200, '2346712891', 1678, 1),
(109834, 'Delhi', 300, '4566278123', 2341, 2),
(106734, 'Jaipur', 156, '3467345672', 9043, 3),
(109823, 'Lucknow', 799, '7635423112', 8754, 4),
(109992, 'Mumbai', 425, '5467345689', 2312, 5),
(107843, 'Allahabad', 677, '7823456129', 5632, 6),
(102235, 'Kolkata', 500, '8945323127', 9076, 7),
(109978, 'Patna', 200, '9823415643', 7823, 8),
(105588, 'Madras', 600, '6723412453', 3452, 9),
(107823, 'Jammu Kashmir', 300, '2341237845', 8954, 10),
(106721, 'Rajasthan', 800, '7448545324', 5634, 11),
(102344, 'Madhya Pradesh', 500, '5634234891', 9078, 12);

SELECT * FROM Ticket;

15 | P a g e
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

CREATE TABLE 'Ticket_Status' ( Ticket_Id int primary key, Confirmed varchar(50),

RAC varchar(50), Waiting varchar(50) , ID int);

INSERT INTO Ticket_Status (Ticket_Id, Confirmed, RAC, Waiting, ID) VALUES

(109900, 'Yes', 'No', 'No', 1),

(109834, 'No', 'Yes', 'No', 2),
(106734, 'No', 'Yes', 'No', 3),
(109823, 'No', 'No', 'Yes', 4),
(109992, 'Yes', 'No', 'No', 5),
(107843, 'No', 'No', 'Yes', 6),
(102235, 'No', 'Yes', 'No', 7),
(109978, 'Yes', 'No', 'No', 8),
(105588, 'No', 'No', 'Yes', 9),
(107823, 'Yes', 'No', 'No', 10),
(106721, 'No', 'Yes', 'No', 11),
(102344, 'Yes', 'No', 'No', 12);

SELECT * FROM Ticket_Status;

16 | P a g e
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

2. CREATING RELATION TABLES

CREATE TABLE Shows (

Ticket_Id INT NOT NULL,
ID INT NOT NULL,
FOREIGN KEY (Ticket_Id) REFERENCES Ticket(Ticket_Id),
FOREIGN KEY (Ticket_Id) REFERENCES Ticket_Status(Ticket_Id)
);

INSERT INTO Shows (Ticket_Id, ID) VALUES

(109900, 1),
(109834, 2),
(106734, 3),
(109823, 4),
(109992, 5),
(107843, 6),
(102235, 7),
(109978, 8),
(105588, 9),
(107823, 10),
(106721, 11),
(102344, 12);

SELECT * FROM Shows;

17 | P a g e
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

CREATE TABLE Travels_In (

ID INT NOT NULL,
Train_No INT NOT NULL,
FOREIGN KEY (ID) REFERENCES "User"(ID),
FOREIGN KEY (Train_No) REFERENCES Train(Train_No)
);

INSERT INTO Travels_In (ID, Train_No) VALUES

(1, 1678),
(2, 2341),
(3, 9043),
(4, 8754),
(5, 2312),
(6, 5632),
(7, 9076),
(8, 7823),
(9, 3452),
(10, 8954),
(11, 5634),
(12, 9078);

SELECT * FROM Travels_In;

CREATE TABLE Books_Cancels (

18 | P a g e
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

ID INT NOT NULL,

First_Name VARCHAR2(50) NOT NULL,
FOREIGN KEY (ID) REFERENCES "User"(ID),
FOREIGN KEY (ID) REFERENCES Ticket(ID)
);

INSERT INTO Books_Cancels (ID, First_Name) VALUES

(1, 'Chandigarh'),
(2, 'Delhi'),
(3, 'Jaipur'),
(4, 'Lucknow'),
(5, 'Mumbai'),
(6, 'Allahabad'),
(7, 'Kolkata'),
(8, 'Patna'),
(9, 'Madras'),
(10, 'Jammu Kashmir'),
(11, 'Rajasthan'),
(12, 'Madhya Pradesh');

SELECT * FROM Books_Cancels;

19 | P a g e
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

CREATE TABLE Reaches (

Train_No INT,
Arrival_Time VARCHAR2(50),
FOREIGN KEY (Train_No) REFERENCES Train(Train_No)
);

INSERT INTO Reaches (Train_No, Arrival_Time) VALUES

(2341, '20:40:00'),
(9043, '01:00:00'),
(8754, '18:00:00'),
(2312, '10:00:00'),
(5632, '10:35:00'),
(9076, '10:05:00'),
(7823, '15:00:00'),
(3452, '18:00:00'),
(8954, '19:55:00'),
(5634, '01:45:00');

SELECT * FROM Reaches;

20 | P a g e
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

CREATE TABLE Start (

Train_No INT,
Arrival_Time VARCHAR2(50),
FOREIGN KEY (Train_No) REFERENCES Train(Train_No)
);

INSERT INTO Start (Train_No, Arrival_Time) VALUES

(1678, '10:00:00'),
(2341, '20:40:00'),
(9043, '01:00:00'),
(8754, '18:00:00'),
(2312, '10:00:00'),
(9076, '10:05:00'),
(3452, '18:00:00'),
(8954, '19:55:00'),
(5634, '01:45:00'),
(9076, '15:00:00');

SELECT * FROM 'Start';

21 | P a g e
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

3. RELATIONAL QUERIES

SELECT Arrival_Time FROM Start

INTERSECT
SELECT Arrival_Time FROM Reaches;

SELECT Arrival_Time FROM Start

UNION
SELECT Arrival_Time FROM Reaches;

22 | P a g e
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

4. QUERIES PERFORMED

Inserting
INSERT INTO User VALUES(13 ,'Monu' ,'Singh' , 'Swar' ,'M' ,'25' ,'8944336564' , 'Geeta
Colony' ,'Delhi', '158043');

SELECT * FROM User;

Updating

23 | P a g e
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

UPDATE User SET Middle_Name = 'Manan' WHERE ID = 4;

SELECT * FROM User;

UPDATE User SET Mobile_no = '9834256148' WHERE ID = 6;

SELECT * FROM User;

24 | P a g e
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

Deleting

DELETE FROM Payment WHERE ID = '10';

SELECT * FROM Payment;

25 | P a g e
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

Altering A Table
ALTER Table Train ADD COLUMN AC_Type Varchar(50);

UPDATE Train set AC_Type='AC' where Train_No='1021';

UPDATE Train set AC_Type='NON-AC' where Train_No='1089';
UPDATE Train set AC_Type='AC' where Train_No='1290';
UPDATE Train set AC_Type='AC' where Train_No='1345';
UPDATE Train set AC_Type='NON-AC' where Train_No='1234';
UPDATE Train set AC_Type='AC' where Train_No='1453';
UPDATE Train set AC_Type='AC' where Train_No='1678';
UPDATE Train set AC_Type='NON-AC' where Train_No='1276';
UPDATE Train set AC_Type='AC' where Train_No='3121';
UPDATE Train set AC_Type='NON-AC' where Train_No='4563';
UPDATE Train set AC_Type='AC' where Train_No='5678';
UPDATE Train set AC_Type='NON-AC' where Train_No='3232';

SELECT * FROM Train;

Dropping

26 | P a g e
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

DROP TABLE Reaches;

SELECT *FROM 'Reaches';

Truncating
TRUNCATE TABLE Payment;

SELECT * FROM Payment;

Renaming Tables
ALTER TABLE Train RENAME Train_Info;

SELECT * FROM Train_Info;

Ashram Express|1021|Third Class|Kolkata|21:30:00|700
Shatabdi Express|1089|First Class|Allahabad|16:30:00|600
Harijan Express|1290|Second Class|Madhya Pradesh|13:00:00|3600
Jammu Mail Express|1345|Third Class|Madras|22:00:00|2500
Delhi Jaipur Double Decker|1234|Third Class|Jaipur|22:45:00|800
Jaipur Delhi Double Decker|1453|Third Class|Patna|09:30:00|800
Delhi Chandigarh Shatabdi|1678|Second Class|Chandigarh|20:30:00|1200
Chandigarh Delhi Shatabdi|1276|First Class|Jammu Kashmir|14:00:00|1200
Ashram Express|3121|Third Class|Lucknow|05:15:00|700
Frontier Express|4563|Second Class|Rajasthan|16:00:00|800
Frontier Express|5678|First Class|Delhi|10:00:00|800
Rajdhani Express|3232|Second Class|Mumbai|09:00:00|2300

Simple Queries, Simple Queries with Aggregate functions.

27 | P a g e
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

1.Average
SELECT AVG(Paid_Amt)
AS
Average_Amount FROM Payment;

2. Maximum
SELECT MAX(Capacity)
AS
Maximum_Capacity FROM Train_Info;

3.Minimum
SELECT MIN(Capacity)
AS
Minimum_Capacity FROM Train_Info;

4. Count
SELECT COUNT(*)
AS
Product_Count FROM products;

Queries with Aggregate functions(group by and having clause)

28 | P a g e
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

SELECT
Ticket_Id, AVG(Paid_Amt)
FROM
Payment
GROUP BY
Ticket_Id;

SELECT
Train_No, SUM(Capacity)
FROM
Train_Info
GROUP BY
Train_No;

29 | P a g e
 THAPAR INSTITUTE OF ENGINEERING AND TECHNOLOGY

SELECT COUNT(ID), State

FROM User
GROUP BY State
HAVING COUNT(ID) > 3
ORDER BY COUNT(ID) DESC;

5|Delhi

Queries involving – Data Functions, String Functions

SELECT Name, ASCII(Name) AS NumCodeOfFirstChar

FROM Ticket;

Allahabad|65
Chandigarh|67
Delhi|68
Jaipur|74
Jammu Kashmir|74
Kolkata|75
Lucknow|76
Madras|77
Madhya Pradesh|77
Mumbai|77
Patna|80
Rajasthan|82

30 | P a g e