CREATE TABLE bank (
    branch_ID NUMBER PRIMARY KEY,
    bname TEXT,
    no_of_customers NUMBER,
    city TEXT,
    state TEXT
);
CREATE TABLE customer (
    cust_ID TEXT PRIMARY KEY,
    cust_name TEXT,
    acc_type TEXT,
    acc_bal NUMBER,
    no_of_loans NUMBER,
    credit_score NUMBER,
    branch_ID NUMBER,
    state TEXT,
    FOREIGN KEY ("branch_ID") REFERENCES bank("branch_ID")
);
CREATE TABLE loan (
    loan_ID TEXT PRIMARY KEY,
    loan_type TEXT,
    cust_ID TEXT,
    branch_ID TEXT,
    amount NUMBER,
    FOREIGN KEY ("branch_ID") REFERENCES bank("branch_ID")
);

Question: How many bank branches are there?
SQL: SELECT count(*) FROM bank

Question: Count the number of bank branches.
SQL: SELECT count(*) FROM bank

Question: How many customers are there?
SQL: SELECT sum(no_of_customers) FROM bank

Question: What is the total number of customers across banks?
SQL: SELECT sum(no_of_customers) FROM bank

Question: Find the number of customers in the banks at New York City.
SQL: SELECT sum(no_of_customers) FROM bank WHERE city  =  'New York City'

Question: What is the total number of customers who use banks in New York City?
SQL: SELECT sum(no_of_customers) FROM bank WHERE city  =  'New York City'

Question: Find the average number of customers in all banks of Utah state.
SQL: SELECT avg(no_of_customers) FROM bank WHERE state  =  'Utah'

Question: What is the average number of customers across banks in the state of Utah?
SQL: SELECT avg(no_of_customers) FROM bank WHERE state  =  'Utah'

Question: Find the average number of customers cross all banks.
SQL: SELECT avg(no_of_customers) FROM bank

Question: What is the average number of bank customers?
SQL: SELECT avg(no_of_customers) FROM bank

Question: Find the city and state of the bank branch named morningside.
SQL: SELECT city ,  state FROM bank WHERE bname  =  'morningside'

Question: What city and state is the bank with the name morningside in?
SQL: SELECT city ,  state FROM bank WHERE bname  =  'morningside'

Question: Find the branch names of banks in the New York state.
SQL: SELECT bname FROM bank WHERE state  =  'New York'

Question: What are the names of banks in the state of New York?
SQL: SELECT bname FROM bank WHERE state  =  'New York'

Question: List the name of all customers sorted by their account balance in ascending order.
SQL: SELECT cust_name FROM customer ORDER BY acc_bal

Question: What are the names of all customers, ordered by account balance?
SQL: SELECT cust_name FROM customer ORDER BY acc_bal

Question: List the name of all different customers who have some loan sorted by their total loan amount.
SQL: SELECT T1.cust_name FROM customer AS T1 JOIN loan AS T2 ON T1.cust_id  =  T2.cust_id GROUP BY T1.cust_name ORDER BY sum(T2.amount)

Question: What are the names of the different customers who have taken out a loan, ordered by the total amount that they have taken?
SQL: SELECT T1.cust_name FROM customer AS T1 JOIN loan AS T2 ON T1.cust_id  =  T2.cust_id GROUP BY T1.cust_name ORDER BY sum(T2.amount)

Question: Find the state, account type, and credit score of the customer whose number of loan is 0.
SQL: SELECT state ,  acc_type ,  credit_score FROM customer WHERE no_of_loans  =  0

Question: What are the states, account types, and credit scores for customers who have 0 loans?
SQL: SELECT state ,  acc_type ,  credit_score FROM customer WHERE no_of_loans  =  0

Question: Find the number of different cities which banks are located at.
SQL: SELECT count(DISTINCT city) FROM bank

Question: In how many different cities are banks located?
SQL: SELECT count(DISTINCT city) FROM bank

Question: Find the number of different states which banks are located at.
SQL: SELECT count(DISTINCT state) FROM bank

Question: In how many different states are banks located?
SQL: SELECT count(DISTINCT state) FROM bank

Question: How many distinct types of accounts are there?
SQL: SELECT count(DISTINCT acc_type) FROM customer

