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 id and name of the stations that have ever had more than 12 bikes available?
assistant
SELECT DISTINCT T1.id , T1.name FROM station AS T1 JOIN status AS T2 ON T1.id = T2.station_id WHERE T2.bikes_available > 12
human
What are the ids of stations that are located in San Francisco and have average bike availability above 10.
assistant
SELECT id FROM station WHERE city = 'San Francisco' INTERSECT SELECT station_id FROM status GROUP BY station_id HAVING avg(bikes_available) > 10
human
What are the names and ids of stations that had more than 14 bikes available on average or were installed in December?
assistant
SELECT T1.name , T1.id FROM station AS T1 JOIN status AS T2 ON T1.id = T2.station_id GROUP BY T2.station_id HAVING avg(T2.bikes_available) > 14 UNION SELECT name , id FROM station WHERE installation_date LIKE '12/%'
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 the ids of stations that have latitude above 37.4 and never had bike availability below 7?
assistant
SELECT id FROM station WHERE lat > 37.4 EXCEPT SELECT station_id FROM status GROUP BY station_id HAVING MIN( bikes_available ) < 7 ;