How do I solve this SQL Query?

Question

Tables:

• CUSTOMER (CUSTOMER-NAME(P), CUSTOMER-STREET, CUSTOMER- CITY)
• BRANCH (BRANCH-NAME(P), BRANCH-CITY, ASSETS)
• ACCOUNT (ACCOUNT-NUMBER(P), BRANCH-NAME, BALANCE)
• LOAN (LOAN-NUMBER(P), BRANCH-NAME, AMOUNT)
• DEPOSITOR (CUSTOMER-NAME(P), ACCOUNT-NUMBER(P))
• BORROWER (CUSTOMER-NAME(P), LOAN-NUMBER(P))

The attributes marked with P are the primary key of the table.

The above is a relational schema and I have to write the SQL syntax for the following query:

• Find names of customers having account in all branches of "Noida" (where Noida is the name of a place)

  1. I tried solving this but the output came out to be those customers which have an account in some branch at Noida. How to do it for all?

  2. I am having a doubt in SQL that can we combine two relations even on non-primary key? For eg:

  depositor.account-number = account.account-number and account.branch-name = branch.branch-name

In the above statement, branch-name is not the primary key for account but is for branch. Therefore, is the above right?

Answer 1

This should be the right query

select *
from CUSTOMER c
where not exists (
        select BRANCH-NAME
        from BRANCH
        where BRANCH-CITY = 'Noida'
        minus
        select BRANCH-NAME
        from ACCOUNT 
            join DEPOSITOR d using (ACCOUNT-NUMBER)
        where d.CUSTOMER-NAME = c.CUSTOMER-NAME
    )