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 operated by the airline with name 'American Airlines'.
assistant
SELECT COUNT( * ) FROM routes AS T1 JOIN airlines AS T2 ON T1.alid = T2.alid WHERE T2.name = 'American Airlines';
human
how many of them list the US as the destination?
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 = 'United States' AND T3.name = 'American Airlines';
human
how about for 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" ;