Name –
Registration id –
The problem statement-
Sample Database: Southern Railways
I. Create tables for the following requirements
Train:
Number, name, source, destination, start_time, reach_time
Passenger:
PNR No, Serial no., Name, Sex, Address, Age, Date of
Journey,
Status, kind of seat, seat no, Train number
II. Insert necessary values into the tables.
III. Constraints
1. Add a primary key constraint to train, Passenger.
2. Add a referential key constraint to passenger.
3. Add a check constraint to insert source and destination
in 3 letters
4. Add a check constraint to enter a valid kind of seat
while a
Passenger record is added for a train.
IV. Write queries for the following:
1. List all train details.
2. List all passenger details.
3. Give a list of trains in ascending order of number.
4. Find out the number of passengers booked for a
particular
Train.
5. List the number of waiting lists in a train “x”.
6. List the number of female passengers who have booked
for
trains (train name wise).
The implementation code-
->create table Train
(Number_t int not null,
Names char(100),
Source_t char(100),
Destination varchar(100),
Start_time decimal(4,2),
Reach_time decimal(4,2));
create table Passenger
(PNR_no int PRIMARY KEY not null,
Serial_num number,
Names char(100) not null,
Sex char(10),
Age number not null,
Date_of_journey date);
alter table Passenger
add Status varchar(20) default 'Not registered';
alter table Passenger
add Kind_of_seat varchar(20);
alter table Passenger
add Seat_no number;
alter table Passenger
add Train_number int;
insert into Train
values(15002, 'CST_Chennai special', 'Kanpur', 'Trichy', 7.40,
21.50);
insert into Train
values(21575, 'ANR_Amaravati local', 'Vijyawada', 'Warangal',
15.25, 20.00);
insert into Train
values(15112, 'CGT_Chitagong special memu', 'Aizol',
'Chitagong', 20.40, 16.00);
insert into Train
values(18025, 'WRT_Bengal super fast', 'Bhopal',
'Bhubaneshwar', 7.00, 21.00);
insert into Train
values(21220, 'INT_Indore memu local', 'Delhi', 'Indore', 5.40,
22.30;
insert into Train
values(20032, 'KRB_Korba local memu', 'Korba', 'Mahasamund',
4.20, 21.30);
insert into Train
values(21220, 'INT_Indore memu local', 'Delhi', 'Indore', 5.40,
22.30);
alter table Train
add number_t int Primary key;
alter table Passenger
drop primary key;
alter table Train
add ID_of_train number primary key;
alter table Passenger
add ID_pass number primary key;
alter table Passenger
add constraint New_slot
foreign key(Train_number) references Train(Number_t);
alter table Train
add check(length(Source_t) = 3 AND length(destination) = 3);
alter table Train
add check(length(Source_t) = 3);
select length(source_t) as ST
from Train;
alter table Train
add check(ST = 3);
select length(source_t) as ST
from Train
check(ST = 3);
alter table Passenger
add check(kind_of_seat != null);
select * from Train;
select * from Passenger;
select * from Train
order by number_t asc;
insert into Passenger
values(1230456, 01, 'Rahul', 'Male', 25, 2022-02-15, 'Confirmed',
'Side', 48, 21220, 501);
insert into Passenger
values(123640, 02, 'Mrunal', 'Female', 27, 2022-10-01,
'Confirmed', 'Upper', 102, 15112, 423);
insert into Passenger
values(105364, 03, 'Aisha', 'Feamle', 22, 2022-01-15,
'Confirmed', 'Middle', 89, 18025, 223);
insert into Passenger
values(1230456, 01, 'Rahul', 'Male', 25, '2022-02-15',
'Confirmed', 'Side', 48, 21220, 501);
insert into Passenger
values(123640, 02, 'Mrunal', 'Female', 27, '2022-10-01',
'Confirmed', 'Upper', 102, 15112, 423);
insert into Passenger
values(105364, 03, 'Aisha', 'Feamle', 22, '2022-01-15',
'Confirmed', 'Middle', 89, 18025, 223);
insert into Passenger
values(1230456, 01, 'Rahul', 'Male', 25, to_date('2022-02-15',
'yyyy-mm-dd'), 'Confirmed', 'Side', 48, 21220, 501);
insert into Passenger
values(123640, 02, 'Mrunal', 'Female', 27, to_date('2022-10-01',
'yyyy-mm-dd'), 'Confirmed', 'Upper', 102, 15112, 423);
insert into Passenger
values(105364, 03, 'Aisha', 'Feamle', 22, to_date('2022-01-15',
'yyyy-mm-dd'), 'Confirmed', 'Middle', 89, 18025, 223);
select count(*) from Passenger
where train_number = 15112;
select count(*) from Passenger
where status = 'Waiting';
select count(*) from Passenger
where sex = 'female';
select count(train_number) from Passenger
where sex = 'female';
Output-
