SuperMax991/spider-text2sql huggingface dataset:
Database: performance_attendance
Schema: member: Member_ID (text), Name (text), Nationality (text), Role (text) | performance: Performance_ID (number), Date (text), Host (text), Location (text), Attendance (number) | member_attendance: Member_ID (number), Performance_ID (number), Num_of_Pieces (number)

Question: How many performances are there?
SQL: SELECT count(*) FROM performance

Question: List the hosts of performances in ascending order of attendance.
SQL: SELECT HOST FROM performance ORDER BY Attendance ASC

Question: What are the dates and locations of performances?
SQL: SELECT Date ,  LOCATION FROM performance

Question: Show the attendances of the performances at location "TD Garden" or "Bell Centre"
SQL: SELECT Attendance FROM performance WHERE LOCATION  =  "TD Garden" OR LOCATION  =  "Bell Centre"

Question: What is the average number of attendees for performances?
SQL: SELECT avg(Attendance) FROM performance

Question: What is the date of the performance with the highest number of attendees?
SQL: SELECT Date FROM performance ORDER BY Attendance DESC LIMIT 1

Question: Show different locations and the number of performances at each location.
SQL: SELECT LOCATION ,  COUNT(*) FROM performance GROUP BY LOCATION

Question: Show the most common location of performances.
SQL: SELECT LOCATION FROM performance GROUP BY LOCATION ORDER BY COUNT(*) DESC LIMIT 1

Question: Show the locations that have at least two performances.
SQL: SELECT LOCATION FROM performance GROUP BY LOCATION HAVING COUNT(*)  >=  2

Question: Show the locations that have both performances with more than 2000 attendees and performances with less than 1000 attendees.
SQL: SELECT LOCATION FROM performance WHERE Attendance  >  2000 INTERSECT SELECT LOCATION FROM performance WHERE Attendance  <  1000

Question: Show the names of members and the location of the performances they attended.
SQL: SELECT T2.Name ,  T3.Location FROM member_attendance AS T1 JOIN member AS T2 ON T1.Member_ID  =  T2.Member_ID JOIN performance AS T3 ON T1.Performance_ID  =  T3.Performance_ID

Question: Show the names of members and the location of performances they attended in ascending alphabetical order of their names.
SQL: SELECT T2.Name ,  T3.Location FROM member_attendance AS T1 JOIN member AS T2 ON T1.Member_ID  =  T2.Member_ID JOIN performance AS T3 ON T1.Performance_ID  =  T3.Performance_ID ORDER BY T2.Name ASC

Question: Show the dates of performances with attending members whose roles are "Violin".
SQL: SELECT T3.Date FROM member_attendance AS T1 JOIN member AS T2 ON T1.Member_ID  =  T2.Member_ID JOIN performance AS T3 ON T1.Performance_ID  =  T3.Performance_ID WHERE T2.Role  =  "Violin"

Question: Show the names of members and the dates of performances they attended in descending order of attendance of the performances.
SQL: SELECT T2.Name ,  T3.Date FROM member_attendance AS T1 JOIN member AS T2 ON T1.Member_ID  =  T2.Member_ID JOIN performance AS T3 ON T1.Performance_ID  =  T3.Performance_ID ORDER BY T3.Attendance DESC

Question: List the names of members who did not attend any performance.
SQL: SELECT Name FROM member WHERE Member_ID NOT IN (SELECT Member_ID FROM member_attendance)
Database: debate
Schema: people: People_ID (number), District (text), Name (text), Party (text), Age (number) | debate: Debate_ID (number), Date (text), Venue (text), Num_of_Audience (number) | debate_people: Debate_ID (number), Affirmative (number), Negative (number), If_Affirmative_Win (others)

Question: How many debates are there?
SQL: SELECT count(*) FROM debate

Question: List the venues of debates in ascending order of the number of audience.
SQL: SELECT Venue FROM debate ORDER BY Num_of_Audience ASC

Question: What are the date and venue of each debate?
SQL: SELECT Date ,  Venue FROM debate

Question: List the dates of debates with number of audience bigger than 150
SQL: SELECT Date FROM debate WHERE Num_of_Audience  >  150

Question: Show the names of people aged either 35 or 36.
SQL: SELECT Name FROM  people WHERE Age  =  35 OR Age  =  36

