Question

Write a SQL Query that lists the top 20 airlines with the highest number of domestic routes.

Data Architecture The following diagram shows the relationships between the data files in the dataset. Note: You may need to maximize the text editor window use the appropriate pairs of foreign/primary keys based on the relational diagram to develop join queries. airport airline route Airline r-> Airline ID Airport ID Airline ID Name Name city Alias Source Airport Source Airport ID IATA Country IATA/FAA Destination Airport Callsign ICAO Destination Airport ID ICAO Latitude Code share Country Longitude Active Stops Altitude Equipment Timezone DST TZDB Timezone

A route is considered domestic for an airline if both the source and destination airports are located in the same country the airline is located in. The query must return airline id, airline callsign, country, and count of domestic routes.

Answer 1

Select top 20 Airline_ID,Alias from airline where Airline_ID in(select Airline_ID from Route where Route.source_airport_id in(select airport_id from airport where country=airline.country) and Route.destination_airport_id in(select airport_id from airport where country=airline.country) group by route.airline_id order by count(route.airline_id) desc);