Hierarchical queries in Oracle

Hierarchical queries in Oracle – The CONNECT BY clause
The pseudocolumn LEVEL returns the number 1 for the root of the hierarchy, 2 for the
child, 3 for the grandchild, and so on.
Simple Hierarchy Operations
Finding the Root Node
we look for the one node with no parent.
In the EMPLOYEE table we discussed earlier, the value for MANAGER_EMP_ID is
NULL for the uppermost employee,
SELECT EMP_ID, LNAME, DEPT_ID, MANAGER_EMP_ID, SALARY, HIRE_DATE
FROM EMPLOYEE
WHERE MANAGER_EMP_ID IS NULL;
EMP_ID LNAME DEPT_ID MANAGER_EMP_ID SALARY HIRE_DATE
--------- ---------- --------- -------------- --------- ---------
7839 KING 10 5000 17-NOV-81
Finding a Node’s Immediate Parent
SELECT E.LNAME "Employee", M.LNAME "Manager"
FROM EMPLOYEE E, EMPLOYEE M
WHERE E.MANAGER_EMP_ID = M.EMP_ID (+);
Employee Manager
-------------------- --------------------
SMITH FORD
ALLEN BLAKE
WARD BLAKE
JONES KING
MARTIN BLAKE
BLAKE KING
CLARK KING
SCOTT JONES
KING
TURNER BLAKE
ADAMS SCOTT
JAMES BLAKE
FORD JONES
MILLER CLARK
14 rows selected.
Finding Leaf Nodes
SELECT EMP_ID, LNAME, DEPT_ID, MANAGER_EMP_ID, SALARY, HIRE_DATE
FROM EMPLOYEE E
WHERE EMP_ID NOT IN
(SELECT MANAGER_EMP_ID FROM EMPLOYEE
WHERE MANAGER_EMP_ID IS NOT NULL);
EMP_ID LNAME DEPT_ID MANAGER_EMP_ID SALARY HIRE_DATE
--------- ---------- --------- -------------- --------- ---------
7369 SMITH 20 7902 800 17-DEC-80
7499 ALLEN 30 7698 1600 20-FEB-81
7521 WARD 30 7698 1250 22-FEB-81
7654 MARTIN 30 7698 1250 28-SEP-81

7844 TURNER 30 7698 1500 08-SEP-81
7876 ADAMS 20 7788 1100 23-MAY-87
7900 JAMES 30 7698 950 03-DEC-81
7934 MILLER 10 7782 1300 23-JAN-82
8 rows selected.
Oracle provides the following three constructs to effectively and efficiently
perform hierarchical queries:
• The START WITH...CONNECT BY clause
• The PRIOR operator
• The LEVEL pseudocolumn
START WITH...CONNECT BY and PRIOR
We can extract information in hierarchical form from a table containing hierarchical
data by using the SELECT statement’s START WITH...CONNECT BY clause
syntax for this clause is:
[[START WITH condition1] CONNECT BY condition2]
START WITH condition1-
Specifies the root row(s) of the hierarchy. All rows that satisfy condition1 are
considered root rows. If we don’t specify the START WITH clause, all rows are
considered root rows, which is usually not desirable. We can include a subquery
in condition1.
CONNECT BY condition2-
Specifies the relationship between parent rows and child rows in the hierarc where columns from
the current row are compared to corresponding parent columns. condition2 must
contain the PRIOR operator, which is used to identify columns from the parent
row. condition2 cannot contain a subquery.
. PRIOR is a built-in Oracle SQL operator that is used with hierarchical queries only. In
a hierarchical query, the CONNECT BY clause specifies the relationship between parent
and child rows. When we use the PRIOR operator in an expression in the CONNECT
BY condition, the expression following the PRIOR keyword is evaluated for
the parent row of the current row in the query. In the following example, PRIOR is
used to connect each row to its parent by connecting MANAGER_EMP_ID in the
child to EMP_ID in the parent:
SELECT LNAME, EMP_ID, MANAGER_EMP_ID
FROM EMPLOYEE
START WITH MANAGER_EMP_ID IS NULL
CONNECT BY PRIOR EMP_ID = MANAGER_EMP_ID;
LNAME EMP_ID MANAGER_EMP_ID
-------------------- ---------- --------------
KING 7839
JONES 7566 7839
SCOTT 7788 7566

ADAMS 7876 7788
FORD 7902 7566
SMITH 7369 7902
BLAKE 7698 7839
ALLEN 7499 7698
WARD 7521 7698
MARTIN 7654 7698
TURNER 7844 7698
JAMES 7900 7698
CLARK 7782 7839
MILLER 7934 7782
14 rows selected LNAME EMP_ID MANAGER_EMP_ID
-------------------- ---------- --------------
KING 7839
JONES 7566 7839
SCOTT 7788 7566
ADAMS 7876 7788
FORD 7902 7566
SMITH 7369 7902
BLAKE 7698 7839
ALLEN 7499 7698
WARD 7521 7698
MARTIN 7654 7698
TURNER 7844 7698
JAMES 7900 7698
CLARK 7782 7839
MILLER 7934 7782
14 rows selected
As usual we’ll work on the EMP table that is already furnished with a hierarchical self relation. On that table,
indeed, there’s a column, MGR, that stores, for each employee, his boss’s EMPNO.
Data contained into the table is as follows:
SQL> select empno, ename, mgr
2 from emp;
EMPNO ENAME MGR
---------- ---------- ----------
7369 SMITH 7902
7499 ALLEN 7698
7521 WARD 7698
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7782 CLARK 7839
7788 SCOTT 7566
7839 KING
7844 TURNER 7698
7876 ADAMS 7788
7900 JAMES 7698
7902 FORD 7566
7934 MILLER 7782
KING is the root of the hierarchy since hi has no boss.
The first example of hierarchical query is the following:

SQL> select empno, ename, mgr, prior ename, level
2 from emp
3 connect by prior empno = mgr
4 start with mgr is null;
EMPNO ENAME MGR PRIORENAME LEVEL
---------- ---------- ---------- ---------- ----------
7839 KING 1
7566 JONES 7839 KING 2
7788 SCOTT 7566 JONES 3
7876 ADAMS 7788 SCOTT 4
7902 FORD 7566 JONES 3
7369 SMITH 7902 FORD 4
7698 BLAKE 7839 KING 2
7499 ALLEN 7698 BLAKE 3
7521 WARD 7698 BLAKE 3
7654 MARTIN 7698 BLAKE 3
7844 TURNER 7698 BLAKE 3
7900 JAMES 7698 BLAKE 3
7782 CLARK 7839 KING 2
7934 MILLER 7782 CLARK 3
In a hierarchy tree, the term level refers to one layer of nodes.
Let’s analyze it: the CONNECT BY clause, mandatory to make a hierarchical query, is used to define
how each record is connected to the hierarchical superior.
The father of the record having MGR=x has EMPNO=x.
On the other hand, given a record with EMPNO=x, all the records having MGR=x are his sons.
The unary operator PRIOR indicates “the father of”.
START WITH clause is used to from which records we want to start the hierarchy, in our example we
want to start from the root of the hierarchy, the employee that has no manager.
The root of the hierarchy could be not unique. In this example it is.
The LEVEL pseudocolumn indicates at which level each record stays in the hierarchy, starting from the
root that has level=1.
Once understood the quey, we can read our resulting tree: KING is the root and has level=1.
Under KING there are three employees at level 2(JONES,BLAKE e CLARK). Then the others.
How does Oracle make the hierarchy? First of all it reads the records.
Then it determines the roots applying the START WITH clause.
Then, starting from each root, it determines the first-level sons applying the CONNECT BY clause and
so on…
To enhance the layout of the output we can use the following trick:
SQL> select empno,lpad(' ',level*3,' ')||ename name,
2 mgr, prior ename, level
3 from emp
4 connect by prior empno = mgr
5 start with mgr is null;
EMPNO NAME MGR PRIORENAME LEVEL
---------- ------------------ ---------- ---------- ----------
7839 KING 1
7566 JONES 7839 KING 2
7788 SCOTT 7566 JONES 3
7876 ADAMS 7788 SCOTT 4
7902 FORD 7566 JONES 3
7369 SMITH 7902 FORD 4
7698 BLAKE 7839 KING 2

