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 have a destination airport in Canada, please?
assistant
SELECT COUNT( * ) FROM airports AS T1 JOIN routes AS T2 ON T1.apid = T2.dst_apid WHERE country = 'Canada';
human
How many routes have a destination airport in the United States, please?
assistant
SELECT COUNT( * ) FROM airports AS T1 JOIN routes AS T2 ON T1.apid = T2.dst_apid WHERE country = 'United States';
human
How many routes have a source airport in Canada and a destination airport in the United States, please?
assistant
SELECT COUNT( * ) FROM ( SELECT rid FROM airports AS T1 JOIN routes AS T2 ON T1.apid = T2.dst_apid WHERE T1.country = 'United States' INTERSECT SELECT rid FROM airports AS T3 JOIN routes AS T4 ON T3.apid = T4.src_apid WHERE T3.country = 'Canada' );
human
What are the names of those source airports that begin with the letter A?
assistant
SELECT t1.name FROM airports AS t1 JOIN routes AS t2 ON t1.apid = t2.src_apid WHERE t1.name LIKE 'A%';
human
What are the ones that are in Canada only?
assistant
SELECT distinct ( t1.name ) FROM airports AS t1 JOIN routes AS t2 ON t1.apid = t2.src_apid WHERE t1.name LIKE "A%" AND t1.country = "Canada" ;