Introduction to Database Management Systems Write the follow

Introduction to Database Management Systems:

Write the following queries in Relational Algebra:

(a) Find the supervisor Ssn of employees who work on projects controlled by department number 5.
(b) Retrieve the names of employees who work on all the projects in which the employee with ssn ‘453453453’ is working on.
(c) Make a list of the Fname and Lname of all employees who are working in department number 5 or employees who are working on projects that are located in Sharjah.

Solution

a) Retrieve the names of all employees who work in department number 5.

Select ename

from emp, dept

where emp.deptno = dept.deptno and dno = 5;

b) Retrieve the names of employees who work on all the projects in which the employee with ssn ‘453453453’ is working on.

SELECT EMPLOYEE.FNAME, EMPLOYEE.LNAME

                          FROM    EMPLOYEE

              WHERE   EXISTS

               (SELECT   *   

                        FROM     WORKS_ON

                        WHERE    WORKS_ON.ESSN = EMPLOYEE.SSN

                                                AND      WORKS_ON.PNO =453453453)

c) Make a list of the Fname and Lname of all employees who are working in department number 5 or employees who are working on projects that are located in Sharjah.

SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME

FROM EMPLOYEE E S

WHERE E.SUPERSSN=S.SSN