Use multiple level subquery to display dependent information for employees, who belong to FINANCE department.

Question

Use the N2 Corporation database tables to design the following subqueries. (Use the spooling method to capture all queries and results in the CHAP8SP1.LST file.)

Answer 1

```
SQL> SELECT *
2 FROM dependent
3 WHERE EmployeeId IN
4 (SELECT EmployeeId
5 FROM employee
6 WHERE DeptId =
7 (SELECT DeptId
8 FROM dept
9 WHERE UPPER(DeptName) = 'FINANCE'));

EMPLOYEEID DEPENDENTID DEPDOB RELATION
---------- ----------- --------- --------
222 1 04-FEB-75 Spouse
222 2 23-AUG-97 Son
222 3 10-JUL-99 Daughter
111 1 12-DEC-45 Spouse
```