Database: customers_and_invoices
Schema: Customers: customer_id (number), customer_first_name (text), customer_middle_initial (text), customer_last_name (text), gender (text), email_address (text), login_name (text), login_password (text), phone_number (text), town_city (text), state_county_province (text), country (text) | Orders: order_id (number), customer_id (number), date_order_placed (time), order_details (text) | Invoices: invoice_number (number), order_id (number), invoice_date (time) | Accounts: account_id (number), customer_id (number), date_account_opened (time), account_name (text), other_account_details (text) | Product_Categories: production_type_code (text), product_type_description (text), vat_rating (number) | Products: product_id (number), parent_product_id (number), production_type_code (text), unit_price (number), product_name (text), product_color (text), product_size (text) | Financial_Transactions: transaction_id (number), account_id (number), invoice_number (number), transaction_type (text), transaction_date (time), transaction_amount (number), transaction_comment (text), other_transaction_details (text) | Order_Items: order_item_id (number), order_id (number), product_id (number), product_quantity (text), other_order_item_details (text) | Invoice_Line_Items: order_item_id (number), invoice_number (number), product_id (number), product_title (text), product_quantity (text), product_price (number), derived_product_cost (number), derived_vat_payable (number), derived_total_cost (number)

Question: Show the number of accounts.
SQL: SELECT count(*) FROM Accounts

Question: How many accounts are there?
SQL: SELECT count(*) FROM Accounts

Question: How many customers have opened an account?
SQL: SELECT count(DISTINCT customer_id) FROM Accounts

Question: Count the number of customers who have an account.
SQL: SELECT count(DISTINCT customer_id) FROM 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

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

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'

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'

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"

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"

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"

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"

Question: How many customers don't have an account?
SQL: SELECT count(*) FROM Customers WHERE customer_id NOT IN (SELECT customer_id FROM Accounts)

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)

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

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

Question: Show customer ids who don't have an account.
SQL: SELECT customer_id FROM Customers EXCEPT SELECT customer_id FROM Accounts

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

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

Question: Count the number of accounts corresponding to each customer id.
SQL: SELECT count(*) ,  customer_id FROM Accounts GROUP BY customer_id

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

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

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

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

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

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

Question: Show the number of customers.
SQL: SELECT count(*) FROM Customers

Question: Count the number of customers.
SQL: SELECT count(*) FROM Customers

Question: Show the number of customers for each gender.
SQL: SELECT gender ,  count(*) FROM Customers GROUP BY gender

Question: How many customers are there of each gender?
SQL: SELECT gender ,  count(*) FROM Customers GROUP BY gender

Question: How many transactions do we have?
SQL: SELECT count(*) FROM Financial_transactions

Question: Count the number of transactions.
SQL: SELECT count(*) FROM Financial_transactions

Question: How many transaction does each account have? Show the number and account id.
SQL: SELECT count(*) ,  account_id FROM Financial_transactions

Question: Count the number of financial transactions that correspond to each account id.
SQL: SELECT count(*) ,  account_id FROM 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"

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"

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

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

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)

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)

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

Question: What are total transaction amounts for each transaction type?
SQL: SELECT transaction_type ,  sum(transaction_amount) FROM Financial_transactions GROUP BY transaction_type

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

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

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

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

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

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

Question: Show all product sizes.
SQL: SELECT DISTINCT product_size FROM Products

Question: What are the different product sizes?
SQL: SELECT DISTINCT product_size FROM Products

Question: Show all product colors.
SQL: SELECT DISTINCT product_color FROM Products

Question: What are the different product colors?
SQL: SELECT DISTINCT product_color FROM Products

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

Question: How many transactions correspond to each invoice number?
SQL: SELECT invoice_number ,  count(*) FROM Financial_transactions GROUP BY invoice_number

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

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

Question: How many invoices do we have?
SQL: SELECT count(*) FROM Invoices

Question: Count the number of invoices.
SQL: SELECT count(*) FROM 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

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

Question: Show the order ids and the number of invoices for each order.
SQL: SELECT order_id ,  count(*) FROM Invoices GROUP BY order_id

Question: How many invoices correspond to each order id?
SQL: SELECT order_id ,  count(*) FROM Invoices GROUP BY order_id

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

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

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

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

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

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

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

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

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

Question: How many order items correspond to each order id?
SQL: SELECT order_id ,  count(*) FROM Order_items GROUP BY order_id

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

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

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

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

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

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

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

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

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 )

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 )