Level in Oracle

SELECT Ename, Job, MGR, Level

FROM Emp;

SELECT

LPAD (LEVEL, ((2 * LEVEL) - 1)) Org_Level,

Ename, Empno, Mgr, Job

FROM Emp

START WITH Job = 'PRESIDENT'

CONNECT BY PRIOR Empno = MGR;

SELECT

LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename Org_Chart,

Empno, MGR, Job

FROM Emp

START WITH Job = 'PRESIDENT'

CONNECT BY PRIOR Empno = MGR;

SELECT

LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename Org_Chart,

Empno, MGR, Job , Sal

FROM Emp

WHERE Job != 'ANALYST'

START WITH Job = 'PRESIDENT'

CONNECT BY PRIOR Empno = MGR;

SELECT

LPAD(' ', ((2 * LEVEL) - 1))||' '||Ename Org_Chart,

Empno, MGR, Job , Sal

FROM Emp

WHERE Job != 'ANALYST'

START WITH Job = 'PRESIDENT'

CONNECT BY PRIOR Empno = MGR;

SELECT

LPAD(' ', ((2 * LEVEL) - 1))||' '||Ename Org_Chart,

Empno, MGR, Job, Sal 

FROM Emp

START WITH Job = 'PRESIDENT'

CONNECT BY PRIOR Empno = MGR 

AND LEVEL <= 2;

SELECT

LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename Org_Chart,

Empno, MGR, Job, Sal

FROM Emp

START WITH Job = 'ANALYST'

CONNECT BY PRIOR Empno =   MGR;

SELECT

LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename Org_Chart,

Empno, MGR, Job, Sal

FROM Emp

START WITH Job = 'ANALYST'

CONNECT BY Empno = PRIOR  MGR;

SELECT

LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename Org_Chart,

Empno, MGR, Job, Sal

FROM Emp

START WITH Job = 'PRESIDENT'

CONNECT BY NOCYCLE PRIOR  Empno = MGR

AND LEVEL = 2;

SELECT

LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename Org_Chart,

Empno, MGR, Job, Sal

FROM Emp

START WITH Job = 'PRESIDENT'

CONNECT BY NOCYCLE PRIOR  Empno = MGR

AND LEVEL IN(2, 4);

SELECT

LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename Org_Chart,

Empno, MGR, Job, Sal

FROM Emp

WHERE LEVEL IN(2, 4)

START WITH Job = 'PRESIDENT'

CONNECT BY NOCYCLE PRIOR  Empno = MGR;

SELECT

LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename Org_Chart,

Empno, MGR, Job, Sal

FROM Emp

WHERE MOD(LEVEL, 2) = 0

START WITH Job = 'PRESIDENT'

CONNECT BY NOCYCLE PRIOR Empno = MGR;

SELECT

LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename Org_Chart,

Empno, MGR, Job, Sal

FROM Emp

WHERE MOD(LEVEL, 2) = 1

START WITH Job = 'PRESIDENT'

CONNECT BY NOCYCLE PRIOR Empno = MGR;

SELECT

LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename Org_Chart,

Empno, MGR, Job, Sal

FROM Emp

WHERE LEVEL BETWEEN 2 AND 4

START WITH Job = 'PRESIDENT'

CONNECT BY NOCYCLE PRIOR Empno = MGR;

SELECT

LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename Org_Chart,

Empno, MGR, Job, Sal

FROM Emp

WHERE LEVEL BETWEEN 2 AND 4 AND Sal > 2500

START WITH Job = 'PRESIDENT'

CONNECT BY NOCYCLE PRIOR Empno = MGR;

SELECT

LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename Org_Chart,

Empno, MGR, Job, Sal

FROM Emp

WHERE Sal = (SELECT MAX(Sal)

                        FROM Emp

                        WHERE LEVEL = 2

                        START WITH Job = 'PRESIDENT'

                        CONNECT BY PRIOR Empno = MGR)

START WITH Job = 'PRESIDENT'

CONNECT BY NOCYCLE PRIOR Empno = MGR;

SELECT

LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename Org_Chart,

Empno, Dept.Deptno, Dname, Sal

FROM Emp, Dept

WHERE Sal >ANY (SELECT MAX(Sal)

                FROM Emp

                WHERE LEVEL = 2

                START WITH Job = 'PRESIDENT'

                CONNECT BY PRIOR Empno = MGR) AND Dept.Deptno = Emp.Deptno

START WITH Job = 'PRESIDENT'

CONNECT BY NOCYCLE PRIOR Empno = MGR;

SELECT

LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename Org_Chart,

Empno, Dept.Deptno, Dname, Sal, Grade

FROM Emp, Dept, SalGrade

WHERE Sal >ANY (SELECT MAX(Sal)

                FROM Emp

                WHERE LEVEL = 2

                START WITH Job = 'PRESIDENT'

                CONNECT BY PRIOR Empno = MGR) AND Dept.Deptno = Emp.Deptno AND

                Sal BETWEEN Losal AND HiSal

START WITH Job = 'PRESIDENT'

CONNECT BY NOCYCLE PRIOR Empno = MGR;

SELECT LEVEL, MAX(Sal)

FROM EMP

WHERE LEVEL = &LEVELNO

CONNECT BY PRIOR Sal > Sal

GROUP BY LEVEL;

SELECT Ename, Sal, Deptno, Job

FROM Emp

WHERE Sal = (SELECT MAX(Sal)

            FROM EMP

            WHERE LEVEL = &LEVELNO

            CONNECT BY PRIOR Sal > Sal

            GROUP BY LEVEL);

           

SELECT LEVEL, MIN(Sal)

FROM EMP

WHERE LEVEL = &LEVELNO

CONNECT BY PRIOR Sal < Sal

GROUP BY LEVEL;

SELECT Ename, Sal, Deptno, Job

FROM Emp

WHERE Sal = (SELECT MIN(Sal)

            FROM EMP

            WHERE LEVEL = &LEVELNO

            CONNECT BY PRIOR Sal < Sal

            GROUP BY LEVEL);

SELECT LEVEL, MIN(HireDate)

FROM EMP

WHERE LEVEL = &LEVELNO

CONNECT BY PRIOR HireDate < HireDate

GROUP BY LEVEL;

SELECT Ename, Sal, HireDate

FROm Emp

WHERE HireDate = (SELECT MIN(HireDate)

                    FROM EMP

                    WHERE LEVEL = &LEVELNO

                    CONNECT BY

                    PRIOR HireDate < HireDate

                    GROUP BY LEVEL);