TRAIN, Sql Server - rextester

TRAIN

create table trains(Train_num varchar(20) primary key, Name varchar(20), source varchar(20), Destination varchar(20), Gen_fair int, AC_fair int, weekdays_available int);
create table trainstatus(train_num varchar(20),date date,AC_Seat int,Gen_seat int,foreign key(train_num) references trains(train_num) on delete cascade);
create table passenger(Adhar_no varchar(10) primary key,Name varchar(20),age int,sex char,address varchar(20),booking_status varchar(20),seat_category varchar(20));
create table booking (passenger_adharno varchar(10), train_num varchar(20),booking_date date,seat_type varchar(20),foreign key (passenger_adharno) references passenger(adhar_no) on delete cascade);

insert into trains values(123,'Tuti','tuticorin','mysore',180,360,600),(124,'Mysore','Bengaluru','Mysore',80,160,300),(125,'Okha','North_india','Turicorin',300,480,700),(126,'Howra','Bengaluru','Calcutta',200,300,450),(127,'Express','Goa','Delhi',300,1200,300);
select * from trains;

insert into trainstatus values(123,'20-JAN-20',50,80),(124,'21-JAN-20',60,70),(125,'26-JAN-20',0,10),(126,'29-JAN-20',100,200),(127,'29-JAN-20',10,20);
select* from trainstatus;

insert into passenger values('A234','Hari',22,'m','Bengaluru','Confirm','Berth'),('B234','Veni',24,'f','Bengaluru','Confirm','Lower'),('C234','Maha',22,'f','Mysuru','Confirm','Middle'),('D234','Sowmiya',36,'f','Bengaluru','Confirm','side'),('E234','Madan',19,'m','Goa','Confirm','Berth');
Select * from passenger;

insert into booking values('A234',125,'29-Jan-20','AC'),('B234',123,'15-Jan-20','Gen'),('C234',127,'27-Jan-20','Gen'),('D234',126,'15-Jan-20','AC'),('E234',125,'9-Jan-20','AC');
select* from booking;

select count(*), destination from trains group by destination order by destination;

select * from trains where train_num in(select train_num from booking where passenger_adharno in (select passenger_adharno from booking b union select adhar_no from passenger where booking_Status='confirmed'));

select * from trains T join trainstatus S on t.train_num=s.train_num;

select t.train_num,t.name,t.source,t.destination,p.* from trains t,passenger p, booking b where p.adhar_no=b.passenger_adharno and b.train_num=t.train_num;


run \| edit \| history \| help	0