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 number of customers in the banks at New York City.
assistant
SELECT sum(no_of_customers) FROM bank WHERE city = 'New York City'
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 of bank branch that provided the greatest total amount of loans.
assistant
SELECT T1.bname FROM bank AS T1 JOIN loan AS T2 ON T1.branch_id = T2.branch_id GROUP BY T1.bname ORDER BY sum(T2.amount) DESC LIMIT 1
human
Find the branch names of banks in the New York state.
assistant
SELECT bname FROM bank WHERE state = 'New York'
human
Find the total amount of loans offered by each bank branch.
assistant
SELECT sum(amount) , T1.bname FROM bank AS T1 JOIN loan AS T2 ON T1.branch_id = T2.branch_id GROUP BY T1.bname
human
Find the total amount of loans provided by bank branches in the state of New York.
assistant
SELECT SUM( T2.amount ) FROM bank AS T1 JOIN loan AS T2 ON T1.branch_id = T2.branch_id WHERE T1.state = "New York" ;