//Sub select examples
//With reference to the mail order database
// find zip codes that where such that some customer and some employee live there
select distinct subselect1.zip from (select zip from customers) as subselect1 where subselect1.zip in (select zip from employees);


//with reference to the bank database
//names of people who are borrowers but not depositors
select debtor.customerName from (select customerName from borrower) as debtor where debtor.customerName not in (select customerName from depositor);

// people who are borrowers and depositors - essentially borrower intersect depositor
select debtor.customerName from (select customerName from borrower) as debtor where debtor.customerName in (select customerName from depositor);


//what does this do?
select debtor.customerName from (select customerName from borrower) as debtor where debtor.customerName in (select customerName from depositor)  and debtor.customerName like '%e_';

//how about this?
select accountNumber from account
    -> where account.balance > some (select balance from loan);

//and this?
select accountNumber from account
    -> where account.balance > all (select balance from loan);

// another way of identifying borrowers who are also depositors
select customerName from borrower where exists (select * from  depositor where depositor.customerName = borrower.customerName); 

// another way of identifying borrowers who are NOT depositors
select customerName from borrower where not exists (select * from  depositor where depositor.customerName = borrower.customerName);