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
For each country and airline name, how many routes are there?
assistant
SELECT T1.country , T1.name , count(*) FROM airlines AS T1 JOIN routes AS T2 ON T1.alid = T2.alid GROUP BY T1.country , T1.name
human
Find the name of airline which runs the most number of routes.
assistant
SELECT T1.name FROM airlines AS T1 JOIN routes AS T2 ON T1.alid = T2.alid GROUP BY T1.name ORDER BY count(*) DESC LIMIT 1
human
Find the name and city of the airport which is the source for the most number of flight routes.
assistant
SELECT T1.name , T1.city , T2.src_apid FROM airports AS T1 JOIN routes AS T2 ON T1.apid = T2.src_apid GROUP BY T2.src_apid ORDER BY count(*) DESC LIMIT 1
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
Find the busiest source airport that runs most number of routes in China.
assistant
SELECT T1.name FROM airports AS T1 JOIN routes AS T2 ON T1.apid = T2.src_apid WHERE T1.country = 'China' GROUP BY T1.name ORDER BY count(*) DESC LIMIT 1
human
What is the name of the airport that is the destination of the most number of routes that start in China?
assistant
SELECT T1.name FROM airports AS T1 JOIN routes AS T2 ON T1.apid = T2.dst_apid WHERE T1.country = "China" GROUP BY T1.name ORDER BY COUNT( * ) DESC LIMIT 1 ;