CREATE TABLE station (
    id NUMBER PRIMARY KEY,
    name TEXT,
    lat NUMBER,
    long NUMBER,
    dock_count NUMBER,
    city TEXT,
    installation_date TEXT
);
CREATE TABLE status (
    station_id NUMBER,
    bikes_available NUMBER,
    docks_available NUMBER,
    time TEXT,
    FOREIGN KEY ("station_id") REFERENCES station("id")
);
CREATE TABLE trip (
    id NUMBER PRIMARY KEY,
    duration NUMBER,
    start_date TEXT,
    start_station_name TEXT,
    start_station_id NUMBER,
    end_date TEXT,
    end_station_name TEXT,
    end_station_id NUMBER,
    bike_id NUMBER,
    subscription_type TEXT,
    zip_code NUMBER
);
CREATE TABLE weather (
    date TEXT,
    max_temperature_f NUMBER,
    mean_temperature_f NUMBER,
    min_temperature_f NUMBER,
    max_dew_point_f NUMBER,
    mean_dew_point_f NUMBER,
    min_dew_point_f NUMBER,
    max_humidity NUMBER,
    mean_humidity NUMBER,
    min_humidity NUMBER,
    max_sea_level_pressure_inches NUMBER,
    mean_sea_level_pressure_inches NUMBER,
    min_sea_level_pressure_inches NUMBER,
    max_visibility_miles NUMBER,
    mean_visibility_miles NUMBER,
    min_visibility_miles NUMBER,
    max_wind_Speed_mph NUMBER,
    mean_wind_speed_mph NUMBER,
    max_gust_speed_mph NUMBER,
    precipitation_inches NUMBER,
    cloud_cover NUMBER,
    events TEXT,
    wind_dir_degrees NUMBER,
    zip_code NUMBER
);

Question: Give me the dates when the max temperature was higher than 85.
SQL: SELECT date FROM weather WHERE max_temperature_f  >  85

Question: What are the dates with a maximum temperature higher than 85?
SQL: SELECT date FROM weather WHERE max_temperature_f  >  85

Question: What are the names of stations that have latitude lower than 37.5?
SQL: SELECT name FROM station WHERE lat  <  37.5

Question: What are the names of all stations with a latitude smaller than 37.5?
SQL: SELECT name FROM station WHERE lat  <  37.5

Question: For each city, return the highest latitude among its stations.
SQL: SELECT city ,  max(lat) FROM station GROUP BY city

Question: For each city, what is the highest latitude for its stations?
SQL: SELECT city ,  max(lat) FROM station GROUP BY city

Question: Give me the start station and end station for the trips with the three oldest id.
SQL: SELECT start_station_name ,  end_station_name FROM trip ORDER BY id LIMIT 3

Question: What is the station station and end station for the trips with the three smallest ids?
SQL: SELECT start_station_name ,  end_station_name FROM trip ORDER BY id LIMIT 3

Question: What is the average latitude and longitude of stations located in San Jose city?
SQL: SELECT avg(lat) ,  avg(long) FROM station WHERE city  =  "San Jose"

Question: What is the average latitude and longitude in San Jose?
SQL: SELECT avg(lat) ,  avg(long) FROM station WHERE city  =  "San Jose"

Question: What is the id of the trip that has the shortest duration?
SQL: SELECT id FROM trip ORDER BY duration LIMIT 1

Question: What is the id of the shortest trip?
SQL: SELECT id FROM trip ORDER BY duration LIMIT 1

Question: What is the total and maximum duration of trips with bike id 636?
SQL: SELECT sum(duration) ,  max(duration) FROM trip WHERE bike_id  =  636

Question: What is the total and maximum duration for all trips with the bike id 636?
SQL: SELECT sum(duration) ,  max(duration) FROM trip WHERE bike_id  =  636

Question: For each zip code, return the average mean temperature of August there.
SQL: SELECT zip_code  ,  avg(mean_temperature_f) FROM weather WHERE date LIKE "8/%" GROUP BY zip_code

