PL/SQL -> SQL -> SQL Queries

SQL Queries for Practice

Go Back

Set Operators in SQL

Psuedo Columns

 
SQL> SET VERIFY OFF 
SQL> cl scr 
 
SQL> SELECT SequenceName.CURRVAL 
  2   
SQL> SELECT SequenceName.NEXTVAL 
  2   
SQL> INSERT INTO TableName 
  2  VALUES(SequenceName.CURRVAL, 
  3   
SQL> INSERT INTO TableName 
  2  VALUES(SequenceName.NEXTVAL, 
  3   
SQL> UPDATE TableName 
  2  SET 
  3  ColumnName = SequenceName.CURRVAL 
  4   
SQL> UPDATE TableName 
  2  SET 
  3  ColumnName = SequenceName.NEXTVAL 
  4   
SQL> cl scr 
 
SQL> SELECT 
  2  SequenceName1.CURRVAL, SequenceName1.NEXTVAL 
  3   
SQL> cl scr 
 
SQL> CREATE TABLE Sample01 
  2  ( 
  3   SampID  NUMBER(4) 
  4   Constraint SampID_PK01 PRIMARY  KEY, 
  5   SampName VARCHAR2(25), 
  6   SampDate DATE 
  7   ); 
 
Table created. 
 
SQL> CREATE SEQUENCE SampleSeq01 
  2  INCREMENT BY 1 
  3  START WITH 0 
  4  MINVALUE 0 
  5  MAXVALUE 5 
  6  NOCACHE 
  7  NOCYCLE 
  8  / 
 
Sequence created. 
 
SQL> SELECT SampleSeq01.CURRVAL FROM DUAL; 
SELECT SampleSeq01.CURRVAL FROM DUAL 
       * 
ERROR at line 1: 
ORA-08002: sequence SAMPLESEQ01.CURRVAL is not yet defined in this session  

SQL> cl scr 
 
SQL> SELECT * FROM Sample01; 
 
no rows selected 
 
SQL> INSERT INTO Sample01 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4  (SampleSeq01.NEXTVAL, 'SAMPLE', '31-AUG-05'); 
 
1 row created. 
 
SQL> SELECT SampleSeq01.CURRVAL FROM DUAL; 
 
   CURRVAL                                                                       
----------                                                                       
         0                                                                       
 
SQL> SELECT * FROM Sample01; 
 
    SAMPID SAMPNAME                  SAMPDATE                                    
---------- ------------------------- ---------                                   
         0 SAMPLE                    31-AUG-05                                   
 
SQL> INSERT INTO Sample01 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4  (SampleSeq01.NEXTVAL, 'SAMPLE', '31-AUG-05'); 
 
1 row created. 
 
SQL> SELECT SampleSeq01.CURRVAL FROM DUAL; 
 
   CURRVAL                                                                       
----------                                                                       
         1                                                                       
 
SQL> SELECT * FROM Sample01; 
 
    SAMPID SAMPNAME                  SAMPDATE                                    
---------- ------------------------- ---------                                   
         0 SAMPLE                    31-AUG-05                                   
         1 SAMPLE                    31-AUG-05                                   
 
SQL> INSERT INTO Sample01 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4  (SampleSeq01.NEXTVAL, 'SAMPLE', '31-AUG-05'); 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample01 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
 

  4* (SampleSeq01.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample01 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq01.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample01 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq01.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample01 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq01.NEXTVAL, 'SAMPLE', '31-AUG-05') 
INSERT INTO Sample01 
* 
ERROR at line 1: 
ORA-08004: sequence SAMPLESEQ01.NEXTVAL exceeds MAXVALUE and cannot be  
instantiated  
 
 
SQL> SELECT SampleSeq01.CURRVAL FROM DUAL; 
 
   CURRVAL                                                                       
----------                                                                       
         5                                                                       
 
SQL> cl scr 
 
SQL> CREATE TABLE Sample02 
  2  ( 
  3   SampID  NUMBER(4) 
  4   Constraint SampID_PK02 PRIMARY  KEY, 
  5   SampName VARCHAR2(25), 
  6   SampDate DATE 
  7   ); 
 
Table created. 
 
SQL> CREATE SEQUENCE SampleSeq02 
  2  INCREMENT BY 1 
  3  START WITH 0 
  4  MINVALUE 0 
  5  MAXVALUE 5 
  6  NOCACHE 
 

  7  CYCLE 
  8  / 
 
Sequence created. 
 
SQL> INSERT INTO Sample02 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4  (SampleSeq02.NEXTVAL, 'SAMPLE', '31-AUG-05'); 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample02 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq02.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample02 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq02.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample02 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq02.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample02 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq02.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample02 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq02.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> SELECT SampleSeq02.CURRVAL FROM DUAL; 
 
   CURRVAL                                                                       
----------                                                                       
 

         5                                                                       
 
SQL> SELECT * FROM Sample02; 
 
    SAMPID SAMPNAME                  SAMPDATE                                    
---------- ------------------------- ---------                                   
         0 SAMPLE                    31-AUG-05                                   
         1 SAMPLE                    31-AUG-05                                   
         2 SAMPLE                    31-AUG-05                                   
         3 SAMPLE                    31-AUG-05                                   
         4 SAMPLE                    31-AUG-05                                   
         5 SAMPLE                    31-AUG-05                                   
 
6 rows selected. 
 
SQL> INSERT INTO Sample02 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4  (SampleSeq02.NEXTVAL, 'SAMPLE', '31-AUG-05'); 
INSERT INTO Sample02 
* 
ERROR at line 1: 
ORA-00001: unique constraint (SCOTT.SAMPID_PK02) violated  
 
 
SQL> SELECT SampleSeq02.CURRVAL FROM DUAL; 
 
   CURRVAL                                                                       
----------                                                                       
         0                                                                       
 
SQL> cl scr 
 
SQL> CREATE TABLE Sample03 
  2  ( 
  3   SampID  NUMBER(4) 
  4   SampName VARCHAR2(25), 
  5   SampDate DATE 
  6   ); 
( 
* 
ERROR at line 2: 
ORA-00922: missing or invalid option  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE Sample03 
  2  ( 
  3   SampID  NUMBER(4), 
  4   SampName VARCHAR2(25), 
  5   SampDate DATE 
  6*  ) 
SQL> / 
 
 

Table created. 
 
SQL> cl scr 
 
SQL> CREATE SEQUENCE SampleSeq03 
  2  INCREMENT BY 1 
  3  START WITH 10 
  4  MINVALUE 0 
  5  MAXVALUE 20 
  6  NOCACHE 
  7  CYCLE 
  8  / 
 
Sequence created. 
 
SQL> INSERT INTO Sample03 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4  (SampleSeq03.NEXTVAL, 'SAMPLE', '31-AUG-05'); 
 
1 row created. 
 
SQL> SELECT SampleSeq03.CURRVAL FROM DUAL; 
 
   CURRVAL                                                                       
----------                                                                       
        10                                                                       
 
SQL> INSERT INTO Sample03 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4  (SampleSeq03.NEXTVAL, 'SAMPLE', '31-AUG-05'); 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample03 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq03.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample03 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq03.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample03 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq03.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 

 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample03 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq03.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample03 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq03.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample03 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq03.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample03 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq03.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample03 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq03.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample03 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq03.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> SELECT * FROM Sample03; 
 
    SAMPID SAMPNAME                  SAMPDATE                                    
---------- ------------------------- ---------                                   
        10 SAMPLE                    31-AUG-05                                   
 

        11 SAMPLE                    31-AUG-05                                   
        12 SAMPLE                    31-AUG-05                                   
        13 SAMPLE                    31-AUG-05                                   
        14 SAMPLE                    31-AUG-05                                   
        15 SAMPLE                    31-AUG-05                                   
        16 SAMPLE                    31-AUG-05                                   
        17 SAMPLE                    31-AUG-05                                   
        18 SAMPLE                    31-AUG-05                                   
        19 SAMPLE                    31-AUG-05                                   
        20 SAMPLE                    31-AUG-05                                   
 
11 rows selected. 
 
SQL> INSERT INTO Sample03 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4  (SampleSeq03.NEXTVAL, 'SAMPLE', '31-AUG-05'); 
 
1 row created. 
 
SQL> / 
 
1 row created. 
 
SQL> / 
 
1 row created. 
 
SQL> SELECT SampleSeq03.CURRVAL FROM DUAL; 
 
   CURRVAL                                                                       
----------                                                                       
         2                                                                       
 
SQL>  SELECT * FROM Sample03; 
 
    SAMPID SAMPNAME                  SAMPDATE                                    
---------- ------------------------- ---------                                   
        10 SAMPLE                    31-AUG-05                                   
        11 SAMPLE                    31-AUG-05                                   
        12 SAMPLE                    31-AUG-05                                   
        13 SAMPLE                    31-AUG-05                                   
        14 SAMPLE                    31-AUG-05                                   
        15 SAMPLE                    31-AUG-05                                   
        16 SAMPLE                    31-AUG-05                                   
        17 SAMPLE                    31-AUG-05                                   
        18 SAMPLE                    31-AUG-05                                   
        19 SAMPLE                    31-AUG-05                                   
        20 SAMPLE                    31-AUG-05                                   
 
    SAMPID SAMPNAME                  SAMPDATE                                    
---------- ------------------------- ---------                                   
         0 SAMPLE                    31-AUG-05                                   
         1 SAMPLE                    31-AUG-05                                   
         2 SAMPLE                    31-AUG-05                                   
 
 

14 rows selected. 
 
SQL> cl scr 
 
SQL> CREATE TABLE Sample04 
  2  ( 
  3   SampID  NUMBER(4) 
  4   SampName VARCHAR2(25), 
  5   SampDate DATE 
  6   ); 
( 
* 
ERROR at line 2: 
ORA-00922: missing or invalid option  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE Sample04 
  2  ( 
  3   SampID  NUMBER(4), 
  4   SampName VARCHAR2(25), 
  5   SampDate DATE 
  6*  ) 
SQL> / 
 
Table created. 
 
SQL> cl scr 
 
SQL> CREATE SEQUENCE SampleSeq04 
  2  INCREMENT BY 10 
  3  START WITH 0 
  4  MINVALUE 0 
  5  MAXVALUE 100 
  6  NOCACHE 
  7  NOCYCLE 
  8  / 
 
Sequence created. 
 
SQL> INSERT INTO Sample04 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4  (SampleSeq04.NEXTVAL, 'SAMPLE', '31-AUG-05'); 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample04 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq04.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 

 
SQL> R 
  1  INSERT INTO Sample04 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq04.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample04 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq04.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> SELECT * FROM Sample04; 
 
    SAMPID SAMPNAME                  SAMPDATE                                    
---------- ------------------------- ---------                                   
         0 SAMPLE                    31-AUG-05                                   
        10 SAMPLE                    31-AUG-05                                   
        20 SAMPLE                    31-AUG-05                                   
        30 SAMPLE                    31-AUG-05                                   
 
SQL> cl scr 
 
SQL> CREATE TABLE Sample05 
  2  ( 
  3   SampID  NUMBER(4), 
  4   SampName VARCHAR2(25), 
  5   SampDate DATE 
  6   ); 
 
Table created. 
 
SQL> CREATE SEQUENCE SampleSeq05 
  2  INCREMENT BY -1 
  3  START WITH 10 
  4  MINVALUE 0 
  5  MAXVALUE 10 
  6  NOCACHE 
  7  NOCYCLE 
  8  / 
 
Sequence created. 
 
SQL> INSERT INTO Sample05 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4  (SampleSeq05.NEXTVAL, 'SAMPLE', '31-AUG-05'); 
 
1 row created. 
 
SQL> R 
 

  1  INSERT INTO Sample05 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq05.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample05 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq05.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> SELECT * FROM Sample05; 
 
    SAMPID SAMPNAME                  SAMPDATE                                    
---------- ------------------------- ---------                                   
        10 SAMPLE                    31-AUG-05                                   
         9 SAMPLE                    31-AUG-05                                   
         8 SAMPLE                    31-AUG-05                                   
 
SQL> cl scr 
 
SQL> CREATE TABLE Sample06 
  2  ( 
  3   SampID  NUMBER(4), 
  4   SampName VARCHAR2(25), 
  5   SampDate DATE 
  6   ); 
 
Table created. 
 
SQL> CREATE SEQUENCE SampleSeq06 
  2  INCREMENT BY 1 
  3  START WITH 0 
  4  MINVALUE 0 
  5  MAXVALUE 20 
  6  NOCACHE 
  7  NOCYCLE 
  8  / 
 
Sequence created. 
 
SQL> INSERT INTO Sample06 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4  (SampleSeq06.NEXTVAL, 'SAMPLE', '31-AUG-05'); 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample06 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
 

  4* (SampleSeq06.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample06 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq06.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> SELECT SampleSeq06.NEXTVAL FROM DUAL; 
 
   NEXTVAL                                                                       
----------                                                                       
         3                                                                       
 
SQL> R 
  1* SELECT SampleSeq06.NEXTVAL FROM DUAL 
 
   NEXTVAL                                                                       
----------                                                                       
         4                                                                       
 
SQL> R 
  1* SELECT SampleSeq06.NEXTVAL FROM DUAL 
 
   NEXTVAL                                                                       
----------                                                                       
         5                                                                       
 
SQL> INSERT INTO Sample06 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4  (SampleSeq06.NEXTVAL, 'SAMPLE', '31-AUG-05'); 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample06 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq06.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample06 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq06.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> SELECT * FROM Sample06; 
 

 
    SAMPID SAMPNAME                  SAMPDATE                                    
---------- ------------------------- ---------                                   
         0 SAMPLE                    31-AUG-05                                   
         1 SAMPLE                    31-AUG-05                                   
         2 SAMPLE                    31-AUG-05                                   
         6 SAMPLE                    31-AUG-05                                   
         7 SAMPLE                    31-AUG-05                                   
         8 SAMPLE                    31-AUG-05                                   
 
6 rows selected. 
 
SQL> CREATE TABLE Sample07_1 
  2  ( 
  3   SampID  NUMBER(4), 
  4   SampName VARCHAR2(25), 
  5   SampDate DATE 
  6   ); 
 
Table created. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE Sample07_2 
  2  ( 
  3   SampID  NUMBER(4), 
  4   SampName VARCHAR2(25), 
  5   SampDate DATE 
  6*  ) 
SQL> / 
 
Table created. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE Sample07_3 
  2  ( 
  3   SampID  NUMBER(4), 
  4   SampName VARCHAR2(25), 
  5   SampDate DATE 
  6*  ) 
SQL> / 
 
Table created. 
 
SQL> CREATE SEQUENCE SampleSeq07 
  2  INCREMENT BY 1 
  3  START WITH 0 
  4  MINVALUE 0 
  5  MAXVALUE 20 
  6  NOCACHE 
  7  NOCYCLE 
  8  / 
 
 

Sequence created. 
 
SQL> INSERT INTO Sample07_1 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4  (SampleSeq07.NEXTVAL, 'SAMPLE', '31-AUG-05'); 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample07_1 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq07.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample07_1 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq07.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO Sample07_2 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq07.NEXTVAL, 'SAMPLE', '31-AUG-05') 
SQL> / 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample07_2 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq07.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample07_2 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq07.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO Sample07_3 
 

  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq07.NEXTVAL, 'SAMPLE', '31-AUG-05') 
SQL> / 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample07_3 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq07.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> R 
  1  INSERT INTO Sample07_3 
  2  (SampID, SampName,  SampDate) 
  3  VALUES 
  4* (SampleSeq07.NEXTVAL, 'SAMPLE', '31-AUG-05') 
 
1 row created. 
 
SQL> SELECT * FROM Sample07_1; 
 
    SAMPID SAMPNAME                  SAMPDATE                                    
---------- ------------------------- ---------                                   
         0 SAMPLE                    31-AUG-05                                   
         1 SAMPLE                    31-AUG-05                                   
         2 SAMPLE                    31-AUG-05                                   
 
SQL> SELECT * FROM Sample07_2; 
 
    SAMPID SAMPNAME                  SAMPDATE                                    
---------- ------------------------- ---------                                   
         3 SAMPLE                    31-AUG-05                                   
         4 SAMPLE                    31-AUG-05                                   
         5 SAMPLE                    31-AUG-05                                   
 
SQL> SELECT * FROM Sample07_3; 
 
    SAMPID SAMPNAME                  SAMPDATE                                    
---------- ------------------------- ---------                                   
         6 SAMPLE                    31-AUG-05                                   
         7 SAMPLE                    31-AUG-05                                   
         8 SAMPLE                    31-AUG-05                                   
SQL> cl scr 
 
SQL> DESC USER_OBJECTS 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 OBJECT_NAME                                        VARCHAR2(128) 
 SUBOBJECT_NAME                                     VARCHAR2(30) 
 OBJECT_ID                                          NUMBER 
 

 DATA_OBJECT_ID                                     NUMBER 
 OBJECT_TYPE                                        VARCHAR2(19) 
 CREATED                                            DATE 
 LAST_DDL_TIME                                      DATE 
 TIMESTAMP                                          VARCHAR2(19) 
 STATUS                                             VARCHAR2(7) 
 TEMPORARY                                          VARCHAR2(1) 
 GENERATED                                          VARCHAR2(1) 
 SECONDARY                                          VARCHAR2(1) 
 
SQL> COLUMN OBJECT_NAME FORMAT A25 
SQL> COLUMN OBJECT_TYPE FORMAT A20 
SQL> SELECT OBJECT_NAME, OBJECT_TYPE 
  2  FROM USER_OBJECTS 
  3  ORDER BY OBJECT_TYPE; 
 
OBJECT_NAME               OBJECT_TYPE                                            
------------------------- --------------------                                   
SAMPID_PK01               INDEX                                                  
SAMPID_PK02               INDEX                                                  
EMP_PRIMARY_KEY           INDEX                                                  
DEPT_PRIMARY_KEY          INDEX                                                  
ORD_PRIMARY_KEY           INDEX                                                  
PRODUCT_PRIMARY_KEY       INDEX                                                  
PRICE_INDEX               INDEX                                                  
ITEM_PRIMARY_KEY          INDEX                                                  
CUSTOMER_PRIMARY_KEY      INDEX                                                  
ORDID                     SEQUENCE                                               
PRODID                    SEQUENCE                                               
 
OBJECT_NAME               OBJECT_TYPE                                            
------------------------- --------------------                                   
SAMPLESEQ03               SEQUENCE                                               
SAMPLESEQ05               SEQUENCE                                               
SAMPLESEQ07               SEQUENCE                                               
SAMPLESEQ06               SEQUENCE                                               
SAMPLESEQ04               SEQUENCE                                               
SAMPLESEQ02               SEQUENCE                                               
SAMPLESEQ01               SEQUENCE                                               
CUSTID                    SEQUENCE                                               
SAMPLE                    TABLE                                                  
PRICE                     TABLE                                                  
PRODUCT                   TABLE                                                  
 
OBJECT_NAME               OBJECT_TYPE                                            
------------------------- --------------------                                   
BONUS                     TABLE                                                  
EMP                       TABLE                                                  
DEPT                      TABLE                                                  
SAMPLE07_3                TABLE                                                  
SAMPLE07_2                TABLE                                                  
SAMPLE07_1                TABLE                                                  
SAMPLE06                  TABLE                                                  
SAMPLE05                  TABLE                                                  
SAMPLE04                  TABLE                                                  
ITEM                      TABLE                                                  
 

ORD                       TABLE                                                  
 
OBJECT_NAME               OBJECT_TYPE                                            
------------------------- --------------------                                   
CUSTOMER                  TABLE                                                  
DUMMY                     TABLE                                                  
SALGRADE                  TABLE                                                  
SAMPLE03                  TABLE                                                  
SAMPLE02                  TABLE                                                  
STUDENTS                  TABLE                                                  
SAMPLETAB                 TABLE                                                  
SAMPLESP                  TABLE                                                  
SAMPLEINS                 TABLE                                                  
SAMPLE01                  TABLE                                                  
SALES                     VIEW                                                   
 
44 rows selected. 
 
SQL> cl scr 
 
SQL> DESC USER_SEQUENCES 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 SEQUENCE_NAME                             NOT NULL VARCHAR2(30) 
 MIN_VALUE                                          NUMBER 
 MAX_VALUE                                          NUMBER 
 INCREMENT_BY                              NOT NULL NUMBER 
 CYCLE_FLAG                                         VARCHAR2(1) 
 ORDER_FLAG                                         VARCHAR2(1) 
 CACHE_SIZE                                NOT NULL NUMBER 
 LAST_NUMBER                               NOT NULL NUMBER 
 
SQL> COLUMN SEQUENCE_NAME FORMAT A15 
SQL> COLUMN MIN_VALUE FORMAT 99 
SQL> COLUMN MAX_VALUE FORMAT 999 
SQL> COLUMN INCREMENT_BY FORMAT 999 
SQL> COLUMN LAST_NUMBER FORMAT 99 
SQL> SELECT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, LAST_NUMBER 
  2  FROM USER_SEQUENCES 
  3  WHERE SEQUENCE_NAME = 'SAMPLESEQ0'||'&GVal'; 
Enter value for gval: 1 
 
SEQUENCE_NAME   MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER                     
--------------- --------- --------- ------------ -----------                     
SAMPLESEQ01             0         5            1           6                     
 
SQL> SELECT SAMPLESEQ01.CURRVAL FROM DUAL; 
SELECT SAMPLESEQ01.CURRVAL FROM DUAL 
       * 
ERROR at line 1: 
ORA-08002: sequence SAMPLESEQ01.CURRVAL is not yet defined in this session  
 
 
SQL> SELECT SAMPLESEQ01.NEXTVAL FROM DUAL; 
SELECT SAMPLESEQ01.NEXTVAL FROM DUAL 
       * 
 

ERROR at line 1: 
ORA-08004: sequence SAMPLESEQ01.NEXTVAL exceeds MAXVALUE and cannot be  
instantiated  
 
 
SQL> SELECT SAMPLESEQ01.CURRVAL FROM DUAL; 
SELECT SAMPLESEQ01.CURRVAL FROM DUAL 
                                * 
ERROR at line 1: 
ORA-08002: sequence SAMPLESEQ01.CURRVAL is not yet defined in this session  
 
 
SQL> cl scr 
 
SQL> SELECT Ename, Sal, Deptno, Job 
  2  FROM Emp 
  3  START WITH Ename = 'KING' 
  4  CONNECT BY PRIOR Empno = MGR; 
 
ENAME             SAL     DEPTNO JOB                                             
---------- ---------- ---------- ---------                                       
KING             5000         10 PRESIDENT                                       
BLAKE            2850         30 MANAGER                                         
MARTIN           1250         30 SALESMAN                                        
ALLEN            1600         30 SALESMAN                                        
TURNER           1500         30 SALESMAN                                        
JAMES             950         30 CLERK                                           
WARD             1250         30 SALESMAN                                        
CLARK            2450         10 MANAGER                                         
MILLER           1300         10 CLERK                                           
JONES            2975         20 MANAGER                                         
FORD             3000         20 ANALYST                                         
 
ENAME             SAL     DEPTNO JOB                                             
---------- ---------- ---------- ---------                                       
SMITH             800         20 CLERK                                           
SCOTT            3000         20 ANALYST                                         
ADAMS            1100         20 CLERK                                           
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT LEVEL, Ename, Sal, Deptno, Job 
  2  FROM Emp 
  3  START WITH Ename = 'KING' 
  4* CONNECT BY PRIOR Empno = MGR 
SQL> / 
 
     LEVEL ENAME             SAL     DEPTNO JOB                                  
---------- ---------- ---------- ---------- ---------                            
         1 KING             5000         10 PRESIDENT                            
         2 BLAKE            2850         30 MANAGER                              
         3 MARTIN           1250         30 SALESMAN                             
         3 ALLEN            1600         30 SALESMAN                             
 

         3 TURNER           1500         30 SALESMAN                             
         3 JAMES             950         30 CLERK                                
         3 WARD             1250         30 SALESMAN                             
         2 CLARK            2450         10 MANAGER                              
         3 MILLER           1300         10 CLERK                                
         2 JONES            2975         20 MANAGER                              
         3 FORD             3000         20 ANALYST                              
 
     LEVEL ENAME             SAL     DEPTNO JOB                                  
---------- ---------- ---------- ---------- ---------                            
         4 SMITH             800         20 CLERK                                
         3 SCOTT            3000         20 ANALYST                              
         4 ADAMS            1100         20 CLERK                                
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT LEVEL, Ename, Sal, Deptno, Job 
  2* FROM Emp 
  3  / 
FROM Emp 
     * 
ERROR at line 2: 
ORA-01788: CONNECT BY clause required in this query block  
 
 
SQL> cl scr 
 
SQL> COLUMN Org_Level FORMAT A15 
SQL> SELECT 
  2  LPAD(LEVEL, ((2 * LEVEL) - 1)) Org_Level, 
  3  Ename, Empno, Mgr, Job 
  4  FROM Emp 
  5  START WITH Job = 'PRESIDENT' 
  6  CONNECT BY PRIOR Empno = MGR; 
 
ORG_LEVEL       ENAME           EMPNO        MGR JOB                             
--------------- ---------- ---------- ---------- ---------                       
1               KING             7839            PRESIDENT                       
  2             BLAKE            7698       7839 MANAGER                         
    3           MARTIN           7654       7698 SALESMAN                        
    3           ALLEN            7499       7698 SALESMAN                        
    3           TURNER           7844       7698 SALESMAN                        
    3           JAMES            7900       7698 CLERK                           
    3           WARD             7521       7698 SALESMAN                        
  2             CLARK            7782       7839 MANAGER                         
    3           MILLER           7934       7782 CLERK                           
  2             JONES            7566       7839 MANAGER                         
    3           FORD             7902       7566 ANALYST                         
 
ORG_LEVEL       ENAME           EMPNO        MGR JOB                             
--------------- ---------- ---------- ---------- ---------                       
      4         SMITH            7369       7902 CLERK                           
    3           SCOTT            7788       7566 ANALYST                         
 

      4         ADAMS            7876       7788 CLERK                           
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 
  2  LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename  Org_Level, Empno, Mgr, Job 
  3  FROM Emp 
  4  START WITH Job = 'PRESIDENT' 
  5* CONNECT BY PRIOR Empno = MGR 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 
  2  LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename  Org_Level, Empno, Mgr, Job 
  3  FROM Emp 
  4  START WITH Job = 'PRESIDENT' 
  5* CONNECT BY PRIOR Empno = MGR 
SQL> / 
 
ORG_LEVEL            EMPNO        MGR JOB                                        
--------------- ---------- ---------- ---------                                  
1 KING                7839            PRESIDENT                                  
  2 BLAKE             7698       7839 MANAGER                                    
    3 MARTIN          7654       7698 SALESMAN                                   
    3 ALLEN           7499       7698 SALESMAN                                   
    3 TURNER          7844       7698 SALESMAN                                   
    3 JAMES           7900       7698 CLERK                                      
    3 WARD            7521       7698 SALESMAN                                   
  2 CLARK             7782       7839 MANAGER                                    
    3 MILLER          7934       7782 CLERK                                      
  2 JONES             7566       7839 MANAGER                                    
    3 FORD            7902       7566 ANALYST                                    
 
ORG_LEVEL            EMPNO        MGR JOB                                        
--------------- ---------- ---------- ---------                                  
      4 SMITH         7369       7902 CLERK                                      
    3 SCOTT           7788       7566 ANALYST                                    
      4 ADAMS         7876       7788 CLERK                                      
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 
  2  LPAD(' ', ((2 * LEVEL) - 1))||Ename  Org_Level, Empno, Mgr, Job 
  3  FROM Emp 
  4  START WITH Job = 'PRESIDENT' 
  5* CONNECT BY PRIOR Empno = MGR 
SQL> / 
 
ORG_LEVEL            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                                    
 
ORG_LEVEL            EMPNO        MGR JOB                                        
--------------- ---------- ---------- ---------                                  
       SMITH          7369       7902 CLERK                                      
     SCOTT            7788       7566 ANALYST                                    
       ADAMS          7876       7788 CLERK                                      
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT 
  2  LPAD(' ', ((2 * LEVEL) - 1))||' '||Ename Org_Chart, 
  3  Empno, MGR, Job , Sal 
  4  FROM Emp 
  5  WHERE Job != 'ANALYST' 
  6  START WITH Job = 'PRESIDENT' 
  7  CONNECT BY PRIOR Empno = MGR; 
 
ORG_CHART                                                                        
-------------------------------------------------------------------------------- 
     EMPNO        MGR JOB              SAL                                       
---------- ---------- --------- ----------                                       
  KING                                                                           
      7839            PRESIDENT       5000                                       
                                                                                 
    BLAKE                                                                        
      7698       7839 MANAGER         2850                                       
                                                                                 
      MARTIN                                                                     
      7654       7698 SALESMAN        1250                                       
                                                                                 
 
ORG_CHART                                                                        
-------------------------------------------------------------------------------- 
     EMPNO        MGR JOB              SAL                                       
---------- ---------- --------- ----------                                       
      ALLEN                                                                      
      7499       7698 SALESMAN        1600                                       
                                                                                 
      TURNER                                                                     
      7844       7698 SALESMAN        1500                                       
                                                                                 
      JAMES                                                                      
      7900       7698 CLERK            950                                       
 

                                                                                 
 
ORG_CHART                                                                        
-------------------------------------------------------------------------------- 
     EMPNO        MGR JOB              SAL                                       
---------- ---------- --------- ----------                                       
      WARD                                                                       
      7521       7698 SALESMAN        1250                                       
                                                                                 
    CLARK                                                                        
      7782       7839 MANAGER         2450                                       
                                                                                 
      MILLER                                                                     
      7934       7782 CLERK           1300                                       
                                                                                 
 
ORG_CHART                                                                        
-------------------------------------------------------------------------------- 
     EMPNO        MGR JOB              SAL                                       
---------- ---------- --------- ----------                                       
    JONES                                                                        
      7566       7839 MANAGER         2975                                       
                                                                                 
        SMITH                                                                    
      7369       7902 CLERK            800                                       
                                                                                 
        ADAMS                                                                    
      7876       7788 CLERK           1100                                       
                                                                                 
 
12 rows selected. 
 
SQL> COLUMN Org_Chart FORMAT A20 
SQL> / 
 
ORG_CHART                 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                  
 
ORG_CHART                 EMPNO        MGR JOB              SAL                  
-------------------- ---------- ---------- --------- ----------                  
        ADAMS              7876       7788 CLERK           1100                  
 
12 rows selected. 
 
SQL> ED 
 

Wrote file afiedt.buf 
 
  1  SELECT 
  2  LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename Org_Chart, 
  3  Empno, MGR, Job , Sal 
  4  FROM Emp 
  5  WHERE Job != 'ANALYST' 
  6  START WITH Job = 'PRESIDENT' 
  7* CONNECT BY PRIOR Empno = MGR 
SQL> / 
 
ORG_CHART                 EMPNO        MGR JOB              SAL                  
-------------------- ---------- ---------- --------- ----------                  
1 KING                     7839            PRESIDENT       5000                  
  2 BLAKE                  7698       7839 MANAGER         2850                  
    3 MARTIN               7654       7698 SALESMAN        1250                  
    3 ALLEN                7499       7698 SALESMAN        1600                  
    3 TURNER               7844       7698 SALESMAN        1500                  
    3 JAMES                7900       7698 CLERK            950                  
    3 WARD                 7521       7698 SALESMAN        1250                  
  2 CLARK                  7782       7839 MANAGER         2450                  
    3 MILLER               7934       7782 CLERK           1300                  
  2 JONES                  7566       7839 MANAGER         2975                  
      4 SMITH              7369       7902 CLERK            800                  
 
ORG_CHART                 EMPNO        MGR JOB              SAL                  
-------------------- ---------- ---------- --------- ----------                  
      4 ADAMS              7876       7788 CLERK           1100                  
 
12 rows selected. 
 
SQL> cl scr 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 
  2  LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename Org_Chart, 
  3  Empno, MGR, Job , Sal 
  4  FROM Emp 
  5  START WITH Ename = 'BLAKE' 
  6* CONNECT BY PRIOR Empno = MGR 
SQL> / 
 
ORG_CHART                 EMPNO        MGR JOB              SAL                  
-------------------- ---------- ---------- --------- ----------                  
1 BLAKE                    7698       7839 MANAGER         2850                  
  2 MARTIN                 7654       7698 SALESMAN        1250                  
  2 ALLEN                  7499       7698 SALESMAN        1600                  
  2 TURNER                 7844       7698 SALESMAN        1500                  
  2 JAMES                  7900       7698 CLERK            950                  
  2 WARD                   7521       7698 SALESMAN        1250                  
 
6 rows selected. 
 
SQL> ED 
 

Wrote file afiedt.buf 
 
  1  SELECT 
  2  LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename Org_Chart, 
  3  Empno, MGR, Job , Sal 
  4  FROM Emp 
  5  START WITH Ename = 'JONES' 
  6* CONNECT BY PRIOR Empno = MGR 
SQL> / 
 
ORG_CHART                 EMPNO        MGR JOB              SAL                  
-------------------- ---------- ---------- --------- ----------                  
1 JONES                    7566       7839 MANAGER         2975                  
  2 FORD                   7902       7566 ANALYST         3000                  
    3 SMITH                7369       7902 CLERK            800                  
  2 SCOTT                  7788       7566 ANALYST         3000                  
    3 ADAMS                7876       7788 CLERK           1100                  
 
SQL> cl scr 
 
SQL> SELECT 
  2  LPAD(' ', ((2 * LEVEL) - 1))||' '||Ename Org_Chart, 
  3  Empno, MGR, Job, Sal 
  4  FROM Emp 
  5  START WITH Job = 'PRESIDENT' 
  6  CONNECT BY PRIOR Empno = MGR 
  7  AND LEVEL <= 2; 
 
ORG_CHART                 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 
  2  LPAD(' ', ((2 * LEVEL) - 1))||' '||Ename Org_Chart, 
  3  Empno, MGR, Job, Sal 
  4  FROM Emp 
  5  WHERE LEVEL <= 2 
  6  START WITH Job = 'PRESIDENT' 
  7* CONNECT BY PRIOR Empno = MGR 
  8  / 
 
ORG_CHART                 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 
  2  LPAD(' ', ((2 * LEVEL) - 1))||' '||Ename Org_Chart, 
  3  Empno, MGR, Job, Sal 
  4  FROM Emp 
  5  WHERE LEVEL = 2 
  6  START WITH Job = 'PRESIDENT' 
  7* CONNECT BY PRIOR Empno = MGR 
SQL> / 
 
ORG_CHART                 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 
  2  LPAD(' ', ((2 * LEVEL) - 1))||' '||Ename Org_Chart, 
  3  Empno, MGR, Job, Sal 
  4  FROM Emp 
  5  START WITH Job = 'PRESIDENT' 
  6  CONNECT BY PRIOR Empno = MGR 
  7* LEVEL = 2 
SQL> / 
LEVEL = 2 
* 
ERROR at line 7: 
ORA-00933: SQL command not properly ended  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 
  2  LPAD(' ', ((2 * LEVEL) - 1))||' '||Ename Org_Chart, 
  3  Empno, MGR, Job, Sal 
  4  FROM Emp 
  5  START WITH Job = 'PRESIDENT' 
  6  CONNECT BY PRIOR Empno = MGR AND 
  7* LEVEL = 2 
SQL> / 
 
ORG_CHART                 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 
 

  2  LPAD(' ', ((2 * LEVEL) - 1))||' '||Ename Org_Chart, 
  3  Empno, MGR, Job, Sal 
  4  FROM Emp 
  5  START WITH Job = 'PRESIDENT' 
  6  CONNECT BY PRIOR Empno = MGR AND 
  7* LEVEL = 2SQL> cl scr 
 
SQL> COLUMN Org_Chart FORMAT A20 
SQL> cl scr 
 
SQL> SELECT 
  2  LPAD(' ', ((2 * LEVEL) - 1))||Ename Org_Chart, 
  3  Empno, MGR, Job, Sal 
  4  FROM Emp 
  5  START WITH Job = 'PRESIDENT' 
  6  CONNECT BY NOCYCLE PRIOR  Empno = MGR 
  7  AND LEVEL = 2; 
 
ORG_CHART                 EMPNO        MGR JOB              SAL                  
-------------------- ---------- ---------- --------- ----------                  
 KING                      7839            PRESIDENT       5000                  
   BLAKE                   7698       7839 MANAGER         2850                  
   CLARK                   7782       7839 MANAGER         2450                  
   JONES                   7566       7839 MANAGER         2975                  
 
SQL> cl scr 
 
SQL> SELECT 
  2  LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename Org_Chart, 
  3  Empno, MGR, Job, Sal 
  4  FROM Emp 
  5  START WITH Job = 'PRESIDENT' 
  6  CONNECT BY NOCYCLE PRIOR  Empno = MGR 
  7  AND LEVEL IN(2, 4); 
 
ORG_CHART                 EMPNO        MGR JOB              SAL                  
-------------------- ---------- ---------- --------- ----------                  
1 KING                     7839            PRESIDENT       5000                  
  2 BLAKE                  7698       7839 MANAGER         2850                  
  2 CLARK                  7782       7839 MANAGER         2450                  
  2 JONES                  7566       7839 MANAGER         2975                  
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 
  2  LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename Org_Chart, 
  3  Empno, MGR, Job, Sal 
  4  FROM Emp 
  5  START WITH Job = 'PRESIDENT' 
  6  CONNECT BY NOCYCLE PRIOR  Empno = MGR 
  7* AND LEVEL IN(2, 3, 4) 
SQL> / 
 
 

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

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

SQL> SELECT 
  2  LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename Org_Chart, 
  3  Empno, MGR, Job, Sal 
  4  FROM Emp 
  5  WHERE LEVEL BETWEEN 2 AND 4 
  6  START WITH Job = 'PRESIDENT' 
  7  CONNECT BY NOCYCLE PRIOR Empno = MGR; 
 
ORG_CHART                 EMPNO        MGR JOB              SAL                  
-------------------- ---------- ---------- --------- ----------                  
  2 BLAKE                  7698       7839 MANAGER         2850                  
    3 MARTIN               7654       7698 SALESMAN        1250                  
    3 ALLEN                7499       7698 SALESMAN        1600                  
    3 TURNER               7844       7698 SALESMAN        1500                  
    3 JAMES                7900       7698 CLERK            950                  
    3 WARD                 7521       7698 SALESMAN        1250                  
  2 CLARK                  7782       7839 MANAGER         2450                  
    3 MILLER               7934       7782 CLERK           1300                  
  2 JONES                  7566       7839 MANAGER         2975                  
    3 FORD                 7902       7566 ANALYST         3000                  
      4 SMITH              7369       7902 CLERK            800                  
 
ORG_CHART                 EMPNO        MGR JOB              SAL                  
-------------------- ---------- ---------- --------- ----------                  
    3 SCOTT                7788       7566 ANALYST         3000                  
      4 ADAMS              7876       7788 CLERK           1100                  
 
13 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT 
  2  LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename Org_Chart, 
  3  Empno, MGR, Job, Sal 
  4  FROM Emp 
  5  WHERE Sal = (SELECT MAX(Sal) 
  6                          FROM Emp 
  7                          WHERE LEVEL = 2 
  8                          START WITH Job = 'PRESIDENT' 
  9                          CONNECT BY PRIOR Empno = MGR) 
 10  START WITH Job = 'PRESIDENT' 
 11  CONNECT BY NOCYCLE PRIOR Empno = MGR; 
 
ORG_CHART                 EMPNO        MGR JOB              SAL                  
-------------------- ---------- ---------- --------- ----------                  
  2 JONES                  7566       7839 MANAGER         2975                  
 
SQL> cl scr 
 
SQL> SELECT 
  2  LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename Org_Chart, 
  3  Empno, Dept.Deptno, Dname, Sal 
  4  FROM Emp, Dept 
  5  WHERE Sal > (SELECT MAX(Sal) 
  6      FROM Emp 
  7      WHERE LEVEL = 2 
 

  8      START WITH Job = 'PRESIDENT' 
  9      CONNECT BY PRIOR Empno = MGR) AND Dept.Deptno = 
Emp.Deptno 
 10  START WITH Job = 'PRESIDENT' 
 11  CONNECT BY NOCYCLE PRIOR Empno = MGR; 
 
ORG_CHART                 EMPNO     DEPTNO DNAME                 SAL             
-------------------- ---------- ---------- -------------- ----------             
1 KING                     7839         10 ACCOUNTING           5000             
    3 SCOTT                7788         20 RESEARCH             3000             
    3 FORD                 7902         20 RESEARCH             3000             
 
SQL> SELECT 
  2  LPAD(LEVEL, ((2 * LEVEL) - 1))||' '||Ename Org_Chart, 
  3  Empno, Dept.Deptno, Dname, Sal, Grade 
  4  FROM Emp, Dept, SalGrade 
  5  WHERE Sal > (SELECT MAX(Sal) 
  6      FROM Emp 
  7      WHERE LEVEL = 2 
  8      START WITH Job = 'PRESIDENT' 
  9      CONNECT BY PRIOR Empno = MGR) AND Dept.Deptno = 
Emp.Deptno AND 
 10      Sal BETWEEN Losal AND HiSal 
 11  START WITH Job = 'PRESIDENT' 
 12  CONNECT BY NOCYCLE PRIOR Empno = MGR; 
 
ORG_CHART                 EMPNO     DEPTNO DNAME                 SAL      GRADE  
-------------------- ---------- ---------- -------------- ---------- ----------  
1 KING                     7839         10 ACCOUNTING           5000          5  
    3 FORD                 7902         20 RESEARCH             3000          4  
    3 SCOTT                7788         20 RESEARCH             3000          4  
 
SQL> cl scr 
 
SQL> SELECT LEVEL, MAX(Sal) 
  2  FROM EMP 
  3  CONNECT BY PRIOR Sal > Sal 
  4  GROUP BY LEVEL; 
 
     LEVEL   MAX(SAL)                                                            
---------- ----------                                                            
         1       5000                                                            
         2       3000                                                            
         3       2975                                                            
         4       2850                                                            
         5       2450                                                            
         6       1600                                                            
         7       1500                                                            
         8       1300                                                            
         9       1250                                                            
        10       1100                                                            
        11        950                                                            
 
     LEVEL   MAX(SAL)                                                            
---------- ----------                                                            
        12        800                                                            
 

 
12 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT LEVEL, MIN(Sal) 
  2  FROM EMP 
  3  CONNECT BY PRIOR Sal < Sal 
  4* GROUP BY LEVEL 
SQL> / 
 
     LEVEL   MIN(SAL)                                                            
---------- ----------                                                            
         1        800                                                            
         2        950                                                            
         3       1100                                                            
         4       1250                                                            
         5       1300                                                            
         6       1500                                                            
         7       1600                                                            
         8       2450                                                            
         9       2850                                                            
        10       2975                                                            
        11       3000                                                            
 
     LEVEL   MIN(SAL)                                                            
---------- ----------                                                            
        12       5000                                                            
 
12 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT MIN(Sal) 
  2  FROM EMP 
  3* GROUP BY Sal 
SQL> / 
 
  MIN(SAL)                                                                       
----------                                                                       
       800                                                                       
       950                                                                       
      1100                                                                       
      1250                                                                       
      1300                                                                       
      1500                                                                       
      1600                                                                       
      2450                                                                       
      2850                                                                       
      2975                                                                       
      3000                                                                       
 
  MIN(SAL)                                                                       
----------                                                                       
 

      5000                                                                       
 
12 rows selected. 
 
SQL> cl scr 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT LEVEL, MAX(Ename) 
  2  FROM EMP 
  3  CONNECT BY PRIOR Ename > Ename 
  4* GROUP BY LEVEL; 
SQL> / 
GROUP BY LEVEL; 
              * 
ERROR at line 4: 
ORA-00911: invalid character  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT LEVEL, MAX(Ename) 
  2  FROM EMP 
  3  CONNECT BY PRIOR Ename > Ename 
  4* GROUP BY LEVEL 
SQL> / 
 
     LEVEL MAX(ENAME)                                                            
---------- ----------                                                            
         1 WARD                                                                  
         2 TURNER                                                                
         3 SMITH                                                                 
         4 SCOTT                                                                 
         5 MILLER                                                                
         6 MARTIN                                                                
         7 KING                                                                  
         8 JONES                                                                 
         9 JAMES                                                                 
        10 FORD                                                                  
        11 CLARK                                                                 
 
     LEVEL MAX(ENAME)                                                            
---------- ----------                                                            
        12 BLAKE                                                                 
        13 ALLEN                                                                 
        14 ADAMS                                                                 
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT LEVEL, MIN(Ename) 
  2  FROM EMP 
 

  3  CONNECT BY PRIOR Ename < Ename 
  4* GROUP BY LEVEL 
SQL> / 
 
     LEVEL MIN(ENAME)                                                            
---------- ----------                                                            
         1 ADAMS                                                                 
         2 ALLEN                                                                 
         3 BLAKE                                                                 
         4 CLARK                                                                 
         5 FORD                                                                  
         6 JAMES                                                                 
         7 JONES                                                                 
         8 KING                                                                  
         9 MARTIN                                                                
        10 MILLER                                                                
        11 SCOTT                                                                 
 
     LEVEL MIN(ENAME)                                                            
---------- ----------                                                            
        12 SMITH                                                                 
        13 TURNER                                                                
        14 WARD                                                                  
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT LEVEL, MIN(HireDate) 
  2  FROM EMP 
  3  CONNECT BY PRIOR HireDate < HireDate 
  4* GROUP BY LEVEL 
SQL> / 
 
     LEVEL MIN(HIRED                                                             
---------- ---------                                                             
         1 17-DEC-80                                                             
         2 20-FEB-81                                                             
         3 22-FEB-81                                                             
         4 02-APR-81                                                             
         5 01-MAY-81                                                             
         6 09-JUN-81                                                             
         7 08-SEP-81                                                             
         8 28-SEP-81                                                             
         9 17-NOV-81                                                             
        10 03-DEC-81                                                             
        11 23-JAN-82                                                             
 
     LEVEL MIN(HIRED                                                             
---------- ---------                                                             
        12 09-DEC-82                                                             
        13 12-JAN-83                                                             
 
13 rows selected. 
 
 

SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT LEVEL, MAX(HireDate) 
  2  FROM EMP 
  3  CONNECT BY PRIOR HireDate < HireDate 
  4* GROUP BY LEVEL 
SQL> / 
 
     LEVEL MAX(HIRED                                                             
---------- ---------                                                             
         1 12-JAN-83                                                             
         2 12-JAN-83                                                             
         3 12-JAN-83                                                             
         4 12-JAN-83                                                             
         5 12-JAN-83                                                             
         6 12-JAN-83                                                             
         7 12-JAN-83                                                             
         8 12-JAN-83                                                             
         9 12-JAN-83                                                             
        10 12-JAN-83                                                             
        11 12-JAN-83                                                             
 
     LEVEL MAX(HIRED                                                             
---------- ---------                                                             
        12 12-JAN-83                                                             
        13 12-JAN-83                                                             
 
13 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT LEVEL, MAX(HireDate) 
  2  FROM EMP 
  3  CONNECT BY PRIOR HireDate > HireDate 
  4* GROUP BY LEVEL 
SQL> / 
 
     LEVEL MAX(HIRED                                                             
---------- ---------                                                             
         1 12-JAN-83                                                             
         2 09-DEC-82                                                             
         3 23-JAN-82                                                             
         4 03-DEC-81                                                             
         5 17-NOV-81                                                             
         6 28-SEP-81                                                             
         7 08-SEP-81                                                             
         8 09-JUN-81                                                             
         9 01-MAY-81                                                             
        10 02-APR-81                                                             
        11 22-FEB-81                                                             
 
     LEVEL MAX(HIRED                                                             
---------- ---------                                                             
        12 20-FEB-81                                                             
 

        13 17-DEC-80                                                             
 
13 rows selected. 
 
SQL> cl scr 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT LEVEL, MAX(Ename), MAX(HireDate) 
  2  FROM EMP 
  3  CONNECT BY PRIOR HireDate > HireDate 
  4* GROUP BY LEVEL 
SQL> / 
 
     LEVEL MAX(ENAME) MAX(HIRED                                                  
---------- ---------- ---------                                                  
         1 WARD       12-JAN-83                                                  
         2 WARD       09-DEC-82                                                  
         3 WARD       23-JAN-82                                                  
         4 WARD       03-DEC-81                                                  
         5 WARD       17-NOV-81                                                  
         6 WARD       28-SEP-81                                                  
         7 WARD       08-SEP-81                                                  
         8 WARD       09-JUN-81                                                  
         9 WARD       01-MAY-81                                                  
        10 WARD       02-APR-81                                                  
        11 WARD       22-FEB-81                                                  
 
     LEVEL MAX(ENAME) MAX(HIRED                                                  
---------- ---------- ---------                                                  
        12 SMITH      20-FEB-81                                                  
        13 SMITH      17-DEC-80                                                  
 
13 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT LEVEL, MAX(Sal) 
  2  FROM EMP 
  3  CONNECT BY PRIOR Sal > Sal 
  4  GROUP BY LEVEL; 
 
     LEVEL   MAX(SAL)                                                            
---------- ----------                                                            
         1       5000                                                            
         2       3000                                                            
         3       2975                                                            
         4       2850                                                            
         5       2450                                                            
         6       1600                                                            
         7       1500                                                            
         8       1300                                                            
         9       1250                                                            
        10       1100                                                            
        11        950                                                            
 

 
     LEVEL   MAX(SAL)                                                            
---------- ----------                                                            
        12        800                                                            
 
12 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT LEVEL, MAX(Sal) 
  2  FROM EMP 
  3  WHERE LEVEL = &LEVELNO 
  4  CONNECT BY PRIOR Sal > Sal 
  5  GROUP BY LEVEL; 
Enter value for levelno: 1 
 
     LEVEL   MAX(SAL)                                                            
---------- ----------                                                            
         1       5000                                                            
 
SQL> / 
Enter value for levelno: 2 
 
     LEVEL   MAX(SAL)                                                            
---------- ----------                                                            
         2       3000                                                            
 
SQL> / 
Enter value for levelno: 3 
 
     LEVEL   MAX(SAL)                                                            
---------- ----------                                                            
         3       2975                                                            
 
SQL> SELECT Ename, Sal, Deptno, Job 
  2  FROM Emp 
  3  WHERE Sal = (SELECT MAX(Sal) 
  4     FROM EMP 
  5     WHERE LEVEL = &LEVELNO 
  6     CONNECT BY PRIOR Sal > Sal 
  7     GROUP BY LEVEL); 
Enter value for levelno: 1 
 
ENAME             SAL     DEPTNO JOB                                             
---------- ---------- ---------- ---------                                       
KING             5000         10 PRESIDENT                                       
 
SQL> / 
Enter value for levelno: 2 
 
ENAME             SAL     DEPTNO JOB                                             
---------- ---------- ---------- ---------                                       
FORD             3000         20 ANALYST                                         
SCOTT            3000         20 ANALYST                                         
 
SQL> / 
 

Enter value for levelno: 3 
 
ENAME             SAL     DEPTNO JOB                                             
---------- ---------- ---------- ---------                                       
JONES            2975         20 MANAGER                                         
 
SQL> cl scr 
 
SQL> SELECT LEVEL, MIN(Sal) 
  2  FROM EMP 
  3  WHERE LEVEL = &LEVELNO 
  4  CONNECT BY PRIOR Sal < Sal 
  5  GROUP BY LEVEL; 
Enter value for levelno: 1 
 
     LEVEL   MIN(SAL)                                                            
---------- ----------                                                            
         1        800                                                            
 
SQL> / 
Enter value for levelno: 2 
 
     LEVEL   MIN(SAL)                                                            
---------- ----------                                                            
         2        950                                                            
 
SQL> SELECT Ename, Sal, Deptno, Job 
  2  FROM Emp 
  3  WHERE Sal = (SELECT MIN(Sal) 
  4     FROM EMP 
  5     WHERE LEVEL = &LEVELNO 
  6     CONNECT BY PRIOR Sal < Sal 
  7     GROUP BY LEVEL); 
Enter value for levelno: 1 
 
ENAME             SAL     DEPTNO JOB                                             
---------- ---------- ---------- ---------                                       
SMITH             800         20 CLERK                                           
 
SQL> / 
Enter value for levelno: 2 
 
ENAME             SAL     DEPTNO JOB                                             
---------- ---------- ---------- ---------                                       
JAMES             950         30 CLERK                                           
 
SQL> / 
Enter value for levelno: 3 
 
ENAME             SAL     DEPTNO JOB                                             
---------- ---------- ---------- ---------                                       
ADAMS            1100         20 CLERK                                           
 
SQL> cl scr 
 
SQL> SELECT LEVEL, MIN(HireDate) Senior 
 

  2  FROM EMP 
  3  WHERE LEVEL = &LEVELNO 
  4  CONNECT BY PRIOR HireDate < HireDate 
  5  GROUP BY LEVEL; 
Enter value for levelno: 1 
 
     LEVEL SENIOR                                                                
---------- ---------                                                             
         1 17-DEC-80                                                             
 
SQL> / 
Enter value for levelno: 5 
 
     LEVEL SENIOR                                                                
---------- ---------                                                             
         5 01-MAY-81                                                             
 
SQL> SELECT Ename, Sal, HireDate 
  2  FROm Emp 
  3  WHERE HireDate = (SELECT MIN(HireDate) 
  4       FROM EMP 
  5       WHERE LEVEL = &LEVELNO 
  6       CONNECT BY 
  7       PRIOR HireDate < HireDate 
  8       GROUP BY LEVEL); 
Enter value for levelno: 1 
 
ENAME             SAL HIREDATE                                                   
---------- ---------- ---------                                                  
SMITH             800 17-DEC-80                                                  
 
SQL> / 
Enter value for levelno: 6 
 
ENAME             SAL HIREDATE                                                   
---------- ---------- ---------                                                  
CLARK            2450 09-JUN-81                                                  
 
SQL> / 
Enter value for levelno: 2 
 
ENAME             SAL HIREDATE                                                   
---------- ---------- ---------                                                  
ALLEN            1600 20-FEB-81                                                  
 
SQL> cl scr 
 
SQL> COLUMN "Path" FORMAT A30 
SQL> SELECT 
  2  Ename Employee, 
  3  CONNECT_BY_ROOT Ename "Manager", 
  4  LEVEL - 1 "Pathlen", 
  5  SYS_CONNECT_BY_PATH(Ename, '/') "Path" 
  6  FROM Emp 
  7  WHERE LEVEL > 1 AND Deptno = &Deptno 
  8  CONNECT BY PRIOR Empno = MGR; 
 

Enter value for deptno: 20 
 
EMPLOYEE   Manager       Pathlen Path                                            
---------- ---------- ---------- ------------------------------                  
SMITH      FORD                1 /FORD/SMITH                                     
ADAMS      SCOTT               1 /SCOTT/ADAMS                                    
FORD       JONES               1 /JONES/FORD                                     
SMITH      JONES               2 /JONES/FORD/SMITH                               
SCOTT      JONES               1 /JONES/SCOTT                                    
ADAMS      JONES               2 /JONES/SCOTT/ADAMS                              
JONES      KING                1 /KING/JONES                                     
FORD       KING                2 /KING/JONES/FORD                                
SMITH      KING                3 /KING/JONES/FORD/SMITH                          
SCOTT      KING                2 /KING/JONES/SCOTT                               
ADAMS      KING                3 /KING/JONES/SCOTT/ADAMS                         
 
11 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT 
  2  Name, 
  3  SUM(Sal) "Total Salary" 
  4  FROM ( 
  5    SELECT 
  6    CONNECT_BY_ROOT Ename AS Name, 
  7    Sal 
  8    FROM Emp 
  9    WHERE Deptno = &GiveDeptno 
 10    CONNECT BY PRIOR Empno = MGR 
 11    ) 
 12  GROUP BY Name; 
Enter value for givedeptno: 20 
 
NAME       Total Salary                                                          
---------- ------------                                                          
ADAMS              1100                                                          
FORD               3800                                                          
JONES             10875                                                          
KING              10875                                                          
SCOTT              4100                                                          
SMITH               800                                                          
 
6 rows selected. 
 
SQL> SELECT SUM(Sal) FROm Emp; 
 
  SUM(SAL)                                                                       
----------                                                                       
     29025                                                                       
 
SQL> cl ascr 
SP2-0158: unknown CLEAR option "ascr" 
SQL> cl scr 
 
SQL> SELECT 
 

  2  Ename Employee, 
  3  CONNECT_BY_ROOT Empno "Root", 
  4  LEVEL, 
  5  SYS_CONNECT_BY_PATH(Ename, '/')  NamePath 
  6  FROM Emp 
  7  WHERE Level <= 4 AND Deptno = &GiveDeptno 
  8  START WITH Ename = 'KING' 
  9  CONNECT BY NOCYCLE PRIOR Empno = MGR AND 
 10  LEVEL <= 4; 
Enter value for givedeptno: 30 
 
EMPLOYEE         Root      LEVEL                                                 
---------- ---------- ----------                                                 
NAMEPATH                                                                         
-------------------------------------------------------------------------------- 
BLAKE            7839          2                                                 
/KING/BLAKE                                                                      
                                                                                 
MARTIN           7839          3                                                 
/KING/BLAKE/MARTIN                                                               
                                                                                 
ALLEN            7839          3                                                 
/KING/BLAKE/ALLEN                                                                
                                                                                 
 
EMPLOYEE         Root      LEVEL                                                 
---------- ---------- ----------                                                 
NAMEPATH                                                                         
-------------------------------------------------------------------------------- 
TURNER           7839          3                                                 
/KING/BLAKE/TURNER                                                               
                                                                                 
JAMES            7839          3                                                 
/KING/BLAKE/JAMES                                                                
                                                                                 
WARD             7839          3                                                 
/KING/BLAKE/WARD                                                                 
                                                                                 
 
6 rows selected. 
 
SQL> COLUMN NAMEPATH FORMAT A30 
SQL> / 
Enter value for givedeptno: 30 
 
EMPLOYEE         Root      LEVEL NAMEPATH                                        
---------- ---------- ---------- ------------------------------                  
BLAKE            7839          2 /KING/BLAKE                                     
MARTIN           7839          3 /KING/BLAKE/MARTIN                              
ALLEN            7839          3 /KING/BLAKE/ALLEN                               
TURNER           7839          3 /KING/BLAKE/TURNER                              
JAMES            7839          3 /KING/BLAKE/JAMES                               
WARD             7839          3 /KING/BLAKE/WARD                                
 
6 rows selected. 
 
 

SQL> cl scr 
 
SQL> SELECT ROWNUM, Ename, Sal, Deptno 
  2  FROM Emp; 
 
    ROWNUM ENAME             SAL     DEPTNO                                      
---------- ---------- ---------- ----------                                      
         1 KING             5000         10                                      
         2 BLAKE            2850         30                                      
         3 CLARK            2450         10                                      
         4 JONES            2975         20                                      
         5 MARTIN           1250         30                                      
         6 ALLEN            1600         30                                      
         7 TURNER           1500         30                                      
         8 JAMES             950         30                                      
         9 WARD             1250         30                                      
        10 FORD             3000         20                                      
        11 SMITH             800         20                                      
 
    ROWNUM ENAME             SAL     DEPTNO                                      
---------- ---------- ---------- ----------                                      
        12 SCOTT            3000         20                                      
        13 ADAMS            1100         20                                      
        14 MILLER           1300         10                                      
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT ROWNUM, Ename, Sal, Deptno 
  2  FROM Emp 
  3* WHERE Deptno = 30 
SQL> / 
 
    ROWNUM ENAME             SAL     DEPTNO                                      
---------- ---------- ---------- ----------                                      
         1 BLAKE            2850         30                                      
         2 MARTIN           1250         30                                      
         3 ALLEN            1600         30                                      
         4 TURNER           1500         30                                      
         5 JAMES             950         30                                      
         6 WARD             1250         30                                      
 
6 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT ROWNUM, Ename, Sal, Deptno 
  2  FROM Emp 
  3* WHERE Deptno = 10 
SQL> / 
 
    ROWNUM ENAME             SAL     DEPTNO                                      
---------- ---------- ---------- ----------                                      
 

         1 KING             5000         10                                      
         2 CLARK            2450         10                                      
         3 MILLER           1300         10                                      
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT ROWNUM, Ename, Sal, Deptno 
  2  FROM Emp 
  3* ORDER BY Sal 
SQL> / 
 
    ROWNUM ENAME             SAL     DEPTNO                                      
---------- ---------- ---------- ----------                                      
        11 SMITH             800         20                                      
         8 JAMES             950         30                                      
        13 ADAMS            1100         20                                      
         5 MARTIN           1250         30                                      
         9 WARD             1250         30                                      
        14 MILLER           1300         10                                      
         7 TURNER           1500         30                                      
         6 ALLEN            1600         30                                      
         3 CLARK            2450         10                                      
         2 BLAKE            2850         30                                      
         4 JONES            2975         20                                      
 
    ROWNUM ENAME             SAL     DEPTNO                                      
---------- ---------- ---------- ----------                                      
        10 FORD             3000         20                                      
        12 SCOTT            3000         20                                      
         1 KING             5000         10                                      
 
14 rows selected. 
SQL> cl scr 
 
SQL> SELECT ROWNUM, Ename, Sal 
  2  FROM Emp; 
 
    ROWNUM ENAME             SAL                                                 
---------- ---------- ----------                                                 
         1 KING             5000                                                 
         2 BLAKE            2850                                                 
         3 CLARK            2450                                                 
         4 JONES            2975                                                 
         5 MARTIN           1250                                                 
         6 ALLEN            1600                                                 
         7 TURNER           1500                                                 
         8 JAMES             950                                                 
         9 WARD             1250                                                 
        10 FORD             3000                                                 
        11 SMITH             800                                                 
 
    ROWNUM ENAME             SAL                                                 
---------- ---------- ----------                                                 
 

        12 SCOTT            3000                                                 
        13 ADAMS            1100                                                 
        14 MILLER           1300                                                 
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT ROWNUM, Ename, Sal 
  2  FROM Emp 
  3* WHERE ROWNUM = 1 
SQL> / 
 
    ROWNUM ENAME             SAL                                                 
---------- ---------- ----------                                                 
         1 KING             5000                                                 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT ROWNUM, Ename, Sal 
  2  FROM Emp 
  3* WHERE ROWNUM = 2 
SQL> / 
 
no rows selected 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT ROWNUM, Ename, Sal 
  2  FROM Emp 
  3* WHERE ROWNUM <= 5 
SQL> / 
 
    ROWNUM ENAME             SAL                                                 
---------- ---------- ----------                                                 
         1 KING             5000                                                 
         2 BLAKE            2850                                                 
         3 CLARK            2450                                                 
         4 JONES            2975                                                 
         5 MARTIN           1250                                                 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT ROWNUM, Ename, Sal 
  2  FROM Emp 
  3* WHERE ROWNUM >= 5 
SQL> / 
 
no rows selected 
 
SQL> cl scr 
 
 

SQL> COLUMN SelectGraph FORMAT A16 
SQL> SELECT 
  2  Ename, 
  3  LPAD('*' , ROWNUM, '*') SelectGraph 
  4  FROM Emp; 
 
ENAME      SELECTGRAPH                                                           
---------- ----------------                                                      
KING       *                                                                     
BLAKE      **                                                                    
CLARK      ***                                                                   
JONES      ****                                                                  
MARTIN     *****                                                                 
ALLEN      ******                                                                
TURNER     *******                                                               
JAMES      ********                                                              
WARD       *********                                                             
FORD       **********                                                            
SMITH      ***********                                                           
 
ENAME      SELECTGRAPH                                                           
---------- ----------------                                                      
SCOTT      ************                                                          
ADAMS      *************                                                         
MILLER     **************                                                        
 
14 rows selected. 
 
SQL> SELECT ROWNUM, Ename 
  2  FROM Emp; 
 
    ROWNUM ENAME                                                                 
---------- ----------                                                            
         1 KING                                                                  
         2 BLAKE                                                                 
         3 CLARK                                                                 
         4 JONES                                                                 
         5 MARTIN                                                                
         6 ALLEN                                                                 
         7 TURNER                                                                
         8 JAMES                                                                 
         9 WARD                                                                  
        10 FORD                                                                  
        11 SMITH                                                                 
 
    ROWNUM ENAME                                                                 
---------- ----------                                                            
        12 SCOTT                                                                 
        13 ADAMS                                                                 
        14 MILLER                                                                
 
14 rows selected. 
 
SQL> SELECT ROWNUM, Ename, Sal 
  2  FROM Emp 
  3  ORDER BY Sal; 
 

 
    ROWNUM ENAME             SAL                                                 
---------- ---------- ----------                                                 
        11 SMITH             800                                                 
         8 JAMES             950                                                 
        13 ADAMS            1100                                                 
         5 MARTIN           1250                                                 
         9 WARD             1250                                                 
        14 MILLER           1300                                                 
         7 TURNER           1500                                                 
         6 ALLEN            1600                                                 
         3 CLARK            2450                                                 
         2 BLAKE            2850                                                 
         4 JONES            2975                                                 
 
    ROWNUM ENAME             SAL                                                 
---------- ---------- ----------                                                 
        10 FORD             3000                                                 
        12 SCOTT            3000                                                 
         1 KING             5000                                                 
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 
  2  Ename, Sal, 
  3  LPAD('*' , ROWNUM, '*') SelectGraph 
  4  FROM Emp 
  5* ORDER BY Sal 
SQL> / 
 
ENAME             SAL SELECTGRAPH                                                
---------- ---------- ----------------                                           
SMITH             800 ***********                                                
JAMES             950 ********                                                   
ADAMS            1100 *************                                              
MARTIN           1250 *****                                                      
WARD             1250 *********                                                  
MILLER           1300 **************                                             
TURNER           1500 *******                                                    
ALLEN            1600 ******                                                     
CLARK            2450 ***                                                        
BLAKE            2850 **                                                         
JONES            2975 ****                                                       
 
ENAME             SAL SELECTGRAPH                                                
---------- ---------- ----------------                                           
FORD             3000 **********                                                 
SCOTT            3000 ************                                               
KING             5000 *                                                          
 
14 rows selected. 
 

 
SQL> cl scr 
 
SQL> COLUMN SelectGraph FORMAT A16 
SQL> COLUMN RecGraph FORMAT A20 
SQL> COLUMN SalGraph FORMAT A20 
SQL> SELECT 
  2  Ename, 
  3  LPAD('*' , ROWNUM, '*' ) SelectGraph, 
  4  LPAD('*', Sal/500, '*') SalGraph, 
  5  LPAD('*', (SYSDATE-HireDate)/500, '*') RecGraph 
  6  FROM Emp; 
 
ENAME      SELECTGRAPH      SALGRAPH             RECGRAPH                        
---------- ---------------- -------------------- --------------------            
KING       *                **********           ********************            
                                                 *                               
                                                                                 
BLAKE      **               *****                ********************            
                                                 *                               
                                                                                 
CLARK      ***              ****                 ********************            
                                                 *                               
                                                                                 
JONES      ****             *****                ********************            
                                                 *                               
 
ENAME      SELECTGRAPH      SALGRAPH             RECGRAPH                        
---------- ---------------- -------------------- --------------------            
                                                                                 
MARTIN     *****            **                   ********************            
                                                 *                               
                                                                                 
ALLEN      ******           ***                  ********************            
                                                 *                               
                                                                                 
TURNER     *******          ***                  ********************            
                                                 *                               
                                                                                 
JAMES      ********         *                    ********************            
 
ENAME      SELECTGRAPH      SALGRAPH             RECGRAPH                        
---------- ---------------- -------------------- --------------------            
WARD       *********        **                   ********************            
                                                 *                               
                                                                                 
FORD       **********       ******               ********************            
SMITH      ***********      *                    ********************            
                                                 *                               
                                                                                 
SCOTT      ************     ******               ********************            
ADAMS      *************    **                   ********************            
MILLER     **************   **                   ********************            
 
14 rows selected. 
 
 

SQL> COLUMN SelectGraph FORMAT A15 
SQL> COLUMN SALGRAPH FORMAT A18 
SQL> COLUMN RecGraph FORMAT A35 
SQL> / 
 
ENAME      SELECTGRAPH     SALGRAPH                                              
---------- --------------- ------------------                                    
RECGRAPH                                                                         
-----------------------------------                                              
KING       *               **********                                            
*********************                                                            
                                                                                 
BLAKE      **              *****                                                 
*********************                                                            
                                                                                 
CLARK      ***             ****                                                  
*********************                                                            
                                                                                 
 
ENAME      SELECTGRAPH     SALGRAPH                                              
---------- --------------- ------------------                                    
RECGRAPH                                                                         
-----------------------------------                                              
JONES      ****            *****                                                 
*********************                                                            
                                                                                 
MARTIN     *****           **                                                    
*********************                                                            
                                                                                 
ALLEN      ******          ***                                                   
*********************                                                            
                                                                                 
 
ENAME      SELECTGRAPH     SALGRAPH                                              
---------- --------------- ------------------                                    
RECGRAPH                                                                         
-----------------------------------                                              
TURNER     *******         ***                                                   
*********************                                                            
                                                                                 
JAMES      ********        *                                                     
********************                                                             
                                                                                 
WARD       *********       **                                                    
*********************                                                            
                                                                                 
 
ENAME      SELECTGRAPH     SALGRAPH                                              
---------- --------------- ------------------                                    
RECGRAPH                                                                         
-----------------------------------                                              
FORD       **********      ******                                                
********************                                                             
                                                                                 
SMITH      ***********     *                                                     
*********************                                                            
 

                                                                                 
SCOTT      ************    ******                                                
********************                                                             
                                                                                 
 
ENAME      SELECTGRAPH     SALGRAPH                                              
---------- --------------- ------------------                                    
RECGRAPH                                                                         
-----------------------------------                                              
ADAMS      *************   **                                                    
********************                                                             
                                                                                 
MILLER     **************  **                                                    
********************                                                             
                                                                                 
 
14 rows selected. 
 
SQL> COLUMN RecGraph FORMAT A30 
SQL> / 
 
ENAME      SELECTGRAPH     SALGRAPH           RECGRAPH                           
---------- --------------- ------------------ ------------------------------     
KING       *               **********         *********************              
BLAKE      **              *****              *********************              
CLARK      ***             ****               *********************              
JONES      ****            *****              *********************              
MARTIN     *****           **                 *********************              
ALLEN      ******          ***                *********************              
TURNER     *******         ***                *********************              
JAMES      ********        *                  ********************               
WARD       *********       **                 *********************              
FORD       **********      ******             ********************               
SMITH      ***********     *                  *********************              
 
ENAME      SELECTGRAPH     SALGRAPH           RECGRAPH                           
---------- --------------- ------------------ ------------------------------     
SCOTT      ************    ******             ********************               
ADAMS      *************   **                 ********************               
MILLER     **************  **                 ********************               
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> R 
  1  SELECT 
  2  Ename, 
  3  LPAD('*' , ROWNUM, '*' ) SelectGraph, 
  4  LPAD('*', Sal/500, '*') SalGraph, 
  5  LPAD('*', (SYSDATE-HireDate)/500, '*') RecGraph 
  6* FROM Emp 
 
ENAME      SELECTGRAPH     SALGRAPH           RECGRAPH                           
---------- --------------- ------------------ ------------------------------     
KING       *               **********         *********************              
 

BLAKE      **              *****              *********************              
CLARK      ***             ****               *********************              
JONES      ****            *****              *********************              
MARTIN     *****           **                 *********************              
ALLEN      ******          ***                *********************              
TURNER     *******         ***                *********************              
JAMES      ********        *                  ********************               
WARD       *********       **                 *********************              
FORD       **********      ******             ********************               
SMITH      ***********     *                  *********************              
 
ENAME      SELECTGRAPH     SALGRAPH           RECGRAPH                           
---------- --------------- ------------------ ------------------------------     
SCOTT      ************    ******             ********************               
ADAMS      *************   **                 ********************               
MILLER     **************  **                 ********************               
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> COLUMN Org_Chart FORMAT A15 
SQL> SELECT 
  2  LPAD('*', ((2 * LEVEL) - 1))||Ename Org_Chart, 
  3  Empno, MGR, Job 
  4  FROM Emp 
  5  START WITH Job = 'PRESIDENT' 
  6  CONNECT BY PRIOR Empno = MGR; 
 
ORG_CHART            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                                    
 
ORG_CHART            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 
  2  LPAD('*', ((2 * LEVEL) - 1)) LevelVAL, Ename Org_Chart 
 

  3  FROM Emp 
  4  START WITH Job = 'PRESIDENT' 
  5* CONNECT BY PRIOR Empno = MGR 
SQL> / 
 
LEVELVAL                                                                         
-------------------------------------------------------------------------------- 
ORG_CHART                                                                        
---------------                                                                  
*                                                                                
KING                                                                             
                                                                                 
  *                                                                              
BLAKE                                                                            
                                                                                 
    *                                                                            
MARTIN                                                                           
                                                                                 
 
LEVELVAL                                                                         
-------------------------------------------------------------------------------- 
ORG_CHART                                                                        
---------------                                                                  
    *                                                                            
ALLEN                                                                            
                                                                                 
    *                                                                            
TURNER                                                                           
                                                                                 
    *                                                                            
JAMES                                                                            
                                                                                 
 
LEVELVAL                                                                         
-------------------------------------------------------------------------------- 
ORG_CHART                                                                        
---------------                                                                  
    *                                                                            
WARD                                                                             
                                                                                 
  *                                                                              
CLARK                                                                            
                                                                                 
    *                                                                            
MILLER                                                                           
                                                                                 
 
LEVELVAL                                                                         
-------------------------------------------------------------------------------- 
ORG_CHART                                                                        
---------------                                                                  
  *                                                                              
JONES                                                                            
                                                                                 
    *                                                                            
FORD                                                                             
 

                                                                                 
      *                                                                          
SMITH                                                                            
                                                                                 
 
LEVELVAL                                                                         
-------------------------------------------------------------------------------- 
ORG_CHART                                                                        
---------------                                                                  
    *                                                                            
SCOTT                                                                            
                                                                                 
      *                                                                          
ADAMS                                                                            
                                                                                 
 
14 rows selected. 
 
SQL> COLUMN LEVELVAL FORMAT A15 
SQL> / 
 
LEVELVAL        ORG_CHART                                                        
--------------- ---------------                                                  
*               KING                                                             
  *             BLAKE                                                            
    *           MARTIN                                                           
    *           ALLEN                                                            
    *           TURNER                                                           
    *           JAMES                                                            
    *           WARD                                                             
  *             CLARK                                                            
    *           MILLER                                                           
  *             JONES                                                            
    *           FORD                                                             
 
LEVELVAL        ORG_CHART                                                        
--------------- ---------------                                                  
      *         SMITH                                                            
    *           SCOTT                                                            
      *         ADAMS                                                            
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT ROWNUM "S.No", SUM(ROWNUM) Sum 
  2  FROM Emp 
  3  WHERE ROWNUM <= &GSeqVal; 
Enter value for gseqval: 5 
SELECT ROWNUM "S.No", SUM(ROWNUM) Sum 
       * 
ERROR at line 1: 
ORA-00937: not a single-group group function  
 
 
SQL> ED 
 

Wrote file afiedt.buf 
 
  1  SELECT ROWNUM "S.No", SUM(ROWNUM) Sum 
  2  FROM Emp 
  3  WHERE ROWNUM <= &GSeqVal 
  4* GROUP BY ROWNUM 
SQL> / 
Enter value for gseqval: 5 
 
      S.No        SUM                                                            
---------- ----------                                                            
         1          1                                                            
         2          2                                                            
         3          3                                                            
         4          4                                                            
         5          5                                                            
 
SQL> cl scr 
 
SQL> SELECT ROWNUM, Ename, Sal 
  2  FROM Emp 
  3  WHERE ROWNUM < 6 
  4  ORDER BY Sal DESC; 
 
    ROWNUM ENAME             SAL                                                 
---------- ---------- ----------                                                 
         1 KING             5000                                                 
         4 JONES            2975                                                 
         2 BLAKE            2850                                                 
         3 CLARK            2450                                                 
         5 MARTIN           1250                                                 
 
SQL> SELECT * 
  2  FROM (SELECT Ename, Sal, Deptno, Job 
  3    FROM Emp 
  4    ORDER BY Sal DESC) 
  5  WHERE ROWNUM < 6; 
 
ENAME             SAL     DEPTNO JOB                                             
---------- ---------- ---------- ---------                                       
KING             5000         10 PRESIDENT                                       
FORD             3000         20 ANALYST                                         
SCOTT            3000         20 ANALYST                                         
JONES            2975         20 MANAGER                                         
BLAKE            2850         30 MANAGER                                         
 
SQL> cl scr 
 
SQL> SELECT LEVEL, ROWNUM, Ename, Sal 
  2  FROM Emp 
  3  WHERE ROWNUM < 6 
  4  START WITH Ename = 'KING' 
  5  CONNECT BY PRIOR Empno = MGR 
  6  ORDER BY Sal DESC; 
 
     LEVEL     ROWNUM ENAME             SAL                                      
 

---------- ---------- ---------- ----------                                      
         1          1 KING             5000                                      
         2          2 BLAKE            2850                                      
         3          4 ALLEN            1600                                      
         3          5 TURNER           1500                                      
         3          3 MARTIN           1250                                      
 
SQL> cl scr 
 
SQL> SELECT ROWNUM, E1.* 
  2  FROM (SELECT ROWNUM, Ename, Sal, Deptno, Job 
  3    FROM Emp 
  4    ORDER BY Sal DESC) E1 
  5  WHERE ROWNUM < 6; 
 
    ROWNUM     ROWNUM ENAME             SAL     DEPTNO JOB                       
---------- ---------- ---------- ---------- ---------- ---------                 
         1          1 KING             5000         10 PRESIDENT                 
         2         10 FORD             3000         20 ANALYST                   
         3         12 SCOTT            3000         20 ANALYST                   
         4          4 JONES            2975         20 MANAGER                   
         5          2 BLAKE            2850         30 MANAGER                   
 
SQL> cl scr 
 
SQL> SELECT RN, Ename, Sal 
  2  FROM (SELECT ROWNUM RN, Ename, Sal 
  3        FROM Emp) 
  4  WHERE RN = &GiveRn; 
Enter value for givern: 1 
 
        RN ENAME             SAL                                                 
---------- ---------- ----------                                                 
         1 KING             5000                                                 
 
SQL> / 
Enter value for givern: 2 
 
        RN ENAME             SAL                                                 
---------- ---------- ----------                                                 
         2 BLAKE            2850                                                 
 
SQL> / 
Enter value for givern: 3 
 
        RN ENAME             SAL                                                 
---------- ---------- ----------                                                 
         3 CLARK            2450                                                 
 
SQL> cl scr 
 
SQL> SELECT ROWNUM, Ename, Sal, Deptno 
  2  FROM Emp 
  3  GROUP BY ROWNUM, Ename, Sal, Deptno 
  4  HAVING ROWNUM = &GRownum; 
Enter value for grownum: 1 
 

 
    ROWNUM ENAME             SAL     DEPTNO                                      
---------- ---------- ---------- ----------                                      
         1 KING             5000         10                                      
 
SQL> / 
Enter value for grownum: 2 
 
    ROWNUM ENAME             SAL     DEPTNO                                      
---------- ---------- ---------- ----------                                      
         2 BLAKE            2850         30                                      
 
SQL> / 
Enter value for grownum: 3 
 
    ROWNUM ENAME             SAL     DEPTNO                                      
---------- ---------- ---------- ----------                                      
         3 CLARK            2450         10                                      
 
SQL> cl scr 
 
SQL> SELECT RN, E2.Ename, E2.Sal 
  2  FROM (SELECT ROWNUM RN, E1.* 
  3     FROM (SELECT Ename, Sal, Deptno, Job 
  4             FROM Emp 
  5             ORDER BY Sal DESC) E1) E2 
  6  WHERE RN = &GiveSalNum; 
Enter value for givesalnum: 1 
 
        RN ENAME             SAL                                                 
---------- ---------- ----------                                                 
         1 KING             5000                                                 
 
SQL> / 
Enter value for givesalnum: 2 
 
        RN ENAME             SAL                                                 
---------- ---------- ----------                                                 
         2 FORD             3000                                                 
 
SQL> SELECT Ename, Sal, E.Deptno, Dname, Grade 
  2  FROM Emp E, Dept D, Salgrade S 
  3  WHERE Sal IN (SELECT E2.Sal 
  4    FROM (SELECT ROWNUM RN, E1.* 
  5        FROM (SELECT Ename, Sal, Deptno, Job 
  6                 FROM Emp 
  7                 ORDER BY Sal DESC) E1) E2 
  8    WHERE E2.RN = &Rn) 
  9  AND E.Deptno = D.Deptno AND 
 10  E.Sal BETWEEN S.Losal AND S.HiSal; 
Enter value for rn: 1 
 
ENAME             SAL     DEPTNO DNAME               GRADE                       
---------- ---------- ---------- -------------- ----------                       
KING             5000         10 ACCOUNTING              5                       
 
 

SQL> / 
Enter value for rn: 2 
 
ENAME             SAL     DEPTNO DNAME               GRADE                       
---------- ---------- ---------- -------------- ----------                       
SCOTT            3000         20 RESEARCH                4                       
FORD             3000         20 RESEARCH                4                       
 
SQL> cl scr 
 
SQL> SELECT RN, Ename, Sal 
  2  FROM (SELECT ROWNUM RN, Ename, Sal 
  3        FROM Emp) 
  4  WHERE MOD(RN, 2) = 0; 
 
        RN ENAME             SAL                                                 
---------- ---------- ----------                                                 
         2 BLAKE            2850                                                 
         4 JONES            2975                                                 
         6 ALLEN            1600                                                 
         8 JAMES             950                                                 
        10 FORD             3000                                                 
        12 SCOTT            3000                                                 
        14 MILLER           1300                                                 
 
7 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT RN, Ename, Sal 
  2  FROM (SELECT ROWNUM RN, Ename, Sal 
  3        FROM Emp) 
  4* WHERE MOD(RN, 2) = 1 
SQL> / 
 
        RN ENAME             SAL                                                 
---------- ---------- ----------                                                 
         1 KING             5000                                                 
         3 CLARK            2450                                                 
         5 MARTIN           1250                                                 
         7 TURNER           1500                                                 
         9 WARD             1250                                                 
        11 SMITH             800                                                 
        13 ADAMS            1100                                                 
 
7 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT RN, Ename, Sal 
  2  FROM (SELECT ROWNUM RN, Ename, Sal 
  3        FROM Emp) 
  4  WHERE RN BETWEEN &GineRn1 AND &GiveRn2; 
Enter value for ginern1: 1 
Enter value for givern2: 5 
 

 
        RN ENAME             SAL                                                 
---------- ---------- ----------                                                 
         1 KING             5000                                                 
         2 BLAKE            2850                                                 
         3 CLARK            2450                                                 
         4 JONES            2975                                                 
         5 MARTIN           1250                                                 
 
SQL> / 
Enter value for ginern1: 6 
Enter value for givern2: 10 
 
        RN ENAME             SAL                                                 
---------- ---------- ----------                                                 
         6 ALLEN            1600                                                 
         7 TURNER           1500                                                 
         8 JAMES             950                                                 
         9 WARD             1250                                                 
        10 FORD             3000                                                 
 
SQL> / 
Enter value for ginern1: 11 
Enter value for givern2: 16 
 
        RN ENAME             SAL                                                 
---------- ---------- ----------                                                 
        11 SMITH             800                                                 
        12 SCOTT            3000                                                 
        13 ADAMS            1100                                                 
        14 MILLER           1300                                                 
 
SQL> cl scr 
 
SQL> SELECT ROWNUM, Ename, Sal 
  2  FROM Emp 
  3  GROUP BY ROWNUM, Ename, Sal 
  4  HAVING ROWNUM BETWEEN &GiveRowNum1 AND &GiveRowNum2; 
Enter value for giverownum1: 1 
Enter value for giverownum2: 5 
 
    ROWNUM ENAME             SAL                                                 
---------- ---------- ----------                                                 
         1 KING             5000                                                 
         2 BLAKE            2850                                                 
         3 CLARK            2450                                                 
         4 JONES            2975                                                 
         5 MARTIN           1250                                                 
 
SQL> / 
Enter value for giverownum1: 6 
Enter value for giverownum2: 10 
 
    ROWNUM ENAME             SAL                                                 
---------- ---------- ----------                                                 
         6 ALLEN            1600                                                 
 

         7 TURNER           1500                                                 
         8 JAMES             950                                                 
         9 WARD             1250                                                 
        10 FORD             3000                                                 
 
SQL> / 
Enter value for giverownum1: 11 
Enter value for giverownum2: 15 
 
    ROWNUM ENAME             SAL                                                 
---------- ---------- ----------                                                 
        11 SMITH             800                                                 
        12 SCOTT            3000                                                 
        13 ADAMS            1100                                                 
        14 MILLER           1300                                                 
 
SQL> cl scr 
 
SQL> SELECT Ename, Sal, E.Deptno, Dname, Grade 
  2  FROM Emp E, Dept D, Salgrade S 
  3  WHERE Sal IN (SELECT E2.Sal 
  4    FROM (SELECT ROWNUM RN, E1.* 
  5        FROM (SELECT Ename, Sal, Deptno, Job 
  6                 FROM Emp 
  7                 ORDER BY Sal DESC) E1) E2 
  8    WHERE E2.RN BETWEEN &RN1 AND &RN2) 
  9  AND E.Deptno = D.Deptno AND 
 10  E.Sal BETWEEN S.Losal AND S.HiSal; 
Enter value for rn1: 1 
Enter value for rn2: 6 
 
ENAME             SAL     DEPTNO DNAME               GRADE                       
---------- ---------- ---------- -------------- ----------                       
KING             5000         10 ACCOUNTING              5                       
SCOTT            3000         20 RESEARCH                4                       
FORD             3000         20 RESEARCH                4                       
JONES            2975         20 RESEARCH                4                       
BLAKE            2850         30 SALES                   4                       
CLARK            2450         10 ACCOUNTING              4                       
 
6 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT ROWID, Ename, Sal, Deptno 
  2  FROM Emp; 
 
ROWID              ENAME             SAL     DEPTNO                              
------------------ ---------- ---------- ----------                              
AAANCuAAEAAAAG/AAA KING             5000         10                              
AAANCuAAEAAAAG/AAB BLAKE            2850         30                              
AAANCuAAEAAAAG/AAC CLARK            2450         10                              
AAANCuAAEAAAAG/AAD JONES            2975         20                              
AAANCuAAEAAAAG/AAE MARTIN           1250         30                              
AAANCuAAEAAAAG/AAF ALLEN            1600         30                              
AAANCuAAEAAAAG/AAG TURNER           1500         30                              
 

AAANCuAAEAAAAG/AAH JAMES             950         30                              
AAANCuAAEAAAAG/AAI WARD             1250         30                              
AAANCuAAEAAAAG/AAJ FORD             3000         20                              
AAANCuAAEAAAAG/AAK SMITH             800         20                              
 
ROWID              ENAME             SAL     DEPTNO                              
------------------ ---------- ---------- ----------                              
AAANCuAAEAAAAG/AAL SCOTT            3000         20                              
AAANCuAAEAAAAG/AAM ADAMS            1100         20                              
AAANCuAAEAAAAG/AAN MILLER           1300         10                              
 
14 rows selected. 
 
SQL> SELECT ROWID, Ename, Sal, Deptno 
  2  FROM Emp 
  3  WHERE ROWID = 
  4  'AAANCuAAEAAAAG/AAI'; 
 
ROWID              ENAME             SAL     DEPTNO                              
------------------ ---------- ---------- ----------                              
AAANCuAAEAAAAG/AAI WARD             1250         30                              
 
SQL> SET AUTOTRACE ON EXPLAIN 
SQL> SELECT ROWID, Ename, Sal, Deptno 
  2  FROM Emp 
  3  WHERE Ename = 'WARD'; 
 
ROWID              ENAME             SAL     DEPTNO                              
------------------ ---------- ---------- ----------                              
AAANCuAAEAAAAG/AAI WARD             1250         30                              
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=20)           
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 Bytes=           
          20)                                                                    
                                                                                 
 
 
 
SQL> SELECT ROWID, Ename, Sal, Deptno 
  2  FROM Emp 
  3  WHERE ROWID = 'AAANCuAAEAAAAG/AAI'; 
 
ROWID              ENAME             SAL     DEPTNO                              
------------------ ---------- ---------- ----------                              
AAANCuAAEAAAAG/AAI WARD             1250         30                              
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=20)           
   1    0   TABLE ACCESS (BY USER ROWID) OF 'EMP' (TABLE) (Cost=1 Card           
          =1 Bytes=20)                                                           
                                                                                 
 

 
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT ROWID, Ename, Sal, Deptno 
  2  FROM Emp 
  3* WHERE ROWID < 'AAANCuAAEAAAAG/AAI' 
SQL> / 
 
ROWID              ENAME             SAL     DEPTNO                              
------------------ ---------- ---------- ----------                              
AAANCuAAEAAAAG/AAA KING             5000         10                              
AAANCuAAEAAAAG/AAB BLAKE            2850         30                              
AAANCuAAEAAAAG/AAC CLARK            2450         10                              
AAANCuAAEAAAAG/AAD JONES            2975         20                              
AAANCuAAEAAAAG/AAE MARTIN           1250         30                              
AAANCuAAEAAAAG/AAF ALLEN            1600         30                              
AAANCuAAEAAAAG/AAG TURNER           1500         30                              
AAANCuAAEAAAAG/AAH JAMES             950         30                              
 
8 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=20)           
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 Bytes=           
          20)                                                                    
                                                                                 
 
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT ROWID, Ename, Sal, Deptno 
  2  FROM Emp 
  3* WHERE ROWID > 'AAANCuAAEAAAAG/AAI' 
SQL> / 
 
ROWID              ENAME             SAL     DEPTNO                              
------------------ ---------- ---------- ----------                              
AAANCuAAEAAAAG/AAJ FORD             3000         20                              
AAANCuAAEAAAAG/AAK SMITH             800         20                              
AAANCuAAEAAAAG/AAL SCOTT            3000         20                              
AAANCuAAEAAAAG/AAM ADAMS            1100         20                              
AAANCuAAEAAAAG/AAN MILLER           1300         10                              
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=20)           
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 Bytes=           
          20)                                                                    
 

                                                                                 
 
 
 
SQL> cl scr 
 
SQL> SELECT Ename, Sal, Job 
  2  FROM Emp 
  3  WHERE ROWID = 'AAAMoOAAEAAAAzXAAJ'; 
FROM Emp 
     * 
ERROR at line 2: 
ORA-01410: invalid ROWID  
 
 
SQL> cl scr 
 
SQL> SELECT B.Sal, SUM(A.Sal) "Cum Sal" 
  2  FROM Emp A, Emp B 
  3  WHERE A.ROWID < = B.ROWID 
  4  GROUP BY B.ROWID, B.Sal; 
 
       SAL    Cum Sal                                                            
---------- ----------                                                            
      5000       5000                                                            
      2850       7850                                                            
      2450      10300                                                            
      2975      13275                                                            
      1250      14525                                                            
      1600      16125                                                            
      1500      17625                                                            
       950      18575                                                            
      1250      19825                                                            
      3000      22825                                                            
       800      23625                                                            
 
       SAL    Cum Sal                                                            
---------- ----------                                                            
      3000      26625                                                            
      1100      27725                                                            
      1300      29025                                                            
 
14 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=10 Bytes=22           
          0)                                                                     
                                                                                 
   1    0   SORT (GROUP BY) (Cost=9 Card=10 Bytes=220)                           
   2    1     MERGE JOIN (Cost=8 Card=10 Bytes=220)                              
   3    2       SORT (JOIN) (Cost=4 Card=14 Bytes=154)                           
   4    3         TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14           
           Bytes=154)                                                            
                                                                                 
 

   5    2       SORT (JOIN) (Cost=4 Card=14 Bytes=154)                           
   6    5         TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14           
           Bytes=154)                                                            
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> SELECT B.Ename, B.Sal, Sum(A.Sal) "Cum Sal" 
  2  FROM Emp A, Emp B 
  3  WHERE A.ROWID < = B.ROWID 
  4  GROUP BY B.ROWID, B.Sal, B.Ename 
  5  ORDER BY "Cum Sal" 
  6  / 
 
ENAME             SAL    Cum Sal                                                 
---------- ---------- ----------                                                 
KING             5000       5000                                                 
BLAKE            2850       7850                                                 
CLARK            2450      10300                                                 
JONES            2975      13275                                                 
MARTIN           1250      14525                                                 
ALLEN            1600      16125                                                 
TURNER           1500      17625                                                 
JAMES             950      18575                                                 
WARD             1250      19825                                                 
FORD             3000      22825                                                 
SMITH             800      23625                                                 
 
ENAME             SAL    Cum Sal                                                 
---------- ---------- ----------                                                 
SCOTT            3000      26625                                                 
ADAMS            1100      27725                                                 
MILLER           1300      29025                                                 
 
14 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=10 Card=10 Bytes=2           
          80)                                                                    
                                                                                 
   1    0   SORT (ORDER BY) (Cost=10 Card=10 Bytes=280)                          
   2    1     SORT (GROUP BY) (Cost=10 Card=10 Bytes=280)                        
   3    2       MERGE JOIN (Cost=8 Card=10 Bytes=280)                            
   4    3         SORT (JOIN) (Cost=4 Card=14 Bytes=238)                         
   5    4           TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=           
          14 Bytes=238)                                                          
                                                                                 
   6    3         SORT (JOIN) (Cost=4 Card=14 Bytes=154)                         
   7    6           TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=           
          14 Bytes=154)                                                          
                                                                                 
 
 

 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT B.Ename, B.Sal,  
  2  Sum(A.Sal) "Cum Sal", 
  3  AVG(A.Sal) "Mov Avg" 
  4  FROM Emp A, Emp B 
  5  WHERE A.ROWID < = B.ROWID 
  6  GROUP BY B.ROWID, B.Sal, B.Ename 
  7* ORDER BY "Cum Sal" 
