Database: loan_1

CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20));
CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID));
CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_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

Question: What is the name, account type, and account balance corresponding to the customer with the highest credit score?
SQL: SELECT cust_name ,  acc_type ,  acc_bal FROM customer ORDER BY credit_score DESC LIMIT 1

Question: Find the name of customer who has the highest amount of loans.
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) DESC LIMIT 1

Question: What is the name of the customer who has greatest 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) DESC LIMIT 1

Question: Find the state which has the most number of customers.
SQL: SELECT state FROM bank GROUP BY state ORDER BY sum(no_of_customers) DESC LIMIT 1

Question: Which state has the greatest total number of bank customers?
SQL: SELECT state FROM bank GROUP BY state ORDER BY sum(no_of_customers) DESC LIMIT 1

Question: For each account type, find the average account balance of customers with credit score lower than 50.
SQL: SELECT avg(acc_bal) ,  acc_type FROM customer WHERE credit_score  <  50 GROUP BY acc_type

Question: What is the average account balance of customers with credit score below 50 for the different account types?
SQL: SELECT avg(acc_bal) ,  acc_type FROM customer WHERE credit_score  <  50 GROUP BY acc_type

Question: For each state, find the total account balance of customers whose credit score is above 100.
SQL: SELECT sum(acc_bal) ,  state FROM customer WHERE credit_score  >  100 GROUP BY state

Question: What is the total account balance for customers with a credit score of above 100 for the different states?
SQL: SELECT sum(acc_bal) ,  state FROM customer WHERE credit_score  >  100 GROUP BY state

Question: Find the total amount of loans offered by each bank branch.
SQL: SELECT sum(amount) ,  T1.bname FROM bank AS T1 JOIN loan AS T2 ON T1.branch_id  =  T2.branch_id GROUP BY T1.bname

Question: What are the names of the different bank branches, and what are their total loan amounts?
SQL: SELECT sum(amount) ,  T1.bname FROM bank AS T1 JOIN loan AS T2 ON T1.branch_id  =  T2.branch_id GROUP BY T1.bname

Question: Find the name of customers who have more than one loan.
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 HAVING count(*)  >  1

Question: What are the names of customers who have taken out more than one loan?
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 HAVING count(*)  >  1

Question: Find the name and account balance of the customers who have loans with a total amount of more than 5000.
SQL: SELECT T1.cust_name ,  T1.acc_type FROM customer AS T1 JOIN loan AS T2 ON T1.cust_id  =  T2.cust_id GROUP BY T1.cust_name HAVING sum(T2.amount)  >  5000

Question: What are the names and account balances for customers who have taken a total amount of more than 5000 in loans?
SQL: SELECT T1.cust_name ,  T1.acc_type FROM customer AS T1 JOIN loan AS T2 ON T1.cust_id  =  T2.cust_id GROUP BY T1.cust_name HAVING sum(T2.amount)  >  5000

Question: Find the name of bank branch that provided the greatest total amount of loans.
SQL: SELECT T1.bname FROM bank AS T1 JOIN loan AS T2 ON T1.branch_id  =  T2.branch_id GROUP BY T1.bname ORDER BY sum(T2.amount) DESC LIMIT 1

Question: What is the name of the bank branch that has lent the greatest amount?
SQL: SELECT T1.bname FROM bank AS T1 JOIN loan AS T2 ON T1.branch_id  =  T2.branch_id GROUP BY T1.bname ORDER BY sum(T2.amount) DESC LIMIT 1

Question: Find the name of bank branch that provided the greatest total amount of loans to customers with credit score is less than 100.
SQL: SELECT T2.bname FROM loan AS T1 JOIN bank AS T2 ON T1.branch_id  =  T2.branch_id JOIN customer AS T3 ON T1.cust_id  =  T3.cust_id WHERE T3.credit_score  <  100 GROUP BY T2.bname ORDER BY sum(T1.amount) DESC LIMIT 1

