This question was asked to me in an screening process of a product based company.
Oracle database has employee table having EMPLOYEE_ID and SALARY as columns. Identify all the employee who get 3rd best salary in the company.
My instant answer was as follows:
SELECT * FROM EMPLOYEE WHERE ROW_NUMBER = 3 ORDER BY SALARY DESC
There was instant reaction, this query is wrong. Yes, it is. It will give me the 3rd most earning employee of the company but it will give me the THIRD BEST salary of the company.
So I modified my query a little bit and answered as follows:
SELECT * FROM EMPLOYEE WHERE SALARY = (SELECT DISTINCT(SALARY FROM EMPLOYEE WHERE ROW_NUMBER = 3 ORDER BY SALARY DESC) )
This query will work fine on oracle database but will not run on any other database. Interviewer was right. Now I did not know the way to figure out the third best salary. I did some Google search and found following options.
SELECT * FROM (SELECT EMPLOYEE_ID, SALARY, RANK() OVER (ORDER BY SALARY DESC) RANKSAL FROM EMPLOYEE) WHERE RANKSAL = 3;
SELECT * FROM EMPLOYEE E WHERE 3=(SELECT COUNT(DISTINCT SALARY) FROM EMPLOYEE WHERE E.SALARY<= SALARY);
SELECT LEVEL,MAX(SALARY) FROM EMPLOYEE WHERE LEVEL =3 CONNECT BY PRIOR SALARY > SALARY GROUP BY LEVEL;
So there are few ways to to figure out the third best salary.
Now during interview this question can be twisted in the following manner.
Oracle database has employee table having EMPLOYEE_ID and SALARY as columns. Identify all the employee who get 3rd best salary in the company.
My instant answer was as follows:
SELECT * FROM EMPLOYEE WHERE ROW_NUMBER = 3 ORDER BY SALARY DESC
There was instant reaction, this query is wrong. Yes, it is. It will give me the 3rd most earning employee of the company but it will give me the THIRD BEST salary of the company.
So I modified my query a little bit and answered as follows:
SELECT * FROM EMPLOYEE WHERE SALARY = (SELECT DISTINCT(SALARY FROM EMPLOYEE WHERE ROW_NUMBER = 3 ORDER BY SALARY DESC) )
This query will work fine on oracle database but will not run on any other database. Interviewer was right. Now I did not know the way to figure out the third best salary. I did some Google search and found following options.
SELECT * FROM (SELECT EMPLOYEE_ID, SALARY, RANK() OVER (ORDER BY SALARY DESC) RANKSAL FROM EMPLOYEE) WHERE RANKSAL = 3;
SELECT * FROM EMPLOYEE E WHERE 3=(SELECT COUNT(DISTINCT SALARY) FROM EMPLOYEE WHERE E.SALARY<= SALARY);
SELECT LEVEL,MAX(SALARY) FROM EMPLOYEE WHERE LEVEL =3 CONNECT BY PRIOR SALARY > SALARY GROUP BY LEVEL;
Now during interview this question can be twisted in the following manner.
- Find the 3rd Minimum Salary.
- Find 10th Maximum/Minimum Salary.
- Find nth Maximum/Minimum salary of the Employee.
- Find all employees having salary less than 3rd best salary.
