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 all the distinct stations from which a trip of duration below 100 started.
assistant
SELECT DISTINCT start_station_name FROM trip WHERE duration < 100
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 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
How many trips started from Mountain View city and ended at Palo Alto city?
assistant
SELECT count(*) FROM station AS T1 JOIN trip AS T2 JOIN station AS T3 JOIN trip AS T4 ON T1.id = T2.start_station_id AND T2.id = T4.id AND T3.id = T4.end_station_id WHERE T1.city = 'Mountain View' AND T3.city = 'Palo Alto'
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 ;