How do you find the Second highest Salary?

This is the most common question asked in Interviews.

EMPLOYEE table has fields EMP_ID and SALARY how do you find the second highest salary?

Answer:

We can write a sub-query to achieve the result

SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)

The first sub-query in the WHERE clause will return the MAX SALARY in the table, the main query SELECT’s the MAX SALARY from the results which doesn’t have the highest SALARY.

How many rows this query will return

Tables EMP and MANAGER has 5 records each.

How many rows will the following query return?

SELECT * FROM EMP, MANAGER

Answer:

25 rows. Each rows in EMP will have five rows of MANAGER which makes 5×5 = 25.