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
I want to know what is the greatest amount of loan to customers that each bank branch has made.
assistant
SELECT T2.bname , MAX( T1.amount ) 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 GROUP BY T2.bname;
human
Can you show me the credit score of the customer receiving the greatest amount of loan?
assistant
SELECT T2.credit_score FROM loan AS T1 JOIN customer AS T2 ON T1.cust_id = T2.cust_id ORDER BY T1.amount DESC LIMIT 1;
human
Can you show me the name of the bank branch providing the greatest total amount of loans to customers?
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 GROUP BY T2.bname ORDER BY SUM( T1.amount ) DESC LIMIT 1 ;