PL/SQL -> SQL -> SQL Queries

SQL Queries for Practice

Go Back

Psuedo Columns

Hierarchy Queries

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> 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> 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> 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> 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 Dept WHERE Dname = 'ACCOUNTING') 
  7                ) 
  8  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 Dept 
  7                                                           WHERE Dname = 'RESEARCH') 
  8                ) 
  9CONNECT BY PRIOR Empno = MGR 
SQL> / 
 
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> 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 Dept 
  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> 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                            
 

---------- ---------- ---------- --------- ----------                            
SMITH            7369       7902 CLERK            800                            
SCOTT            7788       7566 ANALYST         3000                            
ADAMS            7876       7788 CLERK           1100                            
 
14 rows selected. 
 

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

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> 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> 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> SET VERIFY OFF 

 
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> 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> 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> 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> 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> ROLLBACK 
  2  / 
 
Rollback complete. 
 

 
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                                                            
 

JONES      /5000/2975                                                            
 
ENAME      SalPath                                                               
---------- -------------------------                                             
FORD       /5000/2975/3000                                                       
SCOTT      /5000/2975/3000                                                       
KING       /5000                                                                 
 
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 
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. 

Go Back