Write a SQL query that lists the top 20 airlines with the highest number of dome
ID: 3774358 • Letter: W
Question
Write a SQL query that lists the top 20 airlines with the highest number of domestic routes. 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.
Data Architecture: The following diagram shows the relationships between the data files in the dataset.
airport
route
airline
Airport_ID
Airline
Airline_ID
Name
Airline_ID
Name
City
Source_Airport
Alias
Country
Source_Airport_ID
IATA
IATA/FAA
Destination_Airport
ICAO
ICAO
Destination_Airport_ID
Callsign
Latitude
Codeshare
Country
Longitude
Stops
Active
Altitude
Equipment
Timezone
DST
TZDB_Timezone
airport
route
airline
Airport_ID
Airline
Airline_ID
Name
Airline_ID
Name
City
Source_Airport
Alias
Country
Source_Airport_ID
IATA
IATA/FAA
Destination_Airport
ICAO
ICAO
Destination_Airport_ID
Callsign
Latitude
Codeshare
Country
Longitude
Stops
Active
Altitude
Equipment
Timezone
DST
TZDB_Timezone
Explanation / Answer
select TOP 20 al.airline_id,al.callsign,al.country,count(r.airline_id) as domestic_count from airline al inner join route r where al.airline_id=r.airline_id and (select country from airport where airport_id=r.source_airport_id)=(select country from airport where airport_id=r.destination_airport_id);
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.