SQL Tricky Interview Questions

Query To Display Highest Salaried Employee:

SELECT * FROM EMP WHERE SAL =(SELECT MAX(SAL) FROM EMP);

OR

SELECT * FROM (SELECT ENAME,SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) RNK FROM EMP) WHERE RNK=1;


Query to Find Lowest Salaried Employee:

SELECT * FROM (SELECT ENAME,SAL,DENSE_RANK() OVER (ORDER BY SAL ASC) RNK FROM EMP) WHERE RNK=1;

OR

SELECT * FROM EMP WHERE SAL =(SELECT MIN(SAL) FROM EMP);


Query To Find Second Highest Salaried Employee:

SELECT * FROM (SELECT ENAME,SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) RNK FROM EMP) WHERE RNK=2;

Similarly, To Find Nth Highest Salaried Employee Details:


SELECT * FROM (SELECT ENAME,SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) RNK FROM EMP) WHERE RNK=N;

N is integer. For example If you want to get 3rd or 4th highest salaried employee details you should replace N with corresponding integer.

Query To Find Second Lowest Salaried Employee:

SELECT * FROM (SELECT ENAME,SAL,DENSE_RANK() OVER (ORDER BY SAL ASC) RNK FROM EMP) WHERE RNK=2;

Similarly, To Find Nth Lowest Salaried Employee Details:

SELECT * FROM (SELECT ENAME,SAL,DENSE_RANK() OVER (ORDER BY SAL ASC) RNK FROM EMP) WHERE RNK=N;

N is integer. For example If you want to get 3rd or 4th lowest salaried employee details you should replace N with corresponding integer.


Query To Find Max Salaried Employee Details Department wise:

SELECT E.ENAME,E.DEPTNO,E.SAL FROM EMP E,(SELECT DEPTNO,MAX(SAL)SAL FROM EMP GROUP BY DEPTNO) D

WHERE E.DEPTNO=D.DEPTNO AND E.SAL=D.SAL;