Database: bike_1

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

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/%"

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

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

Question: What are the zip codes that have an average mean humidity below 70 and had at least 100 trips come through there?
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

Question: 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?
SQL: SELECT name FROM station WHERE city  =  "Palo Alto" EXCEPT SELECT end_station_name FROM trip GROUP BY end_station_name HAVING count(*)  >  100

Question: What are the names of the stations that are located in Palo Alto but have never been the ending point of the trips
SQL: SELECT name FROM station WHERE city  =  "Palo Alto" EXCEPT SELECT end_station_name FROM trip GROUP BY end_station_name HAVING count(*)  >  100

Question: How many trips started from Mountain View city and ended at Palo Alto city?
SQL: 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"

Question: How many trips stated from  a station in Mountain View and ended at one in Palo Alto?
SQL: 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"

Question: What is the average latitude and longitude of the starting points of all trips?
SQL: SELECT avg(T1.lat) ,  avg(T1.long) FROM station AS T1 JOIN trip AS T2 ON T1.id  =  T2.start_station_id

Question: What is the average latitude and longitude of all starting stations for the trips?
SQL: SELECT avg(T1.lat) ,  avg(T1.long) FROM station AS T1 JOIN trip AS T2 ON T1.id  =  T2.start_station_id