7499 ALLEN 7698 BLAKE 3
7521 WARD 7698 BLAKE 3
7654 MARTIN 7698 BLAKE 3
7844 TURNER 7698 BLAKE 3
7900 JAMES 7698 BLAKE 3
7782 CLARK 7839 KING 2
7934 MILLER 7782 CLARK 3
We just added some white-spaces on the left of the name depending on the level. Now it’s all more
clear…
Records order defines the hierarchy, adding an ORDER BY clause we could completely lose the
hierarchy.
We can decide how to sort siblings, that is to say records on the same level. For example JONES,
BLAKE and CLARK:
SQL> select empno,lpad(' ',level*3,' ')||ename nome,
2 mgr, prior ename, level
3 from emp
4 connect by prior empno = mgr
5 start with mgr is null
6 order siblings by ename;
EMPNO NOME MGR PRIORENAME LEVEL
---------- ------------------ ---------- ---------- ----------
7839 KING 1
7698 BLAKE 7839 KING 2
7499 ALLEN 7698 BLAKE 3
7900 JAMES 7698 BLAKE 3
7654 MARTIN 7698 BLAKE 3
7844 TURNER 7698 BLAKE 3
7521 WARD 7698 BLAKE 3
7782 CLARK 7839 KING 2
7934 MILLER 7782 CLARK 3
7566 JONES 7839 KING 2
7902 FORD 7566 JONES 3
7369 SMITH 7902 FORD 4
7788 SCOTT 7566 JONES 3
7876 ADAMS 7788 SCOTT 4
With no impact on the hierarchy we’ve sorted siblings by name.
If you work on Oracle9i you can stop reading, what follows is available on Oracle10g and 11g…
In any hierarchy infinite loops can raise. For example what happens if KING has a manager himself?
SQL> update emp set mgr=7369 where ename='KING';
KING is one of the SMITH’s sons, SMITH is himself a KING’s great grandson…
What if we run the same query now?
SQL> select empno,lpad(' ',level*3,' ')||ename name,
2 mgr, prior ename, level
3 from emp
4 connect by prior empno = mgr
5 start with mgr is null
6 order siblings by ename;

No rows selected
Of course, because there’s no record that has MGR null… So let’s change the START WITH as follows:
SQL> select empno,lpad(' ',level*3,' ')||ename name,
2 mgr, prior ename, level
3 from emp
4 connect by prior empno = mgr
5 start with empno=7839;
ERROR:
ORA-01436: CONNECT BY loop in user data
Here’s the loop, Oracle can’t create the hierarchy.
Oracle has managed it introducing the NOCYCLE clause
SQL> select empno,lpad(' ',level*3,' ')||ename name,
2 mgr, prior ename, level
3 from emp
4 connect by nocycle prior empno = mgr
5 start with empno=7839;
EMPNO NAME MGR PRIORENAME LEVEL
---------- ------------------ ---------- ---------- ----------
7839 KING 7369 1
7566 JONES 7839 KING 2
7788 SCOTT 7566 JONES 3
7876 ADAMS 7788 SCOTT 4
7902 FORD 7566 JONES 3
7369 SMITH 7902 FORD 4
7698 BLAKE 7839 KING 2
7499 ALLEN 7698 BLAKE 3
7521 WARD 7698 BLAKE 3
7654 MARTIN 7698 BLAKE 3
7844 TURNER 7698 BLAKE 3
7900 JAMES 7698 BLAKE 3
7782 CLARK 7839 KING 2
7934 MILLER 7782 CLARK 3
That clause tells to Oracle to stop when a loop is raised, Oracle goes on on the other branchs of the tree.
After SMITH another instance of KING (and all his descendant) should be extracted, and so on,
generating an infinite output. The NOCYCLE clause prevents that.
CONNECT_BY_ISCYCLE pseudocolumn tells us in which records a loop has been detected:
SQL> select empno,lpad(' ',level*3,' ')||ename name,
2 mgr, prior ename, connect_by_iscycle IFLOOP
3 from emp
4 connect by nocycle prior empno = mgr
5 start with empno=7839;
EMPNO NAME MGR PRIORENAME IFLOOP
---------- ------------------ ---------- ---------- ----------
7839 KING 7369 0
7566 JONES 7839 KING 0
7788 SCOTT 7566 JONES 0
7876 ADAMS 7788 SCOTT 0
7902 FORD 7566 JONES 0
7369 SMITH 7902 FORD 1
7698 BLAKE 7839 KING 0
7499 ALLEN 7698 BLAKE 0
7521 WARD 7698 BLAKE 0
7654 MARTIN 7698 BLAKE 0

7844 TURNER 7698 BLAKE 0
7900 JAMES 7698 BLAKE 0
7782 CLARK 7839 KING 0
7934 MILLER 7782 CLARK 0
In addition to PRIOR another unary operator exists that’s really useful: CONNECT_BY_ROOT.
It allows us to display the root of a given record:
SQL> select empno,lpad(' ',level*3,' ')||ename name,
2 connect_by_root ename boss
3 from emp
4 connect by prior empno = mgr
5 start with mgr is null;
EMPNO NAME BOSS
---------- ------------------ ----------
7839 KING KING
7566 JONES KING
7788 SCOTT KING
7876 ADAMS KING
7902 FORD KING
7369 SMITH KING
7698 BLAKE KING
7499 ALLEN KING
7521 WARD KING
7654 MARTIN KING
7844 TURNER KING
7900 JAMES KING
7782 CLARK KING
7934 MILLER KING
In our example there’s a single root, KING, but if we modify another record:
SQL> update emp set mgr=null where ename='BLAKE';
We get:
SQL> select empno,lpad(' ',level*3,' ')||ename name,
2 connect_by_root ename boss
3 from emp
4 connect by prior empno = mgr
5 start with mgr is null;
EMPNO NAME BOSS
---------- ------------------ ----------
7698 BLAKE BLAKE
7499 ALLEN BLAKE
7521 WARD BLAKE
7654 MARTIN BLAKE
7844 TURNER BLAKE
7900 JAMES BLAKE
7839 KING KING
7566 JONES KING
7788 SCOTT KING
7876 ADAMS KING
7902 FORD KING
7369 SMITH KING
7782 CLARK KING
7934 MILLER KING
Where, for each record, we display who is the “big chief”…
Another really useful function is SYS_CONNECT_BY_PATH, it gets as input a field and a character
and builds the full path from the root to the current record using the character as a separator
SQL> select empno,lpad(' ',level*3,' ')||ename name,

