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
how many routes are there?
assistant
SELECT COUNT( * ) FROM routes;
human
find the number of routes for each destination.
assistant
SELECT COUNT( * ) , dst_apid FROM routes GROUP BY dst_apid;
human
what is the name and city of the destination airport that has the most routes ending there?
assistant
SELECT T1.name , T1.city 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 ;