SQL> / 
 
ENAME             SAL    Cum Sal    Mov Avg                                      
---------- ---------- ---------- ----------                                      
KING             5000       5000       5000                                      
BLAKE            2850       7850       3925                                      
CLARK            2450      10300 3433.33333                                      
JONES            2975      13275    3318.75                                      
MARTIN           1250      14525       2905                                      
ALLEN            1600      16125     2687.5                                      
TURNER           1500      17625 2517.85714                                      
JAMES             950      18575   2321.875                                      
WARD             1250      19825 2202.77778                                      
FORD             3000      22825     2282.5                                      
SMITH             800      23625 2147.72727                                      
 
ENAME             SAL    Cum Sal    Mov Avg                                      
---------- ---------- ---------- ----------                                      
SCOTT            3000      26625    2218.75                                      
ADAMS            1100      27725 2132.69231                                      
MILLER           1300      29025 2073.21429                                      
 
14 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=10 Card=10 Bytes=2           
          80)                                                                    
                                                                                 
   1    0   SORT (ORDER BY) (Cost=10 Card=10 Bytes=280)                          
   2    1     SORT (GROUP BY) (Cost=10 Card=10 Bytes=280)                        
   3    2       MERGE JOIN (Cost=8 Card=10 Bytes=280)                            
   4    3         SORT (JOIN) (Cost=4 Card=14 Bytes=238)                         
   5    4           TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=           
          14 Bytes=238)                                                          
                                                                                 
   6    3         SORT (JOIN) (Cost=4 Card=14 Bytes=154)                         
   7    6           TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=           
          14 Bytes=154)                                                          
                                                                                 
 
 
 
 