Question: Count the number of different account types.
SQL: SELECT count(DISTINCT acc_type) FROM customer

Question: Find the name and account balance of the customer whose name includes the letter ‘a’.
SQL: SELECT cust_name ,  acc_bal FROM customer WHERE cust_name LIKE '%a%'

Question: What are the names and account balances of customers with the letter a in their names?
SQL: SELECT cust_name ,  acc_bal FROM customer WHERE cust_name LIKE '%a%'

Question: Find the total account balance of each customer from Utah or Texas.
SQL: SELECT sum(acc_bal) FROM customer WHERE state  =  'Utah' OR state  =  'Texas'

Question: What are the total account balances for each customer from Utah or Texas?
SQL: SELECT sum(acc_bal) FROM customer WHERE state  =  'Utah' OR state  =  'Texas'

Question: Find the name of customers who have both saving and checking account types.
SQL: SELECT cust_name FROM customer WHERE acc_type  =  'saving' INTERSECT SELECT cust_name FROM customer WHERE acc_type  =  'checking'

Question: What are the names of customers who have both savings and checking accounts?
SQL: SELECT cust_name FROM customer WHERE acc_type  =  'saving' INTERSECT SELECT cust_name FROM customer WHERE acc_type  =  'checking'

Question: Find the name of customers who do not have an saving account.
SQL: SELECT cust_name FROM customer EXCEPT SELECT cust_name FROM customer WHERE acc_type  =  'saving'

Question: What are the names of customers who do not have saving accounts?
SQL: SELECT cust_name FROM customer EXCEPT SELECT cust_name FROM customer WHERE acc_type  =  'saving'

Question: Find the name of customers who do not have a loan with a type of Mortgages.
SQL: SELECT cust_name FROM customer EXCEPT SELECT T1.cust_name FROM customer AS T1 JOIN loan AS T2 ON T1.cust_id  =  T2.cust_id WHERE T2.loan_type  =  'Mortgages'

Question: What are the names of customers who have not taken a Mortage loan?
SQL: SELECT cust_name FROM customer EXCEPT SELECT T1.cust_name FROM customer AS T1 JOIN loan AS T2 ON T1.cust_id  =  T2.cust_id WHERE T2.loan_type  =  'Mortgages'

Question: Find the name of customers who have loans of both Mortgages and Auto.
SQL: SELECT T1.cust_name FROM customer AS T1 JOIN loan AS T2 ON T1.cust_id  =  T2.cust_id WHERE loan_type  =  'Mortgages' INTERSECT SELECT T1.cust_name FROM customer AS T1 JOIN loan AS T2 ON T1.cust_id  =  T2.cust_id WHERE loan_type  =  'Auto'

Question: What are the names of customers who have taken both Mortgage and Auto loans?
SQL: SELECT T1.cust_name FROM customer AS T1 JOIN loan AS T2 ON T1.cust_id  =  T2.cust_id WHERE loan_type  =  'Mortgages' INTERSECT SELECT T1.cust_name FROM customer AS T1 JOIN loan AS T2 ON T1.cust_id  =  T2.cust_id WHERE loan_type  =  'Auto'

Question: Find the name of customers whose credit score is below the average credit scores of all customers.
SQL: SELECT cust_name FROM customer WHERE credit_score  <  (SELECT avg(credit_score) FROM customer)

Question: What are the names of customers with credit score less than the average credit score across customers?
SQL: SELECT cust_name FROM customer WHERE credit_score  <  (SELECT avg(credit_score) FROM customer)

Question: Find the branch name of the bank that has the most number of customers.
SQL: SELECT bname FROM bank ORDER BY no_of_customers DESC LIMIT 1

Question: What is the name of the bank branch with the greatest number of customers?
SQL: SELECT bname FROM bank ORDER BY no_of_customers DESC LIMIT 1

Question: Find the name of customer who has the lowest credit score.
SQL: SELECT cust_name FROM customer ORDER BY credit_score LIMIT 1

Question: What is the name of the customer with the worst credit score?
SQL: SELECT cust_name FROM customer ORDER BY credit_score LIMIT 1

Question: Find the name, account type, and account balance of the customer who has the highest credit score.
SQL: SELECT cust_name ,  acc_type ,  acc_bal FROM customer ORDER BY credit_score DESC LIMIT 1