Question

Database Schema:

branch ( branch_name, branch_city, assets )

customer ( cust_ID, customer_name, customer_street, customer_city )

loan ( loan_number, branch_name, amount )

borrower ( cust_ID, loan_number )

account ( account_number, branch_name, balance )

depositor ( cust_ID, account_number )

write a query to return each cust id and customer name who has a deposit account at every branch located in brooklyn. do not hardcode the brooklyn branch names directly into your query as these may change over time. your solution must include a subquery.

Answer 1

To retrieve each cust ID and customer name who has a deposit account at every branch located in Brooklyn, you can use a subquery.

First, you need to find all the branch names located in Brooklyn by querying the branch table. Then, you can use the retrieved branch names to check if each customer has a deposit account at every branch.

Step-by-step explanation:

Using sub queries, first, you need to find all the branch names located in Brooklyn by querying the branch table.

Then, you can use the retrieved branch names to check if each customer has a deposit account at every branch.

Here's an example query:

SELECT cust_ID, customer_name
FROM customer
WHERE cust_ID IN (
SELECT DISTINCT d.cust_ID
FROM depositor AS d
JOIN account AS a ON d.account_number = a.account_number
JOIN branch AS b ON a.branch_name = b.branch_name
WHERE b.branch_city = 'Brooklyn'
GROUP BY d.cust_ID
HAVING COUNT(DISTINCT b.branch_name) = (
SELECT COUNT(*)
FROM branch
WHERE branch_city = 'Brooklyn'
)
);