IMPORTANT QUESTION IN ORACLE ASKED BY INTERVIEWER

1)select MAX salary .

select  MAX(sal) from emp;

select MAX(salary) from employees;

SELECT LEVEL,MAX(sal) FROM emp WHERE LEVEL=1 CONNECT BY PRIOR sal > sal GROUP BY LEVEL;

2)select 2nd highest salary.

select  MAX(sal) from emp where sal < (select  MAX(sal) from emp)

select  MAX(salary) from employees where salary < (select  MAX(salary) from employees)

SELECT LEVEL, MAX(sal) FROM emp WHERE LEVEL=2 CONNECT BY PRIOR sal > sal GROUP BY LEVEL;

3)select 3rd,4th.......etc  highest salary

SELECT MIN(salary) FROM (SELECT DISTINCT salary FROM employees ORDER BY salary desc ) WHERE rownum <= 3 ORDER BY salary ;

SELECT MIN(sal) FROM (SELECT DISTINCT sal FROM emp ORDER BY sal desc ) WHERE rownum <= 3 ORDER BY sal ;

SELECT LEVEL, MAX(sal) FROM emp WHERE LEVEL=4 CONNECT BY PRIOR sal > sal GROUP BY LEVEL;

4)select MIN salary

select MIN(sal) from emp

5)select 2nd MIN salary

select MIN(sal) from emp where sal > (select  MIN(sal) from emp)

6)TOP 5 EARNER OF COMPANY

SELECT LEVEL,MAX(SAL)  FROM EMP WHERE LEVEL < 6  CONNECT BY PRIOR SAL > SAL GROUP BY LEVEL ORDER BY 1

7)LAST 5 EARNER OF COMPANY

SELECT  LEVEL RANK,MIN(SAL) FROM EMP WHERE LEVEL < 6 CONNECT BY PRIOR SAL < SAL GROUP BY LEVEL ORDER BY 1

SELECT ENAME,SAL SALARY  FROM EMP  WHERE SAL>=(SELECT (MAX(SAL)+MIN(SAL))/2 FROM EMP )

SELECT MAX(SAL),MIN(SAL)  FROM EMP