target-benchmark/spider-queries huggingface dataset:
Database: local_govt_and_lot (tables: customer_event_notes, customer_events)
Question: Which events have the number of notes between one and three? List the event id and the property id.
SQL: SELECT T1.Customer_Event_ID  ,  T1.property_id FROM Customer_Events AS T1 JOIN Customer_Event_Notes AS T2 ON T1.Customer_Event_ID  =  T2.Customer_Event_ID GROUP BY T1.customer_event_id HAVING count(*) BETWEEN 1 AND 3
Database: local_govt_and_lot (tables: timed_status_of_things, things)
Question: What are the distinct id and type of the thing that has the status 'Close' or has a status record before the date '2017-06-19 02:59:21'
SQL: SELECT DISTINCT T2.thing_id ,  T2.Type_of_Thing_Code FROM Timed_Status_of_Things AS T1 JOIN Things AS T2 ON T1.thing_id  =  T2.thing_id WHERE T1.Status_of_Thing_Code  =  'Close' OR T1.Date_and_Date  <  '2017-06-19 02:59:21'
Database: local_govt_and_lot (tables: things, timed_locations_of_things)
Question: How many distinct locations have the things with service detail 'Unsatisfied' been located in?
SQL: SELECT count(DISTINCT T2.Location_Code) FROM Things AS T1 JOIN Timed_Locations_of_Things AS T2 ON T1.thing_id  =  T2.thing_id WHERE T1.service_details  =  'Unsatisfied'
Database: local_govt_and_lot (tables: timed_status_of_things)
Question: How many different status codes of things are there?
SQL: SELECT count(DISTINCT Status_of_Thing_Code) FROM Timed_Status_of_Things
Database: local_govt_and_lot (tables: organizations)
Question: Which organizations are not a parent organization of others? List the organization id.
SQL: SELECT organization_id FROM organizations EXCEPT SELECT parent_organization_id FROM organizations
Database: local_govt_and_lot (tables: residents)
Question: When is the last day any resident moved in?
SQL: SELECT max(date_moved_in) FROM Residents
Database: local_govt_and_lot (tables: residents)
Question: What are the resident details containing the substring 'Miss'?
SQL: SELECT other_details FROM Residents WHERE other_details LIKE '%Miss%'
Database: local_govt_and_lot (tables: customer_events)
Question: List the customer event id and the corresponding move in date and property id.
SQL: SELECT customer_event_id ,  date_moved_in ,  property_id FROM customer_events
Database: local_govt_and_lot (tables: customer_events, customers)
Question: How many customers did not have any event?
SQL: SELECT count(*) FROM customers WHERE customer_id NOT IN ( SELECT customer_id FROM customer_events )
Database: local_govt_and_lot (tables: residents)
Question: What are the distinct move in dates of the residents?
SQL: SELECT DISTINCT date_moved_in FROM residents
Database: school_player (tables: school)
Question: How many schools are there?
SQL: SELECT count(*) FROM school
Database: school_player (tables: school)
Question: Count the number of schools.
SQL: SELECT count(*) FROM school
Database: school_player (tables: school)
Question: List the locations of schools in ascending order of enrollment.
SQL: SELECT LOCATION FROM school ORDER BY Enrollment ASC
Database: school_player (tables: school)
Question: What is the list of school locations sorted in ascending order of school enrollment?
SQL: SELECT LOCATION FROM school ORDER BY Enrollment ASC
Database: school_player (tables: school)
Question: List the locations of schools in descending order of founded year.
SQL: SELECT LOCATION FROM school ORDER BY Founded DESC
Database: school_player (tables: school)
Question: What is the list of school locations sorted in descending order of school foundation year?
SQL: SELECT LOCATION FROM school ORDER BY Founded DESC
Database: school_player (tables: school)
Question: What are the enrollments of schools whose denomination is not "Catholic"?
SQL: SELECT Enrollment FROM school WHERE Denomination != "Catholic"
Database: school_player (tables: school)
Question: List the enrollment for each school that does not have "Catholic" as denomination.
SQL: SELECT Enrollment FROM school WHERE Denomination != "Catholic"
Database: school_player (tables: school)
Question: What is the average enrollment of schools?
SQL: SELECT avg(Enrollment) FROM school
Database: school_player (tables: school)
Question: Take the average of the school enrollment.
SQL: SELECT avg(Enrollment) FROM school
Database: school_player (tables: player)
Question: What are the teams of the players, sorted in ascending alphabetical order?
SQL: SELECT Team FROM player ORDER BY Team ASC
Database: school_player (tables: player)
Question: Find the team of each player and sort them in ascending alphabetical order.
SQL: SELECT Team FROM player ORDER BY Team ASC
Database: school_player (tables: player)
Question: How many different positions of players are there?
SQL: SELECT count(DISTINCT POSITION) FROM player
Database: school_player (tables: player)
Question: Count the number of distinct player positions.
SQL: SELECT count(DISTINCT POSITION) FROM player
Database: school_player (tables: player)
Question: Find the team of the player of the highest age.
SQL: SELECT Team FROM player ORDER BY Age DESC LIMIT 1
Database: school_player (tables: player)
Question: Which team has the oldest player?
SQL: SELECT Team FROM player ORDER BY Age DESC LIMIT 1
Database: school_player (tables: player)
Question: List the teams of the players with the top 5 largest ages.
SQL: SELECT Team FROM player ORDER BY Age DESC LIMIT 5
Database: school_player (tables: player)
Question: What are the teams that have the 5 oldest players?
SQL: SELECT Team FROM player ORDER BY Age DESC LIMIT 5
Database: school_player (tables: school, player)
Question: For each player, show the team and the location of school they belong to.
SQL: SELECT T1.Team ,  T2.Location FROM player AS T1 JOIN school AS T2 ON T1.School_ID  =  T2.School_ID
Database: school_player (tables: school, player)
Question: What are the team and the location of school each player belongs to?
SQL: SELECT T1.Team ,  T2.Location FROM player AS T1 JOIN school AS T2 ON T1.School_ID  =  T2.School_ID
Database: school_player (tables: school, player)
Question: Show the locations of schools that have more than 1 player.
SQL: SELECT T2.Location FROM player AS T1 JOIN school AS T2 ON T1.School_ID  =  T2.School_ID GROUP BY T1.School_ID HAVING COUNT(*)  >  1
Database: school_player (tables: school, player)
Question: Which schools have more than 1 player? Give me the school locations.
SQL: SELECT T2.Location FROM player AS T1 JOIN school AS T2 ON T1.School_ID  =  T2.School_ID GROUP BY T1.School_ID HAVING COUNT(*)  >  1
Database: school_player (tables: school, player)
Question: Show the denomination of the school that has the most players.
SQL: SELECT T2.Denomination FROM player AS T1 JOIN school AS T2 ON T1.School_ID  =  T2.School_ID GROUP BY T1.School_ID ORDER BY COUNT(*) DESC LIMIT 1
Database: school_player (tables: school, player)
Question: What is the denomination of the school the most players belong to?
SQL: SELECT T2.Denomination FROM player AS T1 JOIN school AS T2 ON T1.School_ID  =  T2.School_ID GROUP BY T1.School_ID ORDER BY COUNT(*) DESC LIMIT 1
Database: school_player (tables: school, school_details)
Question: Show locations and nicknames of schools.
SQL: SELECT T1.Location ,  T2.Nickname FROM school AS T1 JOIN school_details AS T2 ON T1.School_ID  =  T2.School_ID
Database: school_player (tables: school, school_details)
Question: What are the location and nickname of each school?
SQL: SELECT T1.Location ,  T2.Nickname FROM school AS T1 JOIN school_details AS T2 ON T1.School_ID  =  T2.School_ID
Database: school_player (tables: school)
Question: Please show different denominations and the corresponding number of schools.
SQL: SELECT Denomination ,  COUNT(*) FROM school GROUP BY Denomination
Database: school_player (tables: school)
Question: For each denomination, return the denomination and the count of schools with that denomination.
SQL: SELECT Denomination ,  COUNT(*) FROM school GROUP BY Denomination
Database: school_player (tables: school)
Question: Please show different denominations and the corresponding number of schools in descending order.
SQL: SELECT Denomination ,  COUNT(*) FROM school GROUP BY Denomination ORDER BY COUNT(*) DESC
Database: school_player (tables: school)
Question: Order denominations in descending order of the count of schools with the denomination. Return each denomination with the count of schools.
SQL: SELECT Denomination ,  COUNT(*) FROM school GROUP BY Denomination ORDER BY COUNT(*) DESC
Database: school_player (tables: school)
Question: List the school color of the school that has the largest enrollment.
SQL: SELECT School_Colors FROM school ORDER BY Enrollment DESC LIMIT 1
Database: school_player (tables: school)
Question: What is the school color of the school with the largest enrollment?
SQL: SELECT School_Colors FROM school ORDER BY Enrollment DESC LIMIT 1
Database: school_player (tables: player, school)
Question: List the locations of schools that do not have any player.
SQL: SELECT LOCATION FROM school WHERE School_ID NOT IN (SELECT School_ID FROM Player)
Database: school_player (tables: player, school)
Question: Which schools do not have any player? Give me the school locations.
SQL: SELECT LOCATION FROM school WHERE School_ID NOT IN (SELECT School_ID FROM Player)
Database: school_player (tables: school)
Question: Show the denomination shared by schools founded before 1890 and schools founded after 1900
SQL: SELECT Denomination FROM school WHERE Founded  <  1890 INTERSECT SELECT Denomination FROM school WHERE Founded  >  1900
Database: school_player (tables: school)
Question: What are the denominations used by both schools founded before 1890 and schools founded  after 1900?
SQL: SELECT Denomination FROM school WHERE Founded  <  1890 INTERSECT SELECT Denomination FROM school WHERE Founded  >  1900
Database: school_player (tables: school_details)
Question: Show the nicknames of schools that are not in division 1.
SQL: SELECT Nickname FROM school_details WHERE Division != "Division 1"
Database: school_player (tables: school_details)
Question: What are the nicknames of schools whose division is not 1?
SQL: SELECT Nickname FROM school_details WHERE Division != "Division 1"
Database: school_player (tables: school)
Question: Show the denomination shared by more than one school.
SQL: SELECT Denomination FROM school GROUP BY Denomination HAVING COUNT(*)  >  1
Database: school_player (tables: school)
Question: What are the denomination more than one school have?
SQL: SELECT Denomination FROM school GROUP BY Denomination HAVING COUNT(*)  >  1
Database: store_product (tables: district)
Question: Find all the distinct district names ordered by city area in descending.
SQL: SELECT DISTINCT District_name FROM district ORDER BY city_area DESC
Database: store_product (tables: district)
Question: What are the different district names in order of descending city area?
SQL: SELECT DISTINCT District_name FROM district ORDER BY city_area DESC
Database: store_product (tables: product)
Question: Find the list of page size which have more than 3 product listed
SQL: SELECT max_page_size FROM product GROUP BY max_page_size HAVING count(*)  >  3
Database: store_product (tables: product)
Question: What is the maximum page size for everything that has more than 3 products listed?
SQL: SELECT max_page_size FROM product GROUP BY max_page_size HAVING count(*)  >  3
Database: store_product (tables: district)
Question: Find the name and population of district with population between 200000 and 2000000
SQL: SELECT District_name ,  City_Population FROM district WHERE City_Population BETWEEN 200000 AND 2000000
Database: store_product (tables: district)
Question: What are the district names and city populations for all districts that between 200,000 and 2,000,000 residents?
SQL: SELECT District_name ,  City_Population FROM district WHERE City_Population BETWEEN 200000 AND 2000000
Database: store_product (tables: district)
Question: Find the name all districts with city area greater than 10 or population larger than 100000
SQL: SELECT district_name FROM district WHERE city_area  >  10 OR City_Population  >  100000
Database: store_product (tables: district)
Question: What are the names of all districts with a city area greater than 10 or have more than 100000 people living there?
SQL: SELECT district_name FROM district WHERE city_area  >  10 OR City_Population  >  100000
Database: store_product (tables: district)
Question: Which district has the largest population?
SQL: SELECT district_name FROM district ORDER BY city_population DESC LIMIT 1
Database: store_product (tables: district)
Question: What is the name of the district with the most residents?
SQL: SELECT district_name FROM district ORDER BY city_population DESC LIMIT 1
Database: store_product (tables: district)
Question: Which district has the least area?
SQL: SELECT district_name FROM district ORDER BY city_area ASC LIMIT 1
Database: store_product (tables: district)
Question: What is the name of the district with the smallest area?
SQL: SELECT district_name FROM district ORDER BY city_area ASC LIMIT 1
Database: store_product (tables: district)
Question: Find the total population of the top 3 districts with the largest area.
SQL: SELECT sum(city_population) FROM district ORDER BY city_area DESC LIMIT 3
Database: store_product (tables: district)
Question: What is the total number of residents for the districts with the 3 largest areas?
SQL: SELECT sum(city_population) FROM district ORDER BY city_area DESC LIMIT 3
Database: store_product (tables: store)
Question: Find all types of store and number of them.
SQL: SELECT TYPE ,  count(*) FROM store GROUP BY TYPE
Database: store_product (tables: store)
Question: For each type of store, how many of them are there?
SQL: SELECT TYPE ,  count(*) FROM store GROUP BY TYPE
Database: store_product (tables: store, district, store_district)
Question: Find the names of all stores in Khanewal District.
SQL: SELECT t1.store_name FROM store AS t1 JOIN store_district AS t2 ON t1.store_id  =  t2.store_id JOIN district AS t3 ON t2.district_id  =  t3.district_id WHERE t3.district_name  =  "Khanewal District"
Database: store_product (tables: store, district, store_district)
Question: What are the names of all the stores located in Khanewal District?
SQL: SELECT t1.store_name FROM store AS t1 JOIN store_district AS t2 ON t1.store_id  =  t2.store_id JOIN district AS t3 ON t2.district_id  =  t3.district_id WHERE t3.district_name  =  "Khanewal District"
Database: store_product (tables: store, district, store_district)
Question: Find all the stores in the district with the most population.
SQL: SELECT t1.store_name FROM store AS t1 JOIN store_district AS t2 ON t1.store_id  =  t2.store_id WHERE district_id  =  (SELECT district_id FROM district ORDER BY city_population DESC LIMIT 1)
Database: store_product (tables: store, district, store_district)
Question: What are the names of all the stores in the largest district by population?
SQL: SELECT t1.store_name FROM store AS t1 JOIN store_district AS t2 ON t1.store_id  =  t2.store_id WHERE district_id  =  (SELECT district_id FROM district ORDER BY city_population DESC LIMIT 1)
Database: store_product (tables: store, district, store_district)
Question: Which city is the headquarter of the store named "Blackville" in?
SQL: SELECT t3.headquartered_city FROM store AS t1 JOIN store_district AS t2 ON t1.store_id  =  t2.store_id JOIN district AS t3 ON t2.district_id  =  t3.district_id WHERE t1.store_name  =  "Blackville"
Database: store_product (tables: store, district, store_district)
Question: What city is the headquarter of the store Blackville?
SQL: SELECT t3.headquartered_city FROM store AS t1 JOIN store_district AS t2 ON t1.store_id  =  t2.store_id JOIN district AS t3 ON t2.district_id  =  t3.district_id WHERE t1.store_name  =  "Blackville"
Database: store_product (tables: store, district, store_district)
Question: Find the number of stores in each city.
SQL: SELECT t3.headquartered_city ,  count(*) FROM store AS t1 JOIN store_district AS t2 ON t1.store_id  =  t2.store_id JOIN district AS t3 ON t2.district_id  =  t3.district_id GROUP BY t3.headquartered_city
Database: store_product (tables: store, district, store_district)
Question: How many stores are headquarted in each city?
SQL: SELECT t3.headquartered_city ,  count(*) FROM store AS t1 JOIN store_district AS t2 ON t1.store_id  =  t2.store_id JOIN district AS t3 ON t2.district_id  =  t3.district_id GROUP BY t3.headquartered_city
Database: store_product (tables: store, district, store_district)
Question: Find the city with the most number of stores.
SQL: SELECT t3.headquartered_city FROM store AS t1 JOIN store_district AS t2 ON t1.store_id  =  t2.store_id JOIN district AS t3 ON t2.district_id  =  t3.district_id GROUP BY t3.headquartered_city ORDER BY count(*) DESC LIMIT 1
Database: store_product (tables: store, district, store_district)
Question: What is the city with the most number of flagship stores?
SQL: SELECT t3.headquartered_city FROM store AS t1 JOIN store_district AS t2 ON t1.store_id  =  t2.store_id JOIN district AS t3 ON t2.district_id  =  t3.district_id GROUP BY t3.headquartered_city ORDER BY count(*) DESC LIMIT 1
Database: store_product (tables: product)
Question: What is the average pages per minute color?
SQL: SELECT avg(pages_per_minute_color) FROM product
Database: store_product (tables: product)
Question: What is the average number of pages per minute color?
SQL: SELECT avg(pages_per_minute_color) FROM product
Database: store_product (tables: store, store_product, product)
Question: What products are available at store named "Miramichi"?
SQL: SELECT t1.product FROM product AS t1 JOIN store_product AS t2 ON t1.product_id  =  t2.product_id JOIN store AS t3 ON t2.store_id  =  t3.store_id WHERE t3.store_name  =  "Miramichi"
Database: store_product (tables: store, store_product, product)
Question: What products are sold at the store named Miramichi?
SQL: SELECT t1.product FROM product AS t1 JOIN store_product AS t2 ON t1.product_id  =  t2.product_id JOIN store AS t3 ON t2.store_id  =  t3.store_id WHERE t3.store_name  =  "Miramichi"
Database: store_product (tables: product)
Question: Find products with max page size as "A4" and pages per minute color smaller than 5.
SQL: SELECT product FROM product WHERE max_page_size  =  "A4" AND pages_per_minute_color  <  5
Database: store_product (tables: product)
Question: What are the products with the maximum page size A4 that also have a pages per minute color smaller than 5?
SQL: SELECT product FROM product WHERE max_page_size  =  "A4" AND pages_per_minute_color  <  5
Database: store_product (tables: product)
Question: Find products with max page size as "A4" or pages per minute color smaller than 5.
SQL: SELECT product FROM product WHERE max_page_size  =  "A4" OR pages_per_minute_color  <  5
Database: store_product (tables: product)
Question: What are the products with the maximum page size eqal to A4 or a pages per minute color less than 5?
SQL: SELECT product FROM product WHERE max_page_size  =  "A4" OR pages_per_minute_color  <  5
Database: store_product (tables: product)
Question: Find all the product whose name contains the word "Scanner".
SQL: SELECT product FROM product WHERE product LIKE "%Scanner%"
Database: store_product (tables: product)
Question: What are all of the products whose name includes the substring "Scanner"?
SQL: SELECT product FROM product WHERE product LIKE "%Scanner%"
Database: store_product (tables: product)
Question: Find the most prominent max page size among all the products.
SQL: SELECT max_page_size FROM product GROUP BY max_page_size ORDER BY count(*) DESC LIMIT 1
Database: store_product (tables: product)
Question: What is the most common maximum page size?
SQL: SELECT max_page_size FROM product GROUP BY max_page_size ORDER BY count(*) DESC LIMIT 1
Database: store_product (tables: product)
Question: Find the name of the products that are not using the most frequently-used max page size.
SQL: SELECT product FROM product WHERE product != (SELECT max_page_size FROM product GROUP BY max_page_size ORDER BY count(*) DESC LIMIT 1)
Database: store_product (tables: product)
Question: What are the names of all products that are not the most frequently-used maximum page size?
SQL: SELECT product FROM product WHERE product != (SELECT max_page_size FROM product GROUP BY max_page_size ORDER BY count(*) DESC LIMIT 1)
Database: store_product (tables: district)
Question: Find the total population of the districts where the area is bigger than the average city area.
SQL: SELECT sum(city_population) FROM district WHERE city_area  >  (SELECT avg(city_area) FROM district)
Database: store_product (tables: district)
Question: What is the total population for all the districts that have an area larger tahn the average city area?
SQL: SELECT sum(city_population) FROM district WHERE city_area  >  (SELECT avg(city_area) FROM district)
Database: store_product (tables: store, district, store_district)
Question: Find the names of districts where have both city mall and village store type stores.
SQL: SELECT t3.District_name FROM store AS t1 JOIN store_district AS t2 ON t1.store_id  =  t2.store_id JOIN district AS t3 ON t2.district_id  =  t3.district_id WHERE t1.Type  =  "City Mall" INTERSECT SELECT t3.District_name FROM store AS t1 JOIN store_district AS t2 ON t1.store_id  =  t2.store_id JOIN district AS t3 ON t2.district_id  =  t3.district_id WHERE t1.Type  =  "Village Store"
Database: store_product (tables: store, district, store_district)
Question: What are the names of the districts that have both mall and village store style shops?
SQL: SELECT t3.District_name FROM store AS t1 JOIN store_district AS t2 ON t1.store_id  =  t2.store_id JOIN district AS t3 ON t2.district_id  =  t3.district_id WHERE t1.Type  =  "City Mall" INTERSECT SELECT t3.District_name FROM store AS t1 JOIN store_district AS t2 ON t1.store_id  =  t2.store_id JOIN district AS t3 ON t2.district_id  =  t3.district_id WHERE t1.Type  =  "Village Store"
Database: soccer_2 (tables: college)
Question: What is the total enrollment number of all colleges?
SQL: SELECT sum(enr) FROM College
Database: soccer_2 (tables: college)
Question: How many students are enrolled in college?
SQL: SELECT sum(enr) FROM College
Database: soccer_2 (tables: college)
Question: What is the average enrollment number?
SQL: SELECT avg(enr) FROM College
Database: soccer_2 (tables: college)
Question: How many students, on average, does each college have enrolled?
SQL: SELECT avg(enr) FROM College
Database: soccer_2 (tables: college)
Question: How many colleges in total?
SQL: SELECT count(*) FROM College
Database: soccer_2 (tables: college)
Question: How many different colleges are there?
SQL: SELECT count(*) FROM College
Database: soccer_2 (tables: player)
Question: How many players have more than 1000 hours of training?
SQL: SELECT count(*) FROM Player WHERE HS  >  1000
Database: soccer_2 (tables: player)
Question: How many different players trained for more than 1000 hours?
SQL: SELECT count(*) FROM Player WHERE HS  >  1000
Database: soccer_2 (tables: college)
Question: How many colleges has more than 15000 students?
SQL: SELECT count(*) FROM College WHERE enr  >  15000
Database: soccer_2 (tables: college)
Question: What is the number of colleges with a student population greater than 15000?
SQL: SELECT count(*) FROM College WHERE enr  >  15000
Database: soccer_2 (tables: player)
Question: What is the average training hours of all players?
SQL: SELECT avg(HS) FROM Player
Database: soccer_2 (tables: player)
Question: How many hours do the players train on average?
SQL: SELECT avg(HS) FROM Player
Database: soccer_2 (tables: player)
Question: Find the name and training hours of players whose hours are below 1500.
SQL: SELECT pName ,  HS FROM Player WHERE HS  <  1500
Database: soccer_2 (tables: player)
Question: What are the names and number of hours spent training for each player who trains for less than 1500 hours?
SQL: SELECT pName ,  HS FROM Player WHERE HS  <  1500
Database: soccer_2 (tables: tryout)
Question: How many different colleges do attend the tryout test?
SQL: SELECT count(DISTINCT cName) FROM tryout
Database: soccer_2 (tables: tryout)
Question: How many different colleges were represented at tryouts?
SQL: SELECT count(DISTINCT cName) FROM tryout