SQL> SPOOL OFF 
SQL> cl scr 
 
SQL> SET VERIFY OFF 
SQL> cl scr 
 
SQL> SELECT Ename, Empno, MGR 
  2  FROM Emp 
  3  START WITH Empno = 7839 
  4  CONNECT BY PRIOR Empno = MGR; 
 
ENAME           EMPNO        MGR                                                 
---------- ---------- ----------                                                 
KING             7839                                                            
BLAKE            7698       7839                                                 
MARTIN           7654       7698                                                 
ALLEN            7499       7698                                                 
TURNER           7844       7698                                                 
JAMES            7900       7698                                                 
WARD             7521       7698                                                 
CLARK            7782       7839                                                 
MILLER           7934       7782                                                 
JONES            7566       7839                                                 
FORD             7902       7566                                                 
 
ENAME           EMPNO        MGR                                                 
---------- ---------- ----------                                                 
SMITH            7369       7902                                                 
SCOTT            7788       7566                                                 
ADAMS            7876       7788                                                 
 
14 rows selected. 
 
SQL> UPDATE Emp 
  2  SET MGR = 7566 
  3  WHERE Empno = 7839; 
 
1 row updated. 
 
SQL> SELECT Ename, Empno, MGR 
  2  FROM Emp; 
 
ENAME           EMPNO        MGR                                                 
---------- ---------- ----------                                                 
KING             7839       7566                                                 
BLAKE            7698       7839                                                 
CLARK            7782       7839                                                 
JONES            7566       7839                                                 
MARTIN           7654       7698                                                 
ALLEN            7499       7698                                                 
TURNER           7844       7698                                                 
JAMES            7900       7698                                                 
WARD             7521       7698                                                 
FORD             7902       7566                                                 
SMITH            7369       7902                                                 
 
 

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

  4  SYS_CONNECT_BY_PATH(Sal, '/') "SalPath" 
  5  FROM Emp 
  6  START WITH Ename = 'KING' 
  7  CONNECT BY NOCYCLE PRIOR Empno =  MGR; 
 