2 sys_connect_by_path(ename,'/') bosses
3 from emp
4 connect by prior empno = mgr
5* start with mgr is null
EMPNO NAME BOSSES
---------- ------------------ --------------------------------
7839 KING /KING
7566 JONES /KING/JONES
7788 SCOTT /KING/JONES/SCOTT
7876 ADAMS /KING/JONES/SCOTT/ADAMS
7902 FORD /KING/JONES/FORD
7369 SMITH /KING/JONES/FORD/SMITH
7698 BLAKE /KING/BLAKE
7499 ALLEN /KING/BLAKE/ALLEN
7521 WARD /KING/BLAKE/WARD
7654 MARTIN /KING/BLAKE/MARTIN
7844 TURNER /KING/BLAKE/TURNER
7900 JAMES /KING/BLAKE/JAMES
7782 CLARK /KING/CLARK
7934 MILLER /KING/CLARK/MILLER
There’s another useful pseudocolumn to show, CONNECT_BY_ISLEAF.
It tells us whether a record is a leaf of the tree or not:
SQL> select empno,lpad(' ',level*3,' ')||ename name,
2 connect_by_isleaf ifleaf
3 from emp
4 connect by prior empno = mgr
5 start with mgr is null;
EMPNO NAME IFLEAF
---------- ------------------ ----------
7839 KING 0
7566 JONES 0
7788 SCOTT 0
7876 ADAMS 1
7902 FORD 0
7369 SMITH 1
7698 BLAKE 0
7499 ALLEN 1
7521 WARD 1
7654 MARTIN 1
7844 TURNER 1
7900 JAMES 1
7782 CLARK 0
7934 MILLER 1
Really interesting is the ability of CONNECT BY to generate more records from a table that contains only one
row:
SQL> select level from dual
2 connect by level<=10;
LEVEL
----------
1
2
3
4
5
6

7
8
9
10
SQL> cl scr
SQL> SET VERIFY OFF
SQL> cl scr
SQL> SELECT Ename, Empno, Mgr, Job
2 FROM Emp
3 CONNECT BY PRIOR Empno = MGR;
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
FORD 7902 7566 ANALYST
SMITH 7369 7902 CLERK
SCOTT 7788 7566 ANALYST
ADAMS 7876 7788 CLERK
MARTIN 7654 7698 SALESMAN
ALLEN 7499 7698 SALESMAN
JAMES 7900 7698 CLERK
WARD 7521 7698 SALESMAN
TURNER 7844 7698 SALESMAN
MILLER 7934 7782 CLERK
ADAMS 7876 7788 CLERK
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
BLAKE 7698 7839 MANAGER
MARTIN 7654 7698 SALESMAN
ALLEN 7499 7698 SALESMAN
JAMES 7900 7698 CLERK
WARD 7521 7698 SALESMAN
TURNER 7844 7698 SALESMAN
CLARK 7782 7839 MANAGER
MILLER 7934 7782 CLERK
JONES 7566 7839 MANAGER
FORD 7902 7566 ANALYST
SMITH 7369 7902 CLERK
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
SCOTT 7788 7566 ANALYST
ADAMS 7876 7788 CLERK
SMITH 7369 7902 CLERK
KING 7839 PRESIDENT
BLAKE 7698 7839 MANAGER
MARTIN 7654 7698 SALESMAN
ALLEN 7499 7698 SALESMAN


JAMES 7900 7698 CLERK
WARD 7521 7698 SALESMAN
TURNER 7844 7698 SALESMAN
CLARK 7782 7839 MANAGER
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
MILLER 7934 7782 CLERK
JONES 7566 7839 MANAGER
FORD 7902 7566 ANALYST


SMITH 7369 7902 CLERK
SCOTT 7788 7566 ANALYST
ADAMS 7876 7788 CLERK
39 rows selected.
SQL> cl scr
SQL> SELECT Ename, Empno, Mgr, Job
2 FROM Emp
3 START WITH Job = 'PRESIDENT'
4 CONNECT BY PRIOR Empno = MGR;
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
KING 7839 PRESIDENT
BLAKE 7698 7839 MANAGER
MARTIN 7654 7698 SALESMAN
ALLEN 7499 7698 SALESMAN
TURNER 7844 7698 SALESMAN
JAMES 7900 7698 CLERK
WARD 7521 7698 SALESMAN
CLARK 7782 7839 MANAGER
MILLER 7934 7782 CLERK
JONES 7566 7839 MANAGER
FORD 7902 7566 ANALYST
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
SMITH 7369 7902 CLERK
SCOTT 7788 7566 ANALYST
ADAMS 7876 7788 CLERK
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job
2 FROM Emp
3 START WITH Ename = 'KING'
4* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
KING 7839 PRESIDENT
BLAKE 7698 7839 MANAGER
MARTIN 7654 7698 SALESMAN
ALLEN 7499 7698 SALESMAN
TURNER 7844 7698 SALESMAN
JAMES 7900 7698 CLERK
WARD 7521 7698 SALESMAN
CLARK 7782 7839 MANAGER
MILLER 7934 7782 CLERK
JONES 7566 7839 MANAGER


FORD 7902 7566 ANALYST
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
SMITH 7369 7902 CLERK
SCOTT 7788 7566 ANALYST
ADAMS 7876 7788 CLERK
14 rows selected.
SQL> SELECT Ename, Empno, Mgr, Job
2 FROM Emp;
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
KING 7839 PRESIDENT
BLAKE 7698 7839 MANAGER
CLARK 7782 7839 MANAGER
JONES 7566 7839 MANAGER
MARTIN 7654 7698 SALESMAN
ALLEN 7499 7698 SALESMAN
TURNER 7844 7698 SALESMAN
JAMES 7900 7698 CLERK
WARD 7521 7698 SALESMAN
FORD 7902 7566 ANALYST
SMITH 7369 7902 CLERK
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
SCOTT 7788 7566 ANALYST
ADAMS 7876 7788 CLERK
MILLER 7934 7782 CLERK
14 rows selected.
SQL> cl scr
SQL> SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Sal = 5000
4 CONNECT BY PRIOR Empno = MGR;
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
JAMES 7900 7698 CLERK 950
WARD 7521 7698 SALESMAN 1250
CLARK 7782 7839 MANAGER 2450
MILLER 7934 7782 CLERK 1300
JONES 7566 7839 MANAGER 2975
FORD 7902 7566 ANALYST 3000


ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
SMITH 7369 7902 CLERK 800
SCOTT 7788 7566 ANALYST 3000
ADAMS 7876 7788 CLERK 1100
14 rows selected.
SQL> cl scr
SQL> SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Sal = (SELECT MAX(Sal)
4 FROM Emp)
5 CONNECT BY PRIOR Empno = MGR;
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
JAMES 7900 7698 CLERK 950
WARD 7521 7698 SALESMAN 1250
CLARK 7782 7839 MANAGER 2450
MILLER 7934 7782 CLERK 1300
JONES 7566 7839 MANAGER 2975
FORD 7902 7566 ANALYST 3000
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
SMITH 7369 7902 CLERK 800
SCOTT 7788 7566 ANALYST 3000
ADAMS 7876 7788 CLERK 1100
14 rows selected.
SQL> SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Sal IN (SELECT Sal
4 FROM Emp
5 WHERE Job = 'ANALYST')
6 CONNECT BY PRIOR Empno = MGR;
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
FORD 7902 7566 ANALYST 3000
SMITH 7369 7902 CLERK 800
SCOTT 7788 7566 ANALYST 3000
ADAMS 7876 7788 CLERK 1100
SQL> SPOOL OFF
SQL> cl scr
SQL> SET VERIFY OFF

