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
Which cities have more than one airport?
assistant
SELECT city FROM airports GROUP BY city HAVING COUNT( * ) > 1;
human
Now show the number of airports in each of those cities
assistant
SELECT city , COUNT( * ) FROM airports GROUP BY city HAVING COUNT( * ) > 1;
human
Which cities have three airports
assistant
SELECT city FROM airports GROUP BY city HAVING COUNT( * ) = 3;
human
Which cities have 4 or more airports
assistant
SELECT city FROM airports GROUP BY city HAVING COUNT( * ) > = 4;
human
Show the number of airports in those cities
assistant
SELECT city , COUNT( * ) FROM airports GROUP BY city HAVING COUNT( * ) > = 4 ;