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 different cities do have some airport in the country of Greenland?
assistant
SELECT count(DISTINCT city) FROM airports WHERE country = 'Greenland'
human
Return the cities with more than 3 airports in the United States.
assistant
SELECT city FROM airports WHERE country = 'United States' GROUP BY city HAVING count(*) > 3
human
List the cities which have more than 2 airports sorted by the number of airports.
assistant
SELECT city FROM airports GROUP BY city HAVING count(*) > 2 ORDER BY count(*)
human
List the cities which have more than one airport and number of airports.
assistant
SELECT city , count(*) FROM airports GROUP BY city HAVING count(*) > 1
human
How many cities are there that have more than 3 airports?
assistant
SELECT count(*) FROM (SELECT city FROM airports GROUP BY city HAVING count(*) > 3)
human
What are the cities with exactly two airports?
assistant
SELECT city FROM airports GROUP BY city HAVING COUNT( * ) = 2 ;