| context (string) | question (string) | answer (string) |
|---|---|---|
"CREATE TABLE head (head_id VARCHAR, name VARCHAR)" | "Which head's name has the substring 'Ha'? List the id and name." | "SELECT head_id, name FROM head WHERE name LIKE '%Ha%'" |
"CREATE TABLE farm (Id VARCHAR)" | "How many farms are there?" | "SELECT COUNT(*) FROM farm" |
"CREATE TABLE farm (Total_Horses VARCHAR)" | "List the total number of horses on farms in ascending order." | "SELECT Total_Horses FROM farm ORDER BY Total_Horses" |
"CREATE TABLE farm_competition (Hosts VARCHAR, Theme VARCHAR)" | "What are the hosts of competitions whose theme is not "Aliens"?" | "SELECT Hosts FROM farm_competition WHERE Theme <> 'Aliens'" |
"CREATE TABLE farm_competition (Theme VARCHAR, YEAR VARCHAR)" | "What are the themes of farm competitions sorted by year in ascending order?" | "SELECT Theme FROM farm_competition ORDER BY YEAR" |
"CREATE TABLE farm (Working_Horses INTEGER, Total_Horses INTEGER)" | "What is the average number of working horses of farms with more than 5000 total number of horses?" | "SELECT AVG(Working_Horses) FROM farm WHERE Total_Horses > 5000" |
"CREATE TABLE farm (Cows INTEGER)" | "What are the maximum and minimum number of cows across all farms." | "SELECT MAX(Cows), MIN(Cows) FROM farm" |
"CREATE TABLE city (Status VARCHAR)" | "How many different statuses do cities have?" | "SELECT COUNT(DISTINCT Status) FROM city" |
"CREATE TABLE city (Official_Name VARCHAR, Population VARCHAR)" | "List official names of cities in descending order of population." | "SELECT Official_Name FROM city ORDER BY Population DESC" |
"CREATE TABLE city (Official_Name VARCHAR, Status VARCHAR, Population VARCHAR)" | "List the official name and status of the city with the largest population." | "SELECT Official_Name, Status FROM city ORDER BY Population DESC LIMIT 1" |
"CREATE TABLE city (Official_Name VARCHAR, City_ID VARCHAR); CREATE TABLE farm_competition (Year VARCHAR, Host_city_ID VARCHAR)" | "Show the years and the official names of the host cities of competitions." | "SELECT T2.Year, T1.Official_Name FROM city AS T1 JOIN farm_competition AS T2 ON T1.City_ID = T2.Host_city_ID" |
"CREATE TABLE farm_competition (Host_city_ID VARCHAR); CREATE TABLE city (Official_Name VARCHAR, City_ID VARCHAR)" | "Show the official names of the cities that have hosted more than one competition." | "SELECT T1.Official_Name FROM city AS T1 JOIN farm_competition AS T2 ON T1.City_ID = T2.Host_city_ID GROUP BY T2.Host_city_ID HAVING COUNT(*) > 1" |
"CREATE TABLE city (Status VARCHAR, City_ID VARCHAR); CREATE TABLE farm_competition (Host_city_ID VARCHAR)" | "Show the status of the city that has hosted the greatest number of competitions." | "SELECT T1.Status FROM city AS T1 JOIN farm_competition AS T2 ON T1.City_ID = T2.Host_city_ID GROUP BY T2.Host_city_ID ORDER BY COUNT(*) DESC LIMIT 1" |
"CREATE TABLE city (City_ID VARCHAR, Population INTEGER); CREATE TABLE farm_competition (Theme VARCHAR, Host_city_ID VARCHAR)" | "Please show the themes of competitions with host cities having populations larger than 1000." | "SELECT T2.Theme FROM city AS T1 JOIN farm_competition AS T2 ON T1.City_ID = T2.Host_city_ID WHERE T1.Population > 1000" |
"CREATE TABLE city (Status VARCHAR, Population INTEGER)" | "Please show the different statuses of cities and the average population of cities with each status." | "SELECT Status, AVG(Population) FROM city GROUP BY Status" |
"CREATE TABLE city (Status VARCHAR)" | "Please show the different statuses, ordered by the number of cities that have each." | "SELECT Status FROM city GROUP BY Status ORDER BY COUNT(*)" |
"CREATE TABLE city (Status VARCHAR)" | "List the most common type of Status across cities." | "SELECT Status FROM city GROUP BY Status ORDER BY COUNT(*) DESC LIMIT 1" |
"CREATE TABLE farm_competition (Official_Name VARCHAR, City_ID VARCHAR, Host_city_ID VARCHAR); CREATE TABLE city (Official_Name VARCHAR, City_ID VARCHAR, Host_city_ID VARCHAR)" | "List the official names of cities that have not held any competition." | "SELECT Official_Name FROM city WHERE NOT City_ID IN (SELECT Host_city_ID FROM farm_competition)" |
"CREATE TABLE city (Status VARCHAR, Population INTEGER)" | "Show the status shared by cities with population bigger than 1500 and smaller than 500." | "SELECT Status FROM city WHERE Population > 1500 INTERSECT SELECT Status FROM city WHERE Population < 500" |
"CREATE TABLE city (Official_Name VARCHAR, Population VARCHAR)" | "Find the official names of cities with population bigger than 1500 or smaller than 500." | "SELECT Official_Name FROM city WHERE Population > 1500 OR Population < 500" |
"CREATE TABLE city (Census_Ranking VARCHAR, Status VARCHAR)" | "Show the census ranking of cities whose status are not "Village"." | "SELECT Census_Ranking FROM city WHERE Status <> "Village"" |
"CREATE TABLE courses (course_name VARCHAR, course_id VARCHAR); CREATE TABLE student_course_registrations (course_Id VARCHAR)" | "which course has most number of registered students?" | "SELECT T1.course_name FROM courses AS T1 JOIN student_course_registrations AS T2 ON T1.course_id = T2.course_Id GROUP BY T1.course_id ORDER BY COUNT(*) DESC LIMIT 1" |
"CREATE TABLE student_course_registrations (student_id VARCHAR)" | "what is id of students who registered some courses but the least number of courses in these students?" | "SELECT student_id FROM student_course_registrations GROUP BY student_id ORDER BY COUNT(*) LIMIT 1" |
"CREATE TABLE candidates (candidate_id VARCHAR); CREATE TABLE people (first_name VARCHAR, last_name VARCHAR, person_id VARCHAR)" | "what are the first name and last name of all candidates?" | "SELECT T2.first_name, T2.last_name FROM candidates AS T1 JOIN people AS T2 ON T1.candidate_id = T2.person_id" |
"CREATE TABLE student_course_attendance (student_id VARCHAR); CREATE TABLE students (student_id VARCHAR)" | "List the id of students who never attends courses?" | "SELECT student_id FROM students WHERE NOT student_id IN (SELECT student_id FROM student_course_attendance)" |
"CREATE TABLE student_course_attendance (student_id VARCHAR)" | "List the id of students who attended some courses?" | "SELECT student_id FROM student_course_attendance" |
"CREATE TABLE courses (course_name VARCHAR, course_id VARCHAR); CREATE TABLE student_course_registrations (student_id VARCHAR, course_id VARCHAR)" | "What are the ids of all students for courses and what are the names of those courses?" | "SELECT T1.student_id, T2.course_name FROM student_course_registrations AS T1 JOIN courses AS T2 ON T1.course_id = T2.course_id" |
"CREATE TABLE student_course_registrations (student_id VARCHAR, registration_date VARCHAR); CREATE TABLE students (student_details VARCHAR, student_id VARCHAR)" | "What is detail of the student who most recently registered course?" | "SELECT T2.student_details FROM student_course_registrations AS T1 JOIN students AS T2 ON T1.student_id = T2.student_id ORDER BY T1.registration_date DESC LIMIT 1" |
"CREATE TABLE student_course_attendance (course_id VARCHAR); CREATE TABLE courses (course_id VARCHAR, course_name VARCHAR)" | "How many students attend course English?" | "SELECT COUNT(*) FROM courses AS T1 JOIN student_course_attendance AS T2 ON T1.course_id = T2.course_id WHERE T1.course_name = "English"" |
"CREATE TABLE courses (course_id VARCHAR); CREATE TABLE student_course_attendance (course_id VARCHAR, student_id VARCHAR)" | "How many courses do the student whose id is 171 attend?" | "SELECT COUNT(*) FROM courses AS T1 JOIN student_course_attendance AS T2 ON T1.course_id = T2.course_id WHERE T2.student_id = 171" |
"CREATE TABLE candidates (candidate_id VARCHAR); CREATE TABLE people (person_id VARCHAR, email_address VARCHAR)" | "Find id of the candidate whose email is [email protected]?" | "SELECT T2.candidate_id FROM people AS T1 JOIN candidates AS T2 ON T1.person_id = T2.candidate_id WHERE T1.email_address = "[email protected]"" |
"CREATE TABLE candidate_assessments (candidate_id VARCHAR, assessment_date VARCHAR)" | "Find id of the candidate who most recently accessed the course?" | "SELECT candidate_id FROM candidate_assessments ORDER BY assessment_date DESC LIMIT 1" |
"CREATE TABLE students (student_details VARCHAR, student_id VARCHAR); CREATE TABLE student_course_registrations (student_id VARCHAR)" | "What is detail of the student who registered the most number of courses?" | "SELECT T1.student_details FROM students AS T1 JOIN student_course_registrations AS T2 ON T1.student_id = T2.student_id GROUP BY T1.student_id ORDER BY COUNT(*) DESC LIMIT 1" |
"CREATE TABLE students (student_id VARCHAR); CREATE TABLE student_course_registrations (student_id VARCHAR)" | "List the id of students who registered some courses and the number of their registered courses?" | "SELECT T1.student_id, COUNT(*) FROM students AS T1 JOIN student_course_registrations AS T2 ON T1.student_id = T2.student_id GROUP BY T1.student_id" |
"CREATE TABLE students (student_id VARCHAR); CREATE TABLE courses (course_name VARCHAR, course_id VARCHAR); CREATE TABLE student_course_registrations (course_id VARCHAR, student_id VARCHAR)" | "How many registed students do each course have? List course name and the number of their registered students?" | "SELECT T3.course_name, COUNT(*) FROM students AS T1 JOIN student_course_registrations AS T2 ON T1.student_id = T2.student_id JOIN courses AS T3 ON T2.course_id = T3.course_id GROUP BY T2.course_id" |
"CREATE TABLE candidate_assessments (candidate_id VARCHAR, asessment_outcome_code VARCHAR)" | "Find id of candidates whose assessment code is "Pass"?" | "SELECT candidate_id FROM candidate_assessments WHERE asessment_outcome_code = "Pass"" |
"CREATE TABLE candidates (candidate_id VARCHAR); CREATE TABLE people (cell_mobile_number VARCHAR, person_id VARCHAR); CREATE TABLE candidate_assessments (candidate_id VARCHAR, asessment_outcome_code VARCHAR)" | "Find the cell mobile number of the candidates whose assessment code is "Fail"?" | "SELECT T3.cell_mobile_number FROM candidates AS T1 JOIN candidate_assessments AS T2 ON T1.candidate_id = T2.candidate_id JOIN people AS T3 ON T1.candidate_id = T3.person_id WHERE T2.asessment_outcome_code = "Fail"" |
"CREATE TABLE student_course_attendance (student_id VARCHAR, course_id VARCHAR)" | "What are the id of students who registered course 301?" | "SELECT student_id FROM student_course_attendance WHERE course_id = 301" |
"CREATE TABLE student_course_attendance (student_id VARCHAR, course_id VARCHAR, date_of_attendance VARCHAR)" | "What is the id of the student who most recently registered course 301?" | "SELECT student_id FROM student_course_attendance WHERE course_id = 301 ORDER BY date_of_attendance DESC LIMIT 1" |
"CREATE TABLE addresses (city VARCHAR, address_id VARCHAR); CREATE TABLE people_addresses (address_id VARCHAR)" | "Find distinct cities of addresses of people?" | "SELECT DISTINCT T1.city FROM addresses AS T1 JOIN people_addresses AS T2 ON T1.address_id = T2.address_id" |
"CREATE TABLE students (student_id VARCHAR); CREATE TABLE addresses (city VARCHAR, address_id VARCHAR); CREATE TABLE people_addresses (address_id VARCHAR, person_id VARCHAR)" | "Find distinct cities of address of students?" | "SELECT DISTINCT T1.city FROM addresses AS T1 JOIN people_addresses AS T2 ON T1.address_id = T2.address_id JOIN students AS T3 ON T2.person_id = T3.student_id" |
"CREATE TABLE courses (course_name VARCHAR)" | "List the names of courses in alphabetical order?" | "SELECT course_name FROM courses ORDER BY course_name" |
"CREATE TABLE people (first_name VARCHAR)" | "List the first names of people in alphabetical order?" | "SELECT first_name FROM people ORDER BY first_name" |
"CREATE TABLE student_course_attendance (student_id VARCHAR); CREATE TABLE student_course_registrations (student_id VARCHAR)" | "What are the id of students who registered courses or attended courses?" | "SELECT student_id FROM student_course_registrations UNION SELECT student_id FROM student_course_attendance" |
"CREATE TABLE student_course_attendance (course_id VARCHAR, student_id VARCHAR); CREATE TABLE student_course_registrations (course_id VARCHAR, student_id VARCHAR)" | "Find the id of courses which are registered or attended by student whose id is 121?" | "SELECT course_id FROM student_course_registrations WHERE student_id = 121 UNION SELECT course_id FROM student_course_attendance WHERE student_id = 121" |
"CREATE TABLE student_course_attendance (student_id VARCHAR); CREATE TABLE student_course_registrations (student_id VARCHAR)" | "What are all info of students who registered courses but not attended courses?" | "SELECT * FROM student_course_registrations WHERE NOT student_id IN (SELECT student_id FROM student_course_attendance)" |
"CREATE TABLE student_course_registrations (student_id VARCHAR, course_id VARCHAR, registration_date VARCHAR); CREATE TABLE courses (course_id VARCHAR, course_name VARCHAR)" | "List the id of students who registered course statistics in the order of registration date." | "SELECT T2.student_id FROM courses AS T1 JOIN student_course_registrations AS T2 ON T1.course_id = T2.course_id WHERE T1.course_name = "statistics" ORDER BY T2.registration_date" |
"CREATE TABLE student_course_attendance (student_id VARCHAR, course_id VARCHAR, date_of_attendance VARCHAR); CREATE TABLE courses (course_id VARCHAR, course_name VARCHAR)" | "List the id of students who attended statistics courses in the order of attendance date." | "SELECT T2.student_id FROM courses AS T1 JOIN student_course_attendance AS T2 ON T1.course_id = T2.course_id WHERE T1.course_name = "statistics" ORDER BY T2.date_of_attendance" |
"CREATE TABLE weather (date VARCHAR, max_temperature_f INTEGER)" | "Give me the dates when the max temperature was higher than 85." | "SELECT date FROM weather WHERE max_temperature_f > 85" |
"CREATE TABLE station (name VARCHAR, lat INTEGER)" | "What are the names of stations that have latitude lower than 37.5?" | "SELECT name FROM station WHERE lat < 37.5" |
"CREATE TABLE station (city VARCHAR, lat INTEGER)" | "For each city, return the highest latitude among its stations." | "SELECT city, MAX(lat) FROM station GROUP BY city" |
"CREATE TABLE trip (start_station_name VARCHAR, end_station_name VARCHAR, id VARCHAR)" | "Give me the start station and end station for the trips with the three oldest id." | "SELECT start_station_name, end_station_name FROM trip ORDER BY id LIMIT 3" |
"CREATE TABLE station (lat INTEGER, long INTEGER, city VARCHAR)" | "What is the average latitude and longitude of stations located in San Jose city?" | "SELECT AVG(lat), AVG(long) FROM station WHERE city = "San Jose"" |
"CREATE TABLE trip (id VARCHAR, duration VARCHAR)" | "What is the id of the trip that has the shortest duration?" | "SELECT id FROM trip ORDER BY duration LIMIT 1" |
"CREATE TABLE trip (duration INTEGER, bike_id VARCHAR)" | "What is the total and maximum duration of trips with bike id 636?" | "SELECT SUM(duration), MAX(duration) FROM trip WHERE bike_id = 636" |
"CREATE TABLE weather (zip_code VARCHAR, mean_temperature_f INTEGER, date VARCHAR)" | "For each zip code, return the average mean temperature of August there." | "SELECT zip_code, AVG(mean_temperature_f) FROM weather WHERE date LIKE "8/%" GROUP BY zip_code" |
"CREATE TABLE trip (bike_id VARCHAR)" | "From the trip record, find the number of unique bikes." | "SELECT COUNT(DISTINCT bike_id) FROM trip" |
"CREATE TABLE station (city VARCHAR)" | "What is the number of distinct cities the stations are located at?" | "SELECT COUNT(DISTINCT city) FROM station" |
"CREATE TABLE station (city VARCHAR)" | "How many stations does Mountain View city has?" | "SELECT COUNT(*) FROM station WHERE city = "Mountain View"" |
"CREATE TABLE station (name VARCHAR, id VARCHAR); CREATE TABLE status (station_id VARCHAR, bikes_available VARCHAR)" | "Return the unique name for stations that have ever had 7 bikes available." | "SELECT DISTINCT T1.name FROM station AS T1 JOIN status AS T2 ON T1.id = T2.station_id WHERE T2.bikes_available = 7" |
"CREATE TABLE trip (start_station_name VARCHAR, start_station_id VARCHAR, start_date VARCHAR)" | "Which start station had the most trips starting from August? Give me the name and id of the station." | "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" |
"CREATE TABLE trip (bike_id VARCHAR, zip_code VARCHAR)" | "Which bike traveled the most often in zip code 94002?" | "SELECT bike_id FROM trip WHERE zip_code = 94002 GROUP BY bike_id ORDER BY COUNT(*) DESC LIMIT 1" |
"CREATE TABLE weather (mean_humidity VARCHAR, mean_visibility_miles VARCHAR)" | "How many days had both mean humidity above 50 and mean visibility above 8?" | "SELECT COUNT(*) FROM weather WHERE mean_humidity > 50 AND mean_visibility_miles > 8" |
"CREATE TABLE trip (start_station_id VARCHAR, duration VARCHAR); CREATE TABLE station (lat VARCHAR, long VARCHAR, city VARCHAR, id VARCHAR)" | "What is the latitude, longitude, city of the station from which the shortest trip started?" | "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" |
"CREATE TABLE status (id VARCHAR, station_id VARCHAR, city VARCHAR, bikes_available INTEGER); CREATE TABLE station (id VARCHAR, station_id VARCHAR, city VARCHAR, bikes_available INTEGER)" | "What are the ids of stations that are located in San Francisco and have average bike availability above 10." | "SELECT id FROM station WHERE city = "San Francisco" INTERSECT SELECT station_id FROM status GROUP BY station_id HAVING AVG(bikes_available) > 10" |
"CREATE TABLE station (name VARCHAR, id VARCHAR); CREATE TABLE station (name VARCHAR, id VARCHAR, installation_date VARCHAR); CREATE TABLE status (station_id VARCHAR, bikes_available INTEGER)" | "What are the names and ids of stations that had more than 14 bikes available on average or were installed in December?" | "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/%"" |
"CREATE TABLE weather (cloud_cover VARCHAR, zip_code VARCHAR)" | "What is the 3 most common cloud cover rates in the region of zip code 94107?" | "SELECT cloud_cover FROM weather WHERE zip_code = 94107 GROUP BY cloud_cover ORDER BY COUNT(*) DESC LIMIT 3" |
"CREATE TABLE weather (zip_code VARCHAR, mean_sea_level_pressure_inches INTEGER)" | "What is the zip code in which the average mean sea level pressure is the lowest?" | "SELECT zip_code FROM weather GROUP BY zip_code ORDER BY AVG(mean_sea_level_pressure_inches) LIMIT 1" |
"CREATE TABLE status (bikes_available INTEGER, station_id VARCHAR, id VARCHAR, city VARCHAR); CREATE TABLE station (bikes_available INTEGER, station_id VARCHAR, id VARCHAR, city VARCHAR)" | "What is the average bike availability in stations that are not located in Palo Alto?" | "SELECT AVG(bikes_available) FROM status WHERE NOT station_id IN (SELECT id FROM station WHERE city = "Palo Alto")" |
"CREATE TABLE station (long INTEGER, id VARCHAR, station_id VARCHAR, bikes_available INTEGER); CREATE TABLE status (long INTEGER, id VARCHAR, station_id VARCHAR, bikes_available INTEGER)" | "What is the average longitude of stations that never had bike availability more than 10?" | "SELECT AVG(long) FROM station WHERE NOT id IN (SELECT station_id FROM status GROUP BY station_id HAVING MAX(bikes_available) > 10)" |
"CREATE TABLE weather (date VARCHAR, zip_code VARCHAR, max_temperature_f VARCHAR)" | "When and in what zip code did max temperature reach 80?" | "SELECT date, zip_code FROM weather WHERE max_temperature_f >= 80" |
"CREATE TABLE trip (id VARCHAR, zip_code VARCHAR); CREATE TABLE weather (zip_code VARCHAR, mean_temperature_f INTEGER)" | "Give me ids for all the trip that took place in a zip code area with average mean temperature above 60." | "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" |
"CREATE TABLE weather (zip_code VARCHAR, max_wind_Speed_mph VARCHAR)" | "For each zip code, return how many times max wind speed reached 25?" | "SELECT zip_code, COUNT(*) FROM weather WHERE max_wind_Speed_mph >= 25 GROUP BY zip_code" |
"CREATE TABLE weather (date VARCHAR, zip_code VARCHAR, min_dew_point_f INTEGER)" | "On which day and in which zip code was the min dew point lower than any day in zip code 94107?" | "SELECT date, zip_code FROM weather WHERE min_dew_point_f < (SELECT MIN(min_dew_point_f) FROM weather WHERE zip_code = 94107)" |
"CREATE TABLE station (installation_date VARCHAR, id VARCHAR); CREATE TABLE trip (id VARCHAR, end_station_id VARCHAR)" | "For each trip, return its ending station's installation date." | "SELECT T1.id, T2.installation_date FROM trip AS T1 JOIN station AS T2 ON T1.end_station_id = T2.id" |
"CREATE TABLE trip (id VARCHAR, start_station_id VARCHAR); CREATE TABLE station (id VARCHAR, dock_count VARCHAR)" | "Which trip started from the station with the largest dock count? Give me the trip id." | "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" |
"CREATE TABLE trip (end_station_id VARCHAR); CREATE TABLE station (id VARCHAR, city VARCHAR)" | "Count the number of trips that did not end in San Francisco city." | "SELECT COUNT(*) FROM trip AS T1 JOIN station AS T2 ON T1.end_station_id = T2.id WHERE T2.city <> "San Francisco"" |
"CREATE TABLE weather (date VARCHAR, EVENTS VARCHAR, zip_code VARCHAR)" | "In zip code 94107, on which day neither Fog nor Rain was not observed?" | "SELECT date FROM weather WHERE zip_code = 94107 AND EVENTS <> "Fog" AND EVENTS <> "Rain"" |
"CREATE TABLE status (id VARCHAR, station_id VARCHAR, lat INTEGER, bikes_available INTEGER); CREATE TABLE station (id VARCHAR, station_id VARCHAR, lat INTEGER, bikes_available INTEGER)" | "What are the ids of stations that have latitude above 37.4 and never had bike availability below 7?" | "SELECT id FROM station WHERE lat > 37.4 EXCEPT SELECT station_id FROM status GROUP BY station_id HAVING MIN(bikes_available) < 7" |
"CREATE TABLE station (name VARCHAR, id VARCHAR); CREATE TABLE status (station_id VARCHAR); CREATE TABLE station (name VARCHAR, city VARCHAR, bikes_available INTEGER)" | "What are names of stations that have average bike availability above 10 and are not located in San Jose city?" | "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"" |
"CREATE TABLE station (name VARCHAR, lat VARCHAR, city VARCHAR)" | "What are the name, latitude, and city of the station with the lowest latitude?" | "SELECT name, lat, city FROM station ORDER BY lat LIMIT 1" |
"CREATE TABLE weather (date VARCHAR, mean_temperature_f VARCHAR, mean_humidity VARCHAR, max_gust_speed_mph VARCHAR)" | "What are the date, mean temperature and mean humidity for the top 3 days with the largest max gust speeds?" | "SELECT date, mean_temperature_f, mean_humidity FROM weather ORDER BY max_gust_speed_mph DESC LIMIT 3" |
"CREATE TABLE station (city VARCHAR)" | "List the name and the number of stations for all the cities that have at least 15 stations." | "SELECT city, COUNT(*) FROM station GROUP BY city HAVING COUNT(*) >= 15" |
"CREATE TABLE trip (start_station_id VARCHAR, start_station_name VARCHAR)" | "Find the ids and names of stations from which at least 200 trips started." | "SELECT start_station_id, start_station_name FROM trip GROUP BY start_station_name HAVING COUNT(*) >= 200" |
"CREATE TABLE weather (zip_code VARCHAR, mean_visibility_miles INTEGER)" | "Find the zip code in which the average mean visibility is lower than 10." | "SELECT zip_code FROM weather GROUP BY zip_code HAVING AVG(mean_visibility_miles) < 10" |
"CREATE TABLE station (city VARCHAR, lat INTEGER)" | "List all the cities in a decreasing order of each city's stations' highest latitude." | "SELECT city FROM station GROUP BY city ORDER BY MAX(lat) DESC" |
"CREATE TABLE weather (date VARCHAR, cloud_cover VARCHAR)" | "What are the dates that had the top 5 cloud cover rates? Also tell me the cloud cover rate." | "SELECT date, cloud_cover FROM weather ORDER BY cloud_cover DESC LIMIT 5" |
"CREATE TABLE trip (id VARCHAR, duration VARCHAR)" | "What are the ids and durations of the trips with the top 3 durations?" | "SELECT id, duration FROM trip ORDER BY duration DESC LIMIT 3" |
"CREATE TABLE station (name VARCHAR, long VARCHAR, id VARCHAR); CREATE TABLE trip (duration INTEGER, start_station_id VARCHAR)" | "For each station, return its longitude and the average duration of trips that started from the station." | "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" |
"CREATE TABLE trip (duration INTEGER, end_station_id VARCHAR); CREATE TABLE station (name VARCHAR, lat VARCHAR, id VARCHAR)" | "For each station, find its latitude and the minimum duration of trips that ended at the station." | "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" |
"CREATE TABLE trip (start_station_name VARCHAR, duration INTEGER)" | "List all the distinct stations from which a trip of duration below 100 started." | "SELECT DISTINCT start_station_name FROM trip WHERE duration < 100" |
"CREATE TABLE weather (zip_code VARCHAR, max_dew_point_f VARCHAR)" | "Find all the zip codes in which the max dew point have never reached 70." | "SELECT DISTINCT zip_code FROM weather EXCEPT SELECT DISTINCT zip_code FROM weather WHERE max_dew_point_f >= 70" |
"CREATE TABLE trip (id VARCHAR, duration INTEGER, zip_code VARCHAR)" | "Find the id for the trips that lasted at least as long as the average duration of trips in zip code 94103." | "SELECT id FROM trip WHERE duration >= (SELECT AVG(duration) FROM trip WHERE zip_code = 94103)" |
"CREATE TABLE weather (date VARCHAR, mean_sea_level_pressure_inches INTEGER)" | "What are the dates in which the mean sea level pressure was between 30.3 and 31?" | "SELECT date FROM weather WHERE mean_sea_level_pressure_inches BETWEEN 30.3 AND 31" |
"CREATE TABLE weather (date VARCHAR, max_temperature_f VARCHAR, min_temperature_f VARCHAR)" | "Find the day in which the difference between the max temperature and min temperature was the smallest. Also report the difference." | "SELECT date, max_temperature_f - min_temperature_f FROM weather ORDER BY max_temperature_f - min_temperature_f LIMIT 1" |
"CREATE TABLE station (id VARCHAR, name VARCHAR); CREATE TABLE status (station_id VARCHAR, bikes_available INTEGER)" | "What are the id and name of the stations that have ever had more than 12 bikes available?" | "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" |
"CREATE TABLE weather (zip_code VARCHAR, mean_humidity INTEGER); CREATE TABLE trip (zip_code VARCHAR, mean_humidity INTEGER)" | "Give me the zip code where the average mean humidity is below 70 and at least 100 trips took place." | "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" |
"CREATE TABLE trip (name VARCHAR, end_station_name VARCHAR, city VARCHAR); CREATE TABLE station (name VARCHAR, end_station_name VARCHAR, city VARCHAR)" | "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?" | "SELECT name FROM station WHERE city = "Palo Alto" EXCEPT SELECT end_station_name FROM trip GROUP BY end_station_name HAVING COUNT(*) > 100" |
"CREATE TABLE station (city VARCHAR, id VARCHAR); CREATE TABLE trip (end_station_id VARCHAR, id VARCHAR); CREATE TABLE station (id VARCHAR, city VARCHAR); CREATE TABLE trip (start_station_id VARCHAR, id VARCHAR)" | "How many trips started from Mountain View city and ended at Palo Alto city?" | "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"" |
"CREATE TABLE trip (start_station_id VARCHAR); CREATE TABLE station (lat INTEGER, long INTEGER, id VARCHAR)" | "What is the average latitude and longitude of the starting points of all trips?" | "SELECT AVG(T1.lat), AVG(T1.long) FROM station AS T1 JOIN trip AS T2 ON T1.id = T2.start_station_id" |