human
Schema:
station (id, name, lat, long, dock_count, city, installation_date)
status (station_id, bikes_available, docks_available, time)
trip (id, duration, start_date, start_station_name, start_station_id, end_date, end_station_name, end_station_id, bike_id, subscription_type, zip_code)
weather (date, max_temperature_f, mean_temperature_f, min_temperature_f, max_dew_point_f, mean_dew_point_f, min_dew_point_f, max_humidity, mean_humidity, min_humidity, max_sea_level_pressure_inches, mean_sea_level_pressure_inches, min_sea_level_pressure_inches, max_visibility_miles, mean_visibility_miles, min_visibility_miles, max_wind_Speed_mph, mean_wind_speed_mph, max_gust_speed_mph, precipitation_inches, cloud_cover, events, wind_dir_degrees, zip_code)
Please answer with SQL.
assistant
Sure, I understand.
human
What is the number of distinct cities the stations are located at?
assistant
SELECT count(DISTINCT city) FROM station
human
List the name and the number of stations for all the cities that have at least 15 stations.
assistant
SELECT city , COUNT(*) FROM station GROUP BY city HAVING COUNT(*) >= 15
human
What are the names of stations that have latitude lower than 37.5?
assistant
SELECT name FROM station WHERE lat < 37.5
human
What are the name, latitude, and city of the station with the lowest latitude?
assistant
SELECT name , lat , city FROM station ORDER BY lat LIMIT 1
human
For each city, return the highest latitude among its stations.
assistant
SELECT city , max(lat) FROM station GROUP BY city
human
List all the cities in a decreasing order of each city's stations' highest latitude.
assistant
SELECT city FROM station GROUP BY city ORDER BY MAX( lat ) DESC ;