SQL Review Exercise 2

Exercise 2

Two queries are said to be equivalent if they give the same answer for every instance of the database. Consider the following database:

For each of the following five pairs of SQL queries, determine whether they are equivalent. If they are not, give a database instance as a counter-example. Otherwise, just state that they are equivalent.

1.  
   • select name 
     from donation A
     where not exists

     (select * from donation B
     where name = 'Campbell'
     and not exists

     (select * from donation C
     where C.organization = B.organization
     and C.name = A.name))

   • select name 
     from donation A
     where not exists

     (select * from donation
     where name = 'Campbell'
     and organization not in

     (select organization 
     from donation B
     where B.name = A.name))

     Yes, they are equivalent.

2.  
   • (select name 
     from government
     where salary >= 100000)
     union
     (select name 
     from government
     where salary < 100000)

   • select name 
from government

     No, a counterexample is:

     government relation

     name	salary
     Albert	150000
     Bobbie	50000
     Charlie	NULL

     Then, the first query returns 

                     Albert, Bobbie 

     but the second query returns 

                    Albert, Bobbie and Charlie

3.  
   • (select name 
     from government
     where rank = minister)
     union
     (select name from donation
     where amount >= 100000)

   • select government.name 
from government, donation
where government.name = donation.name
      and (rank = minister or amount >= 100000)

     No, a counterexample is:

     government relation

     name	rank
     Albert	minister
     Bobbie	clerk

     donation relation

     name	amount
     Charlie	150000
     Don	50000

     Then, the first query returns 

           Albert, Charlie;

     but  the second query returns empty set
     
     
     

4.  
   • select name 
     from politician
     where name not in
          (select name from government)

   • select name 
from politician
where not exists
    (select * 
     from government
     where government.name = politician.name)

     Yes, they are equivalent.

   
   

5.  
   • select name 
     from politician
     where name not in
            (select name from government)

   • select politician.name 
from politician, government
where  not politician.name = government.name

     No, a counterexample is:

     politician relation

     name
     Albert
     Charlie

     government relation

     name
     Albert
     Bobbie

     Then, the first query returns 

             Charlie

     but the second query returns 

             Albert and Charlie

   ---

Updated : Feb 28, 2003