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 airports does the US has?
assistant
SELECT COUNT( * ) FROM airports WHERE country = 'United States';
human
what is the number of airports in each city in the US?
assistant
SELECT COUNT( * ) , city FROM airports WHERE country = 'United States' GROUP BY city;
human
Order the cities by decreasing number of airports.
assistant
SELECT COUNT( * ) , city FROM airports WHERE country = "United States" GROUP BY city ORDER BY COUNT( * ) DESC ;