ENAME           Cycle      LEVEL SalPath                                         
---------- ---------- ---------- --------------------                            
KING                0          1 /5000                                           
BLAKE               0          2 /5000/2850                                      
MARTIN              0          3 /5000/2850/1250                                 
ALLEN               0          3 /5000/2850/1600                                 
TURNER              0          3 /5000/2850/1500                                 
JAMES               0          3 /5000/2850/950                                  
WARD                0          3 /5000/2850/1250                                 
CLARK               0          2 /5000/2450                                      
MILLER              0          3 /5000/2450/1300                                 
JONES               1          2 /5000/2975                                      
FORD                0          3 /5000/2975/3000                                 
 
ENAME           Cycle      LEVEL SalPath                                         
---------- ---------- ---------- --------------------                            
SMITH               0          4 /5000/2975/3000/800                             
SCOTT               0          3 /5000/2975/3000                                 
ADAMS               0          4 /5000/2975/3000/1100                            
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, 
  2  CONNECT_BY_ISCYCLE "Cycle", 
  3  LEVEL, 
  4  SYS_CONNECT_BY_PATH(Sal, '/') "SalPath" 
  5  FROM Emp 
  6  WHERE CONNECT_BY_ISCYCLE = &GCycle 
  7  START WITH Ename = 'KING' 
  8* CONNECT BY NOCYCLE PRIOR Empno =  MGR 
