Set Comparison

1. To compare set elements in terms of inequalities, we can write

    select distinct T.bname
   
   		 from branch T,branch S
   
   		 where T.assets > S.assets
   and S.bcity=``Burnaby''
   
   

   or we can write

    select bname
   
   		 from branch
   
   		 where assets > some
   
   		 		 		 (select assets
   from branch
   where bcity=``Burnaby'')
   
   

   to find branches whose assets are greater than some branch in Burnaby.

2. We can use any of the equality or inequality operators with some. If we change > some to > all, we find branches whose assets are greater than all branches in Burnaby.
3. Example. Find branches with the highest average balance. We cannot compose aggregate functions in SQL, e.g. we cannot do max (avg ...)).

   Instead, we find the branches for which average balance is greater than or equal to all average balances:

    select bname
   
   		 from account
   
   		 group by bname
   
   		 having avg (balance)    all
   
   		 		 		 		 		 (select avg (balance)
   
   		 		 		 		 		 from account
   
   		 		 		 		 		 group by bname)