Example-Find the employee name, salary, department number and average salary of his/her department, for those employees whose salary is more than the average salary of that department.
|
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 A.ENAME, A.SAL, A.DEPTNO, B.AVGSAL
FROM EMP A, ( SELECT DEPTNO, AVG (SAL) AVGSAL
FROM EMP
GROUP BY DEPTNO) B
WHERE A.DEPTNO=B.DEPTNO AND A.SAL> B. AVGSAL;
Output:
|
ENAME
|
SAL
|
DEPTNO
|
AVGSAL
|
|
Kailash
|
3000
|
10
|
2987.5
|
|
Karuna
|
5000
|
20
|
4000
|