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)