SQL> / 
Enter value for gcycle: 0 
 
ENAME           Cycle      LEVEL SalPath                                         
---------- ---------- ---------- --------------------                            
KING                0          1 /5000                                           
BLAKE               0          2 /5000/2850                                      
MARTIN              0          3 /5000/2850/1250                                 
ALLEN               0          3 /5000/2850/1600                                 
TURNER              0          3 /5000/2850/1500                                 
JAMES               0          3 /5000/2850/950                                  
WARD                0          3 /5000/2850/1250                                 
CLARK               0          2 /5000/2450                                      
MILLER              0          3 /5000/2450/1300                                 
FORD                0          3 /5000/2975/3000                                 
SMITH               0          4 /5000/2975/3000/800                             
 
ENAME           Cycle      LEVEL SalPath                                         
 

---------- ---------- ---------- --------------------                            
SCOTT               0          3 /5000/2975/3000                                 
ADAMS               0          4 /5000/2975/3000/1100                            
 
13 rows selected. 
 
SQL> / 
Enter value for gcycle: 1 
 
ENAME           Cycle      LEVEL SalPath                                         
---------- ---------- ---------- --------------------                            
JONES               1          2 /5000/2975                                      
 
SQL> ROLLBACK; 
 
Rollback complete. 
 
SQL> cl scr 
 
