queries.sql

SELECT * FROM cancel_ticket; 
SELECT * FROM station_details; 
SELECT * FROM ticket_class; 
SELECT * FROM user_details; 
SELECT * FROM train_details; 
SELECT * FROM seats;
SELECT * FROM reservation;
SELECT * FROM passenger_details;
SELECT * FROM user_details WHERE status = 'verified';
SELECT * FROM train_details WHERE start_point = 'SSS Hubballi';
SELECT t2.train_name,t1.train_number,station_name FROM train_schedule as t1, train_details as t2 WHERE  t2.train_name = 'Amaravathi Express' and t1.train_number = t2.train_number;
SELECT * FROM train_schedule WHERE train_number = 2;
SELECT * FROM seats WHERE train_number = 1 AND journey_date = '2023-11-03' AND class_name = '1A' AND status = 'AVAILABLE';
SELECT * FROM reservation WHERE user_id = 1;
SELECT * FROM passenger_details WHERE pnr_number = 100003;
SELECT * FROM cancel_ticket WHERE ticket_fare > 100;
SELECT * FROM mapping WHERE start_point = 'SSS Hubballi';
WITH temp(cnt) as (SELECT COUNT(*) FROM seats WHERE train_number = 2 AND class_name = '1A' AND status = 'AVAILABLE'), tmp(ct) as (SELECT COUNT(distinct(seat_no)) from seats where class_name = '1A') SELECT (temp.cnt-tmp.ct) as count from temp,tmp;
SELECT COUNT(*) FROM reservation WHERE journey_date = '2023-11-03';
SELECT * FROM passenger_details WHERE passenger_name = 'M.Suresh';
SELECT COUNT(*) FROM cancel_ticket;
SELECT COUNT(*) FROM seats WHERE class_name = '2A' AND journey_date = '2023-11-03' AND status = 'AVAILABLE';
SELECT u.user_id AS id , pd.passenger_name AS name FROM user_details AS u, reservation AS r, passenger_details AS pd, train_details AS td WHERE td.train_name = 'Amaravathi Express' AND td.train_number = r.train_number AND u.user_id = r.user_id AND r.pnr_number=pd.pnr_number;
SELECT p.pnr_number, p.passenger_name, p.passenger_name, p.passenger_age, p.passenger_gender, p.seat_number, p.berth, r.class_name FROM passenger_details AS p, reservation AS r WHERE p.seat_number = 1 AND r.pnr_number = p.pnr_number AND r.class_name = '1A';
SELECT ts.train_number, td.train_name, td.start_point, ts.arrival_time, ts.departure_time, td.arrival_day, ts.distance AS distance_travelled FROM train_schedule AS ts, train_details AS td WHERE ts.station_name = 'Guntakal'AND ts.train_number = td.train_number;