human
Schema:
bank (branch_ID, bname, no_of_customers, city, state)
customer (cust_ID, cust_name, acc_type, acc_bal, no_of_loans, credit_score, branch_ID, state)
loan (loan_ID, loan_type, cust_ID, branch_ID, amount)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the branch name of the bank that has the most number of customers.
assistant
SELECT bname FROM bank ORDER BY no_of_customers DESC LIMIT 1
human
Find the total amount of loans offered by each bank branch.
assistant
SELECT sum(amount) , T1.bname FROM bank AS T1 JOIN loan AS T2 ON T1.branch_id = T2.branch_id GROUP BY T1.bname
human
How many bank branches are there?
assistant
SELECT count(*) FROM bank
human
Find the branch names of banks in the New York state.
assistant
SELECT bname FROM bank WHERE state = 'New York'
human
Find the number of different cities which banks are located at.
assistant
SELECT count(DISTINCT city) FROM bank
human
Find the number of different states which banks are located at.
assistant
SELECT COUNT( DISTINCT state ) FROM bank ;