Railway Reservation System -(Redesigning IRCTC database) Train(train_number, name, source, destination, start_time, reach_time, traveltime, distance, class, days, type) Ticket( PNR_no,Transactionid, 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)

Write Queries to. (Middle Level)

Use SET Operators

1. Find the train numbers for which reservation have not yet been made.

2. Find the train names that do not have a first AC class coach.

3. Print all the PNR nos available in the database.

4. Find passenger names who have booked to 'Pune'.

Use Nested Query(in Operators)

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 who have booked for the train that starts from 'Chennai'. 

4. Find the trains names that have all the AC coaches and the base fare is less than 3000 for each case. 

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 train 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). 

6. Create a sequence to provide values for the PNR no. 

7. Write a query for full outer join using any of the tables above.