write a SQL query to find the names of top 20 countries that their airlines serv
ID: 3774351 • Letter: W
Question
write a SQL query to find the names of top 20 countries that their airlines serve the largest count of routes. The query must output country name and number of 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 distinct ap.country,count(r.source_airport_id) as no_of_routes from airport ap,airline al,route r where ap.airport_id=r.source_airport_id and r.airline_id=al.airline_id and ap.country=al.country group by r.source_airport_id order by count(r.source_airport_id) desc;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.