Please Use the tables below to Provide the SQL statements: 5) If an employee wor

Question

Please Use the tables below to Provide the SQL statements:

5) If an employee works on all the projects, retrieve the name of the employee.

11) List the name of each department manager who has no dependents. (do not use SQL operators and constructors such as NOT IN, NOT EXISTS, MINUS, COUNT)

13) Retrieve the name of employee whose salary is greater than the average salary of all the employees in department 5.

18) Retrieve the social security numbers of all direct and indirect supervisees James Borg has.

TABLE Employee

NAME                   SSN               BDATE             SEX            SALARY          SUPERSSN       DNO

-------------------           ---------            ---------               ---              ----------              ---------              --------

John B Smith         123456789       09-JAN-55        M               30000              333445555          5

Franklin T Wong      333445555      08-DEC-45       M               40000              888665555          5

Alicia J Zelaya        999887777       19-JUL-85        F                25000              987654321         4

Jennifer S Wallace    987654321     20-JUN-31       F                43000              888665555         4

Ramesh K Narayan    666884444    15-SEP-52       M              38000              333445555         5

Joyce A English         453453453    31-JUL-62        F               25000              333445555         5

Ahmad V Jabbar        987987987    29-MAR-59       M              25000              987654321         4

James E Borg            888665555 10-NOV-27          M              55000                                       1

TABLE Department;

DNAME                        DNUMBER         MGRSSN              MGRSTARTD

---------------                       --------                   ---------                     ---------

Research                             5                              333445555           22-MAY-78

Administration                    4                              987654321           01-JAN-85

Headquarters                      1                              888665555           19-JUN-71

TABLE Project

PNAME                      PNUMBER          PLOCATION                       DNUM

---------------                       --------                 ---------------                       --------

ProductX                             1                         Bellaire                          5

ProductY                             2                         Sugarland                      5

ProductZ                              3                        Houston                         5

Computerization                   10                       Stafford                          4

Reorganization                     20                       Houston                          1

Newbenefits                        30                        Stafford                            4

TABLE Deptlocation

DNUMBER         DLOCATION

--------                   ---------------

1                              Houston

4                              Stafford

5                              Bellaire

5                              Houston

5                              Sugarland

TABLE workson

ESSN                 PNO                     HOURS

---------                --------                    ----------

123456789           1                              32.5

123456789           2                              7.5

666884444           3                              40

453453453           1                              20

453453453           2                              20

333445555           2                              10

333445555           3                              10

333445555           10                           10

333445555           20                           10

999887777           30                           30

999887777           10                           10

ESSN                      PNO            HOURS

---------                      --------            ----------

987987987          10                        35

987987987          30                         5

987654321          30                        20

987654321          20                        15

888665555          20

Explanation / Answer

5)

Select Name from Employee where exists (select pno from Works_On where ssn = essn)

11) SELECT NAME FROM EMPLOYEE WHERE EXISTS ( SELECT * FROM DEPARTMENT WHERE SSN=MGRSSN )AND NOT IN ( SELECT * FROM DEPENDENT WHERE SSN=ESSN )

13)

select Name from employee where dno=5 group by salary having salary>avg(salary);

18)

SELECT SSN, NAMEFROM EMPLOYEEWHERE SUPERSSN = (SELECT SSN FROM EMPLOYEEWHERE (NAME = ‘James E Borg’);

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.