target-benchmark/spider-queries huggingface dataset:
Database: vehicle_driver (tables: vehicle, driver, vehicle_driver)
Question: Return the names and citizenships of drivers who have driven the vehicle with the model 'DJ1'.
SQL: SELECT T1.name ,  T1.citizenship FROM driver AS T1 JOIN vehicle_driver AS T2 ON T1.driver_id  =  T2.driver_id JOIN vehicle AS T3 ON T2.vehicle_id  =  T3.vehicle_id WHERE T3.model  =  'DJ1'
Database: vehicle_driver (tables: driver, vehicle_driver)
Question: How many drivers have not driven any cars?
SQL: SELECT count(*) FROM driver WHERE driver_id NOT IN ( SELECT driver_id FROM vehicle_driver )
Database: vehicle_driver (tables: driver, vehicle_driver)
Question: Count the number of drivers who have not driven any vehicles.
SQL: SELECT count(*) FROM driver WHERE driver_id NOT IN ( SELECT driver_id FROM vehicle_driver )
Database: online_exams (tables: exams)
Question: How many exams are there?
SQL: SELECT count(*) FROM Exams
Database: online_exams (tables: exams)
Question: Count the number of exams.
SQL: SELECT count(*) FROM Exams
Database: online_exams (tables: exams)
Question: List the distinct subject code of exams in ascending alphabetical order .
SQL: select distinct subject_code from exams order by subject_code asc
Database: online_exams (tables: exams)
Question: Give me an alphabetically ordered list of the distinct subject code for exams.
SQL: SELECT DISTINCT Subject_Code FROM Exams ORDER BY Subject_Code
Database: online_exams (tables: exams)
Question: What are the names and dates of the exams with subject code that is not "Database"?
SQL: SELECT Exam_Date ,  Exam_Name FROM Exams WHERE Subject_Code != 'Database'
Database: online_exams (tables: exams)
Question: Find the exams whose subject code is not "Database". What are the exam dates and exam names?
SQL: SELECT Exam_Date ,  Exam_Name FROM Exams WHERE Subject_Code != 'Database'
Database: online_exams (tables: exams)
Question: List the dates of the exams with subject code containing the word "data", in descending order of dates.
SQL: SELECT Exam_Date FROM Exams WHERE Subject_Code LIKE '%data%' ORDER BY Exam_Date DESC
Database: online_exams (tables: exams)
Question: What are the dates of the exams whose subject code contains the substring "data"? Return them in descending order of dates.
SQL: SELECT Exam_Date FROM Exams WHERE Subject_Code LIKE '%data%' ORDER BY Exam_Date DESC
Database: online_exams (tables: questions)
Question: What are the type of questions and their counts?
SQL: SELECT Type_of_Question_Code ,  COUNT(*) FROM Questions GROUP BY Type_of_Question_Code
Database: online_exams (tables: questions)
Question: For each question type, return its type code and its count of occurrence.
SQL: SELECT Type_of_Question_Code ,  COUNT(*) FROM Questions GROUP BY Type_of_Question_Code
Database: online_exams (tables: student_answers)
Question: What are the distinct student answer texts that received comments "Normal"?
SQL: SELECT DISTINCT Student_Answer_Text FROM Student_Answers WHERE Comments  =  "Normal"
Database: online_exams (tables: student_answers)
Question: List all the distinct student answer texts to which comments "Normal" were given?
SQL: SELECT DISTINCT Student_Answer_Text FROM Student_Answers WHERE Comments  =  "Normal"
Database: online_exams (tables: student_answers)
Question: How many different comments are there for student answers?
SQL: SELECT count(DISTINCT Comments) FROM Student_Answers
Database: online_exams (tables: student_answers)
Question: Count the number of different comments for student answers.
SQL: SELECT count(DISTINCT Comments) FROM Student_Answers
Database: online_exams (tables: student_answers)
Question: List all the student answer texts in descending order of count.
SQL: SELECT Student_Answer_Text FROM Student_Answers GROUP BY Student_Answer_Text ORDER BY COUNT(*) DESC
Database: online_exams (tables: student_answers)
Question: Sort the student answer texts in descending order of their frequency of occurrence.
SQL: SELECT Student_Answer_Text FROM Student_Answers GROUP BY Student_Answer_Text ORDER BY COUNT(*) DESC
Database: online_exams (tables: student_answers, students)
Question: Please show the first names of students and the dates of their answers.
SQL: SELECT T2.First_Name ,  T1.Date_of_Answer FROM Student_Answers AS T1 JOIN Students AS T2 ON T1.Student_ID  =  T2.Student_ID
Database: online_exams (tables: student_answers, students)
Question: For each student answer, find the first name of the student and the date of the answer.
SQL: SELECT T2.First_Name ,  T1.Date_of_Answer FROM Student_Answers AS T1 JOIN Students AS T2 ON T1.Student_ID  =  T2.Student_ID
Database: online_exams (tables: student_answers, students)
Question: Please show the email addresses of students and the dates of their answers in descending order of dates.
SQL: SELECT T2.Email_Adress ,  T1.Date_of_Answer FROM Student_Answers AS T1 JOIN Students AS T2 ON T1.Student_ID  =  T2.Student_ID ORDER BY T1.Date_of_Answer DESC
Database: online_exams (tables: student_answers, students)
Question: For each student answer, find the email address of the student and the date of the answer. Sort them in descending order of dates.
SQL: SELECT T2.Email_Adress ,  T1.Date_of_Answer FROM Student_Answers AS T1 JOIN Students AS T2 ON T1.Student_ID  =  T2.Student_ID ORDER BY T1.Date_of_Answer DESC
Database: online_exams (tables: student_assessments)
Question: Please show the least common assessment for students.
SQL: SELECT Assessment FROM Student_Assessments GROUP BY Assessment ORDER BY COUNT(*) ASC LIMIT 1
Database: online_exams (tables: student_assessments)
Question: Which assessment has the smallest frequency count?
SQL: SELECT Assessment FROM Student_Assessments GROUP BY Assessment ORDER BY COUNT(*) ASC LIMIT 1
Database: online_exams (tables: student_answers, students)
Question: Please show the first names of the students that have at least two answer records.
SQL: SELECT T2.First_Name FROM Student_Answers AS T1 JOIN Students AS T2 ON T1.Student_ID  =  T2.Student_ID GROUP BY T1.Student_ID HAVING COUNT(*)  >=  2
Database: online_exams (tables: student_answers, students)
Question: Which students have 2 or more answer records? Give me their first names.
SQL: SELECT T2.First_Name FROM Student_Answers AS T1 JOIN Students AS T2 ON T1.Student_ID  =  T2.Student_ID GROUP BY T1.Student_ID HAVING COUNT(*)  >=  2
Database: online_exams (tables: valid_answers)
Question: What is the most common valid answer text?
SQL: SELECT Valid_Answer_Text FROM Valid_Answers GROUP BY Valid_Answer_Text ORDER BY COUNT(*) DESC LIMIT 1
Database: online_exams (tables: valid_answers)
Question: Find the valid answer text that appeared most frequently.
SQL: SELECT Valid_Answer_Text FROM Valid_Answers GROUP BY Valid_Answer_Text ORDER BY COUNT(*) DESC LIMIT 1
Database: online_exams (tables: students)
Question: List the last names of the students whose gender is not "M".
SQL: SELECT Last_Name FROM Students WHERE Gender_MFU != "M"
Database: online_exams (tables: students)
Question: What are the last names of the students with gender other than "M"?
SQL: SELECT Last_Name FROM Students WHERE Gender_MFU != "M"
Database: online_exams (tables: students)
Question: List each gender and the corresponding number of students.
SQL: SELECT Gender_MFU ,  COUNT(*) FROM Students GROUP BY Gender_MFU
Database: online_exams (tables: students)
Question: For each gender, return the gender code and the number of students who identify as that gender.
SQL: SELECT Gender_MFU ,  COUNT(*) FROM Students GROUP BY Gender_MFU
Database: online_exams (tables: students)
Question: List the last names of the students whose gender is "F" or "M".
SQL: SELECT Last_Name FROM Students WHERE Gender_MFU  =  "F" OR Gender_MFU  =  "M"
Database: online_exams (tables: students)
Question: Which students identify their gender as "F" or "M"? Give me their last names.
SQL: SELECT Last_Name FROM Students WHERE Gender_MFU  =  "F" OR Gender_MFU  =  "M"
Database: online_exams (tables: student_answers, students)
Question: List the first names of the students who do not have any answers.
SQL: SELECT First_Name FROM Students WHERE Student_ID NOT IN (SELECT Student_ID FROM Student_Answers)
Database: online_exams (tables: student_answers, students)
Question: Which students do not have any answers? Find their first names.
SQL: SELECT First_Name FROM Students WHERE Student_ID NOT IN (SELECT Student_ID FROM Student_Answers)
Database: online_exams (tables: student_answers)
Question: Show the student answer texts that received both "Normal" and "Absent" as comments.
SQL: SELECT Student_Answer_Text FROM Student_Answers WHERE Comments  =  "Normal" INTERSECT SELECT Student_Answer_Text FROM Student_Answers WHERE Comments  =  "Absent"
Database: online_exams (tables: student_answers)
Question: Which student answer texts were given both "Normal" and "Absent" as comments?
SQL: SELECT Student_Answer_Text FROM Student_Answers WHERE Comments  =  "Normal" INTERSECT SELECT Student_Answer_Text FROM Student_Answers WHERE Comments  =  "Absent"
Database: online_exams (tables: questions)
Question: Show the types of questions that have at least three questions.
SQL: SELECT Type_of_Question_Code FROM Questions GROUP BY Type_of_Question_Code HAVING count(*)  >=  3
Database: online_exams (tables: questions)
Question: Which types of questions have 3 or more questions? Return the questions type code.
SQL: SELECT Type_of_Question_Code FROM Questions GROUP BY Type_of_Question_Code HAVING count(*)  >=  3
Database: online_exams (tables: students)
Question: Show all information on students.
SQL: SELECT * FROM Students
Database: online_exams (tables: students)
Question: What is al the available information of each student?
SQL: SELECT * FROM Students
Database: customers_and_orders (tables: addresses)
Question: How many addresses do we have?
SQL: SELECT count(*) FROM Addresses
Database: customers_and_orders (tables: addresses)
Question: Count the number of addresses.
SQL: SELECT count(*) FROM Addresses
Database: customers_and_orders (tables: addresses)
Question: List all address ids and address details.
SQL: SELECT address_id ,  address_details FROM Addresses
Database: customers_and_orders (tables: addresses)
Question: What are all the address ids and address details?
SQL: SELECT address_id ,  address_details FROM Addresses
Database: customers_and_orders (tables: products)
Question: How many products do we have?
SQL: SELECT count(*) FROM Products
Database: customers_and_orders (tables: products)
Question: Count the number of products.
SQL: SELECT count(*) FROM Products
Database: customers_and_orders (tables: products)
Question: Show all product ids, product type codes, and product name.
SQL: SELECT product_id ,  product_type_code ,  product_name FROM Products
Database: customers_and_orders (tables: products)
Question: What are the ids, type codes, and names for all products?
SQL: SELECT product_id ,  product_type_code ,  product_name FROM Products
Database: customers_and_orders (tables: products)
Question: What is the price for the product with name Monitor?
SQL: SELECT product_price FROM Products WHERE product_name  =  "Monitor"
Database: customers_and_orders (tables: products)
Question: Give the price of the Monitor product.
SQL: SELECT product_price FROM Products WHERE product_name  =  "Monitor"
Database: customers_and_orders (tables: products)
Question: Show the minimum, average, maximum price for all products.
SQL: SELECT min(product_price) ,  avg(product_price) ,  max(product_price) FROM Products
Database: customers_and_orders (tables: products)
Question: What are the minimum, average, and maximum prices across all products?
SQL: SELECT min(product_price) ,  avg(product_price) ,  max(product_price) FROM Products
Database: customers_and_orders (tables: products)
Question: What is the average price for products with type Clothes?
SQL: SELECT avg(product_price) FROM Products WHERE product_type_code  =  "Clothes"
Database: customers_and_orders (tables: products)
Question: Return the average price of Clothes.
SQL: SELECT avg(product_price) FROM Products WHERE product_type_code  =  "Clothes"
Database: customers_and_orders (tables: products)
Question: How many hardware type products do we have?
SQL: SELECT count(*) FROM Products WHERE product_type_code  =  "Hardware"
Database: customers_and_orders (tables: products)
Question: Count the number of products of the type Hardware.
SQL: SELECT count(*) FROM Products WHERE product_type_code  =  "Hardware"
Database: customers_and_orders (tables: products)
Question: Show all product names with price higher than the average.
SQL: SELECT product_name FROM Products WHERE product_price  >  (SELECT avg(product_price) FROM Products)
Database: customers_and_orders (tables: products)
Question: What are the names of products that have a price above the average for all products.
SQL: SELECT product_name FROM Products WHERE product_price  >  (SELECT avg(product_price) FROM Products)
Database: customers_and_orders (tables: products)
Question: Show all hardware product names with price higher than the average price of hardware type products.
SQL: SELECT product_name FROM Products WHERE product_type_code  =  "Hardware" AND product_price  >  (SELECT avg(product_price) FROM Products WHERE product_type_code  =  "Hardware")
Database: customers_and_orders (tables: products)
Question: What are the names of Hardware product with prices above the average price of Hardware products.
SQL: SELECT product_name FROM Products WHERE product_type_code  =  "Hardware" AND product_price  >  (SELECT avg(product_price) FROM Products WHERE product_type_code  =  "Hardware")
Database: customers_and_orders (tables: products)
Question: What is the name of the most expensive product with type Clothes?
SQL: SELECT product_name FROM Products WHERE product_type_code  =  "Clothes" ORDER BY product_price DESC LIMIT 1
Database: customers_and_orders (tables: products)
Question: Give the name of the most expensive Clothes product.
SQL: SELECT product_name FROM Products WHERE product_type_code  =  "Clothes" ORDER BY product_price DESC LIMIT 1
Database: customers_and_orders (tables: products)
Question: What is the product id and product name for the cheapest Hardware type product?
SQL: SELECT product_id ,  product_name FROM Products WHERE product_type_code  =  "Hardware" ORDER BY product_price ASC LIMIT 1
Database: customers_and_orders (tables: products)
Question: Give the id and name of the cheapest Hardware product.
SQL: SELECT product_id ,  product_name FROM Products WHERE product_type_code  =  "Hardware" ORDER BY product_price ASC LIMIT 1
Database: customers_and_orders (tables: products)
Question: List all product names in descending order of price.
SQL: SELECT product_name FROM Products ORDER BY product_price DESC
Database: customers_and_orders (tables: products)
Question: What are the names of the products, sorted by descending price?
SQL: SELECT product_name FROM Products ORDER BY product_price DESC
Database: customers_and_orders (tables: products)
Question: Show all hardware type products in ascending order of price.
SQL: SELECT product_name FROM Products WHERE product_type_code  =  "Hardware" ORDER BY product_price ASC
Database: customers_and_orders (tables: products)
Question: What are the names of all Hardware products, sorted by price ascending?
SQL: SELECT product_name FROM Products WHERE product_type_code  =  "Hardware" ORDER BY product_price ASC
Database: customers_and_orders (tables: products)
Question: List all product type codes and the number of products in each type.
SQL: SELECT product_type_code ,  count(*) FROM Products GROUP BY product_type_code
Database: customers_and_orders (tables: products)
Question: How many products are there for each product type?
SQL: SELECT product_type_code ,  count(*) FROM Products GROUP BY product_type_code
Database: customers_and_orders (tables: products)
Question: Show all product type codes and the average price for each type.
SQL: SELECT product_type_code ,  avg(product_price) FROM Products GROUP BY product_type_code
Database: customers_and_orders (tables: products)
Question: What is the average price of products for each product type?
SQL: SELECT product_type_code ,  avg(product_price) FROM Products GROUP BY product_type_code
Database: customers_and_orders (tables: products)
Question: What are the product type code with at least two products?
SQL: SELECT product_type_code FROM Products GROUP BY product_type_code HAVING count(*)  >=  2
Database: customers_and_orders (tables: products)
Question: Give the product type codes of product types that have two or more products.
SQL: SELECT product_type_code FROM Products GROUP BY product_type_code HAVING count(*)  >=  2
Database: customers_and_orders (tables: products)
Question: What is the product type code with most number of products?
SQL: SELECT product_type_code FROM Products GROUP BY product_type_code ORDER BY count(*) DESC LIMIT 1
Database: customers_and_orders (tables: products)
Question: What is the most frequent product type code?
SQL: SELECT product_type_code FROM Products GROUP BY product_type_code ORDER BY count(*) DESC LIMIT 1
Database: customers_and_orders (tables: customers)
Question: How many customers do we have?
SQL: SELECT count(*) FROM Customers
Database: customers_and_orders (tables: customers)
Question: Count the number of customers.
SQL: SELECT count(*) FROM Customers
Database: customers_and_orders (tables: customers)
Question: Show all customer ids and customer names.
SQL: SELECT customer_id ,  customer_name FROM Customers
Database: customers_and_orders (tables: customers)
Question: What are the ids and names of all customers?
SQL: SELECT customer_id ,  customer_name FROM Customers
Database: customers_and_orders (tables: customers)
Question: What is the customer address, customer phone, and customer email for Jeromy?
SQL: SELECT customer_address ,  customer_phone ,  customer_email FROM Customers WHERE customer_name  =  "Jeromy"
Database: customers_and_orders (tables: customers)
Question: Give the address, phone, and email for customers with the name Jeromy.
SQL: SELECT customer_address ,  customer_phone ,  customer_email FROM Customers WHERE customer_name  =  "Jeromy"
Database: customers_and_orders (tables: customers)
Question: Show all payment method codes and the number of customers in each code.
SQL: SELECT payment_method_code ,  count(*) FROM Customers GROUP BY payment_method_code
Database: customers_and_orders (tables: customers)
Question: How many customers use each payment method?
SQL: SELECT payment_method_code ,  count(*) FROM Customers GROUP BY payment_method_code
Database: customers_and_orders (tables: customers)
Question: What is the payment method code used by most number of customers?
SQL: SELECT payment_method_code FROM Customers GROUP BY payment_method_code ORDER BY count(*) DESC LIMIT 1
Database: customers_and_orders (tables: customers)
Question: Give the code of the payment method that is most commonly used.
SQL: SELECT payment_method_code FROM Customers GROUP BY payment_method_code ORDER BY count(*) DESC LIMIT 1
Database: customers_and_orders (tables: customers)
Question: Show all customer names with the  payment method code used by least number of customers.
SQL: SELECT customer_name FROM Customers WHERE payment_method_code  =  ( SELECT payment_method_code FROM Customers GROUP BY payment_method_code ORDER BY count(*) ASC LIMIT 1)
Database: customers_and_orders (tables: customers)
Question: What are the names of customers who use the least common payment method?
SQL: SELECT customer_name FROM Customers WHERE payment_method_code  =  ( SELECT payment_method_code FROM Customers GROUP BY payment_method_code ORDER BY count(*) ASC LIMIT 1)
Database: customers_and_orders (tables: customers)
Question: What is the payment method and customer number for customer named Jeromy?
SQL: SELECT payment_method_code ,  customer_number FROM Customers WHERE customer_name  =  "Jeromy"
Database: customers_and_orders (tables: customers)
Question: Give the payment method code and customer number corresponding to the customer named Jeromy.
SQL: SELECT payment_method_code ,  customer_number FROM Customers WHERE customer_name  =  "Jeromy"
Database: customers_and_orders (tables: customers)
Question: What are the distinct payment methods used by customers?
SQL: SELECT DISTINCT payment_method_code FROM Customers
Database: customers_and_orders (tables: customers)
Question: Give the different payment method codes that customers use.
SQL: SELECT DISTINCT payment_method_code FROM Customers
Database: customers_and_orders (tables: products)
Question: Show the id and the product type for all products, order by product name.
SQL: SELECT product_id ,  product_type_code FROM Products ORDER BY product_name
Database: customers_and_orders (tables: products)
Question: What are the ids and product types for all products, sorted alphabetically by product name?
SQL: SELECT product_id ,  product_type_code FROM Products ORDER BY product_name
Database: customers_and_orders (tables: products)
Question: What is the product type with least number of products?
SQL: SELECT product_type_code FROM Products GROUP BY product_type_code ORDER BY count(*) ASC LIMIT 1
Database: customers_and_orders (tables: products)
Question: What is the code of the product type that is least common?
SQL: SELECT product_type_code FROM Products GROUP BY product_type_code ORDER BY count(*) ASC LIMIT 1
Database: customers_and_orders (tables: customer_orders)
Question: How many customer orders do we have?
SQL: SELECT count(*) FROM Customer_orders
Database: customers_and_orders (tables: customer_orders)
Question: Count the number of customer orders.
SQL: SELECT count(*) FROM Customer_orders
Database: customers_and_orders (tables: customer_orders, customers)
Question: Show the order ids, order dates, and order status codes for all orders by customer Jeromy.
SQL: SELECT order_id ,  order_date ,  order_status_code FROM Customer_orders AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.customer_name  =  "Jeromy"
Database: customers_and_orders (tables: customer_orders, customers)
Question: What were the ids, dates, and status codes for orders made by Jeromy?
SQL: SELECT order_id ,  order_date ,  order_status_code FROM Customer_orders AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.customer_name  =  "Jeromy"
Database: customers_and_orders (tables: customer_orders, customers)
Question: Show all customer names, ids and the number of orders by each customer.
SQL: SELECT T2.customer_name ,  T1.customer_id ,  count(*) FROM Customer_orders AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id
Database: customers_and_orders (tables: customer_orders, customers)
Question: What are the names, ids, and number of orders made for each customer?
SQL: SELECT T2.customer_name ,  T1.customer_id ,  count(*) FROM Customer_orders AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id
Database: customers_and_orders (tables: customer_orders, customers)
Question: What is the customer id, name, phone, and email for the customer with most orders?
SQL: SELECT T1.customer_id ,  T2.customer_name ,  T2.customer_phone ,  T2.customer_email FROM Customer_orders AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id ORDER BY count(*) DESC LIMIT 1
Database: customers_and_orders (tables: customer_orders, customers)
Question: Give the id, name, phone, and email corresponding to the customer who made the most orders.
SQL: SELECT T1.customer_id ,  T2.customer_name ,  T2.customer_phone ,  T2.customer_email FROM Customer_orders AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id ORDER BY count(*) DESC LIMIT 1
Database: customers_and_orders (tables: customer_orders)
Question: Show all order status and the number of orders in each status.
SQL: SELECT order_status_code ,  count(*) FROM Customer_orders GROUP BY order_status_code
Database: customers_and_orders (tables: customer_orders)
Question: How many orders have each order status code?
SQL: SELECT order_status_code ,  count(*) FROM Customer_orders GROUP BY order_status_code
Database: customers_and_orders (tables: customer_orders)
Question: What is the order status code that is most common?
SQL: SELECT order_status_code FROM Customer_orders GROUP BY order_status_code ORDER BY count(*) DESC LIMIT 1