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 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 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 and account balance of the customers who have loans with a total amount of more than 5000.
assistant
SELECT T1.cust_name , T1.acc_type FROM customer AS T1 JOIN loan AS T2 ON T1.cust_id = T2.cust_id GROUP BY T1.cust_name HAVING sum(T2.amount) > 5000
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
List the name of all customers sorted by their account balance in ascending order.
assistant
SELECT cust_name FROM customer ORDER BY acc_bal
human
Find the name and account balance of the customer whose name includes the letter a .
assistant
SELECT cust_name , acc_bal FROM customer WHERE cust_name LIKE "%a%" ;