Write a subquery that finds average salary by each department. Check to find if employee 543’s salary satisfies =ANY, ANY, ALL condition against those departmental average salaries.

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 Lname, Fname, Salary
2 FROM employee
3 WHERE EmployeeId = 543
4 AND Salary =ANY
5 (SELECT AVG(Salary)
6 FROM employee
7 GROUP BY DeptId);

no rows selected

SQL> 4
4* AND Salary =ANY
SQL> c/=/<
4* AND Salary SQL> /

LNAME FNAME SALARY
--------------- --------------- ----------
Dev Derek 80000

SQL> 4
4* AND Salary SQL> c/
4* AND Salary >ANY
SQL> /

LNAME FNAME SALARY
--------------- --------------- ----------
Dev Derek 80000

SQL> 4
4* AND Salary >ANY
SQL> c/>ANY/ 4* AND Salary SQL> /

no rows selected

SQL> 4
4* AND Salary SQL> c/
4* AND Salary >ALL
SQL> /

no rows selected
```