lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
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
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"
### Database Schema
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
What is the average latitude and longitude in San Jose?

### SQL
SELECT avg(lat) ,  avg(long) FROM station WHERE city  =  "San Jose"
### Database Schema
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
What is the id of the trip that has the shortest duration?

### SQL
SELECT id FROM trip ORDER BY duration LIMIT 1
### Database Schema
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
What is the id of the shortest trip?

### SQL
SELECT id FROM trip ORDER BY duration LIMIT 1
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
From the trip record, find the number of unique bikes.

### SQL
SELECT count(DISTINCT bike_id) FROM trip
### Database Schema
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
How many different bike ids are there?

### SQL
SELECT count(DISTINCT bike_id) FROM trip
### Database Schema
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
What is the number of distinct cities the stations are located at?

### SQL
SELECT count(DISTINCT city) FROM station
### Database Schema
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
How many different cities have these stations?

### SQL
SELECT count(DISTINCT city) FROM station
### Database Schema
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
How many stations does Mountain View city has?

### SQL
SELECT COUNT(*) FROM station WHERE city  =  "Mountain View"
### Database Schema
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
How many stations are in Mountain View?

### SQL
SELECT COUNT(*) FROM station WHERE city  =  "Mountain View"
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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/%"
### Database Schema
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
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/%"
### Database Schema
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
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