Question: What is the party of the youngest people?
SQL: SELECT Party FROM people ORDER BY Age ASC LIMIT 1

Question: Show different parties of people along with the number of people in each party.
SQL: SELECT Party ,  COUNT(*) FROM people GROUP BY Party

Question: Show the party that has the most people.
SQL: SELECT Party FROM people GROUP BY Party ORDER BY COUNT(*) DESC LIMIT 1

Question: Show the distinct venues of debates
SQL: SELECT DISTINCT Venue FROM debate

Question: Show the names of people, and dates and venues of debates they are on the affirmative side.
SQL: SELECT T3.Name ,  T2.Date ,  T2.Venue FROM debate_people AS T1 JOIN debate AS T2 ON T1.Debate_ID  =  T2.Debate_ID JOIN people AS T3 ON T1.Affirmative  =  T3.People_ID

Question: Show the names of people, and dates and venues of debates they are on the negative side, ordered in ascending alphabetical order of name.
SQL: SELECT T3.Name ,  T2.Date ,  T2.Venue FROM debate_people AS T1 JOIN debate AS T2 ON T1.Debate_ID  =  T2.Debate_ID JOIN people AS T3 ON T1.Negative  =  T3.People_ID ORDER BY T3.Name ASC

Question: Show the names of people that are on affirmative side of debates with number of audience bigger than 200.
SQL: SELECT T3.Name FROM debate_people AS T1 JOIN debate AS T2 ON T1.Debate_ID  =  T2.Debate_ID JOIN people AS T3 ON T1.Affirmative  =  T3.People_ID WHERE T2.Num_of_Audience  >  200

Question: Show the names of people and the number of times they have been on the affirmative side of debates.
SQL: SELECT T2.Name ,  COUNT(*) FROM debate_people AS T1 JOIN people AS T2 ON T1.Affirmative  =  T2.People_ID GROUP BY T2.Name

Question: Show the names of people who have been on the negative side of debates at least twice.
SQL: SELECT T2.Name FROM debate_people AS T1 JOIN people AS T2 ON T1.Negative  =  T2.People_ID GROUP BY T2.Name HAVING COUNT(*)  >=  2

Question: List the names of people that have not been on the affirmative side of debates.
SQL: SELECT Name FROM people WHERE People_id NOT IN (SELECT Affirmative FROM debate_people)
Database: phone_market
Schema: phone: Name (text), Phone_ID (number), Memory_in_G (number), Carrier (text), Price (number) | market: Market_ID (number), District (text), Num_of_employees (number), Num_of_shops (number), Ranking (number) | phone_market: Market_ID (number), Phone_ID (text), Num_of_stock (number)

Question: How many phones are there?
SQL: SELECT count(*) FROM phone

Question: List the names of phones in ascending order of price.
SQL: SELECT Name FROM phone ORDER BY Price ASC

Question: What are the memories and carriers of phones?
SQL: SELECT Memory_in_G ,  Carrier FROM phone

Question: List the distinct carriers of phones with memories bigger than 32.
SQL: SELECT DISTINCT Carrier FROM phone WHERE Memory_in_G  >  32

Question: Show the names of phones with carrier either "Sprint" or "TMobile".
SQL: SELECT Name FROM phone WHERE Carrier  =  "Sprint" OR Carrier  =  "TMobile"

Question: What is the carrier of the most expensive phone?
SQL: SELECT Carrier FROM phone ORDER BY Price DESC LIMIT 1

Question: Show different carriers of phones together with the number of phones with each carrier.
SQL: SELECT Carrier ,  COUNT(*) FROM phone GROUP BY Carrier

Question: Show the most frequently used carrier of the phones.
SQL: SELECT Carrier FROM phone GROUP BY Carrier ORDER BY COUNT(*) DESC LIMIT 1

Question: Show the carriers that have both phones with memory smaller than 32 and phones with memory bigger than 64.
SQL: SELECT Carrier FROM phone WHERE Memory_in_G  <  32 INTERSECT SELECT Carrier FROM phone WHERE Memory_in_G  >  64

Question: Show the names of phones and the districts of markets they are on.
SQL: SELECT T3.Name ,  T2.District FROM phone_market AS T1 JOIN market AS T2 ON T1.Market_ID  =  T2.Market_ID JOIN phone AS T3 ON T1.Phone_ID  =  T3.Phone_ID

