Question

1.

In SQL. Given the following relational database schema . The primary key is underlined. All attributes are of type string if not indicated otherwise.

 branch(branch_name,branch_city,assets: integer)

 customer(customer_i,customer_name,customer_street,customer_city)

 account(account_number,branch_name,balance)

 depositor(customer_id,account_number)

a. Find the names of customers that have accounts in every branch in Miami.

b. Find the names of customers who do not have an account. (assume the bank keeps the customer information even if the account is closed.)

Answer 1

If you have any doubts, please give me comment...

-- a

SELECT customer_name

FROM customer, depositor, account, branch

WHERE customer.customer_id = depositor.customer_id AND depositor.account_number = account.account_number AND account.branch_name = branch.branch_name AND branch_city = 'Miami';

-- b

SELECT customer_name

FROM customer

WHERE customer_id NOT IN(

    SELECT customer_id

    FROM depositor, account

    WHERE depositor.account_number = account.account_number

);