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 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 customer who has the lowest credit score.
assistant
SELECT cust_name FROM customer ORDER BY credit_score LIMIT 1
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 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 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
What are the names of banks that have loaned money to customers with credit scores below 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 ;