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 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 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 bike availability in stations that are not located in Palo Alto?
assistant
SELECT avg(bikes_available) FROM status WHERE station_id NOT IN (SELECT id FROM station WHERE city = 'Palo Alto')
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 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" ;