Answer 1)
Similar Question(s):
Q) 1st and 2nd Maximum salary through same SQL Query
There are so many ways to achieve this, but below i'm mentioning simple and best performing query
In Oracle: SELECT sal FROM(SELECT DISTINCT(sal) FROM employees ORDER BY sal DESC) WHERE ROWNUM<=2;
In SQL Server: SELECT TOP 2 sal FROM(SELECT DISTINCT(sal) FROM employees ORDER BY sal DESC)
If they ask to write without using top use below queries:
Another solution Using UNION
SELECT MAX(sal) FROM employees
UNION
SELECT MAX(sal) FROM employees WHERE sal < (SELECT MAX(sal) FROM employees)
Another solution Using DENSE_RANK
SELECT DISTINCT(sal) FROM
(SELECT sal, DENSE_RANK() OVER (ORDER BY sal DESC) AS SAL_RANK FROM employees) empT
WHERE empT.SAL_RANK <= 2
ORDER BY sal DESC
If the insist to write only correlated subquery
SELECT e.sal FROM employees e
WHERE 1 >= (SELECT count(*) FROM employess s
WHERE s.sal>e.sal)
| 
