Wednesday, October 23, 2013

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);           

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect