1

I'm finding it difficult to understand the logic behind the following SQL queries. Can someone explain a model in understanding queries like this.

1) Find all branches that have greater assets than some branch located in Brooklyn.

select distinct  T.branch_name
from branch as T, branch as S
where  T.assets > S.assets and
             S.branch_city = 'Brooklyn'

2) Find all customers who have an account at all branches located in Brooklyn.

select distinct S.customer_name
from depositor as S
where not exists (
(select branch_name
from branch
where branch_city = 'Brooklyn') 

except
(select R.branch_name
from depositor as T, account as R
where T.account_number = R.account_number and
S.customer_name = T.customer_name ))
tharindu_DG
  • 8,900
  • 6
  • 52
  • 64
  • 1
    See [this answer](https://stackoverflow.com/questions/27682228/how-to-get-matching-data-from-another-sql-table-for-two-different-columns-inner/27682724#). And [this one](https://stackoverflow.com/questions/24423150/relational-algebra-for-banking-scenario/24425914#). – philipxy Mar 20 '15 at 03:33
  • 2
    Your query 1 uses ancient outdated join syntax which you should not be using (see http://stackoverflow.com/q/1599050/65223). Also, these are hardly advanced queries, they are actually trivial. – KM. Apr 30 '15 at 13:34

1 Answers1

0

Query 1 is comparing rows in a table to a single row in the same table. It could be rewritten for better readability as:

select distinct t.branch_name
from branch t
where t.assets > (select nvl(assets,0) from branch
                  where branch_city = 'Brooklyn');

Better query for multiple branches in 'Brooklyn':

create table branch (
  branch_name varchar2(30),
  branch_city varchar2(30),
  assets number(10));

insert into branch (branch_name,branch_city,assets)
values ('Manhasset 01','Manhasset',12345);
insert into branch (branch_name,branch_city,assets)
values ('Brooklyn Branch 12','Brooklyn',22222);
insert into branch (branch_name,branch_city,assets)
values ('White Plains Downtown','White Plains',33333);
insert into branch (branch_name,branch_city,assets)
values ('Brooklyn DUMBO','Brooklyn',44444);
insert into branch (branch_name,branch_city,assets)
values ('Manhattan - Financial District','Manhattan',55555);
insert into branch (branch_name,branch_city,assets)
values ('Bronx Branch 8','Bronx',66666);
insert into branch (branch_name,branch_city,assets)
values ('Queens - La Guardia','Queens',1234523423);
insert into branch (branch_name,branch_city,assets)
values ('Queens - Flushing Meadows','Queens',12);
insert into branch (branch_name,branch_city,assets)
values ('Rikers Island','Rikers Island',2);
insert into branch (branch_name,branch_city,assets)
values ('Harlem - Branch 1','Harlem',99999);
commit;

select * from branch;
BRANCH_NAME                    BRANCH_CITY                        ASSETS
------------------------------ ------------------------------ ----------
Manhasset 01                   Manhasset                           12345
Brooklyn Branch 12             Brooklyn                            22222
White Plains Downtown          White Plains                        33333
Brooklyn DUMBO                 Brooklyn                            44444
Manhattan - Financial District Manhattan                           55555
Bronx Branch 8                 Bronx                               66666
Queens - La Guardia            Queens                         1234523423
Queens - Flushing Meadows      Queens                                 12
Rikers Island                  Rikers Island                           2
Harlem - Branch 1              Harlem                              99999

/* this should now work for multiple or no Brooklyn branches */
select distinct t.branch_name
from branch t
where t.assets > nvl((select min(nvl(assets,0)) from branch
                      where branch_city = 'Brooklyn'),0);

BRANCH_NAME
------------------------------
Harlem - Branch 1
White Plains Downtown
Queens - La Guardia
Brooklyn DUMBO
Manhattan - Financial District
Bronx Branch 8

delete from branch
where branch_city = 'Brooklyn';
commit;

select distinct t.branch_name
from branch t
where t.assets > nvl((select min(nvl(assets,0)) from branch
                      where branch_city = 'Brooklyn'),0);
BRANCH_NAME
------------------------------
Rikers Island
Harlem - Branch 1
White Plains Downtown
Queens - La Guardia
Manhattan - Financial District
Bronx Branch 8
Manhasset 01
Queens - Flushing Meadows

Query 2 how about this:

select distinct d.customer_name 
from depositor d,account a
where d.account_number = a.account_number
and a.branch_name = 'Brooklyn';

using explicit join:

select distinct d.customer_name
from depositor d inner join account a
on d.account_number = a.account_number
and a.branch_name = 'Brooklyn';
Joel Williams
  • 76
  • 5