Database: customers_card_transactions

CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255)
);
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(20),
`customer_last_name` VARCHAR(20),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(255),
`customer_email` VARCHAR(255),
`other_customer_details` VARCHAR(255)
);
CREATE TABLE `Customers_Cards` (
`card_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`card_type_code` VARCHAR(15) NOT NULL,
`card_number` VARCHAR(80),
`date_valid_from` DATETIME,
`date_valid_to` DATETIME,
`other_card_details` VARCHAR(255)
);
CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`previous_transaction_id` INTEGER,
`account_id` INTEGER NOT NULL,
`card_id` INTEGER NOT NULL,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DOUBLE NULL,
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`card_id` ) REFERENCES `Customers_Cards`(`card_id` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
);

Question: How many accounts do we have?
SQL: SELECT count(*) FROM Accounts

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

Question: Show ids, customer ids, names for all accounts.
SQL: SELECT account_id ,  customer_id ,  account_name FROM 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

Question: Show other account details for account with name 338.
SQL: SELECT other_account_details FROM Accounts WHERE account_name  =  "338"

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"

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"

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"

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"

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"

Question: Show all customer ids and the number of accounts for each customer.
SQL: SELECT customer_id ,  count(*) FROM Accounts GROUP BY customer_id

Question: How many accounts are there for each customer id?
SQL: SELECT customer_id ,  count(*) FROM Accounts GROUP BY customer_id

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

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

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

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

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)

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

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

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

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

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

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

Question: Count the number of customers who hold an account.
SQL: SELECT count(DISTINCT customer_id) FROM Accounts

Question: How many customers do we have?
SQL: SELECT count(*) FROM Customers

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

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

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"

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"

Question: Show the number of customer cards.
SQL: SELECT count(*) FROM Customers_cards

Question: How many customer cards are there?
SQL: SELECT count(*) FROM 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

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

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"

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"

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"

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"

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"

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"

Question: How many debit cards do we have?
SQL: SELECT count(*) FROM Customers_cards WHERE card_type_code  =  "Debit"

Question: Count the number of customer cards of the type Debit.
SQL: SELECT count(*) FROM Customers_cards WHERE card_type_code  =  "Debit"

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"

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"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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"

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"

Question: Show all card type codes.
SQL: SELECT DISTINCT card_type_code FROM Customers_Cards

Question: What are the different card type codes?
SQL: SELECT DISTINCT card_type_code FROM Customers_Cards

Question: Show the number of card types.
SQL: SELECT count(DISTINCT card_type_code) FROM Customers_Cards

Question: How many different card types are there?
SQL: SELECT count(DISTINCT card_type_code) FROM Customers_Cards

Question: Show all transaction types.
SQL: SELECT DISTINCT transaction_type FROM Financial_Transactions

Question: What are the different types of transactions?
SQL: SELECT DISTINCT transaction_type FROM Financial_Transactions

Question: Show the number of transaction types.
SQL: SELECT count(DISTINCT transaction_type) FROM Financial_Transactions

Question: How many different types of transactions are there?
SQL: SELECT count(DISTINCT transaction_type) FROM Financial_Transactions

Question: What is the average and total transaction amount?
SQL: SELECT avg(transaction_amount) ,  sum(transaction_amount) FROM 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

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

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

Question: Show the transaction type and the number of transactions.
SQL: SELECT transaction_type ,  count(*) FROM Financial_transactions GROUP BY transaction_type

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

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

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

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

Question: What are the different account ids that have made financial transactions, as well as how many transactions correspond to each?
SQL: SELECT account_id ,  count(*) FROM Financial_transactions GROUP BY account_id