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 all the source airports?
assistant
SELECT T2.src_apid , T2.src_ap , T1.name FROM airports AS T1 JOIN routes AS T2 ON T1.apid = T2.src_apid GROUP BY T1.name;
human
What are all of the routes for each source airport? | do you mean the number of all of the routes? | Yes please.
assistant
SELECT COUNT( * ) FROM airports AS T1 JOIN routes AS T2 ON T1.apid = T2.src_apid GROUP BY T1.name;
human
What are the names of the airports associated with each number of routes?
assistant
SELECT T1.name , COUNT( * ) FROM airports AS T1 JOIN routes AS T2 ON T1.apid = T2.src_apid GROUP BY T1.name ;