A nested query is a query within a query. More specifically, a nested query is a parenthesized query whose result can be used as a value in a number of places, such as instead of a relation. Specify the following queries on the database specified in Figure 5.5 using the concept of nested queries and the relational operators discussed in this chapter. Also show the result of each query as it would apply to the database state in Figure 5.6.

a. List the names of all employees who work in the department that has the employee with the highest salary among all employees.

b. List the names of all employees whose supervisor’s supervisor has ‘888665555’ for Ssn.

c. List the names of employees who make at least $10,000 more than the employee who is paid the least in the company.

Figure 5.5

Figure 5.6

Transcribed Image Text:

Figure 5.6 One possible database state for the COMPANY relational database schema. ΕMPLOYEΕ Fname Minit Lname Sen Bdate Address Sex Salary Super ssn Dno John B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 333445555 1955-12-08 638 Voss, Houston, TX 999887777 | 1968-01-19 3321 Castle, Spring, TX Franklin Wong M 40000 888665555 Alicia Zelaya 25000 987654321 43000 888665555 4 Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M English 453453453 1972-07-31 5631 Rice, Houston, TX 987987987 1969-03-29 080 Dallas, Houston, TX 888665555 1937-11-10 450 Stone, Houston, TX Jennifer 4 K 38000 333445555 25000 333445555 25000 987654321 Ramesh Joyce A F Ahmad V Jabbar M James Borg M 55000 NULL DEPARTMENT DEPT LOCATIONS Dname Dnumber Mgr_san Mgr_start_date Dnumber Dlocation Research 333445555 1988-05-22 1 Houston Administration 4 987654321 1995-01-01 4 Stafford Headquarters 1 888665555 1981-06-19 Bellaire Sugarland Houston WORKS ON PROJECT Essn Pno Hours Prame Pnumber Plocation Dnum 123456789 1. 32.5 ProductX Bellaire 123456789 2 7.5 ProductY 2 Sugarland 666884444 3 40.0 Productz 3 Houston 453453453 20.0 Computerization 10 Stafford 4 453453453 2 20.0 Reorganization 20 Houston 333445555 10.0 Newbenefits 30 Stafford 4 333445555 3 10.0 333445555 10 10.0 DEPENDENT 333445555 20 10.0 Essn Dependent name Relationship Sex Bdate 999887777 30 30.0 333445555 Alice 1986-04-05 Daughter 999887777 10 10.0 333445555 Theodore M 1983-10-25 Son 987987987 10 35.0 333445555 Joy 1958-05-03 Spouse 987987987 30 5.0 987654321 Abner M 1942-02-28 Spouse 987654321 30 20.0 123456789 Michael M 1988-01-04 Son 987654321 20 15.0 123456789 Alice 1988-12-30 Daughter 888665555 20 NULL 123456789 Elizabeth 1967-05-05 Spouse