Question: Show the names of phones and the districts of markets they are on, in ascending order of the ranking of the market.
SQL: SELECT T3.Name ,  T2.District FROM phone_market AS T1 JOIN market AS T2 ON T1.Market_ID  =  T2.Market_ID JOIN phone AS T3 ON T1.Phone_ID  =  T3.Phone_ID ORDER BY T2.Ranking

Question: Show the names of phones that are on market with number of shops greater than 50.
SQL: SELECT T3.Name FROM phone_market AS T1 JOIN market AS T2 ON T1.Market_ID  =  T2.Market_ID JOIN phone AS T3 ON T1.Phone_ID  =  T3.Phone_ID WHERE T2.Num_of_shops  >  50

Question: For each phone, show its names and total number of stocks.
SQL: SELECT T2.Name ,  sum(T1.Num_of_stock) FROM phone_market AS T1 JOIN phone AS T2 ON T1.Phone_ID  =  T2.Phone_ID GROUP BY T2.Name

Question: Show the names of phones that have total number of stocks bigger than 2000, in descending order of the total number of stocks.
SQL: SELECT T2.Name FROM phone_market AS T1 JOIN phone AS T2 ON T1.Phone_ID  =  T2.Phone_ID GROUP BY T2.Name HAVING sum(T1.Num_of_stock)  >=  2000 ORDER BY sum(T1.Num_of_stock) DESC

Question: List the names of phones that are not on any market.
SQL: SELECT Name FROM phone WHERE Phone_id NOT IN (SELECT Phone_ID FROM phone_market)
Database: pilot_record
Schema: aircraft: Aircraft_ID (number), Order_Year (number), Manufacturer (text), Model (text), Fleet_Series (text), Powertrain (text), Fuel_Propulsion (text) | pilot: Pilot_ID (number), Pilot_name (text), Rank (number), Age (number), Nationality (text), Position (text), Join_Year (number), Team (text) | pilot_record: Record_ID (number), Pilot_ID (number), Aircraft_ID (number), Date (text)

Question: How many pilots are there?
SQL: SELECT count(*) FROM pilot

Question: List the names of pilots in ascending order of rank.
SQL: SELECT Pilot_name FROM pilot ORDER BY Rank ASC

Question: What are the positions and teams of pilots?
SQL: SELECT POSITION ,  Team FROM pilot

Question: List the distinct positions of pilots older than 30.
SQL: SELECT DISTINCT POSITION FROM pilot WHERE Age  >  30

Question: Show the names of pilots from team "Bradley" or "Fordham".
SQL: SELECT Pilot_name FROM pilot WHERE Team  =  "Bradley" OR Team  =  "Fordham"

Question: What is the joined year of the pilot of the highest rank?
SQL: SELECT Join_Year FROM pilot ORDER BY Rank ASC LIMIT 1

Question: What are the different nationalities of pilots? Show each nationality and the number of pilots of each nationality.
SQL: SELECT Nationality ,  COUNT(*) FROM pilot GROUP BY Nationality

Question: Show the most common nationality of pilots.
SQL: SELECT Nationality FROM pilot GROUP BY Nationality ORDER BY COUNT(*) DESC LIMIT 1

Question: Show the pilot positions that have both pilots joining after year 2005 and pilots joining before 2000.
SQL: SELECT POSITION FROM pilot WHERE Join_Year	 <  2000 INTERSECT SELECT POSITION FROM pilot WHERE Join_Year	 >  2005

Question: Show the names of pilots and models of aircrafts they have flied with.
SQL: SELECT T3.Pilot_name ,  T2.Model FROM pilot_record AS T1 JOIN aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN pilot AS T3 ON T1.Pilot_ID  =  T3.Pilot_ID

Question: Show the names of pilots and fleet series of the aircrafts they have flied with in ascending order of the rank of the pilot.
SQL: SELECT T3.Pilot_name ,  T2.Fleet_Series FROM pilot_record AS T1 JOIN aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN pilot AS T3 ON T1.Pilot_ID  =  T3.Pilot_ID ORDER BY T3.Rank

Question: Show the fleet series of the aircrafts flied by pilots younger than 34
SQL: SELECT T2.Fleet_Series FROM pilot_record AS T1 JOIN aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN pilot AS T3 ON T1.Pilot_ID  =  T3.Pilot_ID WHERE T3.Age  <  34

