3.8

Consider the bank database of Figure 3.18, where the primary keys are underlined. Construct the following SQL queries for this relational database.
a. Find the ID of each customer of the bank who has an account but not a loan.
b. Find the ID of each customer who lives on the same street and in the same city as customer ‘12345’.
c. Find the name of each branch that has at least one customer who has an account in the bank and who lives in “Harrison”.

  1. Find the ID of each customer of the bank who has an account but not a loan.
(SELECT ID FROM depositor)
EXCEPT 
(SELECT ID FROM borrower)
  1. Find the ID of each customer who lives on the same street and in the same city as customer ‘12345’
SELECT F.ID
FROM customer AS F, customer AS S
WHERE F.customer_street = S.customer_street
    AND F.customer_city = S.customer_city
    AND S.customer_id = '12345';

Another method (using scalar subqueries)

SELECT ID 
FROM customer 
WHERE customer_street = (SELECT customer_street FROM customer WHERE ID = '12345') AND 
    customer_city = (SELECT customer_city FROM customer WHERE ID = '12345')
  1. Find the name of each branch that has at least one customer who has an account in the bank and who lives in “Harrison”.
SELECT DISTINCT branch_name
FROM account, depositor, customer 
WHERE customer.id = depositor.id
    AND depositor.account_number = account.account_number 
    AND customer_city = 'Harrison'