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 cities have more than 3 airports in the United States?
assistant
SELECT city FROM airports WHERE country = 'United States' GROUP BY city HAVING COUNT( * ) > 3;
human
What cities in the United States have more than 4 airports?
assistant
SELECT city FROM airports WHERE country = 'United States' GROUP BY city HAVING COUNT( * ) > 4;
human
What are their names?
assistant
SELECT name FROM airports WHERE country = 'United States' GROUP BY city HAVING COUNT( * ) > 4;
human
Show the names of all airports with elevation over 4000.
assistant
SELECT name FROM airports WHERE elevation > 4000;
human
Show the names of all airports with elevation over 5000.
assistant
SELECT name FROM airports WHERE elevation > 5000 ;