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
What is the average longitude of stations that never had bike availability more than 10?
assistant
SELECT avg(long) FROM station WHERE id NOT IN (SELECT station_id FROM status GROUP BY station_id HAVING max(bikes_available) > 10)
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 names of stations that have average bike availability above 10 and are not located in San Jose city?
assistant
SELECT T1.name FROM station AS T1 JOIN status AS T2 ON T1.id = T2.station_id GROUP BY T2.station_id HAVING avg(bikes_available) > 10 EXCEPT SELECT name FROM station WHERE city = 'San Jose'
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
What is the average latitude and longitude of stations located in San Jose city?
assistant
SELECT avg ( lat ) , avg ( long ) FROM station WHERE city = "San Jose" ;