target-benchmark/spider-queries huggingface dataset:
Database: flight_1 (tables: certificate, aircraft)
Question: Show the name of aircraft which fewest people have its certificate.
SQL: SELECT T2.name FROM Certificate AS T1 JOIN Aircraft AS T2 ON T2.aid  =  T1.aid GROUP BY T1.aid ORDER BY count(*) DESC LIMIT 1
Database: flight_1 (tables: certificate, aircraft)
Question: What are the names of the aircraft that the least people are certified to fly?
SQL: SELECT T2.name FROM Certificate AS T1 JOIN Aircraft AS T2 ON T2.aid  =  T1.aid GROUP BY T1.aid ORDER BY count(*) DESC LIMIT 1
Database: flight_1 (tables: certificate, aircraft)
Question: Show the name and distance of the aircrafts with more than 5000 distance and which at least 5 people have its certificate.
SQL: SELECT T2.name FROM Certificate AS T1 JOIN Aircraft AS T2 ON T2.aid  =  T1.aid WHERE T2.distance  >  5000 GROUP BY T1.aid ORDER BY count(*)  >=  5
Database: flight_1 (tables: certificate, aircraft)
Question: What is the name and distance of every aircraft that can cover a distance of more than 5000 and which at least 5 people can fly?
SQL: SELECT T2.name FROM Certificate AS T1 JOIN Aircraft AS T2 ON T2.aid  =  T1.aid WHERE T2.distance  >  5000 GROUP BY T1.aid ORDER BY count(*)  >=  5
Database: flight_1 (tables: employee, certificate)
Question: what is the salary and name of the employee who has the most number of aircraft certificates?
SQL: SELECT T1.name ,  T1.salary FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid GROUP BY T1.eid ORDER BY count(*) DESC LIMIT 1
Database: flight_1 (tables: employee, certificate)
Question: What is the salaray and name of the employee that is certified to fly the most planes?
SQL: SELECT T1.name ,  T1.salary FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid GROUP BY T1.eid ORDER BY count(*) DESC LIMIT 1
Database: flight_1 (tables: employee, certificate, aircraft)
Question: What is the salary and name of the employee who has the most number of certificates on aircrafts with distance more than 5000?
SQL: SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T3.distance  >  5000 GROUP BY T1.eid ORDER BY count(*) DESC LIMIT 1
Database: flight_1 (tables: employee, certificate, aircraft)
Question: What is the salaray and name of the employee with the most certificates to fly planes more than 5000?
SQL: SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T3.distance  >  5000 GROUP BY T1.eid ORDER BY count(*) DESC LIMIT 1
Database: allergy_1 (tables: allergy_type)
Question: How many allergies are there?
SQL: SELECT count(DISTINCT allergy) FROM Allergy_type
Database: allergy_1 (tables: allergy_type)
Question: How many allergy entries are there?
SQL: SELECT count(DISTINCT allergy) FROM Allergy_type
Database: allergy_1 (tables: allergy_type)
Question: How many different allergy types exist?
SQL: SELECT count(DISTINCT allergytype) FROM Allergy_type
Database: allergy_1 (tables: allergy_type)
Question: How many distinct allergies are there?
SQL: SELECT count(DISTINCT allergytype) FROM Allergy_type
Database: allergy_1 (tables: allergy_type)
Question: Show all allergy types.
SQL: SELECT DISTINCT allergytype FROM Allergy_type
Database: allergy_1 (tables: allergy_type)
Question: What are the different allergy types?
SQL: SELECT DISTINCT allergytype FROM Allergy_type
Database: allergy_1 (tables: allergy_type)
Question: Show all allergies and their types.
SQL: SELECT allergy ,  allergytype FROM Allergy_type
Database: allergy_1 (tables: allergy_type)
Question: What are the allergies and their types?
SQL: SELECT allergy ,  allergytype FROM Allergy_type
Database: allergy_1 (tables: allergy_type)
Question: Show all allergies with type food.
SQL: SELECT DISTINCT allergy FROM Allergy_type WHERE allergytype  =  "food"
Database: allergy_1 (tables: allergy_type)
Question: What are all the different food allergies?
SQL: SELECT DISTINCT allergy FROM Allergy_type WHERE allergytype  =  "food"
Database: allergy_1 (tables: allergy_type)
Question: What is the type of allergy Cat?
SQL: SELECT allergytype FROM Allergy_type WHERE allergy  =  "Cat"
Database: allergy_1 (tables: allergy_type)
Question: What is allergy type of a cat allergy?
SQL: SELECT allergytype FROM Allergy_type WHERE allergy  =  "Cat"
Database: allergy_1 (tables: allergy_type)
Question: How many allergies have type animal?
SQL: SELECT count(*) FROM Allergy_type WHERE allergytype  =  "animal"
Database: allergy_1 (tables: allergy_type)
Question: How many animal type allergies exist?
SQL: SELECT count(*) FROM Allergy_type WHERE allergytype  =  "animal"
Database: allergy_1 (tables: allergy_type)
Question: Show all allergy types and the number of allergies in each type.
SQL: SELECT allergytype ,  count(*) FROM Allergy_type GROUP BY allergytype
Database: allergy_1 (tables: allergy_type)
Question: What are the allergy types and how many allergies correspond to each one?
SQL: SELECT allergytype ,  count(*) FROM Allergy_type GROUP BY allergytype
Database: allergy_1 (tables: allergy_type)
Question: Which allergy type has most number of allergies?
SQL: SELECT allergytype FROM Allergy_type GROUP BY allergytype ORDER BY count(*) DESC LIMIT 1
Database: allergy_1 (tables: allergy_type)
Question: Which allergy type is most common?
SQL: SELECT allergytype FROM Allergy_type GROUP BY allergytype ORDER BY count(*) DESC LIMIT 1
Database: allergy_1 (tables: allergy_type)
Question: Which allergy type has least number of allergies?
SQL: SELECT allergytype FROM Allergy_type GROUP BY allergytype ORDER BY count(*) ASC LIMIT 1
Database: allergy_1 (tables: allergy_type)
Question: Which allergy type is the least common?
SQL: SELECT allergytype FROM Allergy_type GROUP BY allergytype ORDER BY count(*) ASC LIMIT 1
Database: allergy_1 (tables: student)
Question: How many students are there?
SQL: SELECT count(*) FROM Student
Database: allergy_1 (tables: student)
Question: What is the total number of students?
SQL: SELECT count(*) FROM Student
Database: allergy_1 (tables: student)
Question: Show first name and last name for all students.
SQL: SELECT Fname ,  Lname FROM Student
Database: allergy_1 (tables: student)
Question: What are the full names of all students
SQL: SELECT Fname ,  Lname FROM Student
Database: allergy_1 (tables: student)
Question: How many different advisors are listed?
SQL: SELECT count(DISTINCT advisor) FROM Student
Database: allergy_1 (tables: student)
Question: How many advisors are there?
SQL: SELECT count(DISTINCT advisor) FROM Student
Database: allergy_1 (tables: student)
Question: Show all majors.
SQL: SELECT DISTINCT Major FROM Student
Database: allergy_1 (tables: student)
Question: What are the different majors?
SQL: SELECT DISTINCT Major FROM Student
Database: allergy_1 (tables: student)
Question: Show all cities where students live.
SQL: SELECT DISTINCT city_code FROM Student
Database: allergy_1 (tables: student)
Question: What cities do students live in?
SQL: SELECT DISTINCT city_code FROM Student
Database: allergy_1 (tables: student)
Question: Show first name, last name, age for all female students. Their sex is F.
SQL: SELECT Fname ,  Lname ,  Age FROM Student WHERE Sex  =  'F'
Database: allergy_1 (tables: student)
Question: What are the full names and ages for all female students whose sex is F?
SQL: SELECT Fname ,  Lname ,  Age FROM Student WHERE Sex  =  'F'
Database: allergy_1 (tables: student)
Question: Show student ids for all male students.
SQL: SELECT StuID FROM Student WHERE Sex  =  'M'
Database: allergy_1 (tables: student)
Question: What are the student ids for all male students?
SQL: SELECT StuID FROM Student WHERE Sex  =  'M'
Database: allergy_1 (tables: student)
Question: How many students are age 18?
SQL: SELECT count(*) FROM Student WHERE age  =  18
Database: allergy_1 (tables: student)
Question: How many students are 18 years old?
SQL: SELECT count(*) FROM Student WHERE age  =  18
Database: allergy_1 (tables: student)
Question: Show all student ids who are older than 20.
SQL: SELECT StuID FROM Student WHERE age  >  20
Database: allergy_1 (tables: student)
Question: What are the student ids for students over 20 years old?
SQL: SELECT StuID FROM Student WHERE age  >  20
Database: allergy_1 (tables: student)
Question: Which city does the student whose last name is "Kim" live in?
SQL: SELECT city_code FROM Student WHERE LName  =  "Kim"
Database: allergy_1 (tables: student)
Question: Give the city that the student whose family name is Kim lives in.
SQL: SELECT city_code FROM Student WHERE LName  =  "Kim"
Database: allergy_1 (tables: student)
Question: Who is the advisor of student with ID 1004?
SQL: SELECT Advisor FROM Student WHERE StuID  =  1004
Database: allergy_1 (tables: student)
Question: Who advises student 1004?
SQL: SELECT Advisor FROM Student WHERE StuID  =  1004
Database: allergy_1 (tables: student)
Question: How many students live in HKG or CHI?
SQL: SELECT count(*) FROM Student WHERE city_code  =  "HKG" OR city_code  =  "CHI"
Database: allergy_1 (tables: student)
Question: Give the number of students living in either HKG or CHI.
SQL: SELECT count(*) FROM Student WHERE city_code  =  "HKG" OR city_code  =  "CHI"
Database: allergy_1 (tables: student)
Question: Show the minimum, average, and maximum age of all students.
SQL: SELECT min(age) ,  avg(age) ,  max(age) FROM Student
Database: allergy_1 (tables: student)
Question: What is the minimum, mean, and maximum age across all students?
SQL: SELECT min(age) ,  avg(age) ,  max(age) FROM Student
Database: allergy_1 (tables: student)
Question: What is the last name of the youngest student?
SQL: SELECT LName FROM Student WHERE age  =  (SELECT min(age) FROM Student)
Database: allergy_1 (tables: student)
Question: Provide the last name of the youngest student.
SQL: SELECT LName FROM Student WHERE age  =  (SELECT min(age) FROM Student)
Database: allergy_1 (tables: student)
Question: Show the student id of the oldest student.
SQL: SELECT StuID FROM Student WHERE age  =  (SELECT max(age) FROM Student)
Database: allergy_1 (tables: student)
Question: What student id corresponds to the oldest student?
SQL: SELECT StuID FROM Student WHERE age  =  (SELECT max(age) FROM Student)
Database: allergy_1 (tables: student)
Question: Show all majors and corresponding number of students.
SQL: SELECT major ,  count(*) FROM Student GROUP BY major
Database: allergy_1 (tables: student)
Question: How many students are there for each major?
SQL: SELECT major ,  count(*) FROM Student GROUP BY major
Database: allergy_1 (tables: student)
Question: Which major has most number of students?
SQL: SELECT major FROM Student GROUP BY major ORDER BY count(*) DESC LIMIT 1
Database: allergy_1 (tables: student)
Question: What is the largest major?
SQL: SELECT major FROM Student GROUP BY major ORDER BY count(*) DESC LIMIT 1
Database: allergy_1 (tables: student)
Question: Show all ages and corresponding number of students.
SQL: SELECT age ,  count(*) FROM Student GROUP BY age
Database: allergy_1 (tables: student)
Question: How old is each student and how many students are each age?
SQL: SELECT age ,  count(*) FROM Student GROUP BY age
Database: allergy_1 (tables: student)
Question: Show the average age for male and female students.
SQL: SELECT avg(age) ,  sex FROM Student GROUP BY sex
Database: allergy_1 (tables: student)
Question: What are the average ages for male and female students?
SQL: SELECT avg(age) ,  sex FROM Student GROUP BY sex
Database: allergy_1 (tables: student)
Question: Show all cities and corresponding number of students.
SQL: SELECT city_code ,  count(*) FROM Student GROUP BY city_code
Database: allergy_1 (tables: student)
Question: How many students live in each city?
SQL: SELECT city_code ,  count(*) FROM Student GROUP BY city_code
Database: allergy_1 (tables: student)
Question: Show all advisors and corresponding number of students.
SQL: SELECT advisor ,  count(*) FROM Student GROUP BY advisor
Database: allergy_1 (tables: student)
Question: How many students does each advisor have?
SQL: SELECT advisor ,  count(*) FROM Student GROUP BY advisor
Database: allergy_1 (tables: student)
Question: Which advisor has most number of students?
SQL: SELECT advisor FROM Student GROUP BY advisor ORDER BY count(*) DESC LIMIT 1
Database: allergy_1 (tables: student)
Question: Give the advisor with the most students.
SQL: SELECT advisor FROM Student GROUP BY advisor ORDER BY count(*) DESC LIMIT 1
Database: allergy_1 (tables: has_allergy)
Question: How many students have cat allergies?
SQL: SELECT count(*) FROM Has_allergy WHERE Allergy  =  "Cat"
Database: allergy_1 (tables: has_allergy)
Question: How many students are affected by cat allergies?
SQL: SELECT count(*) FROM Has_allergy WHERE Allergy  =  "Cat"
Database: allergy_1 (tables: has_allergy)
Question: Show all student IDs who have at least two allergies.
SQL: SELECT StuID FROM Has_allergy GROUP BY StuID HAVING count(*)  >=  2
Database: allergy_1 (tables: has_allergy)
Question: What are the students ids of students who have more than one allergy?
SQL: SELECT StuID FROM Has_allergy GROUP BY StuID HAVING count(*)  >=  2
Database: allergy_1 (tables: student, has_allergy)
Question: What are the student ids of students who don't have any allergies?
SQL: SELECT StuID FROM Student EXCEPT SELECT StuID FROM Has_allergy
Database: allergy_1 (tables: student, has_allergy)
Question: Which students are unaffected by allergies?
SQL: SELECT StuID FROM Student EXCEPT SELECT StuID FROM Has_allergy
Database: allergy_1 (tables: has_allergy, student)
Question: How many female students have milk or egg allergies?
SQL: SELECT count(*) FROM has_allergy AS T1 JOIN Student AS T2 ON T1.StuID  =  T2.StuID WHERE T2.sex  =  "F" AND T1.allergy  =  "Milk" OR T1.allergy  =  "Eggs"
Database: allergy_1 (tables: has_allergy, student)
Question: How many students who are female are allergic to milk or eggs?
SQL: SELECT count(*) FROM has_allergy AS T1 JOIN Student AS T2 ON T1.StuID  =  T2.StuID WHERE T2.sex  =  "F" AND T1.allergy  =  "Milk" OR T1.allergy  =  "Eggs"
Database: allergy_1 (tables: allergy_type, has_allergy)
Question: How many students have a food allergy?
SQL: SELECT count(*) FROM Has_allergy AS T1 JOIN Allergy_type AS T2 ON T1.allergy  =  T2.allergy WHERE T2.allergytype  =  "food"
Database: allergy_1 (tables: allergy_type, has_allergy)
Question: How many students are affected by food related allergies?
SQL: SELECT count(*) FROM Has_allergy AS T1 JOIN Allergy_type AS T2 ON T1.allergy  =  T2.allergy WHERE T2.allergytype  =  "food"
Database: allergy_1 (tables: has_allergy)
Question: Which allergy has most number of students affected?
SQL: SELECT Allergy FROM Has_allergy GROUP BY Allergy ORDER BY count(*) DESC LIMIT 1
Database: allergy_1 (tables: has_allergy)
Question: Which allergy is the most common?
SQL: SELECT Allergy FROM Has_allergy GROUP BY Allergy ORDER BY count(*) DESC LIMIT 1
Database: allergy_1 (tables: has_allergy)
Question: Show all allergies with number of students affected.
SQL: SELECT Allergy ,  count(*) FROM Has_allergy GROUP BY Allergy
Database: allergy_1 (tables: has_allergy)
Question: How many students have each different allergy?
SQL: SELECT Allergy ,  count(*) FROM Has_allergy GROUP BY Allergy
Database: allergy_1 (tables: allergy_type, has_allergy)
Question: Show all allergy type with number of students affected.
SQL: SELECT T2.allergytype ,  count(*) FROM Has_allergy AS T1 JOIN Allergy_type AS T2 ON T1.allergy  =  T2.allergy GROUP BY T2.allergytype
Database: allergy_1 (tables: allergy_type, has_allergy)
Question: How many students are affected by each allergy type?
SQL: SELECT T2.allergytype ,  count(*) FROM Has_allergy AS T1 JOIN Allergy_type AS T2 ON T1.allergy  =  T2.allergy GROUP BY T2.allergytype
Database: allergy_1 (tables: student, has_allergy)
Question: Find the last name and age of the student who has allergy to both milk and cat.
SQL: SELECT lname ,  age FROM Student WHERE StuID IN (SELECT StuID FROM Has_allergy WHERE Allergy  =  "Milk" INTERSECT SELECT StuID FROM Has_allergy WHERE Allergy  =  "Cat")
Database: allergy_1 (tables: student, has_allergy)
Question: What are the last names and ages of the students who are allergic to milk and cat?
SQL: SELECT lname ,  age FROM Student WHERE StuID IN (SELECT StuID FROM Has_allergy WHERE Allergy  =  "Milk" INTERSECT SELECT StuID FROM Has_allergy WHERE Allergy  =  "Cat")
Database: allergy_1 (tables: student, allergy_type, has_allergy)
Question: What are the allergies and their types that the student with first name Lisa has? And order the result by name of allergies.
SQL: SELECT T1.Allergy ,  T1.AllergyType FROM Allergy_type AS T1 JOIN Has_allergy AS T2 ON T1.Allergy  =  T2.Allergy JOIN Student AS T3 ON T3.StuID  =  T2.StuID WHERE T3.Fname  =  "Lisa" ORDER BY T1.Allergy
Database: allergy_1 (tables: student, allergy_type, has_allergy)
Question: What are the allergies the girl named Lisa has? And what are the types of them? Order the result by allergy names.
SQL: SELECT T1.Allergy ,  T1.AllergyType FROM Allergy_type AS T1 JOIN Has_allergy AS T2 ON T1.Allergy  =  T2.Allergy JOIN Student AS T3 ON T3.StuID  =  T2.StuID WHERE T3.Fname  =  "Lisa" ORDER BY T1.Allergy
Database: allergy_1 (tables: student, has_allergy)
Question: Find the first name and gender of the student who has allergy to milk but not cat.
SQL: SELECT fname ,  sex FROM Student WHERE StuID IN (SELECT StuID FROM Has_allergy WHERE Allergy  =  "Milk" EXCEPT SELECT StuID FROM Has_allergy WHERE Allergy  =  "Cat")
Database: allergy_1 (tables: student, has_allergy)
Question: What are the first name and gender of the students who have allergy to milk but can put up with cats?
SQL: SELECT fname ,  sex FROM Student WHERE StuID IN (SELECT StuID FROM Has_allergy WHERE Allergy  =  "Milk" EXCEPT SELECT StuID FROM Has_allergy WHERE Allergy  =  "Cat")
Database: allergy_1 (tables: student, allergy_type, has_allergy)
Question: Find the average age of the students who have allergies with food and animal types.
SQL: SELECT avg(age) FROM Student WHERE StuID IN ( SELECT T1.StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "food" INTERSECT SELECT T1.StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "animal")
Database: allergy_1 (tables: student, allergy_type, has_allergy)
Question: How old are the students with allergies to food and animal types on average?
SQL: SELECT avg(age) FROM Student WHERE StuID IN ( SELECT T1.StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "food" INTERSECT SELECT T1.StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "animal")
Database: allergy_1 (tables: student, allergy_type, has_allergy)
Question: List the first and last name of the students who do not have any food type allergy.
SQL: SELECT fname ,  lname FROM Student WHERE StuID NOT IN (SELECT T1.StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "food")
Database: allergy_1 (tables: student, allergy_type, has_allergy)
Question: What is the full name of each student who is not allergic to any type of food.
SQL: SELECT fname ,  lname FROM Student WHERE StuID NOT IN (SELECT T1.StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "food")
Database: allergy_1 (tables: student, allergy_type, has_allergy)
Question: Find the number of male (sex is 'M') students who have some food type allery.
SQL: SELECT count(*) FROM Student WHERE sex  =  "M" AND StuID IN (SELECT StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "food")
Database: allergy_1 (tables: student, allergy_type, has_allergy)
Question: How many male students (sex is 'M') are allergic to any type of food?
SQL: SELECT count(*) FROM Student WHERE sex  =  "M" AND StuID IN (SELECT StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "food")
Database: allergy_1 (tables: student, has_allergy)
Question: Find the different first names and cities of the students who have allergy to milk or cat.
SQL: SELECT DISTINCT T1.fname ,  T1.city_code FROM Student AS T1 JOIN Has_Allergy AS T2 ON T1.stuid  =  T2.stuid WHERE T2.Allergy  =  "Milk" OR T2.Allergy  =  "Cat"
Database: allergy_1 (tables: student, has_allergy)
Question: What are the distinct first names and cities of the students who have allergy either to milk or to cat?
SQL: SELECT DISTINCT T1.fname ,  T1.city_code FROM Student AS T1 JOIN Has_Allergy AS T2 ON T1.stuid  =  T2.stuid WHERE T2.Allergy  =  "Milk" OR T2.Allergy  =  "Cat"
Database: allergy_1 (tables: student, allergy_type, has_allergy)
Question: Find the number of students who are older than 18 and do not have allergy to either food or animal.
SQL: SELECT count(*) FROM Student WHERE age  >  18 AND StuID NOT IN ( SELECT StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "food" OR T2.allergytype  =  "animal")
Database: allergy_1 (tables: student, allergy_type, has_allergy)
Question: How many students are over 18 and do not have allergy to food type or animal type?
SQL: SELECT count(*) FROM Student WHERE age  >  18 AND StuID NOT IN ( SELECT StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "food" OR T2.allergytype  =  "animal")
Database: allergy_1 (tables: student, has_allergy)
Question: Find the first name and major of the students who are not allegry to soy.
SQL: SELECT fname ,  major FROM Student WHERE StuID NOT IN (SELECT StuID FROM Has_allergy WHERE Allergy  =  "Soy")
Database: allergy_1 (tables: student, has_allergy)
Question: What are the first name and major of the students who are able to consume soy?
SQL: SELECT fname ,  major FROM Student WHERE StuID NOT IN (SELECT StuID FROM Has_allergy WHERE Allergy  =  "Soy")
Database: store_1 (tables: invoices)
Question: A list of the top 5 countries by number of invoices. List country name and number of invoices.
SQL: SELECT billing_country ,  COUNT(*) FROM invoices GROUP BY billing_country ORDER BY count(*) DESC LIMIT 5;
Database: store_1 (tables: invoices)
Question: What are the top 5 countries by number of invoices and how many do they have?
SQL: SELECT billing_country ,  COUNT(*) FROM invoices GROUP BY billing_country ORDER BY count(*) DESC LIMIT 5;
Database: store_1 (tables: invoices)
Question: A list of the top 8 countries by gross/total invoice size. List country name and gross invoice size.
SQL: SELECT billing_country ,  SUM(total) FROM invoices GROUP BY billing_country ORDER BY SUM(total) DESC LIMIT 8;
Database: store_1 (tables: invoices)
Question: What are the names of the top 8 countries by total invoice size and what are those sizes?
SQL: SELECT billing_country ,  SUM(total) FROM invoices GROUP BY billing_country ORDER BY SUM(total) DESC LIMIT 8;
Database: store_1 (tables: invoices)
Question: A list of the top 10 countries by average invoice size. List country name and average invoice size.
SQL: SELECT billing_country ,  AVG(total) FROM invoices GROUP BY billing_country ORDER BY AVG(total) DESC LIMIT 10;
Database: store_1 (tables: invoices)
Question: What are the names of the countries and average invoice size of the top countries by size?
SQL: SELECT billing_country ,  AVG(total) FROM invoices GROUP BY billing_country ORDER BY AVG(total) DESC LIMIT 10;
Database: store_1 (tables: invoices, customers)
Question: Find out 5 customers who most recently purchased something. List customers' first and last name.
SQL: SELECT T1.first_name ,  T1.last_name FROM customers AS T1 JOIN invoices AS T2 ON T2.customer_id  =  T1.id ORDER BY T2.invoice_date DESC LIMIT 5;
Database: store_1 (tables: invoices, customers)
Question: What are the first and last names of the 5 customers who purchased something most recently?
SQL: SELECT T1.first_name ,  T1.last_name FROM customers AS T1 JOIN invoices AS T2 ON T2.customer_id  =  T1.id ORDER BY T2.invoice_date DESC LIMIT 5;