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 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
human
Find the name and credit score of the customers who have some loans.
assistant
SELECT DISTINCT T1.cust_name , T1.credit_score FROM customer AS T1 JOIN loan AS T2 ON T1.cust_id = T2.cust_id
human
List the name of all different customers who have some loan sorted by their total loan amount.
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)
human
Find the the name of the customers who have a loan with amount more than 3000.
assistant
SELECT T1.cust_name FROM customer AS T1 JOIN loan AS T2 ON T1.cust_id = T2.cust_id WHERE amount > 3000
human
Find the name of customers who have more than one loan.
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 HAVING count(*) > 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 ;