target-benchmark/spider-queries huggingface dataset:
Database: journal_committee (tables: editor)
Question: What are the names and ages of editors?
SQL: SELECT Name ,  Age FROM editor
Database: journal_committee (tables: editor)
Question: List the names of editors who are older than 25.
SQL: SELECT Name FROM editor WHERE Age  >  25
Database: journal_committee (tables: editor)
Question: Show the names of editors of age either 24 or 25.
SQL: SELECT Name FROM editor WHERE Age  =  24 OR Age  =  25
Database: journal_committee (tables: editor)
Question: What is the name of the youngest editor?
SQL: SELECT Name FROM editor ORDER BY Age ASC LIMIT 1
Database: journal_committee (tables: editor)
Question: What are the different ages of editors? Show each age along with the number of editors of that age.
SQL: SELECT Age ,  COUNT(*) FROM editor GROUP BY Age
Database: journal_committee (tables: editor)
Question: Please show the most common age of editors.
SQL: SELECT Age FROM editor GROUP BY Age ORDER BY COUNT(*) DESC LIMIT 1
Database: journal_committee (tables: journal)
Question: Show the distinct themes of journals.
SQL: SELECT DISTINCT Theme FROM journal
Database: journal_committee (tables: journal, journal_committee, editor)
Question: Show the names of editors and the theme of journals for which they serve on committees.
SQL: SELECT T2.Name ,  T3.Theme FROM journal_committee AS T1 JOIN editor AS T2 ON T1.Editor_ID  =  T2.Editor_ID JOIN journal AS T3 ON T1.Journal_ID  =  T3.Journal_ID
Database: journal_committee (tables: journal, journal_committee, editor)
Question: For each journal_committee, find the editor name and the journal theme.
SQL: SELECT T2.Name ,  T3.Theme FROM journal_committee AS T1 JOIN editor AS T2 ON T1.Editor_ID  =  T2.Editor_ID JOIN journal AS T3 ON T1.Journal_ID  =  T3.Journal_ID
Database: journal_committee (tables: journal, journal_committee, editor)
Question: Show the names and ages of editors and the theme of journals for which they serve on committees, in ascending alphabetical order of theme.
SQL: SELECT T2.Name ,  T2.age ,  T3.Theme FROM journal_committee AS T1 JOIN editor AS T2 ON T1.Editor_ID  =  T2.Editor_ID JOIN journal AS T3 ON T1.Journal_ID  =  T3.Journal_ID ORDER BY T3.Theme ASC
Database: journal_committee (tables: journal, journal_committee, editor)
Question: Show the names of editors that are on the committee of journals with sales bigger than 3000.
SQL: SELECT T2.Name FROM journal_committee AS T1 JOIN editor AS T2 ON T1.Editor_ID  =  T2.Editor_ID JOIN journal AS T3 ON T1.Journal_ID  =  T3.Journal_ID WHERE T3.Sales  >  3000
Database: journal_committee (tables: journal_committee, editor)
Question: Show the id, name of each editor and the number of journal committees they are on.
SQL: SELECT T1.editor_id ,  T1.Name ,  COUNT(*) FROM editor AS T1 JOIN journal_committee AS T2 ON T1.Editor_ID  =  T2.Editor_ID GROUP BY T1.editor_id
Database: journal_committee (tables: journal_committee, editor)
Question: Show the names of editors that are on at least two journal committees.
SQL: SELECT T1.Name FROM editor AS T1 JOIN journal_committee AS T2 ON T1.Editor_ID  =  T2.Editor_ID GROUP BY T1.Name HAVING COUNT(*)  >=  2
Database: journal_committee (tables: journal_committee, editor)
Question: List the names of editors that are not on any journal committee.
SQL: SELECT Name FROM editor WHERE editor_id NOT IN (SELECT editor_id FROM journal_committee)
Database: journal_committee (tables: journal, journal_committee)
Question: List the date, theme and sales of the journal which did not have any of the listed editors serving on committee.
SQL: SELECT date ,  theme ,  sales FROM journal EXCEPT SELECT T1.date ,  T1.theme ,  T1.sales FROM journal AS T1 JOIN journal_committee AS T2 ON T1.journal_ID  =  T2.journal_ID
Database: journal_committee (tables: journal, journal_committee)
Question: What is the average sales of the journals that have an editor whose work type is 'Photo'?
SQL: SELECT avg(T1.sales) FROM journal AS T1 JOIN journal_committee AS T2 ON T1.journal_ID  =  T2.journal_ID WHERE T2.work_type  =  'Photo'
Database: customers_card_transactions (tables: accounts)
Question: How many accounts do we have?
SQL: SELECT count(*) FROM Accounts
Database: customers_card_transactions (tables: accounts)
Question: Count the number of accounts.
SQL: SELECT count(*) FROM Accounts
Database: customers_card_transactions (tables: accounts)
Question: Show ids, customer ids, names for all accounts.
SQL: SELECT account_id ,  customer_id ,  account_name FROM Accounts
Database: customers_card_transactions (tables: accounts)
Question: What are the account ids, customer ids, and account names for all the accounts?
SQL: SELECT account_id ,  customer_id ,  account_name FROM Accounts
Database: customers_card_transactions (tables: accounts)
Question: Show other account details for account with name 338.
SQL: SELECT other_account_details FROM Accounts WHERE account_name  =  "338"
Database: customers_card_transactions (tables: accounts)
Question: What are the other account details for the account with the name 338?
SQL: SELECT other_account_details FROM Accounts WHERE account_name  =  "338"
Database: customers_card_transactions (tables: accounts, customers)
Question: What is the first name, last name, and phone of the customer with account name 162?
SQL: SELECT T2.customer_first_name ,  T2.customer_last_name ,  T2.customer_phone FROM Accounts AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T1.account_name  =  "162"
Database: customers_card_transactions (tables: accounts, customers)
Question: Give the full name and phone of the customer who has the account name 162.
SQL: SELECT T2.customer_first_name ,  T2.customer_last_name ,  T2.customer_phone FROM Accounts AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T1.account_name  =  "162"
Database: customers_card_transactions (tables: accounts, customers)
Question: How many accounts does the customer with first name Art and last name Turcotte have?
SQL: SELECT count(*) FROM Accounts AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.customer_first_name  =  "Art" AND T2.customer_last_name  =  "Turcotte"
Database: customers_card_transactions (tables: accounts, customers)
Question: Return the number of accounts that the customer with the first name Art and last name Turcotte has.
SQL: SELECT count(*) FROM Accounts AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.customer_first_name  =  "Art" AND T2.customer_last_name  =  "Turcotte"
Database: customers_card_transactions (tables: accounts)
Question: Show all customer ids and the number of accounts for each customer.
SQL: SELECT customer_id ,  count(*) FROM Accounts GROUP BY customer_id
Database: customers_card_transactions (tables: accounts)
Question: How many accounts are there for each customer id?
SQL: SELECT customer_id ,  count(*) FROM Accounts GROUP BY customer_id
Database: customers_card_transactions (tables: accounts)
Question: Show the customer id and number of accounts with most accounts.
SQL: SELECT customer_id ,  count(*) FROM Accounts GROUP BY customer_id ORDER BY count(*) DESC LIMIT 1
Database: customers_card_transactions (tables: accounts)
Question: What is the customer id of the customer with the most accounts, and how many accounts does this person have?
SQL: SELECT customer_id ,  count(*) FROM Accounts GROUP BY customer_id ORDER BY count(*) DESC LIMIT 1
Database: customers_card_transactions (tables: accounts, customers)
Question: What is the customer first, last name and id with least number of accounts.
SQL: SELECT T2.customer_first_name ,  T2.customer_last_name ,  T1.customer_id FROM Accounts AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id ORDER BY count(*) ASC LIMIT 1
Database: customers_card_transactions (tables: accounts, customers)
Question: Give the full name and customer id of the customer with the fewest accounts.
SQL: SELECT T2.customer_first_name ,  T2.customer_last_name ,  T1.customer_id FROM Accounts AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id ORDER BY count(*) ASC LIMIT 1
Database: customers_card_transactions (tables: accounts, customers)
Question: Show the number of all customers without an account.
SQL: SELECT count(*) FROM Customers WHERE customer_id NOT IN (SELECT customer_id FROM Accounts)
Database: customers_card_transactions (tables: accounts, customers)
Question: How many customers do not have an account?
SQL: SELECT count(*) FROM Customers WHERE customer_id NOT IN (SELECT customer_id FROM Accounts)
Database: customers_card_transactions (tables: accounts, customers)
Question: Show the first names and last names of customers without any account.
SQL: SELECT customer_first_name ,  customer_last_name FROM Customers EXCEPT SELECT T1.customer_first_name ,  T1.customer_last_name FROM Customers AS T1 JOIN Accounts AS T2 ON T1.customer_id  =  T2.customer_id
Database: customers_card_transactions (tables: accounts, customers)
Question: What are the full names of customers who do not have any accounts?
SQL: SELECT customer_first_name ,  customer_last_name FROM Customers EXCEPT SELECT T1.customer_first_name ,  T1.customer_last_name FROM Customers AS T1 JOIN Accounts AS T2 ON T1.customer_id  =  T2.customer_id
Database: customers_card_transactions (tables: accounts, customers)
Question: Show distinct first and last names for all customers with an account.
SQL: SELECT DISTINCT T1.customer_first_name ,  T1.customer_last_name FROM Customers AS T1 JOIN Accounts AS T2 ON T1.customer_id  =  T2.customer_id
Database: customers_card_transactions (tables: accounts, customers)
Question: What are the full names of customers who have accounts?
SQL: SELECT DISTINCT T1.customer_first_name ,  T1.customer_last_name FROM Customers AS T1 JOIN Accounts AS T2 ON T1.customer_id  =  T2.customer_id
Database: customers_card_transactions (tables: accounts)
Question: How many customers have an account?
SQL: SELECT count(DISTINCT customer_id) FROM Accounts
Database: customers_card_transactions (tables: accounts)
Question: Count the number of customers who hold an account.
SQL: SELECT count(DISTINCT customer_id) FROM Accounts
Database: customers_card_transactions (tables: customers)
Question: How many customers do we have?
SQL: SELECT count(*) FROM Customers
Database: customers_card_transactions (tables: customers)
Question: Count the number of customers.
SQL: SELECT count(*) FROM Customers
Database: customers_card_transactions (tables: customers)
Question: Show ids, first names, last names, and phones for all customers.
SQL: SELECT customer_id ,  customer_first_name ,  customer_last_name ,  customer_phone FROM Customers
Database: customers_card_transactions (tables: customers)
Question: What are the ids, full names, and phones of each customer?
SQL: SELECT customer_id ,  customer_first_name ,  customer_last_name ,  customer_phone FROM Customers
Database: customers_card_transactions (tables: customers)
Question: What is the phone and email for customer with first name Aniyah and last name Feest?
SQL: SELECT customer_phone ,  customer_email FROM Customers WHERE customer_first_name  =  "Aniyah" AND customer_last_name  =  "Feest"
Database: customers_card_transactions (tables: customers)
Question: Return the phone and email of the customer with the first name Aniyah and last name Feest.
SQL: SELECT customer_phone ,  customer_email FROM Customers WHERE customer_first_name  =  "Aniyah" AND customer_last_name  =  "Feest"
Database: customers_card_transactions (tables: customers_cards)
Question: Show the number of customer cards.
SQL: SELECT count(*) FROM Customers_cards
Database: customers_card_transactions (tables: customers_cards)
Question: How many customer cards are there?
SQL: SELECT count(*) FROM Customers_cards
Database: customers_card_transactions (tables: customers_cards)
Question: Show ids, customer ids, card type codes, card numbers for all cards.
SQL: SELECT card_id ,  customer_id ,  card_type_code ,  card_number FROM Customers_cards
Database: customers_card_transactions (tables: customers_cards)
Question: What are card ids, customer ids, card types, and card numbers for each customer card?
SQL: SELECT card_id ,  customer_id ,  card_type_code ,  card_number FROM Customers_cards
Database: customers_card_transactions (tables: customers_cards)
Question: Show the date valid from and the date valid to for the card with card number '4560596484842'.
SQL: SELECT date_valid_from ,  date_valid_to FROM Customers_cards WHERE card_number  =  "4560596484842"
Database: customers_card_transactions (tables: customers_cards)
Question: What are the valid from and valid to dates for the card with the number 4560596484842?
SQL: SELECT date_valid_from ,  date_valid_to FROM Customers_cards WHERE card_number  =  "4560596484842"
Database: customers_card_transactions (tables: customers, customers_cards)
Question: What is the first name, last name, and phone of the customer with card 4560596484842.
SQL: SELECT T2.customer_first_name ,  T2.customer_last_name ,  T2.customer_phone FROM Customers_cards AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T1.card_number  =  "4560596484842"
Database: customers_card_transactions (tables: customers, customers_cards)
Question: Return the full name and phone of the customer who has card number 4560596484842.
SQL: SELECT T2.customer_first_name ,  T2.customer_last_name ,  T2.customer_phone FROM Customers_cards AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T1.card_number  =  "4560596484842"
Database: customers_card_transactions (tables: customers, customers_cards)
Question: How many cards does customer Art Turcotte have?
SQL: SELECT count(*) FROM Customers_cards AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.customer_first_name  =  "Art" AND T2.customer_last_name  =  "Turcotte"
Database: customers_card_transactions (tables: customers, customers_cards)
Question: Count the number of cards the customer with the first name Art and last name Turcotte has.
SQL: SELECT count(*) FROM Customers_cards AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.customer_first_name  =  "Art" AND T2.customer_last_name  =  "Turcotte"
Database: customers_card_transactions (tables: customers_cards)
Question: How many debit cards do we have?
SQL: SELECT count(*) FROM Customers_cards WHERE card_type_code  =  "Debit"
Database: customers_card_transactions (tables: customers_cards)
Question: Count the number of customer cards of the type Debit.
SQL: SELECT count(*) FROM Customers_cards WHERE card_type_code  =  "Debit"
Database: customers_card_transactions (tables: customers, customers_cards)
Question: How many credit cards does customer Blanche Huels have?
SQL: SELECT count(*) FROM Customers_cards AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.customer_first_name  =  "Blanche" AND T2.customer_last_name  =  "Huels" AND T1.card_type_code  =  "Credit"
Database: customers_card_transactions (tables: customers, customers_cards)
Question: Count the number of credit cards that the customer with first name Blanche and last name Huels has.
SQL: SELECT count(*) FROM Customers_cards AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.customer_first_name  =  "Blanche" AND T2.customer_last_name  =  "Huels" AND T1.card_type_code  =  "Credit"
Database: customers_card_transactions (tables: customers_cards)
Question: Show all customer ids and the number of cards owned by each customer.
SQL: SELECT customer_id ,  count(*) FROM Customers_cards GROUP BY customer_id
Database: customers_card_transactions (tables: customers_cards)
Question: What are the different customer ids, and how many cards does each one hold?
SQL: SELECT customer_id ,  count(*) FROM Customers_cards GROUP BY customer_id
Database: customers_card_transactions (tables: customers_cards)
Question: What is the customer id with most number of cards, and how many does he have?
SQL: SELECT customer_id ,  count(*) FROM Customers_cards GROUP BY customer_id ORDER BY count(*) DESC LIMIT 1
Database: customers_card_transactions (tables: customers_cards)
Question: Return the id of the customer who has the most cards, as well as the number of cards.
SQL: SELECT customer_id ,  count(*) FROM Customers_cards GROUP BY customer_id ORDER BY count(*) DESC LIMIT 1
Database: customers_card_transactions (tables: customers, customers_cards)
Question: Show id, first and last names for all customers with at least two cards.
SQL: SELECT T1.customer_id ,  T2.customer_first_name ,  T2.customer_last_name FROM Customers_cards AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id HAVING count(*)  >=  2
Database: customers_card_transactions (tables: customers, customers_cards)
Question: What are the ids and full names of customers who hold two or more cards?
SQL: SELECT T1.customer_id ,  T2.customer_first_name ,  T2.customer_last_name FROM Customers_cards AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id HAVING count(*)  >=  2
Database: customers_card_transactions (tables: customers, customers_cards)
Question: What is the customer id, first and last name with least number of accounts.
SQL: SELECT T1.customer_id ,  T2.customer_first_name ,  T2.customer_last_name FROM Customers_cards AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id ORDER BY count(*) ASC LIMIT 1
Database: customers_card_transactions (tables: customers, customers_cards)
Question: Return the id and full name of the customer who has the fewest accounts.
SQL: SELECT T1.customer_id ,  T2.customer_first_name ,  T2.customer_last_name FROM Customers_cards AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id ORDER BY count(*) ASC LIMIT 1
Database: customers_card_transactions (tables: customers_cards)
Question: Show all card type codes and the number of cards in each type.
SQL: SELECT card_type_code ,  count(*) FROM Customers_cards GROUP BY card_type_code
Database: customers_card_transactions (tables: customers_cards)
Question: What are the different card types, and how many cards are there of each?
SQL: SELECT card_type_code ,  count(*) FROM Customers_cards GROUP BY card_type_code
Database: customers_card_transactions (tables: customers_cards)
Question: What is the card type code with most number of cards?
SQL: SELECT card_type_code FROM Customers_cards GROUP BY card_type_code ORDER BY count(*) DESC LIMIT 1
Database: customers_card_transactions (tables: customers_cards)
Question: Return the code of the card type that is most common.
SQL: SELECT card_type_code FROM Customers_cards GROUP BY card_type_code ORDER BY count(*) DESC LIMIT 1
Database: customers_card_transactions (tables: customers_cards)
Question: Show card type codes with at least 5 cards.
SQL: SELECT card_type_code FROM Customers_cards GROUP BY card_type_code HAVING count(*)  >=  5
Database: customers_card_transactions (tables: customers_cards)
Question: What are the codes of card types that have 5 or more cards?
SQL: SELECT card_type_code FROM Customers_cards GROUP BY card_type_code HAVING count(*)  >=  5
Database: customers_card_transactions (tables: customers_cards)
Question: Show all card type codes and the number of customers holding cards in each type.
SQL: SELECT card_type_code ,  count(DISTINCT customer_id) FROM Customers_cards GROUP BY card_type_code
Database: customers_card_transactions (tables: customers_cards)
Question: What are the different card type codes, and how many different customers hold each type?
SQL: SELECT card_type_code ,  count(DISTINCT customer_id) FROM Customers_cards GROUP BY card_type_code
Database: customers_card_transactions (tables: customers_cards, customers)
Question: Show the customer ids and firstname without a credit card.
SQL: SELECT customer_id ,  customer_first_name FROM Customers EXCEPT SELECT T1.customer_id ,  T2.customer_first_name FROM Customers_cards AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE card_type_code  =  "Credit"
Database: customers_card_transactions (tables: customers_cards, customers)
Question: What are the ids and first names of customers who do not hold a credit card?
SQL: SELECT customer_id ,  customer_first_name FROM Customers EXCEPT SELECT T1.customer_id ,  T2.customer_first_name FROM Customers_cards AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE card_type_code  =  "Credit"
Database: customers_card_transactions (tables: customers_cards)
Question: Show all card type codes.
SQL: SELECT DISTINCT card_type_code FROM Customers_Cards
Database: customers_card_transactions (tables: customers_cards)
Question: What are the different card type codes?
SQL: SELECT DISTINCT card_type_code FROM Customers_Cards
Database: customers_card_transactions (tables: customers_cards)
Question: Show the number of card types.
SQL: SELECT count(DISTINCT card_type_code) FROM Customers_Cards
Database: customers_card_transactions (tables: customers_cards)
Question: How many different card types are there?
SQL: SELECT count(DISTINCT card_type_code) FROM Customers_Cards
Database: customers_card_transactions (tables: financial_transactions)
Question: Show all transaction types.
SQL: SELECT DISTINCT transaction_type FROM Financial_Transactions
Database: customers_card_transactions (tables: financial_transactions)
Question: What are the different types of transactions?
SQL: SELECT DISTINCT transaction_type FROM Financial_Transactions
Database: customers_card_transactions (tables: financial_transactions)
Question: Show the number of transaction types.
SQL: SELECT count(DISTINCT transaction_type) FROM Financial_Transactions
Database: customers_card_transactions (tables: financial_transactions)
Question: How many different types of transactions are there?
SQL: SELECT count(DISTINCT transaction_type) FROM Financial_Transactions
Database: customers_card_transactions (tables: financial_transactions)
Question: What is the average and total transaction amount?
SQL: SELECT avg(transaction_amount) ,  sum(transaction_amount) FROM Financial_transactions
Database: customers_card_transactions (tables: financial_transactions)
Question: Return the average transaction amount, as well as the total amount of all transactions.
SQL: SELECT avg(transaction_amount) ,  sum(transaction_amount) FROM Financial_transactions
Database: customers_card_transactions (tables: financial_transactions, customers_cards)
Question: Show the card type codes and the number of transactions.
SQL: SELECT T2.card_type_code ,  count(*) FROM Financial_transactions AS T1 JOIN Customers_cards AS T2 ON T1.card_id  =  T2.card_id GROUP BY T2.card_type_code
Database: customers_card_transactions (tables: financial_transactions, customers_cards)
Question: What are the different card types, and how many transactions have been made with each?
SQL: SELECT T2.card_type_code ,  count(*) FROM Financial_transactions AS T1 JOIN Customers_cards AS T2 ON T1.card_id  =  T2.card_id GROUP BY T2.card_type_code
Database: customers_card_transactions (tables: financial_transactions)
Question: Show the transaction type and the number of transactions.
SQL: SELECT transaction_type ,  count(*) FROM Financial_transactions GROUP BY transaction_type
Database: customers_card_transactions (tables: financial_transactions)
Question: What are the different transaction types, and how many transactions of each have taken place?
SQL: SELECT transaction_type ,  count(*) FROM Financial_transactions GROUP BY transaction_type
Database: customers_card_transactions (tables: financial_transactions)
Question: What is the transaction type that has processed the greatest total amount in transactions?
SQL: SELECT transaction_type FROM Financial_transactions GROUP BY transaction_type ORDER BY sum(transaction_amount) DESC LIMIT 1
Database: customers_card_transactions (tables: financial_transactions)
Question: Return the type of transaction with the highest total amount.
SQL: SELECT transaction_type FROM Financial_transactions GROUP BY transaction_type ORDER BY sum(transaction_amount) DESC LIMIT 1
Database: customers_card_transactions (tables: financial_transactions)
Question: Show the account id and the number of transactions for each account
SQL: SELECT account_id ,  count(*) FROM Financial_transactions GROUP BY account_id