CONNECT BY

CONNECT BY, PRIOR and START WITH

There are two mandatory keywords to build a hierarchy, CONNECT BYand PRIOR. A hierarchy is built when one row is the parent of another row. START WITHdefines the first ancestor.

SELECT
   ENAME
FROM
   EMP
CONNECT BY
   PRIOR EMPNO = MGR
START WITH
   ENAME = 'JONES';

ENAME
----------
JONES
SCOTT
ADAMS
FORD
SMITH

Jones and his employees are returned. Adams is an employee of Scott and Scott is an employee of Jones so Adams is also returned. 

LEVEL

The pseudo-column level returns the depth of the hierarchy. The first level is the root:

SELECT
   ENAME
FROM
   EMP
WHERE
   LEVEL=2
CONNECT BY
   PRIOR EMPNO = MGR
START WITH
   ENAME = 'JONES';

ENAME
--------
SCOTT
FORD

Only the direct employees of Jones are returned. Jones is the first ancestor and has a level of 1. Adams and Smith are one level below the direct employees and belong to the third level started by Jones.

The tree is displayed with the children indented under their parents by using padding with a number of spaces proportional to LEVEL.

SELECT
   CONCAT
   (
      LPAD
      (
         ' ',
         LEVEL*3-3
      ),
      ENAME
   ) ENAME
FROM
   EMP
CONNECT BY
   PRIOR EMPNO = MGR
START WITH
   MGR IS NULL;

ENAME
------------------
KING
   JONES
      SCOTT
         ADAMS
      FORD
         SMITH
   BLAKE
      ALLEN
      WARD
      MARTIN
      TURNER
      JAMES
   CLARK
      MILLER

Starting with the top manager, the names of the employees are padded with white spaces according to their level.

ORDER SIBLINGS BY

The rows in a hierarchical query are returned as a tree, the children following the parent. ORDER SIBLINGS BYpreserves the hierarchy and orders the children of each parent.

SELECT
   CONCAT
   (
      LPAD
      (
         ' ',
         LEVEL*3-3
      ),
      ENAME
   ) ENAME
FROM
   EMP
CONNECT BY
   PRIOR EMPNO = MGR
START WITH
   MGR IS NULL
ORDER SIBLINGS BY
   EMP.ENAME;

ENAME
----------------
KING
   BLAKE
      ALLEN
      JAMES
      MARTIN
      TURNER
      WARD
   CLARK
      MILLER
   JONES
      FORD
         SMITH
      SCOTT
         ADAMS

Clark comes after Blake and before Jones; they are under King and ordered by their name. Their children are sorted and the hierarchical appearance is preserved.

ORDER BYwithout SIBLINGSdestroys the hierarchy:

SELECT
   CONCAT
   (
      LPAD
      (
         ' ',
         LEVEL*3-3
      ),
      ENAME
   ) ENAME
FROM
   EMP
CONNECT BY
   PRIOR EMPNO = MGR
START WITH
   MGR IS NULL
ORDER BY
   EMP.ENAME;

ENAME
--------------
         ADAMS
      ALLEN
   BLAKE
   CLARK
      FORD
      JAMES
   JONES
KING
      MARTIN
      MILLER
      SCOTT
         SMITH
      TURNER
      WARD

Some Complex quries

1) How to delete duplicate records
A) delete from emp where rowid != (select max(rowid) from emp where group by empno);
==========================================================================================================
2) update the salary with avg sal of the employee whoes salary is less than avg salary of their own dept.

update emp a set sal = (select avg(sal) from emp b where a.deptno = b.deptno)
where sal < (select avg(sal) from emp c where  c.deptno = b.deptno);
==========================================================================================================
3) N th  Max sal record

    1)select * from (select rownum R,sal S (select distinct sal s from emp order by sal desc)) where R = &no;
  if you want records then --
    2)select * from emp where rowid in
 ( select a from ( select  rowid a, dense_rank()  over (order by sal desc) r ,sal from (select sal from emp order by sal desc) ) where r = &N)

     3)select max(sal) ,level from emp where level = &n  connect by prior sal>sal GROUP by level;
==========================================================================================================   
4) list of top 5 salaries

select * from (select rownum R,sal S (select distinct sal s from emp order by sal desc)) where R <=5;
==========================================================================================================
5)3 rd max/min sal

select a.sal from emp a where 3=(select count(*)  from emp b where a.sal<=b.sal);
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);
===========================================================================================================
6)select max sal of each dept

select deptno ,max(sal) from emp group by deptno;   
===========================================================================================================
7) fetch alternative records in table(even)

select * from emp where rowid in (select decode( mod(rownum,2) ,0 ,rowid ,null) from emp)

8)fetch alternative records in table(odd)

select * from emp where rowid in (select decode( mod(rownum,2) ,0 ,null,rowid) from emp)

9) last n records

select * from emp minus
(select * from emp where ronum <=
(select count(*)-&n from emp ))

10) Select all records where dept no of both emp and dept table matches.
select * from emp where exists(select * from dept where emp.deptno=dept.deptno)

11)If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?
(Select * from emp) Union (Select * from emp1)

12)How to fetch only common records from two tables emp and emp1?
(Select * from emp) Intersect (Select * from emp1)

13) How can I retrive all records of emp1 those should not present in emp2?
(Select * from emp) Minus (Select * from emp1)

14)Count the total sal  deptno wise where more than 2 employees exist.
SELECT  deptno, sum(sal) As totalsal
FROM emp
GROUP BY deptno
HAVING COUNT(empno) > 2

15) select second highest salry of each deptno.

select max(sal) from emp a
where sal < (select max(sal) from emp b where a.deptno = b.deptno)
group by deptno;