SQL> cl scr
SQL> SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Sal = (SELECT MAX(Sal)
4 FROM Emp
5 WHERE Deptno = (SELECT Deptno
6 FROM
7
WHERE Dname = 'ACCOUNTING')
8 )
9 CONNECT BY PRIOR Empno = MGR;
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
JAMES 7900 7698 CLERK 950
WARD 7521 7698 SALESMAN 1250
CLARK 7782 7839 MANAGER 2450
MILLER 7934 7782 CLERK 1300
JONES 7566 7839 MANAGER 2975
FORD 7902 7566 ANALYST 3000
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
SMITH 7369 7902 CLERK 800
SCOTT 7788 7566 ANALYST 3000
ADAMS 7876 7788 CLERK 1100
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Sal = (SELECT MAX(Sal)
4 FROM Emp
5 WHERE Deptno = (SELECT Deptno
6 FROM
7
WHERE Dname = 'RESEARCH')
8 )
9* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
FORD 7902 7566 ANALYST 3000
SMITH 7369 7902 CLERK 800
Dept
Dept


SCOTT 7788 7566 ANALYST 3000
ADAMS 7876 7788 CLERK 1100
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Sal = (SELECT MAX(Sal)
4 FROM Emp
5 WHERE Deptno = (SELECT Deptno
6 FROM
7
WHERE Dname = 'SALES')
8 )
9* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
JAMES 7900 7698 CLERK 950
WARD 7521 7698 SALESMAN 1250
6 rows selected.
SQL> cl scr
SQL> SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = (SELECT Ename
4 FROM Emp, SalGrade
5 WHERE Emp.Sal BETWEEN SalGrade.LOSAL AND
SalGrade.HiSal
6 AND grade = 5)
7 CONNECT BY PRIOR Empno = MGR;
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
JAMES 7900 7698 CLERK 950
WARD 7521 7698 SALESMAN 1250
CLARK 7782 7839 MANAGER 2450
MILLER 7934 7782 CLERK 1300
JONES 7566 7839 MANAGER 2975
FORD 7902 7566 ANALYST 3000
ENAME EMPNO MGR JOB SAL
Dept


---------- ---------- ---------- --------- ----------
SMITH 7369 7902 CLERK 800
SCOTT 7788 7566 ANALYST 3000
ADAMS 7876 7788 CLERK 1100
14 rows selected.
SQL> cl scr
SQL> SELECT Ename, Empno, MGR, Job
2 FROM Emp
3 START WITH Job = 'ANALYST'
4 CONNECT BY PRIOR Empno = MGR;
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
FORD 7902 7566 ANALYST
SMITH 7369 7902 CLERK
SCOTT 7788 7566 ANALYST
ADAMS 7876 7788 CLERK
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, MGR, Job
2 FROM Emp
3 START WITH Ename = 'JONES'
4* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
JONES 7566 7839 MANAGER
FORD 7902 7566 ANALYST
SMITH 7369 7902 CLERK
SCOTT 7788 7566 ANALYST
ADAMS 7876 7788 CLERK
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, MGR, Job
2 FROM Emp
3 START WITH Ename = 'JONES'
4* CONNECT BY Empno = PRIOR MGR
SQL> /
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
JONES 7566 7839 MANAGER
KING 7839 PRESIDENT
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, MGR, Job

2 FROM Emp
3 START WITH Job = 'ANALYST'
4* CONNECT BY Empno = PRIOR MGR
SQL> /
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
SCOTT 7788 7566 ANALYST
JONES 7566 7839 MANAGER
KING 7839 PRESIDENT
FORD 7902 7566 ANALYST
JONES 7566 7839 MANAGER
KING 7839 PRESIDENT
6 rows selected.
SQL> cl scr
SQL> SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5 /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
JAMES 7900 7698 CLERK 950
WARD 7521 7698 SALESMAN 1250
CLARK 7782 7839 MANAGER 2450
MILLER 7934 7782 CLERK 1300
JONES 7566 7839 MANAGER 2975
FORD 7902 7566 ANALYST 3000
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
SMITH 7369 7902 CLERK 800
SCOTT 7788 7566 ANALYST 3000
ADAMS 7876 7788 CLERK 1100
14 rows selected.
SQL> SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5 AND Job = 'MANAGER';
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850


CLARK 7782 7839 MANAGER 2450
JONES 7566 7839 MANAGER 2975
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 WHERE Job = 'MANAGER'
4 START WITH Ename = 'KING'
5* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
BLAKE 7698 7839 MANAGER 2850
CLARK 7782 7839 MANAGER 2450
JONES 7566 7839 MANAGER 2975
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'JONES'
4* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
JONES 7566 7839 MANAGER 2975
FORD 7902 7566 ANALYST 3000
SMITH 7369 7902 CLERK 800
SCOTT 7788 7566 ANALYST 3000
ADAMS 7876 7788 CLERK 1100
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'BLAKE'
4* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
JAMES 7900 7698 CLERK 950
WARD 7521 7698 SALESMAN 1250
6 rows selected.

SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'CLARK'
4* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
CLARK 7782 7839 MANAGER 2450
MILLER 7934 7782 CLERK 1300
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'JONES'
4 CONNECT BY PRIOR Empno = MGR AND
5* Job = 'MANAGER'
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
JONES 7566 7839 MANAGER 2975
SQL> cl scr
SQL> SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5 AND Job = 'MANAGER';
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
CLARK 7782 7839 MANAGER 2450
JONES 7566 7839 MANAGER 2975
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5* AND Job = 'SALESMAN'
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000

SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5* AND Job = 'MANAGER' OR Job = 'SALESMAN'
SQL> /
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5* AND Job = 'MANAGER' AND Job = 'SALESMAN'
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5* AND (Job = 'MANAGER' OR Job = 'SALESMAN')
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
WARD 7521 7698 SALESMAN 1250
CLARK 7782 7839 MANAGER 2450
JONES 7566 7839 MANAGER 2975
8 rows selected.
SQL> ED
Wrote file afiedt.buf

1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5* AND Job IN('MANAGER', 'SALESMAN')
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
WARD 7521 7698 SALESMAN 1250
CLARK 7782 7839 MANAGER 2450
JONES 7566 7839 MANAGER 2975
8 rows selected.
SQL> cl scr
SQL> SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5 AND Job <> 'SALESMAN';
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
JAMES 7900 7698 CLERK 950
CLARK 7782 7839 MANAGER 2450
MILLER 7934 7782 CLERK 1300
JONES 7566 7839 MANAGER 2975
FORD 7902 7566 ANALYST 3000
SMITH 7369 7902 CLERK 800
SCOTT 7788 7566 ANALYST 3000
ADAMS 7876 7788 CLERK 1100
10 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5* AND Job <> 'CLERK'
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------


KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
WARD 7521 7698 SALESMAN 1250
CLARK 7782 7839 MANAGER 2450
JONES 7566 7839 MANAGER 2975
FORD 7902 7566 ANALYST 3000
SCOTT 7788 7566 ANALYST 3000
10 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5* AND Job <> 'ANALYST'
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
JAMES 7900 7698 CLERK 950
WARD 7521 7698 SALESMAN 1250
CLARK 7782 7839 MANAGER 2450
MILLER 7934 7782 CLERK 1300
JONES 7566 7839 MANAGER 2975
10 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 WHERE Job <> 'ANALYST'
4 START WITH Ename = 'KING'
5* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
JAMES 7900 7698 CLERK 950
Moharana/////sekhar576@gmail.c
om
WARD 7521 7698 SALESMAN 1250
CLARK 7782 7839 MANAGER 2450
MILLER 7934 7782 CLERK 1300
JONES 7566 7839 MANAGER 2975
SMITH 7369 7902 CLERK 800
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
ADAMS 7876 7788 CLERK 1100
12 rows selected.
SQL> cl scr
SQL> SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5 AND Job <> 'SALESMAN'
6 AND Sal > 1500;
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
CLARK 7782 7839 MANAGER 2450
JONES 7566 7839 MANAGER 2975
FORD 7902 7566 ANALYST 3000
SCOTT 7788 7566 ANALYST 3000
6 rows selected.
SQL> SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 WHERE Job <> 'SALESMAN' AND Sal > 1500
4 START WITH Ename = 'KING'
5 CONNECT BY PRIOR Empno = MGR;
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
CLARK 7782 7839 MANAGER 2450
JONES 7566 7839 MANAGER 2975
FORD 7902 7566 ANALYST 3000
SCOTT 7788 7566 ANALYST 3000
6 rows selected.
SQL> SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 WHERE Deptno = (SELECT Deptno
4 FROM Dept
5 WHERE DName = 'SALES')
6 START WITH Ename = 'KING'
7 CONNECT BY PRIOR Empno = MGR;


ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
JAMES 7900 7698 CLERK 950
WARD 7521 7698 SALESMAN 1250
6 rows selected.
SQL> cl scr
SQL> SELECT Ename, Empno, Mgr, Job, Sal,
2 ROUND(SalAvg, 2) SalAvg
3 FROM Emp E, (SELECT Deptno, AVG(Sal) SalAvg
4 FROM Emp
5 GROUP BY Deptno) E1
6 WHERE E.Deptno = E1.Deptno
7 START WITH Ename = 'KING'
8 CONNECT BY PRIOR Empno = MGR;
ENAME EMPNO MGR JOB SAL SALAVG
---------- ---------- ---------- --------- ---------- ----------
KING 7839 PRESIDENT 5000 2916.67
CLARK 7782 7839 MANAGER 2450 2916.67
MILLER 7934 7782 CLERK 1300 2916.67
JONES 7566 7839 MANAGER 2975 2175
SCOTT 7788 7566 ANALYST 3000 2175
ADAMS 7876 7788 CLERK 1100 2175
FORD 7902 7566 ANALYST 3000 2175
SMITH 7369 7902 CLERK 800 2175
BLAKE 7698 7839 MANAGER 2850 1566.67
WARD 7521 7698 SALESMAN 1250 1566.67
JAMES 7900 7698 CLERK 950 1566.67
ENAME EMPNO MGR JOB SAL SALAVG
---------- ---------- ---------- --------- ---------- ----------
TURNER 7844 7698 SALESMAN 1500 1566.67
ALLEN 7499 7698 SALESMAN 1600 1566.67
MARTIN 7654 7698 SALESMAN 1250 1566.67
14 rows selected.
SQL> SELECT Ename, Empno, Mgr, Job, Sal,
2 ROUND(SalAvg, 2) SalAvg
3 FROM Emp E, (SELECT Deptno, AVG(Sal) SalAvg
4 FROM Emp
5 GROUP BY Deptno) E1
6 WHERE E.Deptno = E1.Deptno AND
7 E1.Deptno = (SELECT Deptno
8 FROM Dept
9 WHERE Dname = 'SALES')
10 START WITH Ename = 'KING'
11 CONNECT BY PRIOR Empno = MGR;


