target-benchmark/spider-queries huggingface dataset:
Database: college_1 (tables: employee, class)
Question: Find the first names of professors who are teaching more than one class.
SQL: SELECT T2.emp_fname FROM CLASS AS T1 JOIN employee AS T2 ON T1.prof_num  =  T2.emp_num GROUP BY T1.prof_num HAVING count(*)  >  1
Database: college_1 (tables: employee, class)
Question: What are the first names of all professors who teach more than one class?
SQL: SELECT T2.emp_fname FROM CLASS AS T1 JOIN employee AS T2 ON T1.prof_num  =  T2.emp_num GROUP BY T1.prof_num HAVING count(*)  >  1
Database: college_1 (tables: enroll, student)
Question: Find the first names of students who took exactly one class.
SQL: SELECT T1.stu_fname FROM student AS T1 JOIN enroll AS T2 ON T1.stu_num  =  T2.stu_num GROUP BY T2.stu_num HAVING count(*)  =  1
Database: college_1 (tables: enroll, student)
Question: What are the first names of student who only took one course?
SQL: SELECT T1.stu_fname FROM student AS T1 JOIN enroll AS T2 ON T1.stu_num  =  T2.stu_num GROUP BY T2.stu_num HAVING count(*)  =  1
Database: college_1 (tables: course, department)
Question: Find the name of department that offers the class whose description has the word "Statistics".
SQL: SELECT T2.dept_name FROM course AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code WHERE T1.crs_description LIKE '%Statistics%'
Database: college_1 (tables: course, department)
Question: What is the name of the department that offers a course that has a description including the word "Statistics"?
SQL: SELECT T2.dept_name FROM course AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code WHERE T1.crs_description LIKE '%Statistics%'
Database: college_1 (tables: enroll, student, class)
Question: What is the first name of the student whose last name starting with the letter S and is taking ACCT-211 class?
SQL: SELECT T1.stu_fname FROM student AS T1 JOIN enroll AS T2 ON T1.stu_num  =  T2.stu_num JOIN CLASS AS T3 ON T2.class_code  =  T3.class_code WHERE T3.crs_code  =  'ACCT-211' AND T1.stu_lname LIKE 'S%'
Database: college_1 (tables: enroll, student, class)
Question: What is the first name of the student whose last name starts with the letter S and is taking ACCT-211?
SQL: SELECT T1.stu_fname FROM student AS T1 JOIN enroll AS T2 ON T1.stu_num  =  T2.stu_num JOIN CLASS AS T3 ON T2.class_code  =  T3.class_code WHERE T3.crs_code  =  'ACCT-211' AND T1.stu_lname LIKE 'S%'
Database: sports_competition (tables: club)
Question: How many clubs are there?
SQL: SELECT count(*) FROM club
Database: sports_competition (tables: club)
Question: What is the total number of clubs?
SQL: SELECT count(*) FROM club
Database: sports_competition (tables: club)
Question: List the distinct region of clubs in ascending alphabetical order.
SQL: SELECT DISTINCT Region FROM club ORDER BY Region ASC
Database: sports_competition (tables: club)
Question: What are the different regions of clubs in ascending alphabetical order?
SQL: SELECT DISTINCT Region FROM club ORDER BY Region ASC
Database: sports_competition (tables: club_rank)
Question: What is the average number of gold medals for clubs?
SQL: SELECT avg(Gold) FROM club_rank
Database: sports_competition (tables: club_rank)
Question: What is the average number of gold medals for a club?
SQL: SELECT avg(Gold) FROM club_rank
Database: sports_competition (tables: competition)
Question: What are the types and countries of competitions?
SQL: SELECT Competition_type ,  Country FROM competition
Database: sports_competition (tables: competition)
Question: What are the types of every competition and in which countries are they located?
SQL: SELECT Competition_type ,  Country FROM competition
Database: sports_competition (tables: competition)
Question: What are the distinct years in which the competitions type is not "Tournament"?
SQL: SELECT DISTINCT YEAR FROM competition WHERE Competition_type != "Tournament"
Database: sports_competition (tables: competition)
Question: What are the different years for all competitions that are not of type equal to tournament?
SQL: SELECT DISTINCT YEAR FROM competition WHERE Competition_type != "Tournament"
Database: sports_competition (tables: club_rank)
Question: What are the maximum and minimum number of silver medals for clubs.
SQL: SELECT max(Silver) ,  min(Silver) FROM club_rank
Database: sports_competition (tables: club_rank)
Question: What are the maximum and minimum number of silver medals for all the clubs?
SQL: SELECT max(Silver) ,  min(Silver) FROM club_rank
Database: sports_competition (tables: club_rank)
Question: How many clubs have total medals less than 10?
SQL: SELECT count(*) FROM club_rank WHERE Total  <  10
Database: sports_competition (tables: club_rank)
Question: What is the total number of clubs that have less than 10 medals in total?
SQL: SELECT count(*) FROM club_rank WHERE Total  <  10
Database: sports_competition (tables: club)
Question: List all club names in ascending order of start year.
SQL: SELECT name FROM club ORDER BY Start_year ASC
Database: sports_competition (tables: club)
Question: What are the names of all the clubs starting with the oldest?
SQL: SELECT name FROM club ORDER BY Start_year ASC
Database: sports_competition (tables: club)
Question: List all club names in descending alphabetical order.
SQL: SELECT name FROM club ORDER BY name DESC
Database: sports_competition (tables: club)
Question: What are the names of all the clubs ordered in descending alphabetical order?
SQL: SELECT name FROM club ORDER BY name DESC
Database: sports_competition (tables: club, player)
Question: Please show the names and the players of clubs.
SQL: SELECT T1.name ,  T2.Player_id FROM club AS T1 JOIN player AS T2 ON T1.Club_ID  =  T2.Club_ID
Database: sports_competition (tables: club, player)
Question: What are the names and players of all the clubs?
SQL: SELECT T1.name ,  T2.Player_id FROM club AS T1 JOIN player AS T2 ON T1.Club_ID  =  T2.Club_ID
Database: sports_competition (tables: club, player)
Question: Show the names of clubs that have players with position "Right Wing".
SQL: SELECT T1.name FROM club AS T1 JOIN player AS T2 ON T1.Club_ID  =  T2.Club_ID WHERE T2.Position  =  "Right Wing"
Database: sports_competition (tables: club, player)
Question: What are the names of the clubs that have players in the position of "Right Wing"?
SQL: SELECT T1.name FROM club AS T1 JOIN player AS T2 ON T1.Club_ID  =  T2.Club_ID WHERE T2.Position  =  "Right Wing"
Database: sports_competition (tables: club, player)
Question: What is the average points of players from club with name "AIB".
SQL: SELECT avg(T2.Points) FROM club AS T1 JOIN player AS T2 ON T1.Club_ID  =  T2.Club_ID WHERE T1.name  =  "AIB"
Database: sports_competition (tables: club, player)
Question: What is the average number of points for players from the "AIB" club?
SQL: SELECT avg(T2.Points) FROM club AS T1 JOIN player AS T2 ON T1.Club_ID  =  T2.Club_ID WHERE T1.name  =  "AIB"
Database: sports_competition (tables: player)
Question: List the position of players and the average number of points of players of each position.
SQL: SELECT POSITION ,  avg(Points) FROM player GROUP BY POSITION
Database: sports_competition (tables: player)
Question: For each position, what is the average number of points for players in that position?
SQL: SELECT POSITION ,  avg(Points) FROM player GROUP BY POSITION
Database: sports_competition (tables: player)
Question: List the position of players with average number of points scored by players of that position bigger than 20.
SQL: SELECT POSITION FROM player GROUP BY name HAVING avg(Points)  >=  20
Database: sports_competition (tables: player)
Question: What are the positions of players whose average number of points scored by that position is larger than 20?
SQL: SELECT POSITION FROM player GROUP BY name HAVING avg(Points)  >=  20
Database: sports_competition (tables: competition)
Question: List the types of competition and the number of competitions of each type.
SQL: SELECT Competition_type ,  COUNT(*) FROM competition GROUP BY Competition_type
Database: sports_competition (tables: competition)
Question: What are the types of competition and number of competitions for that type?
SQL: SELECT Competition_type ,  COUNT(*) FROM competition GROUP BY Competition_type
Database: sports_competition (tables: competition)
Question: List the most common type of competition.
SQL: SELECT Competition_type FROM competition GROUP BY Competition_type ORDER BY COUNT(*) DESC LIMIT 1
Database: sports_competition (tables: competition)
Question: What is the most common competition type?
SQL: SELECT Competition_type FROM competition GROUP BY Competition_type ORDER BY COUNT(*) DESC LIMIT 1
Database: sports_competition (tables: competition)
Question: List the types of competition that have at most five competitions of that type.
SQL: SELECT Competition_type FROM competition GROUP BY Competition_type HAVING COUNT(*)  <=  5
Database: sports_competition (tables: competition)
Question: What are the types of competition that have most 5 competitions for that type?
SQL: SELECT Competition_type FROM competition GROUP BY Competition_type HAVING COUNT(*)  <=  5
Database: sports_competition (tables: club, player)
Question: List the names of clubs that do not have any players.
SQL: SELECT name FROM CLub WHERE Club_ID NOT IN (SELECT Club_ID FROM player)
Database: sports_competition (tables: club, player)
Question: What are the names of all clubs that do not have any players?
SQL: SELECT name FROM CLub WHERE Club_ID NOT IN (SELECT Club_ID FROM player)
Database: sports_competition (tables: player)
Question: What are the positions with both players having more than 20 points and less than 10 points.
SQL: SELECT POSITION FROM player WHERE Points  >  20 INTERSECT SELECT POSITION FROM player WHERE Points  <  10
Database: sports_competition (tables: player)
Question: What are the positions of both players that have more than 20 20 points and less than 10 points?
SQL: SELECT POSITION FROM player WHERE Points  >  20 INTERSECT SELECT POSITION FROM player WHERE Points  <  10
Database: sports_competition (tables: player)
Question: Show total points of all players.
SQL: SELECT sum(Points) FROM player
Database: sports_competition (tables: player)
Question: What is the total number of points for all players?
SQL: SELECT sum(Points) FROM player
Database: sports_competition (tables: player)
Question: how many different positions are there?
SQL: SELECT count(DISTINCT POSITION) FROM player
Database: sports_competition (tables: player)
Question: How many different position for players are listed?
SQL: SELECT count(DISTINCT POSITION) FROM player
Database: sports_competition (tables: player)
Question: what are the name of players who get more than the average points.
SQL: SELECT name FROM player WHERE points  >  (SELECT avg(points) FROM player)
Database: sports_competition (tables: player)
Question: What are the names of all players that got more than the average number of points?
SQL: SELECT name FROM player WHERE points  >  (SELECT avg(points) FROM player)
Database: sports_competition (tables: player)
Question: find the number of players whose points are lower than 30 in each position.
SQL: SELECT count(*) ,  POSITION FROM player WHERE points  <  30 GROUP BY POSITION
Database: sports_competition (tables: player)
Question: What is the number of players who have points less than 30 for each position?
SQL: SELECT count(*) ,  POSITION FROM player WHERE points  <  30 GROUP BY POSITION
Database: sports_competition (tables: competition)
Question: which country did participated in the most number of Tournament competitions?
SQL: SELECT country FROM competition WHERE competition_type  =  'Tournament' GROUP BY country ORDER BY count(*) DESC LIMIT 1
Database: sports_competition (tables: competition)
Question: what is the name of the country that participated in the most tournament competitions?
SQL: SELECT country FROM competition WHERE competition_type  =  'Tournament' GROUP BY country ORDER BY count(*) DESC LIMIT 1
Database: sports_competition (tables: competition)
Question: which countries did participated in both Friendly and Tournament type competitions.
SQL: SELECT country FROM competition WHERE competition_type  =  'Friendly' INTERSECT SELECT country FROM competition WHERE competition_type  =  'Tournament'
Database: sports_competition (tables: competition)
Question: What are the countries that participated in both friendly and tournament type competitions?
SQL: SELECT country FROM competition WHERE competition_type  =  'Friendly' INTERSECT SELECT country FROM competition WHERE competition_type  =  'Tournament'
Database: sports_competition (tables: competition)
Question: Find the countries that have never participated in any competition with Friendly type.
SQL: SELECT country FROM competition EXCEPT SELECT country FROM competition WHERE competition_type  =  'Friendly'
Database: sports_competition (tables: competition)
Question: What are the countries that have never participated in any friendly-type competitions?
SQL: SELECT country FROM competition EXCEPT SELECT country FROM competition WHERE competition_type  =  'Friendly'
Database: manufacturer (tables: furniture)
Question: How many furniture components are there in total?
SQL: SELECT sum(num_of_component) FROM furniture
Database: manufacturer (tables: furniture)
Question: Return the name and id of the furniture with the highest market rate.
SQL: SELECT name ,  furniture_id FROM furniture ORDER BY market_rate DESC LIMIT 1
Database: manufacturer (tables: furniture)
Question: find the total market rate of the furnitures that have the top 2 market shares.
SQL: SELECT sum(market_rate) FROM furniture ORDER BY market_rate DESC LIMIT 2
Database: manufacturer (tables: furniture)
Question: Find the component amounts and names of all furnitures that have more than 10 components.
SQL: SELECT Num_of_Component ,  name FROM furniture WHERE Num_of_Component  >  10
Database: manufacturer (tables: furniture)
Question: Find the name and component amount of the least popular furniture.
SQL: SELECT name ,  Num_of_Component FROM furniture ORDER BY market_rate LIMIT 1
Database: manufacturer (tables: furniture, furniture_manufacte)
Question: Find the names of furnitures whose prices are lower than the highest price.
SQL: SELECT t1.name FROM furniture AS t1 JOIN furniture_manufacte AS t2 ON t1.Furniture_ID  =  t2.Furniture_ID WHERE t2.Price_in_Dollar  <  (SELECT max(Price_in_Dollar) FROM furniture_manufacte)
Database: manufacturer (tables: manufacturer)
Question: Which manufacturer has the most number of shops? List its name and year of opening.
SQL: SELECT open_year ,  name FROM manufacturer ORDER BY num_of_shops DESC LIMIT 1
Database: manufacturer (tables: manufacturer)
Question: Find the average number of factories for the manufacturers that have more than 20 shops.
SQL: SELECT avg(Num_of_Factories) FROM manufacturer WHERE num_of_shops  >  20
Database: manufacturer (tables: manufacturer)
Question: List all manufacturer names and ids ordered by their opening year.
SQL: SELECT name ,  manufacturer_id FROM manufacturer ORDER BY open_year
Database: manufacturer (tables: manufacturer)
Question: Give me the name and year of opening of the manufacturers that have either less than 10 factories or more than 10 shops.
SQL: SELECT name ,  open_year FROM manufacturer WHERE num_of_shops  >  10 OR Num_of_Factories  <  10
Database: manufacturer (tables: manufacturer)
Question: what is the average number of factories and maximum number of shops for manufacturers that opened before 1990.
SQL: SELECT max(num_of_shops) ,  avg(Num_of_Factories) FROM manufacturer WHERE open_year  <  1990
Database: manufacturer (tables: manufacturer, furniture_manufacte)
Question: Find the id and number of shops for the company that produces the most expensive furniture.
SQL: SELECT t1.manufacturer_id ,  t1.num_of_shops FROM manufacturer AS t1 JOIN furniture_manufacte AS t2 ON t1.manufacturer_id  =  t2.manufacturer_id ORDER BY t2.Price_in_Dollar DESC LIMIT 1
Database: manufacturer (tables: manufacturer, furniture_manufacte)
Question: Find the number of funiture types produced by each manufacturer as well as the company names.
SQL: SELECT count(*) ,  t1.name FROM manufacturer AS t1 JOIN furniture_manufacte AS t2 ON t1.manufacturer_id  =  t2.manufacturer_id GROUP BY t1.manufacturer_id
Database: manufacturer (tables: furniture, furniture_manufacte)
Question: Give me the names and prices of furnitures which some companies are manufacturing.
SQL: SELECT t1.name ,  t2.price_in_dollar FROM furniture AS t1 JOIN furniture_manufacte AS t2 ON t1.Furniture_ID  =  t2.Furniture_ID
Database: manufacturer (tables: furniture, furniture_manufacte)
Question: Find the market shares and names of furnitures which no any company is producing in our records.
SQL: SELECT Market_Rate ,  name FROM furniture WHERE Furniture_ID NOT IN (SELECT Furniture_ID FROM furniture_manufacte)
Database: manufacturer (tables: furniture, furniture_manufacte, manufacturer)
Question: Find the name of the company that produces both furnitures with less than 6 components and furnitures with more than 10 components.
SQL: SELECT t3.name FROM furniture AS t1 JOIN furniture_manufacte AS t2 ON t1.Furniture_ID  =  t2.Furniture_ID JOIN manufacturer AS t3 ON t2.manufacturer_id  =  t3.manufacturer_id WHERE t1.num_of_component  <  6 INTERSECT SELECT t3.name FROM furniture AS t1 JOIN furniture_manufacte AS t2 ON t1.Furniture_ID  =  t2.Furniture_ID JOIN manufacturer AS t3 ON t2.manufacturer_id  =  t3.manufacturer_id WHERE t1.num_of_component  >  10
Database: hr_1 (tables: employees, departments)
Question: Display the first name and department name for each employee.
SQL: SELECT T1.first_name ,  T2.department_name FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id
Database: hr_1 (tables: employees, departments)
Question: What are the first name and department name of all employees?
SQL: SELECT T1.first_name ,  T2.department_name FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id
Database: hr_1 (tables: employees)
Question: List the full name (first and last name), and salary for those employees who earn below 6000.
SQL: SELECT first_name ,  last_name ,  salary FROM employees WHERE salary  <  6000
Database: hr_1 (tables: employees)
Question: What are the full names and salaries for any employees earning less than 6000?
SQL: SELECT first_name ,  last_name ,  salary FROM employees WHERE salary  <  6000
Database: hr_1 (tables: employees)
Question: Display the first name, and department number for all employees whose last name is "McEwen".
SQL: SELECT first_name ,  department_id FROM employees WHERE last_name  =  'McEwen'
Database: hr_1 (tables: employees)
Question: What are the first names and department numbers for employees with last name McEwen?
SQL: SELECT first_name ,  department_id FROM employees WHERE last_name  =  'McEwen'
Database: hr_1 (tables: employees)
Question: Return all the information for all employees without any department number.
SQL: SELECT * FROM employees WHERE department_id  =  "null"
Database: hr_1 (tables: employees)
Question: What are all the employees without a department number?
SQL: SELECT * FROM employees WHERE department_id  =  "null"
Database: hr_1 (tables: departments)
Question: Display all the information about the department Marketing.
SQL: SELECT * FROM  departments WHERE department_name  =  'Marketing'
Database: hr_1 (tables: departments)
Question: What is all the information about the Marketing department?
SQL: SELECT * FROM  departments WHERE department_name  =  'Marketing'
Database: hr_1 (tables: employees)
Question: when is the hire date for those employees whose first name does not containing the letter M?
SQL: SELECT hire_date FROM employees WHERE first_name NOT LIKE '%M%'
Database: hr_1 (tables: employees)
Question: On what dates were employees without the letter M in their first names hired?
SQL: SELECT hire_date FROM employees WHERE first_name NOT LIKE '%M%'
Database: hr_1 (tables: employees)
Question: display the full name (first and last), hire date, salary, and department number for those employees whose first name does not containing the letter M.
SQL: SELECT first_name ,  last_name ,  hire_date ,  salary ,  department_id FROM employees WHERE first_name NOT LIKE '%M%'
Database: hr_1 (tables: employees)
Question: What are the full name, hire date, salary, and department id for employees without the letter M in their first name?
SQL: SELECT first_name ,  last_name ,  hire_date ,  salary ,  department_id FROM employees WHERE first_name NOT LIKE '%M%'
Database: hr_1 (tables: employees)
Question: display the full name (first and last), hire date, salary, and department number for those employees whose first name does not containing the letter M and make the result set in ascending order by department number.
SQL: SELECT first_name ,  last_name ,  hire_date ,  salary ,  department_id FROM employees WHERE first_name NOT LIKE '%M%' ORDER BY department_id
Database: hr_1 (tables: employees)
Question: What are the full name, hire data, salary and department id for employees without the letter M in their first name, ordered by ascending department id?
SQL: SELECT first_name ,  last_name ,  hire_date ,  salary ,  department_id FROM employees WHERE first_name NOT LIKE '%M%' ORDER BY department_id
Database: hr_1 (tables: employees)
Question: what is the phone number of employees whose salary is in the range of 8000 and 12000?
SQL: SELECT phone_number FROM employees WHERE salary BETWEEN 8000 AND 12000
Database: hr_1 (tables: employees)
Question: Return the phone numbers of employees with salaries between 8000 and 12000.
SQL: SELECT phone_number FROM employees WHERE salary BETWEEN 8000 AND 12000
Database: hr_1 (tables: employees)
Question: display all the information of employees whose salary is in the range of 8000 and 12000 and commission is not null or department number does not equal to 40.
SQL: SELECT * FROM employees WHERE salary BETWEEN 8000 AND 12000 AND commission_pct != "null" OR  department_id != 40
Database: hr_1 (tables: employees)
Question: Return all information about employees with salaries between 8000 and 12000 for which commission is not null or where their department id is not 40.
SQL: SELECT * FROM employees WHERE salary BETWEEN 8000 AND 12000 AND commission_pct != "null" OR  department_id != 40
Database: hr_1 (tables: employees)
Question: What are the full name (first and last name) and salary for all employees who does not have any value for commission?
SQL: SELECT first_name ,  last_name ,  salary FROM  employees WHERE commission_pct  =  "null"
Database: hr_1 (tables: employees)
Question: Return the full names and salaries of employees with null commissions.
SQL: SELECT first_name ,  last_name ,  salary FROM  employees WHERE commission_pct  =  "null"
Database: hr_1 (tables: employees)
Question: Display the first and last name, and salary for those employees whose first name is ending with the letter m.
SQL: SELECT first_name ,  last_name ,  salary FROM employees WHERE first_name LIKE '%m'
Database: hr_1 (tables: employees)
Question: Return the full names and salaries for employees with first names that end with the letter m.
SQL: SELECT first_name ,  last_name ,  salary FROM employees WHERE first_name LIKE '%m'
Database: hr_1 (tables: employees)
Question: Find job id and date of hire for those employees who was hired between November 5th, 2007 and July 5th, 2009.
SQL: SELECT job_id ,  hire_date FROM employees WHERE hire_date BETWEEN '2007-11-05' AND '2009-07-05'
Database: hr_1 (tables: employees)
Question: What are the job ids and dates of hire for employees hired after November 5th, 2007 and before July 5th, 2009?
SQL: SELECT job_id ,  hire_date FROM employees WHERE hire_date BETWEEN '2007-11-05' AND '2009-07-05'
Database: hr_1 (tables: employees)
Question: What are the first and last name for those employees who works either in department 70 or 90?
SQL: SELECT first_name ,  last_name FROM employees WHERE department_id  =  70 OR department_id  =  90
Database: hr_1 (tables: employees)
Question: What are the full names of employees who with in department 70 or 90?
SQL: SELECT first_name ,  last_name FROM employees WHERE department_id  =  70 OR department_id  =  90