next up previous
Next: Test for Empty Relations Up: Nested Subqueries Previous: Set Membership

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) tex2html_wrap_inline1808 all

    (select avg (balance)

    from account

    group by bname)

Osmar Zaiane
Fri May 22 19:39:12 PDT 1998