Example: Find out the employee who draws a salary more than the average salary of all the departments.
|
EMPNO
|
ENAME
|
JOB
|
SAL
|
DEPTNO
|
|
7566
|
Nirmal
|
MANAGER
|
2975
|
10
|
|
7788
|
Kailash
|
ANALYST
|
3000
|
10
|
|
7839
|
Karuna
|
PRESIDENT
|
5000
|
20
|
|
7902
|
Ashwin
|
ANALYST
|
3000
|
20
|
|
7905
|
Ashwini
|
MANAGER
|
4000
|
20
|
SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE SAL> ALL (SELECT AVG (SAL)
FROM EMP
GROUP BY DEPTNO);
OUPUT:
|
EMPNO
|
ENAME
|
JOB
|
SAL
|
|
7839
|
Karuna
|
PRESIDENT
|
5000
|