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 names of the top 10 airlines that operate the most number of routes.
assistant
SELECT T1.name , T2.alid FROM airlines AS T1 JOIN routes AS T2 ON T1.alid = T2.alid GROUP BY T2.alid ORDER BY count(*) DESC LIMIT 10
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
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 number of routes operated by American Airlines.
assistant
SELECT count(*) FROM airlines AS T1 JOIN routes AS T2 ON T1.alid = T2.alid WHERE T1.name = 'American Airlines'
human
Find the number of routes with destination airports in Italy.
assistant
SELECT count(*) FROM routes AS T1 JOIN airports AS T2 ON T1.dst_apid = T2.apid WHERE T2.country = 'Italy'
human
What is the number of routes operated by the airline American Airlines whose destinations are in Italy?
assistant
SELECT COUNT( * ) FROM routes AS T1 JOIN airports AS T2 ON T1.dst_apid = T2.apid JOIN airlines AS T3 ON T1.alid = T3.alid WHERE T2.country = "Italy" AND T3.name = "American Airlines" ;