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
What are the names of the airlines ?
assistant
SELECT name FROM airlines;
human
How many routes are there in the United States ?
assistant
SELECT COUNT( * ) FROM routes AS T1 JOIN airlines AS T2 ON T2.alid = T1.alid WHERE T2.country = 'United States';
human
Can you give me a number of routes per country and airline name ?
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 ;