Example-Search the salary of employees who are not 'ANALYST' but get a salary below than or equal to any people employed as 'ANALYST'.
SELECT EMPNO, ENAME, JOB, SAL
FROMEMP
WHERE SAL <= ANY (SELECT SAL
FROM EMP WHERE JOB = 'ANALYST')
AND JOB<>'ANALYST' ;
|
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
|
OUTPUT:
|
EMPNO
|
ENAME
|
JOB
|
SAL
|
|
7566
|
Nirmal
|
MANAGER
|
2975
|
Search the average salary in every department
SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO;
Result:
|
DEPTNO
|
SAL
|
|
10
|
2987.5
|
|
20
|
4000
|