target-benchmark/spider-queries huggingface dataset:
Database: insurance_and_eClaims (tables: policies, customers)
Question: Which customer is associated with the latest policy?
SQL: SELECT t2.customer_details FROM policies AS t1 JOIN customers AS t2 ON t1.customer_id  =  t2.customer_id WHERE t1.start_date  =  (SELECT max(start_date) FROM policies)
Database: insurance_and_eClaims (tables: policies, customers)
Question: Find the customer who started a policy most recently.
SQL: SELECT t2.customer_details FROM policies AS t1 JOIN customers AS t2 ON t1.customer_id  =  t2.customer_id WHERE t1.start_date  =  (SELECT max(start_date) FROM policies)
Database: customers_and_invoices (tables: accounts)
Question: Show the number of accounts.
SQL: SELECT count(*) FROM Accounts
Database: customers_and_invoices (tables: accounts)
Question: How many accounts are there?
SQL: SELECT count(*) FROM Accounts
Database: customers_and_invoices (tables: accounts)
Question: How many customers have opened an account?
SQL: SELECT count(DISTINCT customer_id) FROM Accounts
Database: customers_and_invoices (tables: accounts)
Question: Count the number of customers who have an account.
SQL: SELECT count(DISTINCT customer_id) FROM Accounts
Database: customers_and_invoices (tables: accounts)
Question: Show the id, the date of account opened, the account name, and other account detail for all accounts.
SQL: SELECT account_id ,  date_account_opened ,  account_name ,  other_account_details FROM Accounts
Database: customers_and_invoices (tables: accounts)
Question: What are the ids, date opened, name, and other details for all accounts?
SQL: SELECT account_id ,  date_account_opened ,  account_name ,  other_account_details FROM Accounts
Database: customers_and_invoices (tables: accounts, customers)
Question: Show the id, the account name, and other account details for all accounts by the customer with first name 'Meaghan'.
SQL: SELECT T1.account_id ,  T1.date_account_opened ,  T1.account_name ,  T1.other_account_details FROM Accounts AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.customer_first_name  =  'Meaghan'
Database: customers_and_invoices (tables: accounts, customers)
Question: What are the ids, names, dates of opening, and other details for accounts corresponding to the customer with the first name "Meaghan"?
SQL: SELECT T1.account_id ,  T1.date_account_opened ,  T1.account_name ,  T1.other_account_details FROM Accounts AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.customer_first_name  =  'Meaghan'
Database: customers_and_invoices (tables: accounts, customers)
Question: Show the account name and other account detail for all accounts by the customer with first name Meaghan and last name Keeling.
SQL: SELECT T1.account_name ,  T1.other_account_details FROM Accounts AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.customer_first_name  =  "Meaghan" AND T2.customer_last_name  =  "Keeling"
Database: customers_and_invoices (tables: accounts, customers)
Question: What are the names and other details for accounts corresponding to the customer named Meaghan Keeling?
SQL: SELECT T1.account_name ,  T1.other_account_details FROM Accounts AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.customer_first_name  =  "Meaghan" AND T2.customer_last_name  =  "Keeling"
Database: customers_and_invoices (tables: accounts, customers)
Question: Show the first name and last name for the customer with account name 900.
SQL: SELECT T2.customer_first_name ,  T2.customer_last_name FROM Accounts AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T1.account_name  =  "900"
Database: customers_and_invoices (tables: accounts, customers)
Question: What are the full names of customers with the account name 900?
SQL: SELECT T2.customer_first_name ,  T2.customer_last_name FROM Accounts AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T1.account_name  =  "900"
Database: customers_and_invoices (tables: accounts, customers)
Question: How many customers don't have an account?
SQL: SELECT count(*) FROM Customers WHERE customer_id NOT IN (SELECT customer_id FROM Accounts)
Database: customers_and_invoices (tables: accounts, customers)
Question: Count the number of customers who do not have an account.
SQL: SELECT count(*) FROM Customers WHERE customer_id NOT IN (SELECT customer_id FROM Accounts)
Database: customers_and_invoices (tables: accounts, customers)
Question: Show the unique first names, last names, and phone numbers for all customers with any account.
SQL: SELECT DISTINCT T1.customer_first_name ,  T1.customer_last_name ,  T1.phone_number FROM Customers AS T1 JOIN Accounts AS T2 ON T1.customer_id  =  T2.customer_id
Database: customers_and_invoices (tables: accounts, customers)
Question: What are the distinct first names, last names, and phone numbers for customers with accounts?
SQL: SELECT DISTINCT T1.customer_first_name ,  T1.customer_last_name ,  T1.phone_number FROM Customers AS T1 JOIN Accounts AS T2 ON T1.customer_id  =  T2.customer_id
Database: customers_and_invoices (tables: accounts, customers)
Question: Show customer ids who don't have an account.
SQL: SELECT customer_id FROM Customers EXCEPT SELECT customer_id FROM Accounts
Database: customers_and_invoices (tables: accounts, customers)
Question: What are the customer ids for customers who do not have an account?
SQL: SELECT customer_id FROM Customers EXCEPT SELECT customer_id FROM Accounts
Database: customers_and_invoices (tables: accounts)
Question: How many accounts does each customer have? List the number and customer id.
SQL: SELECT count(*) ,  customer_id FROM Accounts GROUP BY customer_id
Database: customers_and_invoices (tables: accounts)
Question: Count the number of accounts corresponding to each customer id.
SQL: SELECT count(*) ,  customer_id FROM Accounts GROUP BY customer_id
Database: customers_and_invoices (tables: accounts, customers)
Question: What is the customer id, first and last name with most number of accounts.
SQL: SELECT T1.customer_id ,  T2.customer_first_name ,  T2.customer_last_name FROM Accounts 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_invoices (tables: accounts, customers)
Question: Return the id and full name of the customer with the most accounts.
SQL: SELECT T1.customer_id ,  T2.customer_first_name ,  T2.customer_last_name FROM Accounts 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_invoices (tables: accounts, customers)
Question: Show id, first name and last name for all customers and the number of accounts.
SQL: SELECT T1.customer_id ,  T2.customer_first_name ,  T2.customer_last_name ,  count(*) FROM Accounts AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id
Database: customers_and_invoices (tables: accounts, customers)
Question: What are the the full names and ids for all customers, and how many accounts does each have?
SQL: SELECT T1.customer_id ,  T2.customer_first_name ,  T2.customer_last_name ,  count(*) FROM Accounts AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id
Database: customers_and_invoices (tables: accounts, customers)
Question: Show first name and id for all customers with at least 2 accounts.
SQL: SELECT T2.customer_first_name ,  T1.customer_id FROM Accounts AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id HAVING count(*)  >=  2
Database: customers_and_invoices (tables: accounts, customers)
Question: What are the first names and ids for customers who have two or more accounts?
SQL: SELECT T2.customer_first_name ,  T1.customer_id FROM Accounts AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id HAVING count(*)  >=  2
Database: customers_and_invoices (tables: customers)
Question: Show the number of customers.
SQL: SELECT count(*) FROM Customers
Database: customers_and_invoices (tables: customers)
Question: Count the number of customers.
SQL: SELECT count(*) FROM Customers
Database: customers_and_invoices (tables: customers)
Question: Show the number of customers for each gender.
SQL: SELECT gender ,  count(*) FROM Customers GROUP BY gender
Database: customers_and_invoices (tables: customers)
Question: How many customers are there of each gender?
SQL: SELECT gender ,  count(*) FROM Customers GROUP BY gender
Database: customers_and_invoices (tables: financial_transactions)
Question: How many transactions do we have?
SQL: SELECT count(*) FROM Financial_transactions
Database: customers_and_invoices (tables: financial_transactions)
Question: Count the number of transactions.
SQL: SELECT count(*) FROM Financial_transactions
Database: customers_and_invoices (tables: financial_transactions)
Question: How many transaction does each account have? Show the number and account id.
SQL: SELECT count(*) ,  account_id FROM Financial_transactions
Database: customers_and_invoices (tables: financial_transactions)
Question: Count the number of financial transactions that correspond to each account id.
SQL: SELECT count(*) ,  account_id FROM Financial_transactions
Database: customers_and_invoices (tables: accounts, financial_transactions)
Question: How many transaction does account with name 337 have?
SQL: SELECT count(*) FROM Financial_transactions AS T1 JOIN Accounts AS T2 ON T1.account_id  =  T2.account_id WHERE T2.account_name  =  "337"
Database: customers_and_invoices (tables: accounts, financial_transactions)
Question: Count the number of financial transactions that the account with the name 337 has.
SQL: SELECT count(*) FROM Financial_transactions AS T1 JOIN Accounts AS T2 ON T1.account_id  =  T2.account_id WHERE T2.account_name  =  "337"
Database: customers_and_invoices (tables: financial_transactions)
Question: What is the average, minimum, maximum, and total transaction amount?
SQL: SELECT avg(transaction_amount) ,  min(transaction_amount) ,  max(transaction_amount) ,   sum(transaction_amount) FROM Financial_transactions
Database: customers_and_invoices (tables: financial_transactions)
Question: Return the average, minimum, maximum, and total transaction amounts.
SQL: SELECT avg(transaction_amount) ,  min(transaction_amount) ,  max(transaction_amount) ,   sum(transaction_amount) FROM Financial_transactions
Database: customers_and_invoices (tables: financial_transactions)
Question: Show ids for all transactions whose amounts are greater than the average.
SQL: SELECT transaction_id FROM Financial_transactions WHERE transaction_amount  >  (SELECT avg(transaction_amount) FROM Financial_transactions)
Database: customers_and_invoices (tables: financial_transactions)
Question: What are the ids for transactions that have an amount greater than the average amount of a transaction?
SQL: SELECT transaction_id FROM Financial_transactions WHERE transaction_amount  >  (SELECT avg(transaction_amount) FROM Financial_transactions)
Database: customers_and_invoices (tables: financial_transactions)
Question: Show the transaction types and the total amount of transactions.
SQL: SELECT transaction_type ,  sum(transaction_amount) FROM Financial_transactions GROUP BY transaction_type
Database: customers_and_invoices (tables: financial_transactions)
Question: What are total transaction amounts for each transaction type?
SQL: SELECT transaction_type ,  sum(transaction_amount) FROM Financial_transactions GROUP BY transaction_type
Database: customers_and_invoices (tables: accounts, financial_transactions)
Question: Show the account name, id and the number of transactions for each account.
SQL: SELECT T2.account_name ,  T1.account_id ,  count(*) FROM Financial_transactions AS T1 JOIN Accounts AS T2 ON T1.account_id  =  T2.account_id GROUP BY T1.account_id
Database: customers_and_invoices (tables: accounts, financial_transactions)
Question: Return the names and ids of each account, as well as the number of transactions.
SQL: SELECT T2.account_name ,  T1.account_id ,  count(*) FROM Financial_transactions AS T1 JOIN Accounts AS T2 ON T1.account_id  =  T2.account_id GROUP BY T1.account_id
Database: customers_and_invoices (tables: financial_transactions)
Question: Show the account id with most number of transactions.
SQL: SELECT account_id FROM Financial_transactions GROUP BY account_id ORDER BY count(*) DESC LIMIT 1
Database: customers_and_invoices (tables: financial_transactions)
Question: What is the id of the account with the most transactions?
SQL: SELECT account_id FROM Financial_transactions GROUP BY account_id ORDER BY count(*) DESC LIMIT 1
Database: customers_and_invoices (tables: accounts, financial_transactions)
Question: Show the account id and name with at least 4 transactions.
SQL: SELECT T1.account_id ,  T2.account_name FROM Financial_transactions AS T1 JOIN Accounts AS T2 ON T1.account_id  =  T2.account_id GROUP BY T1.account_id HAVING count(*)  >=  4
Database: customers_and_invoices (tables: accounts, financial_transactions)
Question: What are the ids and names of accounts with 4 or more transactions?
SQL: SELECT T1.account_id ,  T2.account_name FROM Financial_transactions AS T1 JOIN Accounts AS T2 ON T1.account_id  =  T2.account_id GROUP BY T1.account_id HAVING count(*)  >=  4
Database: customers_and_invoices (tables: products)
Question: Show all product sizes.
SQL: SELECT DISTINCT product_size FROM Products
Database: customers_and_invoices (tables: products)
Question: What are the different product sizes?
SQL: SELECT DISTINCT product_size FROM Products
Database: customers_and_invoices (tables: products)
Question: Show all product colors.
SQL: SELECT DISTINCT product_color FROM Products
Database: customers_and_invoices (tables: products)
Question: What are the different product colors?
SQL: SELECT DISTINCT product_color FROM Products
Database: customers_and_invoices (tables: financial_transactions)
Question: Show the invoice number and the number of transactions for each invoice.
SQL: SELECT invoice_number ,  count(*) FROM Financial_transactions GROUP BY invoice_number
Database: customers_and_invoices (tables: financial_transactions)
Question: How many transactions correspond to each invoice number?
SQL: SELECT invoice_number ,  count(*) FROM Financial_transactions GROUP BY invoice_number
Database: customers_and_invoices (tables: financial_transactions, invoices)
Question: What is the invoice number and invoice date for the invoice with most number of transactions?
SQL: SELECT T2.invoice_number ,  T2.invoice_date FROM Financial_transactions AS T1 JOIN Invoices AS T2 ON T1.invoice_number  =  T2.invoice_number GROUP BY T1.invoice_number ORDER BY count(*) DESC LIMIT 1
Database: customers_and_invoices (tables: financial_transactions, invoices)
Question: What is the invoice number and invoice date corresponding to the invoice with the greatest number of transactions?
SQL: SELECT T2.invoice_number ,  T2.invoice_date FROM Financial_transactions AS T1 JOIN Invoices AS T2 ON T1.invoice_number  =  T2.invoice_number GROUP BY T1.invoice_number ORDER BY count(*) DESC LIMIT 1
Database: customers_and_invoices (tables: invoices)
Question: How many invoices do we have?
SQL: SELECT count(*) FROM Invoices
Database: customers_and_invoices (tables: invoices)
Question: Count the number of invoices.
SQL: SELECT count(*) FROM Invoices
Database: customers_and_invoices (tables: orders, invoices)
Question: Show invoice dates and order id and details for all invoices.
SQL: SELECT T1.invoice_date ,  T1.order_id ,  T2.order_details FROM Invoices AS T1 JOIN Orders AS T2 ON T1.order_id  =  T2.order_id
Database: customers_and_invoices (tables: orders, invoices)
Question: What are the invoice dates, order ids, and order details for all invoices?
SQL: SELECT T1.invoice_date ,  T1.order_id ,  T2.order_details FROM Invoices AS T1 JOIN Orders AS T2 ON T1.order_id  =  T2.order_id
Database: customers_and_invoices (tables: invoices)
Question: Show the order ids and the number of invoices for each order.
SQL: SELECT order_id ,  count(*) FROM Invoices GROUP BY order_id
Database: customers_and_invoices (tables: invoices)
Question: How many invoices correspond to each order id?
SQL: SELECT order_id ,  count(*) FROM Invoices GROUP BY order_id
Database: customers_and_invoices (tables: orders, invoices)
Question: What is the order id and order details for the order more than two invoices.
SQL: SELECT T2.order_id ,  T2.order_details FROM Invoices AS T1 JOIN Orders AS T2 ON T1.order_id  =  T2.order_id GROUP BY T2.order_id HAVING count(*)  >  2
Database: customers_and_invoices (tables: orders, invoices)
Question: Return the order ids and details for orderes with two or more invoices.
SQL: SELECT T2.order_id ,  T2.order_details FROM Invoices AS T1 JOIN Orders AS T2 ON T1.order_id  =  T2.order_id GROUP BY T2.order_id HAVING count(*)  >  2
Database: customers_and_invoices (tables: orders, customers)
Question: What is the customer last name, id and phone number with most number of orders?
SQL: SELECT T2.customer_last_name ,  T1.customer_id ,  T2.phone_number FROM 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_invoices (tables: orders, customers)
Question: Return the last name, id and phone number of the customer who has made the greatest number of orders.
SQL: SELECT T2.customer_last_name ,  T1.customer_id ,  T2.phone_number FROM 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_invoices (tables: order_items, products)
Question: Show all product names without an order.
SQL: SELECT product_name FROM Products EXCEPT SELECT T1.product_name FROM Products AS T1 JOIN Order_items AS T2 ON T1.product_id  =  T2.product_id
Database: customers_and_invoices (tables: order_items, products)
Question: What are the names of products that have never been ordered?
SQL: SELECT product_name FROM Products EXCEPT SELECT T1.product_name FROM Products AS T1 JOIN Order_items AS T2 ON T1.product_id  =  T2.product_id
Database: customers_and_invoices (tables: order_items, products)
Question: Show all product names and the total quantity ordered for each product name.
SQL: SELECT T2.product_name ,  sum(T1.product_quantity) FROM Order_items AS T1 JOIN Products AS T2 ON T1.product_id  =  T2.product_id GROUP BY T2.product_name
Database: customers_and_invoices (tables: order_items, products)
Question: What are the different product names, and what is the sum of quantity ordered for each product?
SQL: SELECT T2.product_name ,  sum(T1.product_quantity) FROM Order_items AS T1 JOIN Products AS T2 ON T1.product_id  =  T2.product_id GROUP BY T2.product_name
Database: customers_and_invoices (tables: order_items)
Question: Show the order ids and the number of items in each order.
SQL: SELECT order_id ,  count(*) FROM Order_items GROUP BY order_id
Database: customers_and_invoices (tables: order_items)
Question: How many order items correspond to each order id?
SQL: SELECT order_id ,  count(*) FROM Order_items GROUP BY order_id
Database: customers_and_invoices (tables: order_items)
Question: Show the product ids and the number of unique orders containing each product.
SQL: SELECT product_id ,  count(DISTINCT order_id) FROM Order_items GROUP BY product_id
Database: customers_and_invoices (tables: order_items)
Question: How many distinct order ids correspond to each product?
SQL: SELECT product_id ,  count(DISTINCT order_id) FROM Order_items GROUP BY product_id
Database: customers_and_invoices (tables: order_items, orders, products)
Question: Show all product names and the number of customers having an order on each product.
SQL: SELECT T2.product_name ,  count(*) FROM Order_items AS T1 JOIN Products AS T2 ON T1.product_id  =  T2.product_id JOIN Orders AS T3 ON T3.order_id  =  T1.order_id GROUP BY T2.product_name
Database: customers_and_invoices (tables: order_items, orders, products)
Question: What are teh names of the different products, as well as the number of customers who have ordered each product.
SQL: SELECT T2.product_name ,  count(*) FROM Order_items AS T1 JOIN Products AS T2 ON T1.product_id  =  T2.product_id JOIN Orders AS T3 ON T3.order_id  =  T1.order_id GROUP BY T2.product_name
Database: customers_and_invoices (tables: order_items)
Question: Show order ids and the number of products in each order.
SQL: SELECT order_id ,  count(DISTINCT product_id) FROM Order_items GROUP BY order_id
Database: customers_and_invoices (tables: order_items)
Question: How many different products correspond to each order id?
SQL: SELECT order_id ,  count(DISTINCT product_id) FROM Order_items GROUP BY order_id
Database: customers_and_invoices (tables: order_items)
Question: Show order ids and the total quantity in each order.
SQL: SELECT order_id ,  sum(product_quantity) FROM Order_items GROUP BY order_id
Database: customers_and_invoices (tables: order_items)
Question: Give the order ids for all orders, as well as the total product quantity in each.
SQL: SELECT order_id ,  sum(product_quantity) FROM Order_items GROUP BY order_id
Database: customers_and_invoices (tables: order_items, products)
Question: How many products were not included in any order?
SQL: SELECT count(*) FROM products WHERE product_id NOT IN ( SELECT product_id FROM Order_items )
Database: customers_and_invoices (tables: order_items, products)
Question: Count the number of products that were never ordered.
SQL: SELECT count(*) FROM products WHERE product_id NOT IN ( SELECT product_id FROM Order_items )
Database: wedding (tables: church)
Question: How many churches opened before 1850 are there?
SQL: SELECT count(*) FROM Church WHERE Open_Date  <  1850
Database: wedding (tables: church)
Question: Show the name, open date, and organizer for all churches.
SQL: SELECT name ,  open_date ,  organized_by FROM Church
Database: wedding (tables: church)
Question: List all church names in descending order of opening date.
SQL: SELECT name FROM church ORDER BY open_date DESC
Database: wedding (tables: church)
Question: Show the opening year in whcih at least two churches opened.
SQL: SELECT open_date FROM church GROUP BY open_date HAVING count(*)  >=  2
Database: wedding (tables: church)
Question: Show the organizer and name for churches that opened between 1830 and 1840.
SQL: SELECT organized_by ,  name FROM church WHERE open_date BETWEEN 1830 AND 1840
Database: wedding (tables: church)
Question: Show all opening years and the number of churches that opened in that year.
SQL: SELECT open_date ,  count(*) FROM church GROUP BY open_date
Database: wedding (tables: church)
Question: Show the name and opening year for three churches that opened most recently.
SQL: SELECT name ,  open_date FROM church ORDER BY open_date DESC LIMIT 3
Database: wedding (tables: people)
Question: How many female people are older than 30 in our record?
SQL: SELECT count(*) FROM people WHERE is_male  =  'F' AND age  >  30
Database: wedding (tables: people)
Question: Show the country where people older than 30 and younger than 25 are from.
SQL: SELECT country FROM people WHERE age  <  25 INTERSECT SELECT country FROM people WHERE age  >  30
Database: wedding (tables: people)
Question: Show the minimum, maximum, and average age for all people.
SQL: SELECT min(age) ,  max(age) ,  avg(age) FROM people
Database: wedding (tables: people)
Question: Show the name and country for all people whose age is smaller than the average.
SQL: SELECT name ,  country FROM people WHERE age  <  (SELECT avg(age) FROM people)
Database: wedding (tables: people, wedding)
Question: Show the pair of male and female names in all weddings after year 2014
SQL: SELECT T2.name ,  T3.name FROM wedding AS T1 JOIN people AS T2 ON T1.male_id  =  T2.people_id JOIN people AS T3 ON T1.female_id  =  T3.people_id WHERE T1.year  >  2014
Database: wedding (tables: people, wedding)
Question: Show the name and age for all male people who don't have a wedding.
SQL: SELECT name ,  age FROM people WHERE is_male  =  'T' AND people_id NOT IN (SELECT male_id FROM wedding)