Question: What is the name of the bank branch that has lended the largest total amount in loans, specifically to customers with credit scores below 100?
SQL: SELECT T2.bname FROM loan AS T1 JOIN bank AS T2 ON T1.branch_id  =  T2.branch_id JOIN customer AS T3 ON T1.cust_id  =  T3.cust_id WHERE T3.credit_score  <  100 GROUP BY T2.bname ORDER BY sum(T1.amount) DESC LIMIT 1

Question: Find the name of bank branches that provided some loans.
SQL: SELECT DISTINCT T1.bname FROM bank AS T1 JOIN loan AS T2 ON T1.branch_id  =  T2.branch_id

Question: What are the names of the different banks that have provided loans?
SQL: SELECT DISTINCT T1.bname FROM bank AS T1 JOIN loan AS T2 ON T1.branch_id  =  T2.branch_id

Question: Find the name and credit score of the customers who have some loans.
SQL: SELECT DISTINCT T1.cust_name ,  T1.credit_score FROM customer AS T1 JOIN loan AS T2 ON T1.cust_id  =  T2.cust_id

Question: What are the different names and credit scores of customers who have taken a loan?
SQL: SELECT DISTINCT T1.cust_name ,  T1.credit_score FROM customer AS T1 JOIN loan AS T2 ON T1.cust_id  =  T2.cust_id

Question: Find the the name of the customers who have a loan with amount more than 3000.
SQL: SELECT T1.cust_name FROM customer AS T1 JOIN loan AS T2 ON T1.cust_id  =  T2.cust_id WHERE amount  >  3000

Question: What are the names of customers who have a loan of more than 3000 in amount?
SQL: SELECT T1.cust_name FROM customer AS T1 JOIN loan AS T2 ON T1.cust_id  =  T2.cust_id WHERE amount  >  3000

Question: Find the city and name of bank branches that provide business loans.
SQL: SELECT T1.bname ,  T1.city FROM bank AS T1 JOIN loan AS T2 ON T1.branch_id  =  T2.branch_id WHERE T2.loan_type  =  'Business'

Question: What are the names and cities of bank branches that offer loans for business?
SQL: SELECT T1.bname ,  T1.city FROM bank AS T1 JOIN loan AS T2 ON T1.branch_id  =  T2.branch_id WHERE T2.loan_type  =  'Business'

Question: Find the names of bank branches that have provided a loan to any customer whose credit score is below 100.
SQL: SELECT T2.bname FROM loan AS T1 JOIN bank AS T2 ON T1.branch_id  =  T2.branch_id JOIN customer AS T3 ON T1.cust_id  =  T3.cust_id WHERE T3.credit_score  <  100

Question: What are the names of banks that have loaned money to customers with credit scores below 100?
SQL: SELECT T2.bname FROM loan AS T1 JOIN bank AS T2 ON T1.branch_id  =  T2.branch_id JOIN customer AS T3 ON T1.cust_id  =  T3.cust_id WHERE T3.credit_score  <  100

Question: Find the total amount of loans provided by bank branches in the state of New York.
SQL: SELECT sum(T2.amount) FROM bank AS T1 JOIN loan AS T2 ON T1.branch_id  =  T2.branch_id WHERE T1.state  =  'New York'

Question: What is the total amount of money loaned by banks in New York state?
SQL: SELECT sum(T2.amount) FROM bank AS T1 JOIN loan AS T2 ON T1.branch_id  =  T2.branch_id WHERE T1.state  =  'New York'

Question: Find the average credit score of the customers who have some loan.
SQL: SELECT avg(credit_score) FROM customer WHERE cust_id IN (SELECT cust_id FROM loan)

Question: What is the average credit score for customers who have taken a loan?
SQL: SELECT avg(credit_score) FROM customer WHERE cust_id IN (SELECT cust_id FROM loan)

Question: Find the average credit score of the customers who do not have any loan.
SQL: SELECT avg(credit_score) FROM customer WHERE cust_id NOT IN (SELECT cust_id FROM loan)

Question: What is the average credit score for customers who have never taken a loan?
SQL: SELECT avg(credit_score) FROM customer WHERE cust_id NOT IN (SELECT cust_id FROM loan)