CREATE TABLE Accounts (
    account_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    account_name TEXT,
    other_account_details TEXT
);
CREATE TABLE Customers (
    customer_id NUMBER PRIMARY KEY,
    customer_first_name TEXT,
    customer_last_name TEXT,
    customer_address TEXT,
    customer_phone TEXT,
    customer_email TEXT,
    other_customer_details TEXT
);
CREATE TABLE Customers_Cards (
    card_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    card_type_code TEXT,
    card_number TEXT,
    date_valid_from TIME,
    date_valid_to TIME,
    other_card_details TEXT
);
CREATE TABLE Financial_Transactions (
    transaction_id NUMBER,
    previous_transaction_id NUMBER,
    account_id NUMBER,
    card_id NUMBER,
    transaction_type TEXT,
    transaction_date TIME,
    transaction_amount NUMBER,
    transaction_comment TEXT,
    other_transaction_details TEXT,
    FOREIGN KEY ("account_id") REFERENCES Accounts("account_id"),
    FOREIGN KEY ("card_id") REFERENCES Customers_Cards("card_id")
);

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