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 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
How many bank branches are there?
assistant
SELECT count(*) FROM bank
human
Find the number of different states which banks are located at.
assistant
SELECT count(DISTINCT state) FROM bank
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 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'
human
What are the names of banks in the state of New York?
assistant
SELECT bname FROM bank WHERE state = "New York" ;