1/* sql 2nd highest salary employee */
2select sal, ename
3from emp
4where sal =
5 (
6 select max(sal) from emp where sal <
7 (select max(sal) from emp)
8 )
9----------------------------------------------- option 2
10select *
11from
12(
13 select ename, sal, dense_rank() over(order by sal desc) rank
14 from emp
15)
16where rank =2;
1SELECT E.Employers_name, E.dep_number, E.salary
2FROM Employers E
3WHERE 1 = (SELECT COUNT(DISTINCT salary)
4 FROM Employers B
5 WHERE B.salary > E.salary AND E.dep_number = B.dep_number)
6group by E.dep_number
1--Find out the name of top earner in each departments
2--Output has Name, Department name and max salary of the department
3
4SELECT E.FIRST_NAME , D.DEPARTMENT_NAME, E.SALARY
5FROM EMPLOYEES E
6JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
7WHERE SALARY IN(SELECT MAX(E.SALARY)
8FROM EMPLOYEES E
9JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
10GROUP BY DEPARTMENT_NAME);