Complex SQL queries

1. Top N Query
--> Return 5 top and bottom salary holder from a table

select  name,sal from (select name,sal from emp order by sal desc ) where rownum <=5;

select  name,sal from (select name,sal from emp order by sal ) where rownum <=5;

-->  Select Max salaried employee from department

select name,sal,dept from emp e,(select max(sal) max_sal,dept from emp group by dept) d
where e.dept=d.dept and e.sal=d.max_sal;

2. Ranking
 --> Return 5 top salary holder from each department of a emp table

select name,sal,dept from (select name,sal,dept, rank() over (partition by dept order by sal desc) as sal_rank from emp) where sal_rank <=5;

3. Window function
--> Provide a summary of the current row’s salary value together with the salary values of all previous rows whose HIRE_DATE value falls within 90 days preceding the HIRE_DATE value of the current row

select last_name, first_name, department_id, hire_date, salary, SUM (salary) OVER (PARTITION BY department_id ORDER BY hire_date RANGE 90 PRECEDING) department_total from employee order by department_id, hire_date;

4. Hierarchical Query
 --> How to find out one one employee's subordinate chain?

SELECT emp_id,emp_name,mgr_id from emp
connect by prior emp_id=mgr_id;

--> How to find out which employee at what lever in an Organization?

SELECT emp_id,emp_name,mgr_id,LEVEL from emp 

connect by prior emp_id=mgr_id;

--> Display Each employee and their immediate manager in a department.

 SELECT emp_name "Employee", CONNECT_BY_ROOT emp_name "Manager"
where LEVEL=2 and dept_id=101
connect by prior emp_id=mgr_id;

--> Display the all the superior name of a employee

 SELECT emp_name "Employee", employee_id, CONNECT_BY_ROOT emp_name "Manager",manager_id, LEVEL
      FROM employees
      START WITH employee_id = 100
      CONNECT BY PRIOR employee_id = manager_id;

5. De duplication
 --> How to find de duplicated rows based on come keys.

SELECT  * from ABC where rowid in (select max(rowid) from ABC group by A1,B1,C1);

6. Join with self tables
--> How do you find all employees which are also manager?

SELECT e.name, m.name FROM Employee e, Employee m WHERE e.mgr_id = m.emp_id;

7. Sub query and joining


-->How to find all the employee who joined last one year?

select *,sysdate - to_date(hiredate,'dateformat') datediff from emp where datediff between 0 and 365;