SQL Review Exercise I

Consider the following collection of relation schemes:

1. Find all the professors who are in any one of the committees that professor Piper is in.

   select distinct B.profname 
   from committee A, committee B
   where A.profname = 'Piper' and B.commname = A.commname

   or

   select distinct profname 
   from committee
   where commname in 
           (select commname 
            from committee 
            where profname = 'Piper')

2. Find all the professors who are in at least all those committees that professor Piper is in.

   select distinct B.profname 
   from committee B 
   where  not exists
             ( (select commname 
                 from committee A 
                 where profname = 'Piper') 
              except
              (select commname 
               from committee A 
               where A.profname = B.profname) )

               Note that in Oracle, we would use 'minus' rather than 'except' in this query
   

3. Find all the professors who have not offices in any of  those buildings that Professor Piper has offices in.

   select distinct P.profname 
   from professor P
   where not exists
         ( (select building 
            from department D 
            where P.deptname = D.deptname ) 
           intersect
          (select building 
           from department D1, professor P1
           where  P1.profname = 'Piper' and P1.deptname = D1.deptname) )