SQL> SELECT Ename, Empno, MGR 
  2  FROM Emp 
  3  START WITH Empno = 7839 
  4  CONNECT BY PRIOR Empno = MGR; 
 
ENAME           EMPNO        MGR                                                 
---------- ---------- ----------                                                 
KING             7839                                                            
BLAKE            7698       7839                                                 
MARTIN           7654       7698                                                 
ALLEN            7499       7698                                                 
TURNER           7844       7698                                                 
JAMES            7900       7698                                                 
WARD             7521       7698                                                 
CLARK            7782       7839                                                 
MILLER           7934       7782                                                 
JONES            7566       7839                                                 
FORD             7902       7566                                                 
 
ENAME           EMPNO        MGR                                                 
---------- ---------- ----------                                                 
SMITH            7369       7902                                                 
SCOTT            7788       7566                                                 
ADAMS            7876       7788                                                 
 
14 rows selected. 
 
SQL> SELECT Ename "Employee", CONNECT_BY_ISLEAF "IsLeaf", 
  2  LEVEL, SYS_CONNECT_BY_PATH(Ename, '/') "Path" 
  3  FROM Emp 
  4  START WITH Empno = 7839 
  5  CONNECT BY PRIOR Empno = MGR; 
 
Employee       IsLeaf      LEVEL                                                 
---------- ---------- ----------                                                 
Path                                                                             
-------------------------------------------------------------------------------- 
 

