Write a SQL query that outputs list of top 10 airports that have the largest num
ID: 3774356 • Letter: W
Question
Write a SQL query that outputs list of top 10 airports that have the largest number of distinct airlines flying from them. The query must return airport id, airport name, airport city, airport country, comma separated unduplicated list of airline callsigns flying from the airport sorted in alphabetical order, and count of distinct airlines flying from the airport.
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 10 ap.airport_id,ap.name,ap.city,ap.country,LISTAGG(al.callsign,',') WITHIN group(order by al.callsign) as CallSignList,count(distinct al.airline_id) from airline al inner join airport ap inner join route r where ap.country =al.country and r.source_airport_id=ap.airport_id and r.airline_id=al.airline_id group by al.airline_id;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.