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
Find the name, city, and country of the airport that has the lowest altitude.
assistant
SELECT name , city , country FROM airports ORDER BY elevation LIMIT 1
human
Find the name, city, and country of the airport that has the highest latitude.
assistant
SELECT name , city , country FROM airports ORDER BY elevation DESC LIMIT 1
human
Find the average elevation of all airports for each country.
assistant
SELECT avg(elevation) , country FROM airports GROUP BY country
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
Which country is the airport that has the highest altitude located in?
assistant
SELECT country FROM airports ORDER BY elevation DESC LIMIT 1
human
What is the maximum elevation of all airports in the country of Iceland?
assistant
SELECT MAX( elevation ) FROM airports WHERE country = "Iceland" ;