Question: Show the names of pilots and the number of records they have.
SQL: SELECT T2.Pilot_name ,  COUNT(*) FROM pilot_record AS T1 JOIN pilot AS T2 ON T1.pilot_ID  =  T2.pilot_ID GROUP BY T2.Pilot_name

Question: Show names of pilots that have more than one record.
SQL: SELECT T2.Pilot_name ,  COUNT(*) FROM pilot_record AS T1 JOIN pilot AS T2 ON T1.pilot_ID  =  T2.pilot_ID GROUP BY T2.Pilot_name HAVING COUNT(*)  >  1

Question: List the names of pilots that do not have any record.
SQL: SELECT Pilot_name FROM pilot WHERE Pilot_ID NOT IN (SELECT Pilot_ID FROM pilot_record)
Database: company_1
Schema: works_on: Essn (number), Pno (number), Hours (number) | employee: Fname (text), Minit (text), Lname (text), Ssn (number), Bdate (text), Address (text), Sex (text), Salary (number), Super_ssn (number), Dno (number) | department: Dname (text), Dnumber (number), Mgr_ssn (number), Mgr_start_date (text) | project: Pname (text), Pnumber (number), Plocation (text), Dnum (number) | dependent: Essn (number), Dependent_name (text), Sex (text), Bdate (text), Relationship (text) | dept_locations: Dnumber (number), Dlocation (text)

Question: List all department names ordered by their starting date.
SQL: SELECT dname FROM department ORDER BY mgr_start_date

Question: find all dependent names who have a spouse relation with some employee.
SQL: SELECT Dependent_name FROM dependent WHERE relationship  =  'Spouse'

Question: how many female dependents are there?
SQL: SELECT count(*) FROM dependent WHERE sex  =  'F'

Question: Find the names of departments that are located in Houston.
SQL: SELECT t1.dname FROM department AS t1 JOIN dept_locations AS t2 ON t1.dnumber  =  t2.dnumber WHERE t2.dlocation  =  'Houston'

Question: Return the first names and last names of employees who earn more than 30000 in salary.
SQL: SELECT fname ,  lname FROM employee WHERE salary  >  30000

Question: Find the number of employees of each gender whose salary is lower than 50000.
SQL: SELECT count(*) ,  sex FROM employee WHERE salary  <  50000 GROUP BY sex

Question: list the first and last names, and the addresses of all employees in the ascending order of their birth date.
SQL: SELECT fname ,  lname ,  address FROM employee ORDER BY Bdate
Database: decoration_competition
Schema: college: College_ID (number), Name (text), Leader_Name (text), College_Location (text) | member: Member_ID (number), Name (text), Country (text), College_ID (number) | round: Round_ID (number), Member_ID (number), Decoration_Theme (text), Rank_in_Round (number)

Question: How many members are there?
SQL: SELECT count(*) FROM member

Question: List the names of members in ascending alphabetical order.
SQL: SELECT Name FROM member ORDER BY Name ASC

Question: What are the names and countries of members?
SQL: SELECT Name ,  Country FROM member

Question: Show the names of members whose country is "United States" or "Canada".
SQL: SELECT Name FROM member WHERE Country  =  "United States" OR Country  =  "Canada"

Question: Show the different countries and the number of members from each.
SQL: SELECT Country ,  COUNT(*) FROM member GROUP BY Country

Question: Show the most common country across members.
SQL: SELECT Country FROM member GROUP BY Country ORDER BY COUNT(*) DESC LIMIT 1

Question: Which countries have more than two members?
SQL: SELECT Country FROM member GROUP BY Country HAVING COUNT(*)  >  2

Question: Show the leader names and locations of colleges.
SQL: SELECT Leader_Name ,  College_Location FROM college

Question: Show the names of members and names of colleges they go to.
SQL: SELECT T2.Name ,  T1.Name FROM college AS T1 JOIN member AS T2 ON T1.College_ID  =  T2.College_ID

Question: Show the names of members and the locations of colleges they go to in ascending alphabetical order of member names.
SQL: SELECT T2.Name ,  T1.College_Location FROM college AS T1 JOIN member AS T2 ON T1.College_ID  =  T2.College_ID ORDER BY T2.Name ASC

