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 )

Question 1 (Must include a subquery): Write a query to find the cust_ID and customer name of each customer at the bank who only has at least one loan at the bank, and no deposit accounts.

Answer 1

To find customers with loans and no deposit accounts, a query that uses EXISTS and NOT EXISTS to filter customers based on associated records in the borrower and depositor tables, respectively, is required.

Step-by-step explanation:

The given question is about writing a SQL query to find the cust_ID and customer name for each customer with at least one loan and no deposit accounts. Here is how you might write the query:

SELECT c.cust_ID, c.customer_name
FROM customer c
WHERE EXISTS (
SELECT *
FROM borrower b
WHERE b.cust_ID = c.cust_ID
)
AND NOT EXISTS (
SELECT *
FROM depositor d
WHERE d.cust_ID = c.cust_ID
);

This query first checks for customers who are borrowers using the EXISTS clause and ensures these customers do not have an account using the NOT EXISTS clause in conjunction with the depositor table. It's important to remember that each subquery is correlated with the outer query by matching the cust_ID in customer to cust_ID in borrower and depositor respectively.