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 are the date, mean temperature and mean humidity for the top 3 days with the largest max gust speeds?
SQL: SELECT date ,  mean_temperature_f ,  mean_humidity FROM weather ORDER BY max_gust_speed_mph DESC LIMIT 3

Question: What is the date, average temperature and mean humidity for the days with the 3 largest maximum gust speeds?
SQL: SELECT date ,  mean_temperature_f ,  mean_humidity FROM weather ORDER BY max_gust_speed_mph DESC LIMIT 3

Question: List the name and the number of stations for all the cities that have at least 15 stations.
SQL: SELECT city ,  COUNT(*) FROM station GROUP BY city HAVING COUNT(*)  >=  15

Question: What is the name of every city that has at least 15 stations and how many stations does it have?
SQL: SELECT city ,  COUNT(*) FROM station GROUP BY city HAVING COUNT(*)  >=  15

Question: Find the ids and names of stations from which at least 200 trips started.
SQL: SELECT start_station_id ,  start_station_name FROM trip GROUP BY start_station_name HAVING COUNT(*)  >=  200

Question: What are the ids and names of all start stations that were the beginning of at least 200 trips?
SQL: SELECT start_station_id ,  start_station_name FROM trip GROUP BY start_station_name HAVING COUNT(*)  >=  200

Question: Find the zip code in which the average mean visibility is lower than 10.
SQL: SELECT zip_code FROM weather GROUP BY zip_code HAVING avg(mean_visibility_miles)  <  10

Question: For each zip code, select all those that have an average mean visiblity below 10.
SQL: SELECT zip_code FROM weather GROUP BY zip_code HAVING avg(mean_visibility_miles)  <  10

Question: List all the cities in a decreasing order of each city's stations' highest latitude.
SQL: SELECT city FROM station GROUP BY city ORDER BY max(lat) DESC

Question: For each city, list their names in decreasing order by their highest station latitude.
SQL: SELECT city FROM station GROUP BY city ORDER BY max(lat) DESC

Question: What are the dates that had the top 5 cloud cover rates? Also tell me the cloud cover rate.
SQL: SELECT date ,  cloud_cover FROM weather ORDER BY cloud_cover DESC LIMIT 5

Question: What are the dates that have the 5 highest cloud cover rates and what are the rates?
SQL: SELECT date ,  cloud_cover FROM weather ORDER BY cloud_cover DESC LIMIT 5

Question: What are the ids and durations of the trips with the top 3 durations?
SQL: SELECT id ,  duration FROM trip ORDER BY duration DESC LIMIT 3

Question: What are the ids of the trips that lasted the longest and how long did they last?
SQL: SELECT id ,  duration FROM trip ORDER BY duration DESC LIMIT 3

Question: For each station, return its longitude and the average duration of trips that started from the station.
SQL: SELECT T1.name ,  T1.long ,  avg(T2.duration) FROM station AS T1 JOIN trip AS T2 ON T1.id  =  T2.start_station_id GROUP BY T2.start_station_id

Question: For each start station id, what is its name, longitude and average duration of trips started there?
SQL: SELECT T1.name ,  T1.long ,  avg(T2.duration) FROM station AS T1 JOIN trip AS T2 ON T1.id  =  T2.start_station_id GROUP BY T2.start_station_id

Question: For each station, find its latitude and the minimum duration of trips that ended at the station.
SQL: SELECT T1.name ,  T1.lat ,  min(T2.duration) FROM station AS T1 JOIN trip AS T2 ON T1.id  =  T2.end_station_id GROUP BY T2.end_station_id

Question: For each end station id, what is its name, latitude, and minimum duration for trips ended there?
SQL: SELECT T1.name ,  T1.lat ,  min(T2.duration) FROM station AS T1 JOIN trip AS T2 ON T1.id  =  T2.end_station_id GROUP BY T2.end_station_id

Question: List all the distinct stations from which a trip of duration below 100 started.
SQL: SELECT DISTINCT start_station_name FROM trip WHERE duration  <  100

Question: What are all the different start station names for a trip that lasted less than 100?
SQL: SELECT DISTINCT start_station_name FROM trip WHERE duration  <  100

Question: Find all the zip codes in which the max dew point have never reached 70.
SQL: SELECT DISTINCT zip_code FROM weather EXCEPT SELECT DISTINCT zip_code FROM weather WHERE max_dew_point_f  >=  70

Question: What are all the different zip codes that have a maximum dew point that was always below 70?
SQL: SELECT DISTINCT zip_code FROM weather EXCEPT SELECT DISTINCT zip_code FROM weather WHERE max_dew_point_f  >=  70

Question: Find the id for the trips that lasted at least as long as the average duration of trips in zip code 94103.
SQL: SELECT id FROM trip WHERE duration  >=  (SELECT avg(duration) FROM trip WHERE zip_code  =  94103)

Question: What are the ids of all trips that had a duration as long as the average trip duration in the zip code 94103?
SQL: SELECT id FROM trip WHERE duration  >=  (SELECT avg(duration) FROM trip WHERE zip_code  =  94103)

Question: What are the dates in which the mean sea level pressure was between 30.3 and 31?
SQL: SELECT date FROM weather WHERE mean_sea_level_pressure_inches BETWEEN 30.3 AND 31

Question: What are the dates that have an average sea level pressure between 30.3 and 31?
SQL: SELECT date FROM weather WHERE mean_sea_level_pressure_inches BETWEEN 30.3 AND 31

Question: Find the day in which the difference between the max temperature and min temperature was the smallest. Also report the difference.
SQL: SELECT date ,  max_temperature_f - min_temperature_f FROM weather ORDER BY max_temperature_f - min_temperature_f LIMIT 1

Question: What are the days that had the smallest temperature range, and what was that range?
SQL: SELECT date ,  max_temperature_f - min_temperature_f FROM weather ORDER BY max_temperature_f - min_temperature_f LIMIT 1

Question: What are the id and name of the stations that have ever had more than 12 bikes available?
SQL: 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

Question: What are the different ids and names of the stations that have had more than 12 bikes available?
SQL: 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

Question: Give me the zip code where the average mean humidity is below 70 and at least 100 trips took place.
SQL: SELECT zip_code FROM weather GROUP BY zip_code HAVING avg(mean_humidity)  <  70 INTERSECT SELECT zip_code FROM trip GROUP BY zip_code HAVING count(*)  >=  100