Queries for the Air India Database

air_india_queries.sql

# Check for expired aircrafts
SELECT AcID, Mfg_Date, Type FROM AirCraft JOIN AirCraft_Type ON (AirCraft.Ac_Type = AirCraft_Type.ActID) WHERE YEAR(CURDATE()) - YEAR(Mfg_Date)> 2 LIMIT 10;

# Flights btw airports
SELECT FlId, FlightDate, Fare, Departure, Arrival FROM ((Flight_Schedule JOIN AirFare ON (Flight_Schedule.NetFare=AirFare.AfID)) ) JOIN Route ON (Route=RtID) WHERE Airport='New Delhi' AND Destination='Bangalore';

# Passengers on Flight
SELECT Name, Age, Mobile FROM Transaction JOIN Passenger ON (Transaction.Passenger = PsID) JOIN Contact_Details ON (Contacts=CnID) WHERE Flight=5 ORDER BY Name LIMIT 10;

# Minor Passengers with complete address
SELECT DISTINCT Name, Age, Mobile, Address, StateName, CountryName FROM Transaction JOIN Passenger ON (Transaction.Passenger = PsID) JOIN Contact_Details ON (Contacts=CnID) JOIN State ON (State=StID) JOIN Country ON (CtID=Country)  WHERE Flight=5 AND Age < 18 ORDER BY Name, Age  ;

# Total Fare collected between two dates
SELECT SUM(Fare) AS SALES FROM Transaction JOIN Flight_Schedule ON (Flight=FlID) JOIN AirFare ON (NetFare=AfID) WHERE DATE(BookingDate) BETWEEN '2016-10-01' AND '2016-10-20';

# Total number of tickets booked between particular dates
SELECT COUNT(PsID) AS Tickets FROM Transaction JOIN Passenger ON (Transaction.Passenger = PsID) JOIN Contact_Details ON (Contacts=CnID) WHERE DATE(BookingDate) BETWEEN '2016-10-10' AND '2016-11-25';

# Number of persons travelling from a given Airport between two given dates
SELECT COUNT(PsID) AS NUM_CUSTOMERS FROM Route JOIN AirFare ON (RtID = Route) JOIN Flight_Schedule ON (AfID = NetFare) JOIN Transaction ON (Flight=FlID) JOIN Passenger ON (Transaction.Passenger = PsID) JOIN Contact_Details ON (Contacts=CnID)  WHERE DATE(FlightDate) BETWEEN '2016-11-1' AND '2016-11-30' AND Airport = 'New Delhi';

# Number of persons travelling to a given Airport between two given dates
SELECT COUNT(PsID) AS NUM_CUSTOMERS FROM Route JOIN AirFare ON (RtID = Route) JOIN Flight_Schedule ON (AfID = NetFare) JOIN Transaction ON (Flight=FlID) JOIN Passenger ON (Transaction.Passenger = PsID) JOIN Contact_Details ON (Contacts=CnID)  WHERE DATE(FlightDate) BETWEEN '2016-11-1' AND '2016-11-30' AND Destination = 'New Delhi';

# Number of persons travelling from a given source Airport to a given destination Airport between two given dates
SELECT COUNT(PsID) AS NUM_CUSTOMERS FROM Route JOIN AirFare ON (RtID = Route) JOIN Flight_Schedule ON (AfID = NetFare) JOIN Transaction ON (Flight=FlID) JOIN Passenger ON (Transaction.Passenger = PsID) JOIN Contact_Details ON (Contacts=CnID)  WHERE DATE(FlightDate) BETWEEN '2016-11-1' AND '2016-11-30' AND  Airport = 'New Delhi' AND Destination = 'Bangalore';

# State Wise Analysis of Sales
SELECT StateName, Count(PsID) AS Tickets FROM Transaction JOIN Passenger ON (Transaction.Passenger = PsID) JOIN Contact_Details ON (Contacts=CnID) JOIN State ON (State = StID) GROUP BY StateName ORDER BY Count(PsID) DESC;