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 name of customer who has the highest amount of loans.
assistant
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
human
Find the name of bank branch that provided the greatest total amount of loans to customers with credit score is less than 100.
assistant
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
human
Find the name of bank branches that provided some loans.
assistant
SELECT DISTINCT T1.bname FROM bank AS T1 JOIN loan AS T2 ON T1.branch_id = T2.branch_id
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
Find the name of bank branch that provided the greatest total amount of loans.
assistant
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 ;