SQL Review Exercise 3

For each of the following SQL statements, give an equivalent relational algebra expression, if possible.

1. select distinct B.age, B.rank 
   from employee A, employee B 
   where A.enum > B.enum

   _{B.age, B.rank}(_{A.enum > B.enum}(_A(employee) × _B(employee)))

2. select max(salary) 
   from employee 
   where rank = manager

   _salary(_rank=manager(employee)) - _{employee.salary}(_{employee.salary < E.salary}(_rank=manager(employee) × _rank=manager(_E(employee))))

3. select deptnum, max(salary) 
   from employee 
   where rank = manager
   group by deptnum

   NOT POSSIBLE

4. select distinct enum 
   from employee 
   where not exists
        (select * 
         from projects 
         where not exists
                   (select * 
                    from works 
                    where employee.enum = works.enum and works.pnum = projects.pnum))

   _{employee.enum, works.pnum}(_{employee.enum = works.enum} (employee × works)) / _pnum(projects)

Updated: Mar 1, 2003