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 average latitude and longitude of the starting points of all trips?
assistant
SELECT avg(T1.lat) , avg(T1.long) FROM station AS T1 JOIN trip AS T2 ON T1.id = T2.start_station_id
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 stations does Mountain View city has?
assistant
SELECT COUNT(*) FROM station WHERE city = 'Mountain View'
human
Count the number of trips that did not end in San Francisco city.
assistant
SELECT count(*) FROM trip AS T1 JOIN station AS T2 ON T1.end_station_id = T2.id WHERE T2.city != 'San Francisco'
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
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" ;