SQL Queries

1.  How to find the top 5 Or Topn employee salaries

select a.EmpNo, a.Sal from(

select Empno,sal, ROW_NUMBER() over(Order by sal desc) salord from Emp)a where a.salord <= 5

2.  Find out nth highest salary from emp table?

SELECT DISTINCT (a.sal) FROM EMP A WHERE 5 = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);

3.  Find out nth highest salary DEPT wise from emp table?

SELECT * FROM (SELECT DISTINCT DeptNo,(SAL),DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS RNK FROM EMP)a WHERE a.RNK = 1

4.  How do I eliminate the duplicate rows ?

delete from Emp where EmpNo in(select EmpNo from Emp group by EmpNo having count(*) >1)

5.  How to find 2nd max salary from emp ?

select max(sal) from emp  where sal not in(select max(sal) from emp)

6.  Co-related sub query:

Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.

Eg:

select e1.EmpNo, e1.sal, e1.deptno from emp e1 where e1.sal = (select max(sal) from emp e2 where e2.deptno = e1.deptno)

Nested Subquery:

Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.

Eg:

select EmpNo, sal, deptno from emp where  sal in (select max(sal) from emp group by deptno)

7.  With Clause:

with W_dept as (select Deptno,Dname from Dept where DeptNo =10)

select emp.EmpNo,emp.Ename,W_dept.Dname from emp , W_dept where Emp.DeptNo = W_dept.DeptNo

8.  Write a query to find the employees whose salary is less than the salary of all employees in department of id 10

(Multi row Subquery)

SELECT Empno, SAL FROM EMP

WHERE SAL < ALL(SELECT  SAL  FROM EMP  WHERE DeptNo = 10)