Please use the tables in the flights database. Your deliverable should include t
ID: 3882468 • Letter: P
Question
Please use the tables in the flights database. Your deliverable should include the SQL queries that you write in support of your conclusions. 1. Show the total number of flights. 2. Show the total number of flights by airline (carrier). 3. Show all of the airlines, ordered by number of flights in descending order. 4. Show only the top 5 airlines, by number of flights, ordered by number of flights in descending order. 5. Show only the top 5 airlines, by number of flights of distance 1,000 miles or greater, ordered by number of flights in descending order. 6. Create a question that (a) uses data from the flights database, and (b) requires aggregation to answer it, and write down both the question, and the query that answers the question.
Explanation / Answer
Assuming Table name is FLIGHTS
1. Show the total number of flights.
Select * from FLIGHTS
It will return all the flights data from FLIGHTS table
2. Show the total number of flights by airline (carrier).
Select total_flights from FLIGHTS where airline=’carrier_name’
Here total_flights is the field containing data for number of flights for that carrier. You can use any carrier name in place of carrier_name to check results for any carrier.
3. Show all of the airlines, ordered by number of flights in descending order.
Select * from FLIGHTS ORDER BY total_flights desc
Here total_flights is the field containing data for number of flights for that carrier. It will return all flights data in descending order by its total_flights field.
4. Show only the top 5 airlines, by number of flights, ordered by number of flights in descending order.
Select * from FLIGHTS ORDER BY total_flights desc limit 5
Here total_flights is the field containing data for number of flights for that carrier. It will return all flights data in descending order by its total_flights field with upto 5 records only(Top 5).