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 total account balance of each customer from Utah or Texas.
assistant
SELECT sum(acc_bal) FROM customer WHERE state = 'Utah' OR state = 'Texas'
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 names of bank branches that have provided a loan to any customer whose credit score is 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
human
Find the name, account type, and account balance of the customer who has the highest credit score.
assistant
SELECT cust_name , acc_type , acc_bal FROM customer ORDER BY credit_score DESC LIMIT 1
human
For each account type, find the average account balance of customers with credit score lower than 50.
assistant
SELECT avg(acc_bal) , acc_type FROM customer WHERE credit_score < 50 GROUP BY acc_type
human
For each state, find the total account balance of customers whose credit score is above 100.
assistant
SELECT SUM( acc_bal ) , state FROM customer WHERE credit_score > 100 GROUP BY state ;