target-benchmark/spider-queries huggingface dataset:
Database: tracking_software_problems (tables: problem_log, staff)
Question: List the problem id and log id which are assigned to the staff named Rylan Homenick.
SQL: SELECT DISTINCT T2.problem_id ,  T2.problem_log_id FROM staff AS T1 JOIN problem_log AS T2 ON T1.staff_id = T2.assigned_to_staff_id WHERE T1.staff_first_name = "Rylan" AND T1.staff_last_name = "Homenick"
Database: tracking_software_problems (tables: problem_log, staff)
Question: Which problem id and log id are assigned to the staff named Rylan Homenick?
SQL: SELECT DISTINCT T2.problem_id ,  T2.problem_log_id FROM staff AS T1 JOIN problem_log AS T2 ON T1.staff_id = T2.assigned_to_staff_id WHERE T1.staff_first_name = "Rylan" AND T1.staff_last_name = "Homenick"
Database: tracking_software_problems (tables: problems, product)
Question: How many problems are there for product voluptatem?
SQL: SELECT count(*) FROM product AS T1 JOIN problems AS T2 ON T1.product_id = T2.product_id WHERE T1.product_name = "voluptatem"
Database: tracking_software_problems (tables: problems, product)
Question: How many problems did the product called "voluptatem" have in record?
SQL: SELECT count(*) FROM product AS T1 JOIN problems AS T2 ON T1.product_id = T2.product_id WHERE T1.product_name = "voluptatem"
Database: tracking_software_problems (tables: problems, product)
Question: How many problems does the product with the most problems have? List the number of the problems and product name.
SQL: SELECT count(*) ,  T1.product_name FROM product AS T1 JOIN problems AS T2 ON T1.product_id = T2.product_id GROUP BY T1.product_name ORDER BY count(*) DESC LIMIT 1
Database: tracking_software_problems (tables: problems, product)
Question: Which product has the most problems? Give me the number of problems and the product name.
SQL: SELECT count(*) ,  T1.product_name FROM product AS T1 JOIN problems AS T2 ON T1.product_id = T2.product_id GROUP BY T1.product_name ORDER BY count(*) DESC LIMIT 1
Database: tracking_software_problems (tables: problems, staff)
Question: Give me a list of descriptions of the problems that are reported by the staff whose first name is Christop.
SQL: SELECT T1.problem_description FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Christop"
Database: tracking_software_problems (tables: problems, staff)
Question: Which problems are reported by the staff with first name "Christop"? Show the descriptions of the problems.
SQL: SELECT T1.problem_description FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Christop"
Database: tracking_software_problems (tables: problems, staff)
Question: Find the ids of the problems that are reported by the staff whose last name is Bosco.
SQL: SELECT T1.problem_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_last_name = "Bosco"
Database: tracking_software_problems (tables: problems, staff)
Question: Which problems are reported by the staff with last name "Bosco"? Show the ids of the problems.
SQL: SELECT T1.problem_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_last_name = "Bosco"
Database: tracking_software_problems (tables: problems)
Question: What are the ids of the problems which are reported after 1978-06-26?
SQL: SELECT problem_id FROM problems WHERE date_problem_reported > "1978-06-26"
Database: tracking_software_problems (tables: problems)
Question: Find the ids of the problems reported after 1978-06-26.
SQL: SELECT problem_id FROM problems WHERE date_problem_reported > "1978-06-26"
Database: tracking_software_problems (tables: problems)
Question: What are the ids of the problems which are reported before 1978-06-26?
SQL: SELECT problem_id FROM problems WHERE date_problem_reported < "1978-06-26"
Database: tracking_software_problems (tables: problems)
Question: Which problems are reported before 1978-06-26? Give me the ids of the problems.
SQL: SELECT problem_id FROM problems WHERE date_problem_reported < "1978-06-26"
Database: tracking_software_problems (tables: product, problems)
Question: For each product which has problems, what are the number of problems and the product id?
SQL: SELECT count(*) ,  T2.product_id FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id GROUP BY T2.product_id
Database: tracking_software_problems (tables: product, problems)
Question: For each product with some problems, list the count of problems and the product id.
SQL: SELECT count(*) ,  T2.product_id FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id GROUP BY T2.product_id
Database: tracking_software_problems (tables: product, problems)
Question: For each product that has problems, find the number of problems reported after 1986-11-13 and the product id?
SQL: SELECT count(*) ,  T2.product_id FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id WHERE T1.date_problem_reported > "1986-11-13" GROUP BY T2.product_id
Database: tracking_software_problems (tables: product, problems)
Question: What are the products that have problems reported after 1986-11-13? Give me the product id and the count of problems reported after 1986-11-13.
SQL: SELECT count(*) ,  T2.product_id FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id WHERE T1.date_problem_reported > "1986-11-13" GROUP BY T2.product_id
Database: tracking_software_problems (tables: product)
Question: List the names of all the distinct product names in alphabetical order?
SQL: SELECT DISTINCT product_name FROM product ORDER BY product_name
Database: tracking_software_problems (tables: product)
Question: Sort all the distinct product names in alphabetical order.
SQL: SELECT DISTINCT product_name FROM product ORDER BY product_name
Database: tracking_software_problems (tables: product)
Question: List all the distinct product names ordered by product id?
SQL: SELECT DISTINCT product_name FROM product ORDER BY product_id
Database: tracking_software_problems (tables: product)
Question: What is the list of distinct product names sorted by product id?
SQL: SELECT DISTINCT product_name FROM product ORDER BY product_id
Database: tracking_software_problems (tables: problems, staff)
Question: What are the id of problems reported by the staff named Dameon Frami or Jolie Weber?
SQL: SELECT product_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Dameon" AND T2.staff_last_name = "Frami" UNION SELECT product_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Jolie" AND T2.staff_last_name = "Weber"
Database: tracking_software_problems (tables: problems, staff)
Question: Which problems were reported by the staff named Dameon Frami or Jolie Weber? Give me the ids of the problems.
SQL: SELECT product_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Dameon" AND T2.staff_last_name = "Frami" UNION SELECT product_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Jolie" AND T2.staff_last_name = "Weber"
Database: tracking_software_problems (tables: problems, staff)
Question: What are the product ids for the problems reported by Christop Berge with closure authorised by Ashley Medhurst?
SQL: SELECT product_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Christop" AND T2.staff_last_name = "Berge" INTERSECT SELECT product_id FROM problems AS T1 JOIN staff AS T2 ON T1.closure_authorised_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Ashley" AND T2.staff_last_name = "Medhurst"
Database: tracking_software_problems (tables: problems, staff)
Question: For which product was there a problem reported by Christop Berge, with closure authorised by Ashley Medhurst? Return the product ids.
SQL: SELECT product_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Christop" AND T2.staff_last_name = "Berge" INTERSECT SELECT product_id FROM problems AS T1 JOIN staff AS T2 ON T1.closure_authorised_by_staff_id = T2.staff_id WHERE T2.staff_first_name = "Ashley" AND T2.staff_last_name = "Medhurst"
Database: tracking_software_problems (tables: problems, staff)
Question: What are the ids of the problems reported before the date of any problem reported by Lysanne Turcotte?
SQL: SELECT T1.problem_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE date_problem_reported  <  ( SELECT min(date_problem_reported) FROM problems AS T3 JOIN staff AS T4 ON T3.reported_by_staff_id = T4.staff_id WHERE T4.staff_first_name = "Lysanne" AND T4.staff_last_name = "Turcotte" )
Database: tracking_software_problems (tables: problems, staff)
Question: Which problems were reported before the date of any problem reported by the staff Lysanne Turcotte? Give me the ids of the problems.
SQL: SELECT T1.problem_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE date_problem_reported  <  ( SELECT min(date_problem_reported) FROM problems AS T3 JOIN staff AS T4 ON T3.reported_by_staff_id = T4.staff_id WHERE T4.staff_first_name = "Lysanne" AND T4.staff_last_name = "Turcotte" )
Database: tracking_software_problems (tables: problems, staff)
Question: What are the ids of the problems reported after the date of any problems reported by Rylan Homenick?
SQL: SELECT T1.problem_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE date_problem_reported  >  ( SELECT max(date_problem_reported) FROM problems AS T3 JOIN staff AS T4 ON T3.reported_by_staff_id = T4.staff_id WHERE T4.staff_first_name = "Rylan" AND T4.staff_last_name = "Homenick" )
Database: tracking_software_problems (tables: problems, staff)
Question: Find the ids of the problems reported after the date of any problems reported by the staff Rylan Homenick.
SQL: SELECT T1.problem_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE date_problem_reported  >  ( SELECT max(date_problem_reported) FROM problems AS T3 JOIN staff AS T4 ON T3.reported_by_staff_id = T4.staff_id WHERE T4.staff_first_name = "Rylan" AND T4.staff_last_name = "Homenick" )
Database: tracking_software_problems (tables: product, problems)
Question: Find the top 3 products which have the largest number of problems?
SQL: SELECT T2.product_name FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id GROUP BY T2.product_name ORDER BY count(*) DESC LIMIT 3
Database: tracking_software_problems (tables: product, problems)
Question: What are the three products that have the most problems?s
SQL: SELECT T2.product_name FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id GROUP BY T2.product_name ORDER BY count(*) DESC LIMIT 3
Database: tracking_software_problems (tables: product, problems)
Question: List the ids of the problems from the product "voluptatem" that are reported after 1995?
SQL: SELECT T1.problem_id FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id WHERE T2.product_name = "voluptatem" AND T1.date_problem_reported > "1995"
Database: tracking_software_problems (tables: product, problems)
Question: What are the ids of the problems that are from the product "voluptatem" and are reported after 1995?
SQL: SELECT T1.problem_id FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id WHERE T2.product_name = "voluptatem" AND T1.date_problem_reported > "1995"
Database: tracking_software_problems (tables: product, problems, staff)
Question: Find the first and last name of the staff members who reported problems from the product "rem" but not "aut"?
SQL: SELECT T3.staff_first_name ,  T3.staff_last_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T2.product_name = "rem" EXCEPT SELECT T3.staff_first_name ,  T3.staff_last_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T2.product_name = "aut"
Database: tracking_software_problems (tables: product, problems, staff)
Question: Which staff members who reported problems from the product "rem" but not "aut"? Give me their first and last names.
SQL: SELECT T3.staff_first_name ,  T3.staff_last_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T2.product_name = "rem" EXCEPT SELECT T3.staff_first_name ,  T3.staff_last_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T2.product_name = "aut"
Database: tracking_software_problems (tables: product, problems, staff)
Question: Find the products which have problems reported by both Lacey Bosco and Kenton Champlin?
SQL: SELECT T2.product_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T3.staff_first_name = "Lacey" AND T3.staff_last_name = "Bosco" INTERSECT SELECT T2.product_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T3.staff_first_name = "Kenton" AND T3.staff_last_name = "Champlin"
Database: tracking_software_problems (tables: product, problems, staff)
Question: Which products have problems reported by both the staff named Lacey Bosco and the staff named Kenton Champlin?
SQL: SELECT T2.product_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T3.staff_first_name = "Lacey" AND T3.staff_last_name = "Bosco" INTERSECT SELECT T2.product_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T3.staff_first_name = "Kenton" AND T3.staff_last_name = "Champlin"
Database: shop_membership (tables: branch)
Question: How many branches where have more than average number of memberships are there?
SQL: SELECT count(*) FROM branch WHERE membership_amount  >  (SELECT avg(membership_amount) FROM branch)
Database: shop_membership (tables: branch)
Question: What is the number of branches that have more than the average number of memberships?
SQL: SELECT count(*) FROM branch WHERE membership_amount  >  (SELECT avg(membership_amount) FROM branch)
Database: shop_membership (tables: branch)
Question: Show name, address road, and city for all branches sorted by open year.
SQL: SELECT name ,  address_road ,  city FROM branch ORDER BY open_year
Database: shop_membership (tables: branch)
Question: What are the names, address roads, and cities of the branches ordered by opening year?
SQL: SELECT name ,  address_road ,  city FROM branch ORDER BY open_year
Database: shop_membership (tables: branch)
Question: What are names for top three branches with most number of membership?
SQL: SELECT name FROM branch ORDER BY membership_amount DESC LIMIT 3
Database: shop_membership (tables: branch)
Question: What are the names for the 3 branches that have the most memberships?
SQL: SELECT name FROM branch ORDER BY membership_amount DESC LIMIT 3
Database: shop_membership (tables: branch)
Question: Show all distinct city where branches with at least 100 memberships are located.
SQL: SELECT DISTINCT city FROM branch WHERE membership_amount  >=  100
Database: shop_membership (tables: branch)
Question: What are the different cities that have more than 100 memberships?
SQL: SELECT DISTINCT city FROM branch WHERE membership_amount  >=  100
Database: shop_membership (tables: branch)
Question: List all open years when at least two shops are opened.
SQL: SELECT open_year FROM branch GROUP BY open_year HAVING count(*)  >=  2
Database: shop_membership (tables: branch)
Question: What are the opening years in which at least two shops opened?
SQL: SELECT open_year FROM branch GROUP BY open_year HAVING count(*)  >=  2
Database: shop_membership (tables: branch)
Question: Show minimum and maximum amount of memberships for all branches opened in 2011 or located at city London.
SQL: SELECT min(membership_amount) ,  max(membership_amount) FROM branch WHERE open_year  =  2011 OR city  =  'London'
Database: shop_membership (tables: branch)
Question: What are the minimum and maximum membership amounts for all branches that either opened in 2011 or are located in London?
SQL: SELECT min(membership_amount) ,  max(membership_amount) FROM branch WHERE open_year  =  2011 OR city  =  'London'
Database: shop_membership (tables: branch)
Question: Show the city and the number of branches opened before 2010 for each city.
SQL: SELECT city ,  count(*) FROM branch WHERE open_year  <  2010 GROUP BY city
Database: shop_membership (tables: branch)
Question: For each city, how many branches opened before 2010?
SQL: SELECT city ,  count(*) FROM branch WHERE open_year  <  2010 GROUP BY city
Database: shop_membership (tables: member)
Question: How many different levels do members have?
SQL: SELECT count(DISTINCT LEVEL) FROM member
Database: shop_membership (tables: member)
Question: What are the different membership levels?
SQL: SELECT count(DISTINCT LEVEL) FROM member
Database: shop_membership (tables: member)
Question: Show card number, name, and hometown for all members in a descending order of level.
SQL: SELECT card_number ,  name ,  hometown FROM member ORDER BY LEVEL DESC
Database: shop_membership (tables: member)
Question: What are the card numbers, names, and hometowns of every member ordered by descending level?
SQL: SELECT card_number ,  name ,  hometown FROM member ORDER BY LEVEL DESC
Database: shop_membership (tables: member)
Question: Show the membership level with most number of members.
SQL: SELECT LEVEL FROM member GROUP BY LEVEL ORDER BY count(*) DESC LIMIT 1
Database: shop_membership (tables: member)
Question: What is the membership level with the most people?
SQL: SELECT LEVEL FROM member GROUP BY LEVEL ORDER BY count(*) DESC LIMIT 1
Database: shop_membership (tables: membership_register_branch, member, branch)
Question: Show all member names and registered branch names sorted by register year.
SQL: SELECT T3.name ,  T2.name FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id JOIN member AS T3 ON T1.member_id  =  T3.member_id ORDER BY T1.register_year
Database: shop_membership (tables: membership_register_branch, member, branch)
Question: What are the names of the members and branches at which they are registered sorted by year of registration?
SQL: SELECT T3.name ,  T2.name FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id JOIN member AS T3 ON T1.member_id  =  T3.member_id ORDER BY T1.register_year
Database: shop_membership (tables: membership_register_branch, branch)
Question: Show all branch names with the number of members in each branch registered after 2015.
SQL: SELECT T2.name ,  count(*) FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id WHERE T1.register_year  >  2015 GROUP BY T2.branch_id
Database: shop_membership (tables: membership_register_branch, branch)
Question: For each branch id, what are the names of the branches that were registered after 2015?
SQL: SELECT T2.name ,  count(*) FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id WHERE T1.register_year  >  2015 GROUP BY T2.branch_id
Database: shop_membership (tables: membership_register_branch, member)
Question: Show member names without any registered branch.
SQL: SELECT name FROM member WHERE member_id NOT IN (SELECT member_id FROM membership_register_branch)
Database: shop_membership (tables: membership_register_branch, member)
Question: What are the names of the members that have never registered at any branch?
SQL: SELECT name FROM member WHERE member_id NOT IN (SELECT member_id FROM membership_register_branch)
Database: shop_membership (tables: membership_register_branch, branch)
Question: List the branch name and city without any registered members.
SQL: SELECT name ,  city FROM branch WHERE branch_id NOT IN (SELECT branch_id FROM membership_register_branch)
Database: shop_membership (tables: membership_register_branch, branch)
Question: What are the names and cities of the branches that do not have any registered members?
SQL: SELECT name ,  city FROM branch WHERE branch_id NOT IN (SELECT branch_id FROM membership_register_branch)
Database: shop_membership (tables: membership_register_branch, branch)
Question: What is the name and open year for the branch with most number of memberships registered in 2016?
SQL: SELECT T2.name ,  T2.open_year FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id WHERE T1.register_year  =  2016 GROUP BY T2.branch_id ORDER BY count(*) DESC LIMIT 1
Database: shop_membership (tables: membership_register_branch, branch)
Question: What is the name and opening year for the branch that registered the most members in 2016?
SQL: SELECT T2.name ,  T2.open_year FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id WHERE T1.register_year  =  2016 GROUP BY T2.branch_id ORDER BY count(*) DESC LIMIT 1
Database: shop_membership (tables: membership_register_branch, member)
Question: Show the member name and hometown who registered a branch in 2016.
SQL: SELECT T2.name ,  T2.hometown FROM membership_register_branch AS T1 JOIN member AS T2 ON T1.member_id  =  T2.member_id WHERE T1.register_year  =  2016
Database: shop_membership (tables: membership_register_branch, member)
Question: What are the member names and hometowns of those who registered at a branch in 2016?
SQL: SELECT T2.name ,  T2.hometown FROM membership_register_branch AS T1 JOIN member AS T2 ON T1.member_id  =  T2.member_id WHERE T1.register_year  =  2016
Database: shop_membership (tables: branch)
Question: Show all city with a branch opened in 2001 and a branch with more than 100 membership.
SQL: SELECT city FROM branch WHERE open_year  =  2001 AND membership_amount  >  100
Database: shop_membership (tables: branch)
Question: What are the cities that have a branch that opened in 2001 and a branch with more than 100 members?
SQL: SELECT city FROM branch WHERE open_year  =  2001 AND membership_amount  >  100
Database: shop_membership (tables: branch)
Question: Show all cities without a branch having more than 100 memberships.
SQL: SELECT city FROM branch EXCEPT SELECT city FROM branch WHERE membership_amount  >  100
Database: shop_membership (tables: branch)
Question: What are the cities that do not have any branches with more than 100 members?
SQL: SELECT city FROM branch EXCEPT SELECT city FROM branch WHERE membership_amount  >  100
Database: shop_membership (tables: purchase, branch)
Question: What is the sum of total pounds of purchase in year 2018 for all branches in London?
SQL: SELECT sum(total_pounds) FROM purchase AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id WHERE T2.city  =  'London' AND T1.year  =  2018
Database: shop_membership (tables: purchase, branch)
Question: How many total pounds were purchased in the year 2018 at all London branches?
SQL: SELECT sum(total_pounds) FROM purchase AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id WHERE T2.city  =  'London' AND T1.year  =  2018
Database: shop_membership (tables: purchase, member)
Question: What is the total number of purchases for members with level 6?
SQL: SELECT count(*) FROM purchase AS T1 JOIN member AS T2 ON T1.member_id  =  T2.member_id WHERE T2.level  =  6
Database: shop_membership (tables: purchase, member)
Question: What are the total purchases for members rated at level 6?
SQL: SELECT count(*) FROM purchase AS T1 JOIN member AS T2 ON T1.member_id  =  T2.member_id WHERE T2.level  =  6