KING                0          1                                                 
/KING                                                                            
                                                                                 
BLAKE               0          2                                                 
/KING/BLAKE                                                                      
                                                                                 
MARTIN              1          3                                                 
/KING/BLAKE/MARTIN                                                               
                                                                                 
 
Employee       IsLeaf      LEVEL                                                 
---------- ---------- ----------                                                 
Path                                                                             
-------------------------------------------------------------------------------- 
ALLEN               1          3                                                 
/KING/BLAKE/ALLEN                                                                
                                                                                 
TURNER              1          3                                                 
/KING/BLAKE/TURNER                                                               
                                                                                 
JAMES               1          3                                                 
/KING/BLAKE/JAMES                                                                
                                                                                 
 
Employee       IsLeaf      LEVEL                                                 
---------- ---------- ----------                                                 
Path                                                                             
-------------------------------------------------------------------------------- 
WARD                1          3                                                 
/KING/BLAKE/WARD                                                                 
                                                                                 
CLARK               0          2                                                 
/KING/CLARK                                                                      
                                                                                 
MILLER              1          3                                                 
/KING/CLARK/MILLER                                                               
                                                                                 
 
Employee       IsLeaf      LEVEL                                                 
---------- ---------- ----------                                                 
Path                                                                             
-------------------------------------------------------------------------------- 
JONES               0          2                                                 
/KING/JONES                                                                      
                                                                                 
