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
Hello! Can you provide me with a list of all of the customers with a credit score below 100?
assistant
SELECT * FROM customer WHERE credit_score < 100;
human
Can you provide me with a list of all of the loans that both Mary and Jack received?
assistant
SELECT t2.loan_type FROM customer AS t1 JOIN loan AS t2 ON t1.cust_id = t2.cust_id WHERE t1.cust_name = 'Mary' INTERSECT SELECT t4.loan_type FROM customer AS t3 JOIN loan AS t4 ON t3.cust_id = t4.cust_id WHERE t3.cust_name = 'Jack';
human
Can you show me which bank branch provided the highest number of loans 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 GROUP BY T2.bname ORDER BY SUM( T1.amount ) DESC LIMIT 1 ;