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 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
How many stations does Mountain View city has?
assistant
SELECT COUNT(*) FROM station WHERE city = 'Mountain View'
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 names of stations that are located in Palo Alto city but have never been the ending point of trips more than 100 times?
assistant
SELECT name FROM station WHERE city = 'Palo Alto' EXCEPT SELECT end_station_name FROM trip GROUP BY end_station_name HAVING count(*) > 100
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
How many different cities have these stations?
assistant
SELECT COUNT( DISTINCT city ) FROM station ;