FORD                0          3                                                 
/KING/JONES/FORD                                                                 
                                                                                 
SMITH               1          4                                                 
/KING/JONES/FORD/SMITH                                                           
                                                                                 
 
Employee       IsLeaf      LEVEL                                                 
---------- ---------- ----------                                                 
Path                                                                             
-------------------------------------------------------------------------------- 
 

SCOTT               0          3                                                 
/KING/JONES/SCOTT                                                                
                                                                                 
ADAMS               1          4                                                 
/KING/JONES/SCOTT/ADAMS                                                          
                                                                                 
 
14 rows selected. 
 
SQL> COLUMN "Path" FORMAT A25 
SQL> / 
 
Employee       IsLeaf      LEVEL Path                                            
---------- ---------- ---------- -------------------------                       
KING                0          1 /KING                                           
BLAKE               0          2 /KING/BLAKE                                     
MARTIN              1          3 /KING/BLAKE/MARTIN                              
ALLEN               1          3 /KING/BLAKE/ALLEN                               
TURNER              1          3 /KING/BLAKE/TURNER                              
JAMES               1          3 /KING/BLAKE/JAMES                               
WARD                1          3 /KING/BLAKE/WARD                                
CLARK               0          2 /KING/CLARK                                     
MILLER              1          3 /KING/CLARK/MILLER                              
JONES               0          2 /KING/JONES                                     
FORD                0          3 /KING/JONES/FORD                                
 
Employee       IsLeaf      LEVEL Path                                            
---------- ---------- ---------- -------------------------                       
SMITH               1          4 /KING/JONES/FORD/SMITH                          
SCOTT               0          3 /KING/JONES/SCOTT                               
ADAMS               1          4 /KING/JONES/SCOTT/ADAMS                         
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename "Employee", CONNECT_BY_ISLEAF "IsLeaf", 
  2  LEVEL, SYS_CONNECT_BY_PATH(Ename, '/') "Path" 
  3  FROM Emp 
  4  WHERE CONNECT_BY_ISLEAF = &GLeaf 
  5  START WITH Empno = 7839 
  6* CONNECT BY PRIOR Empno = MGR 
SQL> / 
Enter value for gleaf: 1 
 
Employee       IsLeaf      LEVEL Path                                            
---------- ---------- ---------- -------------------------                       
MARTIN              1          3 /KING/BLAKE/MARTIN                              
ALLEN               1          3 /KING/BLAKE/ALLEN                               
TURNER              1          3 /KING/BLAKE/TURNER                              
JAMES               1          3 /KING/BLAKE/JAMES                               
WARD                1          3 /KING/BLAKE/WARD                                
MILLER              1          3 /KING/CLARK/MILLER                              
SMITH               1          4 /KING/JONES/FORD/SMITH                          
ADAMS               1          4 /KING/JONES/SCOTT/ADAMS                         
 

8 rows selected. 
 
SQL> / 
Enter value for gleaf: 0 
 
Employee       IsLeaf      LEVEL Path                                            
---------- ---------- ---------- -------------------------                       
KING                0          1 /KING                                           
BLAKE               0          2 /KING/BLAKE                                     
CLARK               0          2 /KING/CLARK                                     
JONES               0          2 /KING/JONES                                     
FORD                0          3 /KING/JONES/FORD                                
SCOTT               0          3 /KING/JONES/SCOTT                               
 
6 rows selected. 

Go Back