SQL Queries: Flights database

 1.) Using the flights.m_ticket_prices and the m_airports table, find the distinct routes (route is a distinct origin-destination pair) where the origin and the destination are part of the same state; in the output show the origin, dest, and the state of the airports.

 

2.) Use the table flights.m_ticket_prices. For each route (origin-destination pair), list the following statistics:

• cheapest fare
• most expensive fare
• the average fare
• number of carriers serving the route
• total number of passengers for the route

Report results only for routes with at least 3 carriers and more than  10,000 total passengers. 

 

3.) Flights: For each state of the origin airport, calculate the following metrics: the number of origin airports in the state, the number of carriers operating flights that originate from the state, the total number of passengers originating from the state, and the average fare per mile. Use the m_ticket_prices and the m_airports tables to find the information that you need. 

 

4.)

 

Flights: Using the table flights.m_ticket_prices, for each carrier, report the number of routes they maintain, the number of airports their flights leave from, and their average fare per mile. Report results only for carriers having more than 20,000 passengers across all their flights.