Question: Show the distinct leader names of colleges associated with members from country "Canada".
SQL: SELECT DISTINCT T1.Leader_Name FROM college AS T1 JOIN member AS T2 ON T1.College_ID  =  T2.College_ID WHERE T2.Country  =  "Canada"

Question: Show the names of members and the decoration themes they have.
SQL: SELECT T1.Name ,  T2.Decoration_Theme FROM member AS T1 JOIN round AS T2 ON T1.Member_ID  =  T2.Member_ID

Question: Show the names of members that have a rank in round higher than 3.
SQL: SELECT T1.Name FROM member AS T1 JOIN round AS T2 ON T1.Member_ID  =  T2.Member_ID WHERE T2.Rank_in_Round  >  3

Question: Show the names of members in ascending order of their rank in rounds.
SQL: SELECT T1.Name FROM member AS T1 JOIN round AS T2 ON T1.Member_ID  =  T2.Member_ID ORDER BY Rank_in_Round ASC

Question: List the names of members who did not participate in any round.
SQL: SELECT Name FROM member WHERE Member_ID NOT IN (SELECT Member_ID FROM round)
Database: school_bus
Schema: driver: Driver_ID (number), Name (text), Party (text), Home_city (text), Age (number) | school: School_ID (number), Grade (text), School (text), Location (text), Type (text) | school_bus: School_ID (number), Driver_ID (number), Years_Working (number), If_full_time (others)

Question: How many drivers are there?
SQL: SELECT count(*) FROM driver

Question: Show the name, home city, and age for all drivers.
SQL: SELECT name ,  home_city ,  age FROM driver

Question: Show the party and the number of drivers in each party.
SQL: SELECT party ,  count(*) FROM driver GROUP BY party

Question: Show the name of drivers in descending order of age.
SQL: SELECT name FROM driver ORDER BY age DESC

Question: Show all different home cities.
SQL: SELECT DISTINCT home_city FROM driver

Question: Show the home city with the most number of drivers.
SQL: SELECT home_city FROM driver GROUP BY home_city ORDER BY count(*) DESC LIMIT 1

Question: Show the party with drivers from Hartford and drivers older than 40.
SQL: SELECT party FROM driver WHERE home_city  =  'Hartford' AND age  >  40

Question: Show home city where at least two drivers older than 40 are from.
SQL: SELECT home_city FROM driver WHERE age  >  40 GROUP BY home_city HAVING count(*)  >=  2

Question: Show all home cities except for those having a driver older than 40.
SQL: SELECT home_city FROM driver EXCEPT SELECT home_city FROM driver WHERE age  >  40

Question: Show the names of the drivers without a school bus.
SQL: SELECT name FROM driver WHERE driver_id NOT IN (SELECT driver_id FROM school_bus)

Question: Show the types of schools that have two schools.
SQL: SELECT TYPE FROM school GROUP BY TYPE HAVING count(*)  =  2

Question: Show the school name and driver name for all school buses.
SQL: SELECT T2.school ,  T3.name FROM school_bus AS T1 JOIN school AS T2 ON T1.school_id  =  T2.school_id JOIN driver AS T3 ON T1.driver_id  =  T3.driver_id

Question: What is the maximum, minimum and average years spent working on a school bus?
SQL: SELECT max(years_working) ,  min(years_working) ,  avg(years_working) FROM school_bus

Question: Show the school name and type for schools without a school bus.
SQL: SELECT school ,  TYPE FROM school WHERE school_id NOT IN (SELECT school_id FROM school_bus)

Question: Show the type of school and the number of buses for each type.
SQL: SELECT T2.type ,  count(*) FROM school_bus AS T1 JOIN school AS T2 ON T1.school_id  =  T2.school_id GROUP BY T2.type

Question: How many drivers are from Hartford city or younger than 40?
SQL: SELECT count(*) FROM driver WHERE home_city  =  'Hartford' OR age  <  40

Question: List names for drivers from Hartford city and younger than 40.
SQL: SELECT name FROM driver WHERE home_city  =  'Hartford' AND age  <  40

Question: find the name of driver who is driving the school bus with the longest working history.
SQL: SELECT t1.name FROM driver AS t1 JOIN school_bus AS t2 ON t1.driver_id  =  t2.driver_id ORDER BY years_working DESC LIMIT 1