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: What is the 3 most common cloud cover rates in the region of zip code 94107?
SQL: SELECT cloud_cover FROM weather WHERE zip_code  =  94107 GROUP BY cloud_cover ORDER BY COUNT (*) DESC LIMIT 3

Question: What are the 3 most common cloud covers in the zip code of 94107?
SQL: SELECT cloud_cover FROM weather WHERE zip_code  =  94107 GROUP BY cloud_cover ORDER BY COUNT (*) DESC LIMIT 3

Question: What is the zip code in which the average mean sea level pressure is the lowest?
SQL: SELECT zip_code FROM weather GROUP BY zip_code ORDER BY avg(mean_sea_level_pressure_inches) LIMIT 1

Question: What is the zip code that has the lowest average mean sea level pressure?
SQL: SELECT zip_code FROM weather GROUP BY zip_code ORDER BY avg(mean_sea_level_pressure_inches) LIMIT 1

Question: What is the average bike availability in stations that are not located in Palo Alto?
SQL: SELECT avg(bikes_available) FROM status WHERE station_id NOT IN (SELECT id FROM station WHERE city  =  "Palo Alto")

Question: What is the average bike availablility for stations not in Palo Alto?
SQL: SELECT avg(bikes_available) FROM status WHERE station_id NOT IN (SELECT id FROM station WHERE city  =  "Palo Alto")

Question: What is the average longitude of stations that never had bike availability more than 10?
SQL: SELECT avg(long) FROM station WHERE id NOT IN (SELECT station_id FROM status GROUP BY station_id HAVING max(bikes_available)  >  10)

Question: What is the mean longitude for all stations that have never had more than 10 bikes available?
SQL: SELECT avg(long) FROM station WHERE id NOT IN (SELECT station_id FROM status GROUP BY station_id HAVING max(bikes_available)  >  10)

Question: When and in what zip code did max temperature reach 80?
SQL: SELECT date ,  zip_code FROM weather WHERE max_temperature_f  >=  80

Question: What zip codes have a station with a max temperature greater than or equal to 80 and when did it reach that temperature?
SQL: SELECT date ,  zip_code FROM weather WHERE max_temperature_f  >=  80

Question: Give me ids for all the trip that took place in a zip code area with average mean temperature above 60.
SQL: SELECT T1.id FROM trip AS T1 JOIN weather AS T2 ON T1.zip_code  =  T2.zip_code GROUP BY T2.zip_code HAVING avg(T2.mean_temperature_f)  >  60

Question: For each zip code, find the ids of all trips that have a higher average mean temperature above 60?
SQL: SELECT T1.id FROM trip AS T1 JOIN weather AS T2 ON T1.zip_code  =  T2.zip_code GROUP BY T2.zip_code HAVING avg(T2.mean_temperature_f)  >  60

Question: For each zip code, return how many times max wind speed reached 25?
SQL: SELECT zip_code , count(*) FROM weather WHERE max_wind_Speed_mph  >=  25 GROUP BY zip_code

Question: For each zip code, how many times has the maximum wind speed reached 25 mph?
SQL: SELECT zip_code , count(*) FROM weather WHERE max_wind_Speed_mph  >=  25 GROUP BY zip_code

Question: On which day and in which zip code was the min dew point lower than any day in zip code 94107?
SQL: SELECT date ,  zip_code FROM weather WHERE min_dew_point_f  <  (SELECT min(min_dew_point_f) FROM weather WHERE zip_code  =  94107)

Question: Which days had a minimum dew point smaller than any day in zip code 94107, and in which zip codes were those measurements taken?
SQL: SELECT date ,  zip_code FROM weather WHERE min_dew_point_f  <  (SELECT min(min_dew_point_f) FROM weather WHERE zip_code  =  94107)

Question: For each trip, return its ending station's installation date.
SQL: SELECT T1.id ,  T2.installation_date FROM trip AS T1 JOIN station AS T2 ON T1.end_station_id  =  T2.id

Question: What is the installation date for each ending station on all the trips?
SQL: SELECT T1.id ,  T2.installation_date FROM trip AS T1 JOIN station AS T2 ON T1.end_station_id  =  T2.id

Question: Which trip started from the station with the largest dock count? Give me the trip id.
SQL: SELECT T1.id FROM trip AS T1 JOIN station AS T2 ON T1.start_station_id  =  T2.id ORDER BY T2.dock_count DESC LIMIT 1

Question: What is the id of the trip that started from the station with the highest dock count?
SQL: SELECT T1.id FROM trip AS T1 JOIN station AS T2 ON T1.start_station_id  =  T2.id ORDER BY T2.dock_count DESC LIMIT 1

Question: Count the number of trips that did not end in San Francisco city.
SQL: SELECT count(*) FROM trip AS T1 JOIN station AS T2 ON T1.end_station_id  =  T2.id WHERE T2.city !=  "San Francisco"

Question: How many trips did not end in San Francisco?
SQL: SELECT count(*) FROM trip AS T1 JOIN station AS T2 ON T1.end_station_id  =  T2.id WHERE T2.city != "San Francisco"

Question: In zip code 94107, on which day neither Fog nor Rain was not observed?
SQL: SELECT date FROM weather WHERE zip_code  =  94107 AND EVENTS != "Fog" AND EVENTS != "Rain"

Question: On which day has it neither been foggy nor rained in the zip code of 94107?
SQL: SELECT date FROM weather WHERE zip_code  =  94107 AND EVENTS != "Fog" AND EVENTS != "Rain"

Question: What are the ids of stations that have latitude above 37.4 and never had bike availability below 7?
SQL: SELECT id FROM station WHERE lat  >  37.4 EXCEPT SELECT station_id FROM status GROUP BY station_id HAVING min(bikes_available)  <  7

Question: What are the ids of all stations that have a latitude above 37.4 and have never had less than 7 bikes available?
SQL: SELECT id FROM station WHERE lat  >  37.4 EXCEPT SELECT station_id FROM status GROUP BY station_id HAVING min(bikes_available)  <  7

Question: What are names of stations that have average bike availability above 10 and are not located in San Jose city?
SQL: 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"

Question: What are the names of all stations that have more than 10 bikes available and are not located in San Jose?
SQL: 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"

Question: What are the name, latitude, and city of the station with the lowest latitude?
SQL: SELECT name ,  lat ,  city FROM station ORDER BY lat LIMIT 1

Question: What is the name, latitude, and city of the station that is located the furthest South?
SQL: SELECT name ,  lat ,  city FROM station ORDER BY lat LIMIT 1