Question: For each zip code, what is the average mean temperature for all dates that start with '8'?
SQL: SELECT zip_code  ,  avg(mean_temperature_f) FROM weather WHERE date LIKE "8/%" GROUP BY zip_code

Question: From the trip record, find the number of unique bikes.
SQL: SELECT count(DISTINCT bike_id) FROM trip

Question: How many different bike ids are there?
SQL: SELECT count(DISTINCT bike_id) FROM trip

Question: What is the number of distinct cities the stations are located at?
SQL: SELECT count(DISTINCT city) FROM station

Question: How many different cities have these stations?
SQL: SELECT count(DISTINCT city) FROM station

Question: How many stations does Mountain View city has?
SQL: SELECT COUNT(*) FROM station WHERE city  =  "Mountain View"

Question: How many stations are in Mountain View?
SQL: SELECT COUNT(*) FROM station WHERE city  =  "Mountain View"

Question: Return the unique name for stations that have ever had 7 bikes available.
SQL: SELECT DISTINCT T1.name FROM station AS T1 JOIN status AS T2 ON T1.id  =  T2.station_id WHERE T2.bikes_available  =  7

Question: What are the different names for each station that has ever had 7 bikes available?
SQL: SELECT DISTINCT T1.name FROM station AS T1 JOIN status AS T2 ON T1.id  =  T2.station_id WHERE T2.bikes_available  =  7

Question: Which start station had the most trips starting from August? Give me the name and id of the station.
SQL: SELECT start_station_name ,  start_station_id FROM trip WHERE start_date LIKE "8/%" GROUP BY start_station_name ORDER BY COUNT(*) DESC LIMIT 1

Question: What are the start station's name and id for the one that had the most start trips in August?
SQL: SELECT start_station_name ,  start_station_id FROM trip WHERE start_date LIKE "8/%" GROUP BY start_station_name ORDER BY COUNT(*) DESC LIMIT 1

Question: Which bike traveled the most often in zip code 94002?
SQL: SELECT bike_id FROM trip WHERE zip_code  =  94002 GROUP BY bike_id ORDER BY COUNT(*) DESC LIMIT 1

Question: What is the id of the bike that traveled the most in 94002?
SQL: SELECT bike_id FROM trip WHERE zip_code  =  94002 GROUP BY bike_id ORDER BY COUNT(*) DESC LIMIT 1

Question: How many days had both mean humidity above 50 and mean visibility above 8?
SQL: SELECT COUNT(*) FROM weather WHERE mean_humidity  >  50 AND mean_visibility_miles  >  8

Question: What is the number of days that had an average humity above 50 and an average visibility above 8?
SQL: SELECT COUNT(*) FROM weather WHERE mean_humidity  >  50 AND mean_visibility_miles  >  8

Question: What is the latitude, longitude, city of the station from which the shortest trip started?
SQL: SELECT T1.lat ,  T1.long ,  T1.city FROM station AS T1 JOIN trip AS T2 ON T1.id  =  T2.start_station_id ORDER BY T2.duration LIMIT 1

Question: What is the latitude, longitude, and city of the station from which the trip with smallest duration started?
SQL: SELECT T1.lat ,  T1.long ,  T1.city FROM station AS T1 JOIN trip AS T2 ON T1.id  =  T2.start_station_id ORDER BY T2.duration LIMIT 1

Question: What are the ids of stations that are located in San Francisco and have average bike availability above 10.
SQL: SELECT id FROM station WHERE city  =  "San Francisco" INTERSECT SELECT station_id FROM status GROUP BY station_id HAVING avg(bikes_available)  >  10

Question: What are the ids of the stations in San Francisco that normally have more than 10 bikes available?
SQL: SELECT id FROM station WHERE city  =  "San Francisco" INTERSECT SELECT station_id FROM status GROUP BY station_id HAVING avg(bikes_available)  >  10

Question: What are the names and ids of stations that had more than 14 bikes available on average or were installed in December?
SQL: 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/%"

Question: What are the names and ids of all stations that have more than 14 bikes available on average or had bikes installed in December?
SQL: 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/%"