human
Schema:
routes (rid, dst_apid, dst_ap, src_apid, src_ap, alid, airline, codeshare)
airports (apid, name, city, country, x, y, elevation, iata, icao)
airlines (alid, name, iata, icao, callsign, country, active)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the name and city of the airport which is the destination of the most number of routes.
assistant
SELECT T1.name , T1.city , T2.dst_apid FROM airports AS T1 JOIN routes AS T2 ON T1.apid = T2.dst_apid GROUP BY T2.dst_apid ORDER BY count(*) DESC LIMIT 1
human
List the cities which have more than one airport and number of airports.
assistant
SELECT city , count(*) FROM airports GROUP BY city HAVING count(*) > 1
human
Find the name, city, and country of the airport that has the highest latitude.
assistant
SELECT name , city , country FROM airports ORDER BY elevation DESC LIMIT 1
human
Find the number of airports whose name contain the word 'International'.
assistant
SELECT count(*) FROM airports WHERE name LIKE '%International%'
human
Find the cities which have exactly two airports.
assistant
SELECT city FROM airports GROUP BY city HAVING count(*) = 2
human
Find the name of the airports located in Cuba or Argentina.
assistant
SELECT name FROM airports WHERE country = "Cuba" OR country = "Argentina" ;