SQL10feb2.sql

USE PRODUCT_DB;


Create tables below and perform the queries.
in table give proper constraints.

1. Table 1 : Trains  (
    train_id (not blank),
    train_name (not blank),
    source (not blank),
    destination (not blank),
    departure_time (not blank),
    arrival_time (not blank),
    distance_km (should be > 0)

2. Table 2 : Passengers (
   passenger_id,
    name (not blank),
    age should be(age > 0),
    gender shoul be ('M', 'F')),
    city 

3. Table 3 : Reservations (
   res_id ,
    passenger_ID,
    train_id ,
    travel_date (not blank),
    class should be ('Sleeper', 'AC1', 'AC2', 'AC3')),
    fare (fare >= 0),
    status should be('Confirmed', 'Waiting', 'Cancelled'))





--List all trains running from Delhi as source station.


--Find all passengers who are above 40 years of age.


--Get the total number of reservations made.



--Count how many reservations are in 'Confirmed' status.

--Show the average fare paid for each train.

--List reservations ordered by fare in descending order.

--Find passengers who booked AC classes (AC2, AC3).

--Get total revenue collected per train (only Confirmed reservations).


--Find the train with the maximum distance_km.


--Show number of male and female passengers.

--Get the passenger names who have booked tickets in Sleeper class.

--Find total number of reservations made from each city (JOIN with Passengers).

--Show train name and total passengers booked for it, ordered by passenger count descending.


--Find the average age of passengers who booked 'Confirmed' tickets.


--Display travel_date and number of reservations made on that date.


Show all reservations where fare is greater than 1000.

List all passengers who live in Delhi or Mumbai.

Show all trains whose distance is more than 1200 km.

Find reservations which are not "Cancelled".

Show the details of trains that have "Exp" in their name.

List all passengers ordered by their age in descending order.

Display reservations sorted by travel_date (earliest first).

Find all trains ordered by distance (longest route first).

Show passengers ordered by name alphabetically.

List reservations ordered by class and then by fare.

Count how many passengers are from each city.

Find the total fare collected from all Confirmed reservations.

Show the minimum, maximum, and average age of passengers.

Find the highest fare paid in Sleeper class.

Get the average fare per travel_date.

Show each class and the total number of reservations in it.

Find trains that have more than 1 reservation.

List cities where more than 1 passenger lives.

Show gender-wise average age of passengers.

Find passengers who booked more than 1 ticket.

Show passenger name, train name, and fare for each reservation.

List all passengers and the train name they booked (if any).

Show all trains and the number of passengers booked in each.

Find all passengers who booked Rajdhani Exp.

List passenger names with their travel_date and status.

Find the top 2 highest fare reservations.

Find the train with the lowest average fare.

Show the train(s) where total distance travelled by all passengers > 1000 km.

Find passengers whose reservation status is "Waiting".

Show the passenger(s) who paid the maximum fare overall.

Show all passengers whose age is between 20 and 40.

Find trains that start from Kolkata or Chennai.

Show all reservations made after 2025-09-05.

Display passengers whose name starts with 'A'.

List passengers whose city is NOT Delhi.

Show the 3 youngest passengers.

Show the 2 longest-distance trains.

Display the 5 most expensive reservations (highest fare).

List trains in alphabetical order of train_name.

Show passengers sorted by age (youngest first).

Find the average fare of all reservations.

Count the total number of male passengers.

Show the maximum distance among all trains.

Find the total number of Sleeper class reservations.

Find the total fare paid by passengers from Mumbai.

Count the number of reservations per status (Confirmed/Waiting/Cancelled).

Find the total number of passengers per gender.

Show the average fare for each class.

Display the number of trains starting from each source city.

Show total reservations grouped by travel_date.

Show passenger name, city, and train_name they booked.

List all reservations with passenger name and status.

Show train_name and number of confirmed passengers on it.

Display all passengers with train_name (if booked, else show NULL).

Find which passengers booked Garib Rath train.


Show train_id and total fare collected, but only where fare > 1000.

List source cities that have more than 1 train.

Find passengers grouped by city where count > 1.

Show classes that earned more than 2000 fare in total.

List trains that have at least 2 passengers booked.

Find the passenger(s) with the highest age.

Show the train(s) with the shortest distance.

Find the reservation(s) with the lowest fare.

List passengers who paid above the average fare.

Find trains whose distance is above the average train distance.


Show all reservations in September 2025.

Find the earliest travel_date booked.

Find the latest travel_date booked.

Count how many reservations are made per day.

List passengers who booked tickets on the same date.

Show passenger name, train_name, and distance travelled.

Find the city that contributed the highest number of passengers.

Display each train and its average fare (confirmed only).

Show passengers who booked tickets in more than one class.

Find train_name with maximum number of reservations