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, 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
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
Find the average credit score of the customers who have some loan.
assistant
SELECT avg(credit_score) FROM customer WHERE cust_id IN (SELECT cust_id FROM loan)
human
Find the average credit score of the customers who do not have any loan.
assistant
SELECT avg(credit_score) FROM customer WHERE cust_id NOT IN (SELECT cust_id FROM loan)
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
What are the names of customers with credit score less than the average credit score across customers?
assistant
SELECT cust_name FROM customer WHERE credit_score < ( SELECT avg ( credit_score ) FROM customer ) ;