ENAME EMPNO MGR JOB SAL SALAVG
---------- ---------- ---------- --------- ---------- ----------
BLAKE 7698 7839 MANAGER 2850 1566.67
WARD 7521 7698 SALESMAN 1250 1566.67
JAMES 7900 7698 CLERK 950 1566.67
TURNER 7844 7698 SALESMAN 1500 1566.67
ALLEN 7499 7698 SALESMAN 1600 1566.67
MARTIN 7654 7698 SALESMAN 1250 1566.67
6 rows selected.
SQL> SELECT Ename, Empno, Mgr, Job, Sal, ROUND(SalAvg, 2) SalAvg, ROUND(((SELECT
AVG(Sal) FROM Emp) - SalAvg), 2) AvgDif
2 FROM Emp E, (SELECT Deptno, AVG(Sal) SalAvg
3 FROM Emp
4 GROUP BY Deptno) E1
5 WHERE E.Deptno = E1.Deptno AND
6 E1.Deptno = (SELECT Deptno
7 FROM Dept
8 WHERE Dname = 'SALES')
9 START WITH Ename = 'KING'
10 CONNECT BY PRIOR Empno = MGR;
ENAME EMPNO MGR JOB SAL SALAVG AVGDIF
---------- ---------- ---------- --------- ---------- ---------- ----------
BLAKE 7698 7839 MANAGER 2850 1566.67 506.55
WARD 7521 7698 SALESMAN 1250 1566.67 506.55
JAMES 7900 7698 CLERK 950 1566.67 506.55
TURNER 7844 7698 SALESMAN 1500 1566.67 506.55
ALLEN 7499 7698 SALESMAN 1600 1566.67 506.55
MARTIN 7654 7698 SALESMAN 1250 1566.67 506.55
6 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal, ROUND(SalAvg, 2) SalAvg, ROUND(((SELECT
AVG(Sal) FROM Emp) - SalAvg), 2) AvgDif
2 FROM Emp E, (SELECT Deptno, AVG(Sal) SalAvg
3 FROM Emp
4 GROUP BY Deptno) E1
5 WHERE E.Deptno = E1.Deptno AND
6 E1.Deptno = (SELECT Deptno
7 FROM Dept
8 WHERE Dname = 'SALES')
9 START WITH Ename = (
10 SELECT Ename
11 FROM Emp
12 WHERE Sal = (
13 SELECT
14 MAX(Sal)
15 FROM Emp
16 WHERE Deptno = (
17 SELECT Deptno

18 FROM Dept
19 WHERE Dname = 'ACCOUNTING'
20
21
22
23* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME EMPNO MGR JOB SAL SALAVG AVGDIF
---------- ---------- ---------- --------- ---------- ---------- ----------
BLAKE 7698 7839 MANAGER 2850 1566.67 506.55
WARD 7521 7698 SALESMAN 1250 1566.67 506.55
JAMES 7900 7698 CLERK 950 1566.67 506.55
TURNER 7844 7698 SALESMAN 1500 1566.67 506.55
ALLEN 7499 7698 SALESMAN 1600 1566.67 506.55
MARTIN 7654 7698 SALESMAN 1250 1566.67 506.55
6 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal, ROUND(SalAvg, 2) SalAvg, ROUND(((SELECT
AVG(Sal) FROM Emp) - SalAvg), 2) AvgDif
2 FROM Emp E, (SELECT Deptno, AVG(Sal) SalAvg
3 FROM Emp
4 GROUP BY Deptno) E1
5 WHERE E.Deptno = E1.Deptno AND
6 E1.Deptno = (SELECT Deptno
7 FROM Dept
8 WHERE Dname = 'SALES')
9 START WITH Ename = (
10 SELECT Ename
11 FROM Emp
12 WHERE Sal = (
13 SELECT
14 MAX(Sal)
15 FROM Emp
16 WHERE Deptno = (
17 SELECT Deptno
18 FROM Dept
19 WHERE Dname = 'ACCOUNTING'
20
21
22
23* CONNECT BY PRIOR Empno = MGR
SQL> SPOOL OFF
SQL> cl scr
SQL> SET VERIFY OFF
SQL> cl scr
SQL> SELECT Ename, Sal, Job,
2 CONNECT_BY_ROOT(Ename) Boss
3 FROM Emp
4 START WITH Ename = 'KING'

5 CONNECT BY PRIOR Empno = MGR;
ENAME SAL JOB BOSS
---------- ---------- --------- ----------
KING 5000 PRESIDENT KING
BLAKE 2850 MANAGER KING
MARTIN 1250 SALESMAN KING
ALLEN 1600 SALESMAN KING
TURNER 1500 SALESMAN KING
JAMES 950 CLERK KING
WARD 1250 SALESMAN KING
CLARK 2450 MANAGER KING
MILLER 1300 CLERK KING
JONES 2975 MANAGER KING
FORD 3000 ANALYST KING
ENAME SAL JOB BOSS
---------- ---------- --------- ----------
SMITH 800 CLERK KING
SCOTT 3000 ANALYST KING
ADAMS 1100 CLERK KING
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Job,
2 CONNECT_BY_ROOT(Job) BossJob
3 FROM Emp
4 START WITH Ename = 'KING'
5* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME SAL JOB BOSSJOB
---------- ---------- --------- ---------
KING 5000 PRESIDENT PRESIDENT
BLAKE 2850 MANAGER PRESIDENT
MARTIN 1250 SALESMAN PRESIDENT
ALLEN 1600 SALESMAN PRESIDENT
TURNER 1500 SALESMAN PRESIDENT
JAMES 950 CLERK PRESIDENT
WARD 1250 SALESMAN PRESIDENT
CLARK 2450 MANAGER PRESIDENT
MILLER 1300 CLERK PRESIDENT
JONES 2975 MANAGER PRESIDENT
FORD 3000 ANALYST PRESIDENT
ENAME SAL JOB BOSSJOB
---------- ---------- --------- ---------
SMITH 800 CLERK PRESIDENT
SCOTT 3000 ANALYST PRESIDENT
ADAMS 1100 CLERK PRESIDENT
14 rows selected.

SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Job,
2 CONNECT_BY_ROOT(Sal) - Sal DiffBossEmpSal
3 FROM Emp
4 START WITH Ename = 'KING'
5* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME SAL JOB DIFFBOSSEMPSAL
---------- ---------- --------- --------------
KING 5000 PRESIDENT 0
BLAKE 2850 MANAGER 2150
MARTIN 1250 SALESMAN 3750
ALLEN 1600 SALESMAN 3400
TURNER 1500 SALESMAN 3500
JAMES 950 CLERK 4050
WARD 1250 SALESMAN 3750
CLARK 2450 MANAGER 2550
MILLER 1300 CLERK 3700
JONES 2975 MANAGER 2025
FORD 3000 ANALYST 2000
ENAME SAL JOB DIFFBOSSEMPSAL
---------- ---------- --------- --------------
SMITH 800 CLERK 4200
SCOTT 3000 ANALYST 2000
ADAMS 1100 CLERK 3900
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Job, Sal EmpSal,
2 CONNECT_BY_ROOT(Sal) BossSal,
3 CONNECT_BY_ROOT(Sal) - Sal DiffBossEmpSal
4 FROM Emp
5 START WITH Ename = 'KING'
6* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME JOB EMPSAL BOSSSAL DIFFBOSSEMPSAL
---------- --------- ---------- ---------- --------------
KING PRESIDENT 5000 5000 0
BLAKE MANAGER 2850 5000 2150
MARTIN SALESMAN 1250 5000 3750
ALLEN SALESMAN 1600 5000 3400
TURNER SALESMAN 1500 5000 3500
JAMES CLERK 950 5000 4050
WARD SALESMAN 1250 5000 3750
CLARK MANAGER 2450 5000 2550
MILLER CLERK 1300 5000 3700
JONES MANAGER 2975 5000 2025
FORD ANALYST 3000 5000 2000


ENAME JOB EMPSAL BOSSSAL DIFFBOSSEMPSAL
---------- --------- ---------- ---------- --------------
SMITH CLERK 800 5000 4200
SCOTT ANALYST 3000 5000 2000
ADAMS CLERK 1100 5000 3900
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Job,
2 CONNECT_BY_ROOT(Ename) BossName
3 FROM Emp
4 START WITH Ename = 'KING'
5* CONNECT BY Empno = PRIOR MGR
SQL> /
ENAME JOB BOSSNAME
---------- --------- ----------
KING PRESIDENT KING
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Job,
2 CONNECT_BY_ROOT(Ename) BossName
3 FROM Emp
4 START WITH Job = 'ANALYST'
5* CONNECT BY Empno = PRIOR MGR
SQL> /
ENAME JOB BOSSNAME
---------- --------- ----------
SCOTT ANALYST SCOTT
JONES MANAGER SCOTT
KING PRESIDENT SCOTT
FORD ANALYST FORD
JONES MANAGER FORD
KING PRESIDENT FORD
6 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Job,
2 CONNECT_BY_ROOT(Ename) BossName
3 FROM Emp
4 START WITH Empno = 7839
5* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME JOB BOSSNAME
---------- --------- ----------


KING PRESIDENT KING
BLAKE MANAGER KING
MARTIN SALESMAN KING
ALLEN SALESMAN KING
TURNER SALESMAN KING
JAMES CLERK KING
WARD SALESMAN KING
CLARK MANAGER KING
MILLER CLERK KING
JONES MANAGER KING
FORD ANALYST KING
ENAME JOB BOSSNAME
---------- --------- ----------
SMITH CLERK KING
SCOTT ANALYST KING
ADAMS CLERK KING
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Job,
2 CONNECT_BY_ROOT(Ename) BossName
3 FROM Emp
4 START WITH MGR = 7839
5* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME JOB BOSSNAME
---------- --------- ----------
BLAKE MANAGER BLAKE
MARTIN SALESMAN BLAKE
ALLEN SALESMAN BLAKE
TURNER SALESMAN BLAKE
JAMES CLERK BLAKE
WARD SALESMAN BLAKE
CLARK MANAGER CLARK
MILLER CLERK CLARK
JONES MANAGER JONES
FORD ANALYST JONES
SMITH CLERK JONES
ENAME JOB BOSSNAME
---------- --------- ----------
SCOTT ANALYST JONES
ADAMS CLERK JONES
13 rows selected.
SQL> cl scr
SQL> SELECT ENAME Name, HireDate,
2 CONNECT_BY_ROOT Ename Boss,
3 CONNECT_BY_ROOT HireDate BossHire,

4 ROUND(CONNECT_BY_ROOT HireDate - HireDate) Days
5 FROM EMP
6 START WITH Job = 'PRESIDENT'
7 CONNECT BY PRIOR EMPNO = MGR;
NAME HIREDATE BOSS BOSSHIRE DAYS
---------- --------- ---------- --------- ----------
KING 17-NOV-81 KING 17-NOV-81 0
BLAKE 01-MAY-81 KING 17-NOV-81 200
MARTIN 28-SEP-81 KING 17-NOV-81 50
ALLEN 20-FEB-81 KING 17-NOV-81 270
TURNER 08-SEP-81 KING 17-NOV-81 70
JAMES 03-DEC-81 KING 17-NOV-81 -16
WARD 22-FEB-81 KING 17-NOV-81 268
CLARK 09-JUN-81 KING 17-NOV-81 161
MILLER 23-JAN-82 KING 17-NOV-81 -67
JONES 02-APR-81 KING 17-NOV-81 229
FORD 03-DEC-81 KING 17-NOV-81 -16
NAME HIREDATE BOSS BOSSHIRE DAYS
---------- --------- ---------- --------- ----------
SMITH 17-DEC-80 KING 17-NOV-81 335
SCOTT 09-DEC-82 KING 17-NOV-81 -387
ADAMS 12-JAN-83 KING 17-NOV-81 -421
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT ENAME Name, HireDate,
2 CONNECT_BY_ROOT Ename Boss,
3 CONNECT_BY_ROOT HireDate BossHire,
4 ROUND(CONNECT_BY_ROOT HireDate - HireDate) Days
5 FROM EMP
6 WHERE
7 ROUND(CONNECT_BY_ROOT HireDate - HireDate) = &GVal
8 START WITH Job = 'PRESIDENT'
9* CONNECT BY PRIOR EMPNO = MGR
SQL> /
Enter value for gval: 1
no rows selected
SQL> ED
Wrote file afiedt.buf
1 SELECT ENAME Name, HireDate,
2 CONNECT_BY_ROOT Ename Boss,
3 CONNECT_BY_ROOT HireDate BossHire,
4 ROUND(CONNECT_BY_ROOT HireDate - HireDate) Days
5 FROM EMP
6 WHERE
7 SIGN(ROUND(CONNECT_BY_ROOT HireDate - HireDate)) = &GVal
8 START WITH Job = 'PRESIDENT'
9* CONNECT BY PRIOR EMPNO = MGR

SQL> /
Enter value for gval: 1
NAME HIREDATE BOSS BOSSHIRE DAYS
---------- --------- ---------- --------- ----------
BLAKE 01-MAY-81 KING 17-NOV-81 200
MARTIN 28-SEP-81 KING 17-NOV-81 50
ALLEN 20-FEB-81 KING 17-NOV-81 270
TURNER 08-SEP-81 KING 17-NOV-81 70
WARD 22-FEB-81 KING 17-NOV-81 268
CLARK 09-JUN-81 KING 17-NOV-81 161
JONES 02-APR-81 KING 17-NOV-81 229
SMITH 17-DEC-80 KING 17-NOV-81 335
8 rows selected.
SQL> /
Enter value for gval: -1
NAME HIREDATE BOSS BOSSHIRE DAYS
---------- --------- ---------- --------- ----------
JAMES 03-DEC-81 KING 17-NOV-81 -16
MILLER 23-JAN-82 KING 17-NOV-81 -67
FORD 03-DEC-81 KING 17-NOV-81 -16
SCOTT 09-DEC-82 KING 17-NOV-81 -387
ADAMS 12-JAN-83 KING 17-NOV-81 -421
SQL> cl cr
SP2-0158: unknown CLEAR option "cr"
SQL> cl scr
SQL> ED
Wrote file afiedt.buf
1 SELECT ENAME Name, HireDate
2 FROM EMP
3 WHERE
4 START WITH Job = 'PRESIDENT'
5* CONNECT BY PRIOR EMPNO = MGR
SQL> /
START WITH Job = 'PRESIDENT'
ERROR at line 4:
ORA-00936: missing expression
SQL> ED
Wrote file afiedt.buf
1 SELECT ENAME Name, HireDate
2 FROM EMP
3 START WITH Job = 'PRESIDENT'
4* CONNECT BY PRIOR EMPNO = MGR
SQL> /
NAME HIREDATE
*


---------- ---------
KING 17-NOV-81
BLAKE 01-MAY-81
MARTIN 28-SEP-81
ALLEN 20-FEB-81
TURNER 08-SEP-81
JAMES 03-DEC-81
WARD 22-FEB-81
CLARK 09-JUN-81
MILLER 23-JAN-82
JONES 02-APR-81
FORD 03-DEC-81
NAME HIREDATE
---------- ---------
SMITH 17-DEC-80
SCOTT 09-DEC-82
ADAMS 12-JAN-83
14 rows selected.
SQL> cl scr
SQL> COLUMN Path FORMAT A35
SQL> SELECT Ename,
2 SYS_CONNECT_BY_PATH(Ename, '/') "Path"
3 FROM Emp
4 START WITH Ename = 'KING'
5 CONNECT BY PRIOR Empno = MGR;
ENAME Path
---------- -----------------------------------
KING /KING
BLAKE /KING/BLAKE
MARTIN /KING/BLAKE/MARTIN
ALLEN /KING/BLAKE/ALLEN
TURNER /KING/BLAKE/TURNER
JAMES /KING/BLAKE/JAMES
WARD /KING/BLAKE/WARD
CLARK /KING/CLARK
MILLER /KING/CLARK/MILLER
JONES /KING/JONES
FORD /KING/JONES/FORD
ENAME Path
---------- -----------------------------------
SMITH /KING/JONES/FORD/SMITH
SCOTT /KING/JONES/SCOTT
ADAMS /KING/JONES/SCOTT/ADAMS
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename,

2 SYS_CONNECT_BY_PATH(Ename, '=>') "Path"
3 FROM Emp
4 START WITH Ename = 'KING'
5* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME Path
---------- -----------------------------------
KING =>KING
BLAKE =>KING=>BLAKE
MARTIN =>KING=>BLAKE=>MARTIN
ALLEN =>KING=>BLAKE=>ALLEN
TURNER =>KING=>BLAKE=>TURNER
JAMES =>KING=>BLAKE=>JAMES
WARD =>KING=>BLAKE=>WARD
CLARK =>KING=>CLARK
MILLER =>KING=>CLARK=>MILLER
JONES =>KING=>JONES
FORD =>KING=>JONES=>FORD
ENAME Path
---------- -----------------------------------
SMITH =>KING=>JONES=>FORD=>SMITH
SCOTT =>KING=>JONES=>SCOTT
ADAMS =>KING=>JONES=>SCOTT=>ADAMS
14 rows selected.
SQL> cl scr
SQL> SELECT Empno, Ename, MGR
2 FROM Emp;
EMPNO ENAME MGR
---------- ---------- ----------
7839 KING
7698 BLAKE 7839
7782 CLARK 7839
7566 JONES 7839
7654 MARTIN 7698
7499 ALLEN 7698
7844 TURNER 7698
7900 JAMES 7698
7521 WARD 7698
7902 FORD 7566
7369 SMITH 7902
EMPNO ENAME MGR
---------- ---------- ----------
7788 SCOTT 7566
7876 ADAMS 7788
7934 MILLER 7782
14 rows selected.
SQL> UPDATE Emp

2 SET MGR = 7566
3 WHERE Empno = 7839;
1 row updated.
SQL> SELECT Empno, Ename, MGR
2 FROM Emp;
EMPNO ENAME MGR
---------- ---------- ----------
7839 KING 7566
7698 BLAKE 7839
7782 CLARK 7839
7566 JONES 7839
7654 MARTIN 7698
7499 ALLEN 7698
7844 TURNER 7698
7900 JAMES 7698
7521 WARD 7698
7902 FORD 7566
7369 SMITH 7902
EMPNO ENAME MGR
---------- ---------- ----------
7788 SCOTT 7566
7876 ADAMS 7788
7934 MILLER 7782
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Empno, Ename, MGR
2 FROM Emp
3 START WITH Ename = 'KING'
4* CONNECT BY PRIOR Empno = MGR
SQL> /
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected
SQL> SELECT Ename,
2 SYS_CONNECT_BY_PATH(Sal, '/') "SalPath"
3 FROM Emp
4 START WITH Ename = 'KING'
5 CONNECT BY NOCYCLE PRIOR Empno = MGR;
ENAME
----------
SalPath
--------------------------------------------------------------------------------
KING


/5000
BLAKE
/5000/2850
MARTIN
/5000/2850/1250
ENAME
----------
SalPath
--------------------------------------------------------------------------------
ALLEN
/5000/2850/1600
TURNER
/5000/2850/1500
JAMES
/5000/2850/950
ENAME
----------
SalPath
--------------------------------------------------------------------------------
WARD
/5000/2850/1250
CLARK
/5000/2450
MILLER
/5000/2450/1300
ENAME
----------
SalPath
--------------------------------------------------------------------------------
JONES
/5000/2975
FORD
/5000/2975/3000
SMITH
/5000/2975/3000/800
ENAME
----------
SalPath
--------------------------------------------------------------------------------
SCOTT


/5000/2975/3000
ADAMS
/5000/2975/3000/1100
14 rows selected.
SQL> COLUMN "SalPath" FORMAT A25
SQL> /
ENAME SalPath
---------- -------------------------
KING /5000
BLAKE /5000/2850
MARTIN /5000/2850/1250
ALLEN /5000/2850/1600
TURNER /5000/2850/1500
JAMES /5000/2850/950
WARD /5000/2850/1250
CLARK /5000/2450
MILLER /5000/2450/1300
JONES /5000/2975
FORD /5000/2975/3000
ENAME SalPath
---------- -------------------------
SMITH /5000/2975/3000/800
SCOTT /5000/2975/3000
ADAMS /5000/2975/3000/1100
14 rows selected.
SQL> cl scr
SQL> ROLLBACK
2 /
Rollback complete.
SQL> cl scr
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename,
2 SYS_CONNECT_BY_PATH(Sal, '/') "SalPath"
3 FROM Emp
4 START WITH Ename = 'KING'
5* CONNECT BY PRIOR Empno = MGR;
SQL> /
CONNECT BY PRIOR Empno = MGR;
*
ERROR at line 5:
ORA-00911: invalid character

SQL> ED
Wrote file afiedt.buf
1 SELECT Ename,
2 SYS_CONNECT_BY_PATH(Sal, '/') "SalPath"
3 FROM Emp
4 START WITH Ename = 'KING'
5* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME SalPath
---------- -------------------------
KING /5000
BLAKE /5000/2850
MARTIN /5000/2850/1250
ALLEN /5000/2850/1600
TURNER /5000/2850/1500
JAMES /5000/2850/950
WARD /5000/2850/1250
CLARK /5000/2450
MILLER /5000/2450/1300
JONES /5000/2975
FORD /5000/2975/3000
ENAME SalPath
---------- -------------------------
SMITH /5000/2975/3000/800
SCOTT /5000/2975/3000
ADAMS /5000/2975/3000/1100
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename,
2 SYS_CONNECT_BY_PATH(Sal, '/') "SalPath"
3 FROM Emp
4 START WITH Ename = 'KING'
5 CONNECT BY PRIOR Empno = MGR
6* ORDER BY Sal
SQL> /
ENAME SalPath
---------- -------------------------
SMITH /5000/2975/3000/800
JAMES /5000/2850/950
ADAMS /5000/2975/3000/1100
MARTIN /5000/2850/1250
WARD /5000/2850/1250
MILLER /5000/2450/1300
TURNER /5000/2850/1500
ALLEN /5000/2850/1600
CLARK /5000/2450
BLAKE /5000/2850
Moharana/////sekhar576@gmail.c
om
JONES /5000/2975
ENAME SalPath
---------- -------------------------
FORD /5000/2975/3000
SCOTT /5000/2975/3000
KING /5000
14 rows selected.
SQL> cl scr
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Job
2 FROM Emp
3 START WITH Ename = 'KING'
4* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME SAL JOB
---------- ---------- ---------
KING 5000 PRESIDENT
BLAKE 2850 MANAGER
MARTIN 1250 SALESMAN
ALLEN 1600 SALESMAN
TURNER 1500 SALESMAN
JAMES 950 CLERK
WARD 1250 SALESMAN
CLARK 2450 MANAGER
MILLER 1300 CLERK
JONES 2975 MANAGER
FORD 3000 ANALYST
ENAME SAL JOB
---------- ---------- ---------
SMITH 800 CLERK
SCOTT 3000 ANALYST
ADAMS 1100 CLERK
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Job
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5* ORDER SIBLINGS BY Sal
SQL> /
ENAME SAL JOB
---------- ---------- ---------
KING 5000 PRESIDENT


CLARK 2450 MANAGER
MILLER 1300 CLERK
BLAKE 2850 MANAGER
JAMES 950 CLERK
MARTIN 1250 SALESMAN
WARD 1250 SALESMAN
TURNER 1500 SALESMAN
ALLEN 1600 SALESMAN
JONES 2975 MANAGER
FORD 3000 ANALYST
ENAME SAL JOB
---------- ---------- ---------
SMITH 800 CLERK
SCOTT 3000 ANALYST
ADAMS 1100 CLERK
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Job
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5* ORDER SIBLINGS BY Ename
SQL> /
ENAME SAL JOB
---------- ---------- ---------
KING 5000 PRESIDENT
BLAKE 2850 MANAGER
ALLEN 1600 SALESMAN
JAMES 950 CLERK
MARTIN 1250 SALESMAN
TURNER 1500 SALESMAN
WARD 1250 SALESMAN
CLARK 2450 MANAGER
MILLER 1300 CLERK
JONES 2975 MANAGER
FORD 3000 ANALYST
ENAME SAL JOB
---------- ---------- ---------
SMITH 800 CLERK
SCOTT 3000 ANALYST
ADAMS 1100 CLERK
14 rows selected.