PL/SQL -> SQL -> SQL Queries

SQL Queries for Practice

Go Back

Update And Delete

On-line Analytical Processing (OLAP):

SQL> SELECT Deptno, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY Deptno; 
 
DEPTNO   SUM(SAL)                                                                
------ ----------                                                                
    10       8750                                                                
    20      10875                                                                
    30       9400                                                                
 
SQL> SELECT SUM(Sal) 
  2  FROM Emp; 
 
  SUM(SAL)                                                                       
----------                                                                       
     29025                                                                       
 
SQL> cl scr 
 
SQL> SET AUTOTRACE ON EXPLAIN 
SQL> SELECT Deptno, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY Deptno; 
 
DEPTNO   SUM(SAL)                                                                
------ ----------                                                                
    10       8750                                                                
    20      10875                                                                
    30       9400                                                                
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=36           
          4)                                                                     
                                                                                 
   1    0   SORT (GROUP BY) (Cost=4 Card=14 Bytes=364)                           
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
          es=364)                                                                
                                                                                 
 
 
 
SQL> SELECT SUM(Sal) FROM Emp; 
 
  SUM(SAL)                                                                       
----------                                                                       
     29025                                                                       
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=13)           
   1    0   SORT (AGGREGATE)                                                     
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
          es=182)                                                                
                                                                                 
 
 
 
 
SQL> SELECT Deptno, SUM(Sal), (SELECT SUM(Sal) FROM Emp) SalSum 
  2  FROM Emp 
  3  GROUP BY Deptno; 
 
DEPTNO   SUM(SAL)     SALSUM                                                     
------ ---------- ----------                                                     
    10       8750      29025                                                     
    20      10875      29025                                                     
    30       9400      29025                                                     
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=36           
          4)                                                                     
                                                                                 
   1    0   SORT (AGGREGATE)                                                     
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
          es=182)                                                                
                                                                                 
   3    0   SORT (GROUP BY) (Cost=4 Card=14 Bytes=364)                           
   4    3     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
          es=364)                                                                
                                                                                 
 
 
 
SQL> SELECT Deptno, SUM(Sal), SalSum 
  2  FROM Emp, (SELECT SUM(Sal) SalSum FROM Emp) 
  3  GROUP BY Deptno, SalSum; 
 
DEPTNO   SUM(SAL)     SALSUM                                                     
------ ---------- ----------                                                     
    10       8750      29025                                                     
    20      10875      29025                                                     
    30       9400      29025                                                     
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=14 Bytes=54           
          6)                                                                     
                                                                                 
   1    0   SORT (GROUP BY) (Cost=7 Card=14 Bytes=546)                           
   2    1     MERGE JOIN (CARTESIAN) (Cost=6 Card=14 Bytes=546)                  
   3    2       VIEW (Cost=3 Card=1 Bytes=13)                                    
   4    3         SORT (AGGREGATE)                                               
   5    4           TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=           
          14 Bytes=182)                                                          
                                                                                 
   6    2       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 B           
          ytes=364)                                                              
                                                                                 
 
 
 
 
SQL> SELECT Deptno, SUM(Sal) SalSum 
  2  FROM Emp 
  3  GROUP BY Deptno 
  4  UNION 
  5  SELECT NULL, SUM(Sal) 
  6  FROM Emp; 
 
DEPTNO     SALSUM                                                                
------ ----------                                                                
    10       8750                                                                
    20      10875                                                                
    30       9400                                                                
            29025                                                                
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=15 Bytes=37           
          7)                                                                     
                                                                                 
   1    0   SORT (UNIQUE) (Cost=9 Card=15 Bytes=377)                             
   2    1     UNION-ALL                                                          
   3    2       SORT (GROUP BY) (Cost=5 Card=14 Bytes=364)                       
   4    3         TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14           
           Bytes=364)                                                            
                                                                                 
   5    2       SORT (AGGREGATE) (Cost=4 Card=1 Bytes=13)                        
   6    5         TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14           
           Bytes=182)                                                            
                                                                                 
 
 
 
SQL> SELECT Deptno, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY ROLLUP(Deptno); 
 
DEPTNO   SUM(SAL)                                                                
------ ----------                                                                
    10       8750                                                                
    20      10875                                                                
    30       9400                                                                
            29025                                                                
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=36           
          4)                                                                     
                                                                                 
   1    0   SORT (GROUP BY ROLLUP) (Cost=4 Card=14 Bytes=364)                    
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
          es=364)                                                                
                                                                                 
 
 
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Deptno, SUM(Sal) 
  2  FROM Emp 
  3* GROUP BY ROLLUP(Deptno) 
SQL> SPOOL OFF 
SQL> cl scr 
 
SQL> SELECT Job, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY ROLLUP(Job); 
 
JOB         SUM(SAL)                                                             
--------- ----------                                                             
ANALYST         6000                                                             
CLERK           4150                                                             
MANAGER         8275                                                             
PRESIDENT       5000                                                             
SALESMAN        5600                                                             
               29025                                                             
 
6 rows selected. 
 
SQL> SELECT Ename, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY Ename; 
 
ENAME        SUM(SAL)                                                            
---------- ----------                                                            
ADAMS            1100                                                            
ALLEN            1600                                                            
BLAKE            2850                                                            
CLARK            2450                                                            
FORD             3000                                                            
JAMES             950                                                            
JONES            2975                                                            
KING             5000                                                            
MARTIN           1250                                                            
MILLER           1300                                                            
SCOTT            3000                                                            
 
ENAME        SUM(SAL)                                                            
---------- ----------                                                            
SMITH             800                                                            
TURNER           1500                                                            
WARD             1250                                                            
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, SUM(Sal) 
  2  FROM Emp 
 
  3* GROUP BY ROLLUP(Ename) 
SQL> / 
 
ENAME        SUM(SAL)                                                            
---------- ----------                                                            
ADAMS            1100                                                            
ALLEN            1600                                                            
BLAKE            2850                                                            
CLARK            2450                                                            
FORD             3000                                                            
JAMES             950                                                            
JONES            2975                                                            
KING             5000                                                            
MARTIN           1250                                                            
MILLER           1300                                                            
SCOTT            3000                                                            
 
ENAME        SUM(SAL)                                                            
---------- ----------                                                            
SMITH             800                                                            
TURNER           1500                                                            
WARD             1250                                                            
                29025                                                            
 
15 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT Deptno, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY ROLLUP(Deptno); 
 
    DEPTNO   SUM(SAL)                                                            
---------- ----------                                                            
        10       8750                                                            
        20      10875                                                            
        30       9400                                                            
                29025                                                            
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Job, SUM(Sal) 
  2  FROM Emp 
  3* GROUP BY ROLLUP(JOb) 
SQL> / 
 
JOB         SUM(SAL)                                                             
--------- ----------                                                             
ANALYST         6000                                                             
CLERK           4150                                                             
MANAGER         8275                                                             
PRESIDENT       5000                                                             
SALESMAN        5600                                                             
               29025                                                             
 
6 rows selected. 
 
 
SQL> cl scr 
 
SQL> COLUMN Deptno FORMAT A15 
SQL> SELECT 
  2  NVL(TO_CHAR(Deptno), 'All Departments') Deptno, 
  3  AVG(SAL) AVGSal 
  4  FROM Emp 
  5  GROUP BY ROLLUP(Deptno); 
 
DEPTNO              AVGSAL                                                       
--------------- ----------                                                       
10              2916.66667                                                       
20                    2175                                                       
30              1566.66667                                                       
All Departments 2073.21429                                                       
 
SQL> cl scr 
 
SQL> SELECT Deptno, Job, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY Deptno, Job; 
 
    DEPTNO JOB         SUM(SAL)                                                  
---------- --------- ----------                                                  
########## CLERK           1300                                                  
########## MANAGER         2450                                                  
########## PRESIDENT       5000                                                  
########## CLERK           1900                                                  
########## ANALYST         6000                                                  
########## MANAGER         2975                                                  
########## CLERK            950                                                  
########## MANAGER         2850                                                  
########## SALESMAN        5600                                                  
 
9 rows selected. 
 
SQL> COLUMN Deptno  FORMAT 999 
SQL> / 
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
    10 CLERK           1300                                                      
    10 MANAGER         2450                                                      
    10 PRESIDENT       5000                                                      
    20 CLERK           1900                                                      
    20 ANALYST         6000                                                      
    20 MANAGER         2975                                                      
    30 CLERK            950                                                      
    30 MANAGER         2850                                                      
    30 SALESMAN        5600                                                      
 
9 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
 
  1  SELECT Deptno, Job, SUM(Sal) 
  2  FROM Emp 
  3* GROUP BY ROLLUP(Deptno, Job) 
SQL> / 
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
    10 CLERK           1300                                                      
    10 MANAGER         2450                                                      
    10 PRESIDENT       5000                                                      
    10                 8750                                                      
    20 CLERK           1900                                                      
    20 ANALYST         6000                                                      
    20 MANAGER         2975                                                      
    20                10875                                                      
    30 CLERK            950                                                      
    30 MANAGER         2850                                                      
    30 SALESMAN        5600                                                      
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
    30                 9400                                                      
                      29025                                                      
 
13 rows selected. 
 
SQL> SELECT Deptno, Job, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY Deptno, Job; 
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
    10 CLERK           1300                                                      
    10 MANAGER         2450                                                      
    10 PRESIDENT       5000                                                      
    20 CLERK           1900                                                      
    20 ANALYST         6000                                                      
    20 MANAGER         2975                                                      
    30 CLERK            950                                                      
    30 MANAGER         2850                                                      
    30 SALESMAN        5600                                                      
 
9 rows selected. 
 
SQL> SELECT Deptno, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY Deptno; 
 
DEPTNO   SUM(SAL)                                                                
------ ----------                                                                
    10       8750                                                                
    20      10875                                                                
    30       9400                                                                
 
SQL> SELECT SUM(Sal) 
  2  FROM Emp; 
 
 
  SUM(SAL)                                                                       
----------                                                                       
     29025                                                                       
 
SQL> SET AUTOTRACE ON EXPLAIN 
SQL> SELECT Deptno, Job, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY Deptno, Job; 
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
    10 CLERK           1300                                                      
    10 MANAGER         2450                                                      
    10 PRESIDENT       5000                                                      
    20 CLERK           1900                                                      
    20 ANALYST         6000                                                      
    20 MANAGER         2975                                                      
    30 CLERK            950                                                      
    30 MANAGER         2850                                                      
    30 SALESMAN        5600                                                      
 
9 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=44           
          8)                                                                     
                                                                                 
   1    0   SORT (GROUP BY) (Cost=4 Card=14 Bytes=448)                           
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
          es=448)                                                                
                                                                                 
 
 
 
SQL> SELECT Deptno, Job, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY ROLLUP(Deptno, Job); 
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
    10 CLERK           1300                                                      
    10 MANAGER         2450                                                      
    10 PRESIDENT       5000                                                      
    10                 8750                                                      
    20 CLERK           1900                                                      
    20 ANALYST         6000                                                      
    20 MANAGER         2975                                                      
    20                10875                                                      
    30 CLERK            950                                                      
    30 MANAGER         2850                                                      
    30 SALESMAN        5600                                                      
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
    30                 9400                                                      
 
                      29025                                                      
 
13 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=44           
          8)                                                                     
                                                                                 
   1    0   SORT (GROUP BY ROLLUP) (Cost=4 Card=14 Bytes=448)                    
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
          es=448)                                                                
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> COLUMN Deptno FORMAT A15 
SQL> SELECT 
  2  NVL(TO_CHAR(Deptno), 'All Departments') Deptno, 
  3  NVL(Job, 'All Jobs') Jobs, 
  4  SUM(Sal) Salary 
  5  FROM Emp 
  6  GROUP BY ROLLUP(Deptno, Job); 
 
DEPTNO          JOBS          SALARY                                             
--------------- --------- ----------                                             
10              CLERK           1300                                             
10              MANAGER         2450                                             
10              PRESIDENT       5000                                             
10              All Jobs        8750                                             
20              CLERK           1900                                             
20              ANALYST         6000                                             
20              MANAGER         2975                                             
20              All Jobs       10875                                             
30              CLERK            950                                             
30              MANAGER         2850                                             
30              SALESMAN        5600                                             
 
DEPTNO          JOBS          SALARY                                             
--------------- --------- ----------                                             
30              All Jobs        9400                                             
All Departments All Jobs       29025                                             
 
13 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=44           
          8)                                                                     
                                                                                 
   1    0   SORT (GROUP BY ROLLUP) (Cost=4 Card=14 Bytes=448)                    
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
          es=448)                                                                
 
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> SELECT 
  2   TO_CHAR(HireDate, 'YYYY') "Year", 
  3   SUM(Sal) SumSal 
  4  FROM Emp 
  5  GROUP BY ROLLUP(TO_CHAR(HireDate, 'YYYY')); 
 
Year     SUMSAL                                                                  
---- ----------                                                                  
1980        800                                                                  
1981      22825                                                                  
1982       4300                                                                  
1983       1100                                                                  
          29025                                                                  
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=30           
          8)                                                                     
                                                                                 
   1    0   SORT (GROUP BY ROLLUP) (Cost=4 Card=14 Bytes=308)                    
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
          es=308)                                                                
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> SET AUTOTRACE OFF EXPLIAN 
SP2-0158: unknown SET option "EXPLIAN" 
SQL> SET AUTOTRACE OFF EXPLAIN 
SQL> cl scr 
 
SQL> SELECT Deptno, Job, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY ROLLUP(Deptno, Job); 
 
    DEPTNO JOB         SUM(SAL)                                                  
---------- --------- ----------                                                  
########## CLERK           1300                                                  
########## MANAGER         2450                                                  
########## PRESIDENT       5000                                                  
##########                 8750                                                  
########## CLERK           1900                                                  
########## ANALYST         6000                                                  
########## MANAGER         2975                                                  
##########                10875                                                  
########## CLERK            950                                                  
########## MANAGER         2850                                                  
########## SALESMAN        5600                                                  
 
 
    DEPTNO JOB         SUM(SAL)                                                  
---------- --------- ----------                                                  
##########                 9400                                                  
                          29025                                                  
 
13 rows selected. 
 
SQL> COLUMN DEPTNO FORMAT 99 
SQL> / 
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
    10 CLERK           1300                                                      
    10 MANAGER         2450                                                      
    10 PRESIDENT       5000                                                      
    10                 8750                                                      
    20 CLERK           1900                                                      
    20 ANALYST         6000                                                      
    20 MANAGER         2975                                                      
    20                10875                                                      
    30 CLERK            950                                                      
    30 MANAGER         2850                                                      
    30 SALESMAN        5600                                                      
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
    30                 9400                                                      
                      29025                                                      
 
13 rows selected. 
 
SQL> SELECT Deptno, Job, 
  2  SUM(Sal) Salary 
  3  FROM Emp 
  4  GROUP BY CUBE(Deptno, Job) 
  5  ORDER BY Deptno; 
 
DEPTNO JOB           SALARY                                                      
------ --------- ----------                                                      
    10 CLERK           1300                                                      
    10 MANAGER         2450                                                      
    10 PRESIDENT       5000                                                      
    10                 8750                                                      
    20 ANALYST         6000                                                      
    20 CLERK           1900                                                      
    20 MANAGER         2975                                                      
    20                10875                                                      
    30 CLERK            950                                                      
    30 MANAGER         2850                                                      
    30 SALESMAN        5600                                                      
 
DEPTNO JOB           SALARY                                                      
------ --------- ----------                                                      
    30                 9400                                                      
       ANALYST         6000                                                      
       CLERK           4150                                                      
 
       MANAGER         8275                                                      
       PRESIDENT       5000                                                      
       SALESMAN        5600                                                      
                      29025                                                      
 
18 rows selected. 
 
SQL> SELECT Deptno, Job, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY Deptno, Job; 
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
    10 CLERK           1300                                                      
    10 MANAGER         2450                                                      
    10 PRESIDENT       5000                                                      
    20 CLERK           1900                                                      
    20 ANALYST         6000                                                      
    20 MANAGER         2975                                                      
    30 CLERK            950                                                      
    30 MANAGER         2850                                                      
    30 SALESMAN        5600                                                      
 
9 rows selected. 
 
SQL> SELECT Deptno, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY Deptno; 
 
DEPTNO   SUM(SAL)                                                                
------ ----------                                                                
    10       8750                                                                
    20      10875                                                                
    30       9400                                                                
 
SQL> SELECT Job,, SUM(Sal) 
  2   
SQL> SELECT Job, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY Job; 
 
JOB         SUM(SAL)                                                             
--------- ----------                                                             
ANALYST         6000                                                             
CLERK           4150                                                             
MANAGER         8275                                                             
PRESIDENT       5000                                                             
SALESMAN        5600                                                             
 
SQL> SELECT SUM(Sal) 
  2  FROm Emp; 
 
  SUM(SAL)                                                                       
----------                                                                       
     29025                                                                       
 
SQL> cl scr 
 
 
SQL> SELECT Deptno, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY ROLLUP(Deptno); 
 
DEPTNO   SUM(SAL)                                                                
------ ----------                                                                
    10       8750                                                                
    20      10875                                                                
    30       9400                                                                
            29025                                                                
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Deptno, GROUPING(Deptno) GrpBIT, SUM(Sal) 
  2  FROM Emp 
  3* GROUP BY ROLLUP(Deptno) 
SQL> / 
 
DEPTNO     GRPBIT   SUM(SAL)                                                     
------ ---------- ----------                                                     
    10          0       8750                                                     
    20          0      10875                                                     
    30          0       9400                                                     
                1      29025                                                     
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Deptno, GROUPING(Deptno) GrpBIT, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY ROLLUP(Deptno) 
  4* HAVING GROUPING(Deptno) IN(&GrpBit1, &GrpBit2) 
SQL> / 
Enter value for grpbit1: 0 
Enter value for grpbit2: 0 
old   4: HAVING GROUPING(Deptno) IN(&GrpBit1, &GrpBit2) 
new   4: HAVING GROUPING(Deptno) IN(0, 0) 
 
DEPTNO     GRPBIT   SUM(SAL)                                                     
------ ---------- ----------                                                     
    10          0       8750                                                     
    20          0      10875                                                     
    30          0       9400                                                     
 
SQL> / 
Enter value for grpbit1: 0 
Enter value for grpbit2: 1 
old   4: HAVING GROUPING(Deptno) IN(&GrpBit1, &GrpBit2) 
new   4: HAVING GROUPING(Deptno) IN(0, 1) 
 
DEPTNO     GRPBIT   SUM(SAL)                                                     
------ ---------- ----------                                                     
    10          0       8750                                                     
    20          0      10875                                                     
    30          0       9400                                                     
 
                1      29025                                                     
 
SQL> / 
Enter value for grpbit1: 1 
Enter value for grpbit2: 1 
old   4: HAVING GROUPING(Deptno) IN(&GrpBit1, &GrpBit2) 
new   4: HAVING GROUPING(Deptno) IN(1, 1) 
 
DEPTNO     GRPBIT   SUM(SAL)                                                     
------ ---------- ----------                                                     
                1      29025                                                     
 
SQL> cl scr 
 
SQL> SELECT Deptno, Job, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY Deptno, Job; 
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
    10 CLERK           1300                                                      
    10 MANAGER         2450                                                      
    10 PRESIDENT       5000                                                      
    20 CLERK           1900                                                      
    20 ANALYST         6000                                                      
    20 MANAGER         2975                                                      
    30 CLERK            950                                                      
    30 MANAGER         2850                                                      
    30 SALESMAN        5600                                                      
 
9 rows selected. 
 
SQL> SELECT Deptno, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY Deptno; 
 
DEPTNO   SUM(SAL)                                                                
------ ----------                                                                
    10       8750                                                                
    20      10875                                                                
    30       9400                                                                
 
SQL> SELECT Job, SUm(Sal) 
  2  FROM Emp 
  3  GROUP BY Job; 
 
JOB         SUM(SAL)                                                             
--------- ----------                                                             
ANALYST         6000                                                             
CLERK           4150                                                             
MANAGER         8275                                                             
PRESIDENT       5000                                                             
SALESMAN        5600                                                             
 
SQL> SELECT SUM(Sal) 
  2  FROM Emp; 
 
 
  SUM(SAL)                                                                       
----------                                                                       
     29025                                                                       
 
SQL> SELECT Deptno, Job, SUM(Sal) 
  2  FROM EMp 
  3  GROUP BY CUBE(Deptno, Job) 
  4  ORDER BY Deptno; 
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
    10 CLERK           1300                                                      
    10 MANAGER         2450                                                      
    10 PRESIDENT       5000                                                      
    10                 8750                                                      
    20 ANALYST         6000                                                      
    20 CLERK           1900                                                      
    20 MANAGER         2975                                                      
    20                10875                                                      
    30 CLERK            950                                                      
    30 MANAGER         2850                                                      
    30 SALESMAN        5600                                                      
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
    30                 9400                                                      
       ANALYST         6000                                                      
       CLERK           4150                                                      
       MANAGER         8275                                                      
       PRESIDENT       5000                                                      
       SALESMAN        5600                                                      
                      29025                                                      
 
18 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Deptno, Job, SUM(Sal) 
  2  FROM EMp 
  3  GROUP BY CUBE(Deptno, Job) 
  4  HAVING GROUPING(&GColumn) IN(&GrpBIT1, &GrpBit2) 
  5* ORDER BY Deptno 
SQL> / 
Enter value for gcolumn: Deptno 
Enter value for grpbit1: 0 
Enter value for grpbit2: 0 
old   4: HAVING GROUPING(&GColumn) IN(&GrpBIT1, &GrpBit2) 
new   4: HAVING GROUPING(Deptno) IN(0, 0) 
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
    10 CLERK           1300                                                      
    10 MANAGER         2450                                                      
    10 PRESIDENT       5000                                                      
    10                 8750                                                      
    20 ANALYST         6000                                                      
 
    20 CLERK           1900                                                      
    20 MANAGER         2975                                                      
    20                10875                                                      
    30 CLERK            950                                                      
    30 MANAGER         2850                                                      
    30 SALESMAN        5600                                                      
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
    30                 9400                                                      
 
12 rows selected. 
 
SQL> / 
Enter value for gcolumn: Deptno 
Enter value for grpbit1: 1 
Enter value for grpbit2: 1 
old   4: HAVING GROUPING(&GColumn) IN(&GrpBIT1, &GrpBit2) 
new   4: HAVING GROUPING(Deptno) IN(1, 1) 
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
       ANALYST         6000                                                      
       CLERK           4150                                                      
       MANAGER         8275                                                      
       PRESIDENT       5000                                                      
       SALESMAN        5600                                                      
                      29025                                                      
 
6 rows selected. 
 
SQL> / 
Enter value for gcolumn: Deptno 
Enter value for grpbit1: 0 
Enter value for grpbit2: 1 
old   4: HAVING GROUPING(&GColumn) IN(&GrpBIT1, &GrpBit2) 
new   4: HAVING GROUPING(Deptno) IN(0, 1) 
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
    10 CLERK           1300                                                      
    10 MANAGER         2450                                                      
    10 PRESIDENT       5000                                                      
    10                 8750                                                      
    20 ANALYST         6000                                                      
    20 CLERK           1900                                                      
    20 MANAGER         2975                                                      
    20                10875                                                      
    30 CLERK            950                                                      
    30 MANAGER         2850                                                      
    30 SALESMAN        5600                                                      
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
    30                 9400                                                      
       ANALYST         6000                                                      
       CLERK           4150                                                      
 
       MANAGER         8275                                                      
       PRESIDENT       5000                                                      
       SALESMAN        5600                                                      
                      29025                                                      
 
18 rows selected. 
 
SQL> / 
Enter value for gcolumn: Job 
Enter value for grpbit1: 0 
Enter value for grpbit2: 0 
old   4: HAVING GROUPING(&GColumn) IN(&GrpBIT1, &GrpBit2) 
new   4: HAVING GROUPING(Job) IN(0, 0) 
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
    10 CLERK           1300                                                      
    10 MANAGER         2450                                                      
    10 PRESIDENT       5000                                                      
    20 CLERK           1900                                                      
    20 ANALYST         6000                                                      
    20 MANAGER         2975                                                      
    30 CLERK            950                                                      
    30 SALESMAN        5600                                                      
    30 MANAGER         2850                                                      
       CLERK           4150                                                      
       SALESMAN        5600                                                      
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
       PRESIDENT       5000                                                      
       MANAGER         8275                                                      
       ANALYST         6000                                                      
 
14 rows selected. 
 
SQL> / 
Enter value for gcolumn: Job 
Enter value for grpbit1: 1 
Enter value for grpbit2: 1 
old   4: HAVING GROUPING(&GColumn) IN(&GrpBIT1, &GrpBit2) 
new   4: HAVING GROUPING(Job) IN(1, 1) 
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
    10                 8750                                                      
    20                10875                                                      
    30                 9400                                                      
                      29025                                                      
 
SQL> / 
Enter value for gcolumn: Job 
Enter value for grpbit1: 1 
Enter value for grpbit2: 0 
old   4: HAVING GROUPING(&GColumn) IN(&GrpBIT1, &GrpBit2) 
new   4: HAVING GROUPING(Job) IN(1, 0) 
 
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
    10 CLERK           1300                                                      
    10 MANAGER         2450                                                      
    10 PRESIDENT       5000                                                      
    10                 8750                                                      
    20 ANALYST         6000                                                      
    20 CLERK           1900                                                      
    20 MANAGER         2975                                                      
    20                10875                                                      
    30 CLERK            950                                                      
    30 MANAGER         2850                                                      
    30 SALESMAN        5600                                                      
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
    30                 9400                                                      
       ANALYST         6000                                                      
       CLERK           4150                                                      
       MANAGER         8275                                                      
       PRESIDENT       5000                                                      
       SALESMAN        5600                                                      
                      29025                                                      
 
18 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT Ename, Job, Sal, Deptno, 
  2  DECODE(Deptno, 
  3    10, 'ACCOUNTING', 
  4    20, 'RESEARCH', 
  5    30 , 'SALES', 
  6    40 , 'OPERATIONS', 
  7    'OTHER')  Departments 
  8  FROM Emp 
  9  ORDER BY Departments; 
 
ENAME      JOB              SAL DEPTNO DEPARTMENT                                
---------- --------- ---------- ------ ----------                                
KING       PRESIDENT       5000     10 ACCOUNTING                                
CLARK      MANAGER         2450     10 ACCOUNTING                                
MILLER     CLERK           1300     10 ACCOUNTING                                
JONES      MANAGER         2975     20 RESEARCH                                  
SCOTT      ANALYST         3000     20 RESEARCH                                  
ADAMS      CLERK           1100     20 RESEARCH                                  
SMITH      CLERK            800     20 RESEARCH                                  
FORD       ANALYST         3000     20 RESEARCH                                  
BLAKE      MANAGER         2850     30 SALES                                     
MARTIN     SALESMAN        1250     30 SALES                                     
ALLEN      SALESMAN        1600     30 SALES                                     
 
ENAME      JOB              SAL DEPTNO DEPARTMENT                                
---------- --------- ---------- ------ ----------                                
TURNER     SALESMAN        1500     30 SALES                                     
JAMES      CLERK            950     30 SALES                                     
WARD       SALESMAN        1250     30 SALES                                     
 
 
14 rows selected. 
 
SQL> SELECT INITCAP(Ename)||' Takes Care of '|| 
  2  DECODE(Job, 
  3    'ANALYST', 'Analysis', 
  4    'CLERK', 'Filing', 
  5    'MANAGER', 'Managing', 
  6    'PRESIDENT', 'Administration', 
  7    'SALESMAN', 'Sales') Responsibilities 
  8  FROM Emp; 
 
RESPONSIBILITIES                                                                 
---------------------------------------                                          
King Takes Care of Administration                                                
Blake Takes Care of Managing                                                     
Clark Takes Care of Managing                                                     
Jones Takes Care of Managing                                                     
Martin Takes Care of Sales                                                       
Allen Takes Care of Sales                                                        
Turner Takes Care of Sales                                                       
James Takes Care of Filing                                                       
Ward Takes Care of Sales                                                         
Ford Takes Care of Analysis                                                      
Smith Takes Care of Filing                                                       
 
RESPONSIBILITIES                                                                 
---------------------------------------                                          
Scott Takes Care of Analysis                                                     
Adams Takes Care of Filing                                                       
Miller Takes Care of Filing                                                      
 
14 rows selected. 
 
SQL> SELECT 
  2  ROWNUM, DECODE(ROWNUM, 
  3        1, 'One', 
  4        2, 'Two', 
  5        3, 'Three') Spell 
  6  FROM Emp 
  7  WHERE ROWNUM <= 4; 
 
    ROWNUM SPELL                                                                 
---------- -----                                                                 
         1 One                                                                   
         2 Two                                                                   
         3 Three                                                                 
         4                                                                       
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 
  2  ROWNUM, DECODE(ROWNUM, 
  3        1, 'One', 
  4        2, 'Two', 
  5        3, 'Three', 
 
  6        'Donot Know') Spell 
  7  FROM Emp 
  8* WHERE ROWNUM <= &GiveVal 
SQL> / 
Enter value for giveval: 3 
old   8: WHERE ROWNUM <= &GiveVal 
new   8: WHERE ROWNUM <= 3 
 
    ROWNUM SPELL                                                                 
---------- ----------                                                            
         1 One                                                                   
         2 Two                                                                   
         3 Three                                                                 
 
SQL> / 
Enter value for giveval: 6 
old   8: WHERE ROWNUM <= &GiveVal 
new   8: WHERE ROWNUM <= 6 
 
    ROWNUM SPELL                                                                 
---------- ----------                                                            
         1 One                                                                   
         2 Two                                                                   
         3 Three                                                                 
         4 Donot Know                                                            
         5 Donot Know                                                            
         6 Donot Know                                                            
 
6 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 
  2  ROWNUM "S.No", DECODE(ROWNUM, 
  3        1, 'One', 
  4        2, 'Two', 
  5        3, 'Three', 
  6        'Donot Know') Spell 
  7  FROM Emp 
  8* WHERE ROWNUM <= &GiveVal 
SQL> / 
Enter value for giveval: 4 
old   8: WHERE ROWNUM <= &GiveVal 
new   8: WHERE ROWNUM <= 4 
 
      S.No SPELL                                                                 
---------- ----------                                                            
         1 One                                                                   
         2 Two                                                                   
         3 Three                                                                 
         4 Donot Know                                                            
 
SQL> cl scr 
 
SQL> SELECT Ename, Job, 
  2  DECODE(Job, 
 
  3    'CLERK', 'E', 
  4    'SALESMAN', 'D', 
  5    'ANALYST', 'C', 
  6    'MANAGER', 'B', 
  7    'PRESIDENT', 'A', 
  8    'O') Grades 
  9  FROM EMP ORDER BY JOB; 
 
ENAME      JOB       G                                                           
---------- --------- -                                                           
FORD       ANALYST   C                                                           
SCOTT      ANALYST   C                                                           
JAMES      CLERK     E                                                           
SMITH      CLERK     E                                                           
MILLER     CLERK     E                                                           
ADAMS      CLERK     E                                                           
BLAKE      MANAGER   B                                                           
CLARK      MANAGER   B                                                           
JONES      MANAGER   B                                                           
KING       PRESIDENT A                                                           
MARTIN     SALESMAN  D                                                           
 
ENAME      JOB       G                                                           
---------- --------- -                                                           
ALLEN      SALESMAN  D                                                           
WARD       SALESMAN  D                                                           
TURNER     SALESMAN  D                                                           
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT Ename, Job, 
  2  DECODE(Job, 
  3    'CLERK', 'E', 
  4    'SALESMAN', 'D', 
  5    'ANALYST', 'C', 
  6    'MANAGER', 'B', 
  7    'PRESIDENT', 'A', 
  8    'O') Grades, Deptno, 
  9  DECODE(Deptno, 
 10  10, 'ACCOUNTING', 
 11  20, 'RESEARCH', 
 12  30 , 'SALES', 
 13  40 , 'OPERATIONS', 
 14  'OTHER')  Departments, 
 15  Sal, Grade 
 16  FROM EMP E, Salgrade S 
 17  WHERE Sal BETWEEN LoSal AND HiSal 
 18  ORDER BY JOB; 
 
ENAME      JOB       G DEPTNO DEPARTMENT        SAL      GRADE                   
---------- --------- - ------ ---------- ---------- ----------                   
FORD       ANALYST   C     20 RESEARCH         3000          4                   
SCOTT      ANALYST   C     20 RESEARCH         3000          4                   
SMITH      CLERK     E     20 RESEARCH          800          1                   
JAMES      CLERK     E     30 SALES             950          1                   
 
ADAMS      CLERK     E     20 RESEARCH         1100          1                   
MILLER     CLERK     E     10 ACCOUNTING       1300          2                   
CLARK      MANAGER   B     10 ACCOUNTING       2450          4                   
BLAKE      MANAGER   B     30 SALES            2850          4                   
JONES      MANAGER   B     20 RESEARCH         2975          4                   
KING       PRESIDENT A     10 ACCOUNTING       5000          5                   
MARTIN     SALESMAN  D     30 SALES            1250          2                   
 
ENAME      JOB       G DEPTNO DEPARTMENT        SAL      GRADE                   
---------- --------- - ------ ---------- ---------- ----------                   
WARD       SALESMAN  D     30 SALES            1250          2                   
TURNER     SALESMAN  D     30 SALES            1500          3                   
ALLEN      SALESMAN  D     30 SALES            1600          3                   
 
14 rows selected. 
 
SQL> SET AUTOTRACE ON EXPLAIN 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Job, 
  2  DECODE(Job, 
  3    'CLERK', 'E', 
  4    'SALESMAN', 'D', 
  5    'ANALYST', 'C', 
  6    'MANAGER', 'B', 
  7    'PRESIDENT', 'A', 
  8    'O') Grades, D.Deptno, 
  9  Dname, 
 10  Sal, Grade 
 11  FROM EMP E, Salgrade S, Dept D 
 12  WHERE  
 13  E.Deptno = D.Deptno AND 
 14  Sal BETWEEN LoSal AND HiSal 
 15* ORDER BY JOB 
SQL> / 
 
ENAME      JOB       G DEPTNO DNAME                 SAL      GRADE               
---------- --------- - ------ -------------- ---------- ----------               
FORD       ANALYST   C     20 RESEARCH             3000          4               
SCOTT      ANALYST   C     20 RESEARCH             3000          4               
SMITH      CLERK     E     20 RESEARCH              800          1               
JAMES      CLERK     E     30 SALES                 950          1               
ADAMS      CLERK     E     20 RESEARCH             1100          1               
MILLER     CLERK     E     10 ACCOUNTING           1300          2               
CLARK      MANAGER   B     10 ACCOUNTING           2450          4               
BLAKE      MANAGER   B     30 SALES                2850          4               
JONES      MANAGER   B     20 RESEARCH             2975          4               
KING       PRESIDENT A     10 ACCOUNTING           5000          5               
MARTIN     SALESMAN  D     30 SALES                1250          2               
 
ENAME      JOB       G DEPTNO DNAME                 SAL      GRADE               
---------- --------- - ------ -------------- ---------- ----------               
WARD       SALESMAN  D     30 SALES                1250          2               
TURNER     SALESMAN  D     30 SALES                1500          3               
ALLEN      SALESMAN  D     30 SALES                1600          3               
 
 
14 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=10 Card=1 Bytes=10           
          0)                                                                     
                                                                                 
   1    0   SORT (ORDER BY) (Cost=10 Card=1 Bytes=100)                           
   2    1     NESTED LOOPS (Cost=9 Card=1 Bytes=100)                             
   3    2       MERGE JOIN (Cost=8 Card=1 Bytes=78)                              
   4    3         SORT (JOIN) (Cost=4 Card=5 Bytes=195)                          
   5    4           TABLE ACCESS (FULL) OF 'SALGRADE' (TABLE) (Cost=3            
          Card=5 Bytes=195)                                                      
                                                                                 
   6    3         FILTER                                                         
   7    6           SORT (JOIN) (Cost=4 Card=14 Bytes=546)                       
   8    7             TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Car           
          d=14 Bytes=546)                                                        
                                                                                 
   9    2       TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=           
          1 Card=1 Bytes=22)                                                     
                                                                                 
  10    9         INDEX (UNIQUE SCAN) OF 'DEPT_PRIMARY_KEY' (INDEX (UN           
          IQUE)) (Cost=0 Card=1)                                                 
                                                                                 
 
 
 
SQL> SELECT Ename, Job, 
  2  DECODE(Job, 
  3    'CLERK', 'E', 
  4    'SALESMAN', 'D', 
  5    'ANALYST', 'C', 
  6    'MANAGER', 'B', 
  7    'PRESIDENT', 'A', 
  8    'O') Grades, Deptno, 
  9  DECODE(Deptno, 
 10  10, 'ACCOUNTING', 
 11  20, 'RESEARCH', 
 12  30 , 'SALES', 
 13  40 , 'OPERATIONS', 
 14  'OTHER')  Departments, 
 15  Sal, Grade 
 16  FROM EMP E, Salgrade S 
 17  WHERE Sal BETWEEN LoSal AND HiSal 
 18  ORDER BY JOB; 
 
ENAME      JOB       G DEPTNO DEPARTMENT        SAL      GRADE                   
---------- --------- - ------ ---------- ---------- ----------                   
FORD       ANALYST   C     20 RESEARCH         3000          4                   
SCOTT      ANALYST   C     20 RESEARCH         3000          4                   
SMITH      CLERK     E     20 RESEARCH          800          1                   
JAMES      CLERK     E     30 SALES             950          1                   
ADAMS      CLERK     E     20 RESEARCH         1100          1                   
MILLER     CLERK     E     10 ACCOUNTING       1300          2                   
CLARK      MANAGER   B     10 ACCOUNTING       2450          4                   
 
BLAKE      MANAGER   B     30 SALES            2850          4                   
JONES      MANAGER   B     20 RESEARCH         2975          4                   
KING       PRESIDENT A     10 ACCOUNTING       5000          5                   
MARTIN     SALESMAN  D     30 SALES            1250          2                   
 
ENAME      JOB       G DEPTNO DEPARTMENT        SAL      GRADE                   
---------- --------- - ------ ---------- ---------- ----------                   
WARD       SALESMAN  D     30 SALES            1250          2                   
TURNER     SALESMAN  D     30 SALES            1500          3                   
ALLEN      SALESMAN  D     30 SALES            1600          3                   
 
14 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=1 Bytes=78)           
   1    0   SORT (ORDER BY) (Cost=9 Card=1 Bytes=78)                             
   2    1     MERGE JOIN (Cost=8 Card=1 Bytes=78)                                
   3    2       SORT (JOIN) (Cost=4 Card=5 Bytes=195)                            
   4    3         TABLE ACCESS (FULL) OF 'SALGRADE' (TABLE) (Cost=3 Ca           
          rd=5 Bytes=195)                                                        
                                                                                 
   5    2       FILTER                                                           
   6    5         SORT (JOIN) (Cost=4 Card=14 Bytes=546)                         
   7    6           TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=           
          14 Bytes=546)                                                          
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> SELECT Ename, Job, 
  2  DECODE(MGR, 
  3    7566, (SELECT Ename 
  4      FROM Emp 
  5      WHERE Empno = 7566), 
  6    7698, (SELECT Ename 
  7      FROM Emp 
  8      WHERE Empno = 7698), 
  9    7782, (SELECT Ename 
 10      FROM Emp 
 11      WHERE Empno = 7782), 
 12    7788, (SELECT Ename 
 13      FROM Emp 
 14      WHERE Empno = 7788), 
 15    7839, (SELECT Ename 
 16      FROM Emp 
 17      WHERE Empno = 7839), 
 18    7902, (SELECT Ename 
 19      FROM Emp 
 20      WHERE Empno = 7902), 
 21    'Do Not Know')  Manager, 
 22  DECODE(Job, 
 23    'CLERK', 'E', 
 24    'SALESMAN', 'D', 
 
 25    'ANALYST', 'C', 
 26    'MANAGER', 'B', 
 27    'PRESIDENT', 'A', 
 28    'O') Grades, Deptno, 
 29  DECODE(Deptno, 
 30    10, 'ACCOUNTING', 
 31    20, 'RESEARCH', 
 32    30 , 'SALES', 
 33    40 , 'OPERATIONS', 
 34    'OTHER')  Departments, 
 35  Sal, Grade 
 36  FROM EMP E, Salgrade S 
 37  WHERE Sal BETWEEN LoSal AND HiSal 
 38  ORDER BY JOB; 
 
ENAME      JOB       MANAGER     G DEPTNO DEPARTMENT        SAL      GRADE       
---------- --------- ----------- - ------ ---------- ---------- ----------       
FORD       ANALYST   JONES       C     20 RESEARCH         3000          4       
SCOTT      ANALYST   JONES       C     20 RESEARCH         3000          4       
SMITH      CLERK     FORD        E     20 RESEARCH          800          1       
JAMES      CLERK     BLAKE       E     30 SALES             950          1       
ADAMS      CLERK     SCOTT       E     20 RESEARCH         1100          1       
MILLER     CLERK     CLARK       E     10 ACCOUNTING       1300          2       
CLARK      MANAGER   KING        B     10 ACCOUNTING       2450          4       
BLAKE      MANAGER   KING        B     30 SALES            2850          4       
JONES      MANAGER   KING        B     20 RESEARCH         2975          4       
KING       PRESIDENT Do Not Know A     10 ACCOUNTING       5000          5       
MARTIN     SALESMAN  BLAKE       D     30 SALES            1250          2       
 
ENAME      JOB       MANAGER     G DEPTNO DEPARTMENT        SAL      GRADE       
---------- --------- ----------- - ------ ---------- ---------- ----------       
WARD       SALESMAN  BLAKE       D     30 SALES            1250          2       
TURNER     SALESMAN  BLAKE       D     30 SALES            1500          3       
ALLEN      SALESMAN  BLAKE       D     30 SALES            1600          3       
 
14 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=1 Bytes=91)           
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Car           
          d=1 Bytes=20)                                                          
                                                                                 
   2    1     INDEX (UNIQUE SCAN) OF 'EMP_PRIMARY_KEY' (INDEX (UNIQUE)           
          ) (Cost=1 Card=1)                                                      
                                                                                 
   3    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Car           
          d=1 Bytes=20)                                                          
                                                                                 
   4    3     INDEX (UNIQUE SCAN) OF 'EMP_PRIMARY_KEY' (INDEX (UNIQUE)           
          ) (Cost=1 Card=1)                                                      
                                                                                 
   5    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Car           
          d=1 Bytes=20)                                                          
                                                                                 
   6    5     INDEX (UNIQUE SCAN) OF 'EMP_PRIMARY_KEY' (INDEX (UNIQUE)           
 
          ) (Cost=1 Card=1)                                                      
                                                                                 
   7    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Car           
          d=1 Bytes=20)                                                          
                                                                                 
   8    7     INDEX (UNIQUE SCAN) OF 'EMP_PRIMARY_KEY' (INDEX (UNIQUE)           
          ) (Cost=1 Card=1)                                                      
                                                                                 
   9    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Car           
          d=1 Bytes=20)                                                          
                                                                                 
  10    9     INDEX (UNIQUE SCAN) OF 'EMP_PRIMARY_KEY' (INDEX (UNIQUE)           
          ) (Cost=1 Card=1)                                                      
                                                                                 
  11    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Car           
          d=1 Bytes=20)                                                          
                                                                                 
  12   11     INDEX (UNIQUE SCAN) OF 'EMP_PRIMARY_KEY' (INDEX (UNIQUE)           
          ) (Cost=1 Card=1)                                                      
                                                                                 
  13    0   SORT (ORDER BY) (Cost=9 Card=1 Bytes=91)                             
  14   13     MERGE JOIN (Cost=8 Card=1 Bytes=91)                                
  15   14       SORT (JOIN) (Cost=4 Card=5 Bytes=195)                            
  16   15         TABLE ACCESS (FULL) OF 'SALGRADE' (TABLE) (Cost=3 Ca           
          rd=5 Bytes=195)                                                        
                                                                                 
  17   14       FILTER                                                           
  18   17         SORT (JOIN) (Cost=4 Card=14 Bytes=728)                         
  19   18           TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=           
          14 Bytes=728)                                                          
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> SELECT 
  2  TO_CHAR(HireDate, 'YYYY') Year, COUNT(*) EmpCnt 
  3  FROM Emp 
  4  GROUP BY ROLLUP(TO_CHAR(HireDate, 'YYYY')); 
 
YEAR     EMPCNT                                                                  
---- ----------                                                                  
1980          1                                                                  
1981         10                                                                  
1982          2                                                                  
1983          1                                                                  
             14                                                                  
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=12           
          6)                                                                     
                                                                                 
   1    0   SORT (GROUP BY ROLLUP) (Cost=4 Card=14 Bytes=126)                    
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
 
          es=126)                                                                
                                                                                 
 
 
 
SQL> SELECT 
  2   SUM(DECODE(TO_CHAR(HireDate, 'YYYY'), 
  3      1980, 1, 0))  "1980" , 
  4   SUM(DECODE(TO_CHAR(HireDate, 'YYYY'), 
  5      1981, 1, 0))  "1981" , 
  6   SUM(DECODE(TO_CHAR(HireDate, 'YYYY'), 
  7      1982, 1, 0))  "1982" , 
  8   SUM(DECODE(TO_CHAR(HireDate, 'YYYY'), 
  9      1983, 1, 0))  "1983", 
 10  COUNT(*) Total 
 11  FROM Emp; 
 
      1980       1981       1982       1983      TOTAL                           
---------- ---------- ---------- ---------- ----------                           
         1         10          2          1         14                           
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=9)            
   1    0   SORT (AGGREGATE)                                                     
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
          es=126)                                                                
                                                                                 
 
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 
  2   COUNT(DECODE(TO_CHAR(HireDate, 'YYYY'), 
  3      1980, 1, 0))  "1980" , 
  4   COUNT(DECODE(TO_CHAR(HireDate, 'YYYY'), 
  5      1981, 1, 0))  "1981" , 
  6   COUNT(DECODE(TO_CHAR(HireDate, 'YYYY'), 
  7      1982, 1, 0))  "1982" , 
  8   COUNT(DECODE(TO_CHAR(HireDate, 'YYYY'), 
  9      1983, 1, 0))  "1983", 
 10  COUNT(*) Total 
 11* FROM Emp 
SQL> / 
 
      1980       1981       1982       1983      TOTAL                           
---------- ---------- ---------- ---------- ----------                           
        14         14         14         14         14                           
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=9)            
   1    0   SORT (AGGREGATE)                                                     
 
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
          es=126)                                                                
                                                                                 
 
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 
  2   COUNT(DECODE(TO_CHAR(HireDate, 'YYYY'), 
  3      1980, 1, NULL))  "1980" , 
  4   COUNT(DECODE(TO_CHAR(HireDate, 'YYYY'), 
  5      1981, 1, NULL))  "1981" , 
  6   COUNT(DECODE(TO_CHAR(HireDate, 'YYYY'), 
  7      1982, 1, NULL))  "1982" , 
  8   COUNT(DECODE(TO_CHAR(HireDate, 'YYYY'), 
  9      1983, 1, NULL))  "1983", 
 10  COUNT(*) Total 
 11* FROM Emp 
SQL> / 
 
      1980       1981       1982       1983      TOTAL                           
---------- ---------- ---------- ---------- ----------                           
         1         10          2          1         14                           
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=9)            
   1    0   SORT (AGGREGATE)                                                     
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
          es=126)                                                                
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> SELECT Ename, Job, Sal, 
  2  DECODE(Job, 
  3    'ANALYST', Sal * 1.1, 
  4    'CLERK', Sal * 1.15, 
  5    'MANAGER', Sal * 1.2, 
  6    Sal) "REVISED SALARY " 
  7  FROM Emp; 
 
ENAME      JOB              SAL REVISED SALARY                                   
---------- --------- ---------- ---------------                                  
KING       PRESIDENT       5000            5000                                  
BLAKE      MANAGER         2850            3420                                  
CLARK      MANAGER         2450            2940                                  
JONES      MANAGER         2975            3570                                  
MARTIN     SALESMAN        1250            1250                                  
ALLEN      SALESMAN        1600            1600                                  
TURNER     SALESMAN        1500            1500                                  
JAMES      CLERK            950          1092.5                                  
 
WARD       SALESMAN        1250            1250                                  
FORD       ANALYST         3000            3300                                  
SMITH      CLERK            800             920                                  
 
ENAME      JOB              SAL REVISED SALARY                                   
---------- --------- ---------- ---------------                                  
SCOTT      ANALYST         3000            3300                                  
ADAMS      CLERK           1100            1265                                  
MILLER     CLERK           1300            1495                                  
 
14 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=36           
          4)                                                                     
                                                                                 
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes           
          =364)                                                                  
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> SELECT Ename, Job, Sal, 
  2  DECODE(Job, 'ANALYST', Sal * 1.1, 
  3     'CLERK', Sal * 1.15, 
  4     'MANAGER', Sal * 1.2, 
  5     Sal) "REVISED SALARY", 
  6  DECODE(Job, 'ANALYST', 'Revised', 
  7     'CLERK', 'Revised', 
  8     'MANAGER', 'Revised', 
  9     'Sorry!') "Status" 
 10  FROM Emp; 
 
ENAME      JOB              SAL REVISED SALARY Status                            
---------- --------- ---------- -------------- -------                           
KING       PRESIDENT       5000           5000 Sorry!                            
BLAKE      MANAGER         2850           3420 Revised                           
CLARK      MANAGER         2450           2940 Revised                           
JONES      MANAGER         2975           3570 Revised                           
MARTIN     SALESMAN        1250           1250 Sorry!                            
ALLEN      SALESMAN        1600           1600 Sorry!                            
TURNER     SALESMAN        1500           1500 Sorry!                            
JAMES      CLERK            950         1092.5 Revised                           
WARD       SALESMAN        1250           1250 Sorry!                            
FORD       ANALYST         3000           3300 Revised                           
SMITH      CLERK            800            920 Revised                           
 
ENAME      JOB              SAL REVISED SALARY Status                            
---------- --------- ---------- -------------- -------                           
SCOTT      ANALYST         3000           3300 Revised                           
ADAMS      CLERK           1100           1265 Revised                           
MILLER     CLERK           1300           1495 Revised                           
 
 
14 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=36           
          4)                                                                     
                                                                                 
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes           
          =364)                                                                  
                                                                                 
 
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Job, Sal, 
  2  DECODE( 
  3   Job,  
  4   'ANALYST', (Sal * 1.1)||' Revised.', 
  5      'CLERK', (Sal * 1.15)||' Revised.', 
  6      'MANAGER', (Sal * 1.2)||' Revised.', 
  7      Sal||' Sorry!') "REVISED SALARY" 
  8* FROM Emp 
SQL> / 
 
ENAME      JOB              SAL                                                  
---------- --------- ----------                                                  
REVISED SALARY                                                                   
-------------------------------------------------                                
KING       PRESIDENT       5000                                                  
5000 Sorry!                                                                      
                                                                                 
BLAKE      MANAGER         2850                                                  
3420 Revised.                                                                    
                                                                                 
CLARK      MANAGER         2450                                                  
2940 Revised.                                                                    
                                                                                 
 
ENAME      JOB              SAL                                                  
---------- --------- ----------                                                  
REVISED SALARY                                                                   
-------------------------------------------------                                
JONES      MANAGER         2975                                                  
3570 Revised.                                                                    
                                                                                 
MARTIN     SALESMAN        1250                                                  
1250 Sorry!                                                                      
                                                                                 
ALLEN      SALESMAN        1600                                                  
1600 Sorry!                                                                      
                                                                                 
 
ENAME      JOB              SAL                                                  
---------- --------- ----------                                                  
 
REVISED SALARY                                                                   
-------------------------------------------------                                
TURNER     SALESMAN        1500                                                  
1500 Sorry!                                                                      
                                                                                 
JAMES      CLERK            950                                                  
1092.5 Revised.                                                                  
                                                                                 
WARD       SALESMAN        1250                                                  
1250 Sorry!                                                                      
                                                                                 
 
ENAME      JOB              SAL                                                  
---------- --------- ----------                                                  
REVISED SALARY                                                                   
-------------------------------------------------                                
FORD       ANALYST         3000                                                  
3300 Revised.                                                                    
                                                                                 
SMITH      CLERK            800                                                  
920 Revised.                                                                     
                                                                                 
SCOTT      ANALYST         3000                                                  
3300 Revised.                                                                    
                                                                                 
 
ENAME      JOB              SAL                                                  
---------- --------- ----------                                                  
REVISED SALARY                                                                   
-------------------------------------------------                                
ADAMS      CLERK           1100                                                  
1265 Revised.                                                                    
                                                                                 
MILLER     CLERK           1300                                                  
1495 Revised.                                                                    
                                                                                 
 
14 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=36           
          4)                                                                     
                                                                                 
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes           
          =364)                                                                  
                                                                                 
 
 
 
SQL> COLUMN "REVISED SALARY" FORMAT A25 
SQL> / 
 
ENAME      JOB              SAL REVISED SALARY                                   
---------- --------- ---------- -------------------------                        
KING       PRESIDENT       5000 5000 Sorry!                                      
 
BLAKE      MANAGER         2850 3420 Revised.                                    
CLARK      MANAGER         2450 2940 Revised.                                    
JONES      MANAGER         2975 3570 Revised.                                    
MARTIN     SALESMAN        1250 1250 Sorry!                                      
ALLEN      SALESMAN        1600 1600 Sorry!                                      
TURNER     SALESMAN        1500 1500 Sorry!                                      
JAMES      CLERK            950 1092.5 Revised.                                  
WARD       SALESMAN        1250 1250 Sorry!                                      
FORD       ANALYST         3000 3300 Revised.                                    
SMITH      CLERK            800 920 Revised.                                     
 
ENAME      JOB              SAL REVISED SALARY                                   
---------- --------- ---------- -------------------------                        
SCOTT      ANALYST         3000 3300 Revised.                                    
ADAMS      CLERK           1100 1265 Revised.                                    
MILLER     CLERK           1300 1495 Revised.                                    
 
14 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=36           
          4)                                                                     
                                                                                 
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes           
          =364)                                                                  
                                                                                 
 
 
 
SQL> SELECT Ename, Job, Sal, 
  2  DECODE(Job, 'ANALYST', Sal * 1.1, 
  3     'CLERK', Sal * 1.15, 
  4     'MANAGER', Sal * 1.2, 
  5     Sal) "REVISED SALARY", 
  6  DECODE(Job, 'ANALYST', Sal * 1.1, 
  7     'CLERK', Sal * 1.15, 
  8     'MANAGER', Sal * 1.2, 
  9     Sal) - Sal "Extra Amount", 
 10  DECODE(Job, 'ANALYST', 'Revised', 
 11     'CLERK', 'Revised', 
 12     'MANAGER', 'Revised', 
 13     'Sorry!') "Status" 
 14  FROM Emp; 
 
ENAME      JOB              SAL REVISED SALARY Extra Amount Status               
---------- --------- ---------- -------------- ------------ -------              
KING       PRESIDENT       5000     ##########            0 Sorry!               
BLAKE      MANAGER         2850     ##########          570 Revised              
CLARK      MANAGER         2450     ##########          490 Revised              
JONES      MANAGER         2975     ##########          595 Revised              
MARTIN     SALESMAN        1250     ##########            0 Sorry!               
ALLEN      SALESMAN        1600     ##########            0 Sorry!               
TURNER     SALESMAN        1500     ##########            0 Sorry!               
JAMES      CLERK            950     ##########        142.5 Revised              
WARD       SALESMAN        1250     ##########            0 Sorry!               
 
FORD       ANALYST         3000     ##########          300 Revised              
SMITH      CLERK            800     ##########          120 Revised              
 
ENAME      JOB              SAL REVISED SALARY Extra Amount Status               
---------- --------- ---------- -------------- ------------ -------              
SCOTT      ANALYST         3000     ##########          300 Revised              
ADAMS      CLERK           1100     ##########          165 Revised              
MILLER     CLERK           1300     ##########          195 Revised              
 
14 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=36           
          4)                                                                     
                                                                                 
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes           
          =364)                                                                  
                                                                                 
 
 
 
SQL> COLUMN "REVISED SALARY" 
COLUMN   REVISED SALARY ON 
FORMAT   A25 
SQL> COLUMN "REVISED SALARY" FORMAT 99999 
SQL> / 
 
ENAME      JOB              SAL REVISED SALARY Extra Amount Status               
---------- --------- ---------- -------------- ------------ -------              
KING       PRESIDENT       5000           5000            0 Sorry!               
BLAKE      MANAGER         2850           3420          570 Revised              
CLARK      MANAGER         2450           2940          490 Revised              
JONES      MANAGER         2975           3570          595 Revised              
MARTIN     SALESMAN        1250           1250            0 Sorry!               
ALLEN      SALESMAN        1600           1600            0 Sorry!               
TURNER     SALESMAN        1500           1500            0 Sorry!               
JAMES      CLERK            950           1093        142.5 Revised              
WARD       SALESMAN        1250           1250            0 Sorry!               
FORD       ANALYST         3000           3300          300 Revised              
SMITH      CLERK            800            920          120 Revised              
 
ENAME      JOB              SAL REVISED SALARY Extra Amount Status               
---------- --------- ---------- -------------- ------------ -------              
SCOTT      ANALYST         3000           3300          300 Revised              
ADAMS      CLERK           1100           1265          165 Revised              
MILLER     CLERK           1300           1495          195 Revised              
 
14 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=36           
          4)                                                                     
                                                                                 
 
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes           
          =364)                                                                  
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> R 
  1  SELECT Ename, Job, Sal, 
  2  DECODE(Job, 'ANALYST', Sal * 1.1, 
  3     'CLERK', Sal * 1.15, 
  4     'MANAGER', Sal * 1.2, 
  5     Sal) "REVISED SALARY", 
  6  DECODE(Job, 'ANALYST', Sal * 1.1, 
  7     'CLERK', Sal * 1.15, 
  8     'MANAGER', Sal * 1.2, 
  9     Sal) - Sal "Extra Amount", 
 10  DECODE(Job, 'ANALYST', 'Revised', 
 11     'CLERK', 'Revised', 
 12     'MANAGER', 'Revised', 
 13     'Sorry!') "Status" 
 14* FROM Emp 
 
ENAME      JOB              SAL REVISED SALARY Extra Amount Status               
---------- --------- ---------- -------------- ------------ -------              
KING       PRESIDENT       5000           5000            0 Sorry!               
BLAKE      MANAGER         2850           3420          570 Revised              
CLARK      MANAGER         2450           2940          490 Revised              
JONES      MANAGER         2975           3570          595 Revised              
MARTIN     SALESMAN        1250           1250            0 Sorry!               
ALLEN      SALESMAN        1600           1600            0 Sorry!               
TURNER     SALESMAN        1500           1500            0 Sorry!               
JAMES      CLERK            950           1093        142.5 Revised              
WARD       SALESMAN        1250           1250            0 Sorry!               
FORD       ANALYST         3000           3300          300 Revised              
SMITH      CLERK            800            920          120 Revised              
 
ENAME      JOB              SAL REVISED SALARY Extra Amount Status               
---------- --------- ---------- -------------- ------------ -------              
SCOTT      ANALYST         3000           3300          300 Revised              
ADAMS      CLERK           1100           1265          165 Revised              
MILLER     CLERK           1300           1495          195 Revised              
 
14 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=36           
          4)                                                                     
                                                                                 
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes           
          =364)                                                                  
                                                                                 
 
 
 
 
SQL> cl scr 
 
SQL> SELECT 
  2  COUNT(DECODE(Deptno, 10, '*', NULL)) D10_COUNT, 
  3  SUM(DECODE(Deptno, 10, Sal, NULL)) D10_Sal, 
  4  COUNT(DECODE(Deptno, 20, '*', NULL)) D20_COUNT, 
  5  SUM(DECODE(Deptno, 20, Sal, NULL)) D20_Sal, 
  6  COUNT(DECODE(Deptno, 30, '*', NULL)) D30_COUNT, 
  7  SUM(DECODE(Deptno, 30, Sal, NULL)) D30_Sal 
  8  FROM Emp; 
 
 D10_COUNT    D10_SAL  D20_COUNT    D20_SAL  D30_COUNT    D30_SAL                
---------- ---------- ---------- ---------- ---------- ----------                
         3       8750          5      10875          6       9400                
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=26)           
   1    0   SORT (AGGREGATE)                                                     
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
          es=364)                                                                
                                                                                 
 
 
 
SQL> SELECT Deptno, COUNT(*), SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY Deptno; 
 
DEPTNO   COUNT(*)   SUM(SAL)                                                     
------ ---------- ----------                                                     
    10          3       8750                                                     
    20          5      10875                                                     
    30          6       9400                                                     
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=36           
          4)                                                                     
                                                                                 
   1    0   SORT (GROUP BY) (Cost=4 Card=14 Bytes=364)                           
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
          es=364)                                                                
                                                                                 
 
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Deptno, COUNT(*), SUM(Sal) 
  2  FROM Emp 
  3* GROUP BY ROLLUP(Deptno) 
SQL> / 
 
 
DEPTNO   COUNT(*)   SUM(SAL)                                                     
------ ---------- ----------                                                     
    10          3       8750                                                     
    20          5      10875                                                     
    30          6       9400                                                     
               14      29025                                                     
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=36           
          4)                                                                     
                                                                                 
   1    0   SORT (GROUP BY ROLLUP) (Cost=4 Card=14 Bytes=364)                    
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
          es=364)                                                                
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> COLUMN D10_COUNT FORMAT 99 
SQL> COLUMN D10_SAL FORMAT 99999 
SQL> COLUMN D20_COUNT FORMAT 99 
SQL> COLUMN D20_SAL FORMAT 99999 
SQL> COLUMN D30_COUNT FORMAT 99 
SQL> COLUMN D30_SAL FORMAT 99999 
SQL> COLUMN EMPCOUNT FORMAT 99 
SQL> COLUMN SALSUM FORMAT 99999 
SQL> SELECT 
  2  COUNT(DECODE(Deptno, 10, '*', NULL)) D10_COUNT, 
  3  SUM(DECODE(Deptno, 10, Sal, NULL)) D10_Sal, 
  4  COUNT(DECODE(Deptno, 20, '*', NULL)) D20_COUNT, 
  5  SUM(DECODE(Deptno, 20, Sal, NULL)) D20_Sal, 
  6  COUNT(DECODE(Deptno, 30, '*', NULL)) D30_COUNT, 
  7  SUM(DECODE(Deptno, 30, Sal, NULL)) D30_Sal, 
  8  COUNT(*) EmpCount, SUM(Sal) SalSum 
  9  FROM Emp; 
 
D10_COUNT D10_SAL D20_COUNT D20_SAL D30_COUNT D30_SAL EMPCOUNT SALSUM            
--------- ------- --------- ------- --------- ------- -------- ------            
        3    8750         5   10875         6    9400       14  29025            
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=26)           
   1    0   SORT (AGGREGATE)                                                     
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
          es=364)                                                                
                                                                                 
 
 
 
SQL> cl scr 
 
 
SQL> SELECT Job, 
  2  SUM(DECODE(Deptno, 10, Sal)) "Deptno 10", 
  3  SUM(DECODE(Deptno, 20, Sal)) "Deptno 20", 
  4  SUM(DECODE(Deptno, 30, Sal)) "Deptno 30", 
  5  SUM(Sal) "Total" 
  6  FROM Emp 
  7  GROUP BY Job; 
 
JOB        Deptno 10  Deptno 20  Deptno 30      Total                            
--------- ---------- ---------- ---------- ----------                            
ANALYST                    6000                  6000                            
CLERK           1300       1900        950       4150                            
MANAGER         2450       2975       2850       8275                            
PRESIDENT       5000                             5000                            
SALESMAN                              5600       5600                            
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=44           
          8)                                                                     
                                                                                 
   1    0   SORT (GROUP BY) (Cost=4 Card=14 Bytes=448)                           
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
          es=448)                                                                
                                                                                 
 
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Job, 
  2  SUM(DECODE(Deptno, 10, Sal)) "Deptno 10", 
  3  SUM(DECODE(Deptno, 20, Sal)) "Deptno 20", 
  4  SUM(DECODE(Deptno, 30, Sal)) "Deptno 30", 
  5  SUM(Sal) "Total" 
  6  FROM Emp 
  7* GROUP BY ROLLUP(Job) 
SQL> / 
 
JOB        Deptno 10  Deptno 20  Deptno 30      Total                            
--------- ---------- ---------- ---------- ----------                            
ANALYST                    6000                  6000                            
CLERK           1300       1900        950       4150                            
MANAGER         2450       2975       2850       8275                            
PRESIDENT       5000                             5000                            
SALESMAN                              5600       5600                            
                8750      10875       9400      29025                            
 
6 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=44           
 
          8)                                                                     
                                                                                 
   1    0   SORT (GROUP BY ROLLUP) (Cost=4 Card=14 Bytes=448)                    
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
          es=448)                                                                
                                                                                 
 
 
 
SQL> SELECT Deptno, 
  2  SUM(DECODE(Job, 'PRESIDENT', Sal)) "President", 
  3  SUM(DECODE(Job, 'ANALYST', Sal)) "Analyst", 
  4  SUM(DECODE(Job, 'MANAGER', Sal)) "Manager", 
  5  SUM(DECODE(Job, 'CLERK', Sal)) "Clerk", 
  6  SUM(DECODE(Job, 'SALESMAN', Sal)) "Salesman", 
  7  SUM(Sal) "Total" 
  8  FROM Emp 
  9  GROUP BY Deptno; 
 
DEPTNO  President    Analyst    Manager      Clerk   Salesman      Total         
------ ---------- ---------- ---------- ---------- ---------- ----------         
    10       5000                  2450       1300                  8750         
    20                  6000       2975       1900                 10875         
    30                             2850        950       5600       9400         
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=44           
          8)                                                                     
                                                                                 
   1    0   SORT (GROUP BY) (Cost=4 Card=14 Bytes=448)                           
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
          es=448)                                                                
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> COLUMN President FORMAT A9 
SQL> COLUMN Analyst FORMAT A9 
SQL> COLUMN Manager FORMAT A9 
SQL> COLUMN Clerk FORMAT A9 
SQL> COLUMN Salesman FORMAT A9 
SQL> SELECT Deptno, 
  2  NVL(TO_CHAR(SUM(DECODE(Job, 'PRESIDENT', Sal))), '***') "President", 
  3  NVL(TO_CHAR(SUM(DECODE(Job, 'ANALYST', Sal))), '***') "Analyst", 
  4  NVL(TO_CHAR(SUM(DECODE(Job, 'MANAGER', Sal))), '***') "Manager", 
  5  NVl(TO_CHAR(SUM(DECODE(Job, 'CLERK', Sal))), '***') "Clerk", 
  6  NVL(TO_CHAR(SUM(DECODE(Job, 'SALESMAN', Sal))), '***') "Salesman", 
  7  SUM(Sal) "Total" 
  8  FROM Emp 
  9  GROUP BY Deptno; 
 
DEPTNO President Analyst   Manager   Clerk     Salesman       Total              
------ --------- --------- --------- --------- --------- ----------              
 
    10 5000      ***       2450      1300      ***             8750              
    20 ***       6000      2975      1900      ***            10875              
    30 ***       ***       2850      950       5600            9400              
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=44           
          8)                                                                     
                                                                                 
   1    0   SORT (GROUP BY) (Cost=4 Card=14 Bytes=448)                           
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
          es=448)                                                                
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> SET PAGESIZE 200 
SQL> BREAK ON Month SKIP 1 
SQL> COLUMN Month FORMAT A18 
SQL> COLUMN Sun FORMAT A5 
SQL> COLUMN Mon FORMAT A5 
SQL> COLUMN Tue FORMAT A5 
SQL> COLUMN Wed FORMAT A5 
SQL> COLUMN Thu FORMAT A5 
SQL> COLUMN Fri FORMAT A5 
SQL> COLUMN Sat FORMAT A5 
SQL> SELECT 
  2   LPAD(Month, 20 - (20 - LENGTH(Month)) / 2 ) Month, 
  3   "Sun", 
  4   "Mon", 
  5   "Tue", 
  6   "Wed", 
  7   "Thu", 
  8   "Fri", 
  9   "Sat" 
 10  FROM ( 
 11    SELECT 
 12    TO_CHAR(DT,'FMMonth YYYY') Month, 
 13    TO_CHAR(DT + 1,'IW') Week, 
 14    MAX(DECODE(TO_CHAR(DT, 'D'), '1', LPAD(TO_CHAR(DT, 'FMDD'), 2))) 
"Sun", 
 15    MAX(DECODE(TO_CHAR(DT, 'D'), '2', LPAD(TO_CHAR(DT, 'FMDD'), 2))) 
"Mon", 
 16    MAX(DECODE(TO_CHAR(DT, 'D'), '3', LPAD(TO_CHAR(DT, 'FMDD'), 2))) 
"Tue", 
 17    MAX(DECODE(TO_CHAR(DT, 'D'), '4', LPAD(TO_CHAR(DT, 'FMDD'), 2))) 
"Wed", 
 18    MAX(DECODE(TO_CHAR(DT, 'D'), '5', LPAD(TO_CHAR(DT, 'FMDD'), 2))) 
"Thu", 
 19    MAX(DECODE(TO_CHAR(DT, 'D'), '6', LPAD(TO_CHAR(DT, 'FMDD'), 2))) 
"Fri", 
 20    MAX(DECODE(TO_CHAR(DT, 'D'), '7', LPAD(TO_CHAR(DT, 'FMDD'), 2))) 
"Sat" 
 21    FROM( 
 
 22      SELECT TRUNC(SYSDATE, 'Y') - 1 + ROWNUM DT 
 23      FROM ALL_OBJECTS 
 24      WHERE 
 25      ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE, 'Year'), 12) - 
TRUNC(SYSDATE, 'Year') 
 26      ) 
 27    GROUP BY TO_CHAR(DT, 'FMMonth YYYY'), TO_CHAR(DT + 1, 'IW') 
 28   ) 
 29  ORDER BY TO_DATE(MONTH, 'Month YYYY' ), TO_NUMBER(Week) 
 30  / 
 
MONTH              Sun   Mon   Tue   Wed   Thu   Fri   Sat                       
------------------ ----- ----- ----- ----- ----- ----- -----                     
    January 2010    3     4     5     6     7     8     9                        
                   10    11    12    13    14    15    16                        
                   17    18    19    20    21    22    23                        
                   24    25    26    27    28    29    30                        
                   31                                                            
                                                  1     2                        
                                                                                 
   February 2010          1     2     3     4     5     6                        
                    7     8     9    10    11    12    13                        
                   14    15    16    17    18    19    20                        
                   21    22    23    24    25    26    27                        
                   28                                                            
                                                                                 
     March 2010           1     2     3     4     5     6                        
                    7     8     9    10    11    12    13                        
                   14    15    16    17    18    19    20                        
                   21    22    23    24    25    26    27                        
                   28    29    30    31                                          
                                                                                 
     April 2010                             1     2     3                        
                    4     5     6     7     8     9    10                        
                   11    12    13    14    15    16    17                        
                   18    19    20    21    22    23    24                        
                   25    26    27    28    29    30                              
                                                                                 
      May 2010                                          1                        
                    2     3     4     5     6     7     8                        
                    9    10    11    12    13    14    15                        
                   16    17    18    19    20    21    22                        
                   23    24    25    26    27    28    29                        
                   30    31                                                      
                                                                                 
     June 2010                  1     2     3     4     5                        
                    6     7     8     9    10    11    12                        
                   13    14    15    16    17    18    19                        
                   20    21    22    23    24    25    26                        
                   27    28    29    30                                          
                                                                                 
     July 2010                              1     2     3                        
                    4     5     6     7     8     9    10                        
                   11    12    13    14    15    16    17                        
                   18    19    20    21    22    23    24                        
                   25    26    27    28    29    30    31                        
                                                                                 
 
    August 2010     1     2     3     4     5     6     7                        
                    8     9    10    11    12    13    14                        
                   15    16    17    18    19    20    21                        
                   22    23    24    25    26    27    28                        
                   29    30    31                                                
                                                                                 
   September 2010                     1     2     3     4                        
                    5     6     7     8     9    10    11                        
                   12    13    14    15    16    17    18                        
                   19    20    21    22    23    24    25                        
                   26    27    28    29    30                                    
                                                                                 
    October 2010                                  1     2                        
                    3     4     5     6     7     8     9                        
                   10    11    12    13    14    15    16                        
                   17    18    19    20    21    22    23                        
                   24    25    26    27    28    29    30                        
                   31                                                            
                                                                                 
   November 2010          1     2     3     4     5     6                        
                    7     8     9    10    11    12    13                        
                   14    15    16    17    18    19    20                        
                   21    22    23    24    25    26    27                        
                   28    29    30                                                
                                                                                 
   December 2010                      1     2     3     4                        
                    5     6     7     8     9    10    11                        
                   12    13    14    15    16    17    18                        
                   19    20    21    22    23    24    25                        
                   26    27    28    29    30    31                              
                                                                                 
 
63 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
ERROR: 
ORA-01039: insufficient privileges on underlying objects of the view  
 
 
SP2-0612: Error generating AUTOTRACE EXPLAIN report 
SQL> SPOOL OFF 
SQL> cl scr 
 
SQL> COLUMN DEPARTMENTS FORMAT A15 
SQL> COLUMN SUM(Sal) FORMAT 99999 
SQL> SELECT 
  2  DECODE(GROUPING(Deptno), 
  3    1, 'All Departments', 
  4    Deptno) Departments, 
  5    SUM(Sal) 
  6  FROM Emp 
  7  GROUP BY ROLLUP(Deptno); 
 
DEPARTMENTS     SUM(SAL)                                                         
--------------- --------                                                         
 
10                  8750                                                         
20                 10875                                                         
30                  9400                                                         
All Departments    29025                                                         
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 
  2  DECODE(GROUPING(Deptno), 
  3   1, 'All Departments Investment : ', 
  4   'Department '||Deptno||' Investments : ') Departments, 
  5    SUM(Sal) 
  6  FROM Emp 
  7* GROUP BY ROLLUP(Deptno) 
SQL> COLUMN DEPARTMENTS FORMAT A25 
SQL> / 
 
DEPARTMENTS               SUM(SAL)                                               
------------------------- --------                                               
Department 10 Investments     8750                                               
 :                                                                               
                                                                                 
Department 20 Investments    10875                                               
 :                                                                               
                                                                                 
Department 30 Investments     9400                                               
 :                                                                               
                                                                                 
All Departments Investmen    29025                                               
t :                                                                              
 
DEPARTMENTS               SUM(SAL)                                               
------------------------- --------                                               
                                                                                 
 
SQL> COLUMN DEPARTMENTS FORMAT A28 
SQL> / 
 
DEPARTMENTS                  SUM(SAL)                                            
---------------------------- --------                                            
Department 10 Investments :      8750                                            
Department 20 Investments :     10875                                            
Department 30 Investments :      9400                                            
All Departments Investment :    29025                                            
                                                                                 
                                                                                 
 
SQL> cl scr 
 
SQL> R 
  1  SELECT 
  2  DECODE(GROUPING(Deptno), 
  3   1, 'All Departments Investment : ', 
  4   'Department '||Deptno||' Investments : ') Departments, 
  5    SUM(Sal) 
  6  FROM Emp 
 
  7* GROUP BY ROLLUP(Deptno) 
 
DEPARTMENTS                  SUM(SAL)                                            
---------------------------- --------                                            
Department 10 Investments :      8750                                            
Department 20 Investments :     10875                                            
Department 30 Investments :      9400                                            
All Departments Investment :    29025                                            
                                                                                 
                                                                                 
 
SQL> cl scr 
 
SQL> SELECT 
  2  DECODE(GROUPING(Job), 
  3    1, 'All Designations', 
  4    Job) Designations, 
  5    SUM(Sal) 
  6  FROM Emp 
  7  GROUP BY ROLLUP(Job); 
 
DESIGNATIONS     SUM(SAL)                                                        
---------------- --------                                                        
ANALYST              6000                                                        
CLERK                4150                                                        
MANAGER              8275                                                        
PRESIDENT            5000                                                        
SALESMAN             5600                                                        
All Designations    29025                                                        
 
6 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT 
  2  DECODE(GROUPING(Deptno), 
  3    1 , 'All Departments', 
  4    Deptno) Departments, 
  5  DECODE(GROUPING(Job), 
  6    1, 'All Designations', 
  7    Job) Designations, 
  8  SUM(Sal) 
  9  FROM Emp 
 10  GROUP BY CUBE(Deptno, Job) 
 11  ORDER BY Deptno; 
 
DEPARTMENTS                  DESIGNATIONS     SUM(SAL)                           
---------------------------- ---------------- --------                           
10                           CLERK                1300                           
10                           MANAGER              2450                           
10                           PRESIDENT            5000                           
10                           All Designations     8750                           
20                           ANALYST              6000                           
20                           CLERK                1900                           
20                           MANAGER              2975                           
20                           All Designations    10875                           
30                           CLERK                 950                           
 
30                           MANAGER              2850                           
30                           SALESMAN             5600                           
 
DEPARTMENTS                  DESIGNATIONS     SUM(SAL)                           
---------------------------- ---------------- --------                           
30                           All Designations     9400                           
All Departments              ANALYST              6000                           
All Departments              CLERK                4150                           
All Departments              MANAGER              8275                           
All Departments              PRESIDENT            5000                           
All Departments              SALESMAN             5600                           
All Departments              All Designations    29025                           
 
18 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT Deptno, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY Deptno; 
 
    DEPTNO SUM(SAL)                                                              
---------- --------                                                              
        10     8750                                                              
        20    10875                                                              
        30     9400                                                              
 
SQL> SELECT Job, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY Job; 
 
JOB       SUM(SAL)                                                               
--------- --------                                                               
ANALYST       6000                                                               
CLERK         4150                                                               
MANAGER       8275                                                               
PRESIDENT     5000                                                               
SALESMAN      5600                                                               
 
SQL> SELECT Deptno, Job, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY 
  4  (Deptno, Job); 
 
    DEPTNO JOB       SUM(SAL)                                                    
---------- --------- --------                                                    
        10 CLERK         1300                                                    
        10 MANAGER       2450                                                    
        10 PRESIDENT     5000                                                    
        20 CLERK         1900                                                    
        20 ANALYST       6000                                                    
        20 MANAGER       2975                                                    
        30 CLERK          950                                                    
        30 MANAGER       2850                                                    
        30 SALESMAN      5600                                                    
 
9 rows selected. 
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Deptno, Job, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY 
  4* GROUPING SETS(Deptno, Job) 
SQL> / 
 
    DEPTNO JOB       SUM(SAL)                                                    
---------- --------- --------                                                    
        10               8750                                                    
        20              10875                                                    
        30               9400                                                    
           ANALYST       6000                                                    
           CLERK         4150                                                    
           MANAGER       8275                                                    
           PRESIDENT     5000                                                    
           SALESMAN      5600                                                    
 
8 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT  
  2  Deptno,  
  3  Job,  
  4  MGR, 
  5  TO_CHAR(HireDate, 'YYYY') Year, 
  6  TO_CHAR(HireDate, 'Q') Quarter, 
  7  TO_CHAR(HireDate, 'Month') Month, 
  8  TO_CHAR(HireDate, 'Day') WeekDay 
  9  SUM(Sal) 
 10  FROM Emp 
 11  GROUP BY 
 12  GROUPING SETS 
 13  ( 
 14   Deptno,  
 15   Job, 
 16   MGR, 
 17   TO_CHAR(HireDate, 'YYYY'), 
 18   TO_CHAR(HireDate, 'Q'), 
 19   TO_CHAR(HireDate, 'Month'), 
 20   TO_CHAR(HireDate, 'Day') 
 21* ) 
SQL> / 
SUM(Sal) 
* 
ERROR at line 9: 
ORA-00923: FROM keyword not found where expected  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
 
  1  SELECT  
  2  Deptno,  
  3  Job,  
  4  MGR, 
  5  TO_CHAR(HireDate, 'YYYY') Year, 
  6  TO_CHAR(HireDate, 'Q') Quarter, 
  7  TO_CHAR(HireDate, 'Month') Month, 
  8  TO_CHAR(HireDate, 'Day') WeekDay, 
  9  SUM(Sal) 
 10  FROM Emp 
 11  GROUP BY 
 12  GROUPING SETS 
 13  ( 
 14   Deptno,  
 15   Job, 
 16   MGR, 
 17   TO_CHAR(HireDate, 'YYYY'), 
 18   TO_CHAR(HireDate, 'Q'), 
 19   TO_CHAR(HireDate, 'Month'), 
 20   TO_CHAR(HireDate, 'Day') 
 21* ) 
SQL> / 
 
    DEPTNO JOB              MGR YEAR Q MONTH     WEEKDAY   SUM(SAL)              
---------- --------- ---------- ---- - --------- --------- --------              
        10                                                     8750              
        20                                                    10875              
        30                                                     9400              
           ANALYST                                             6000              
           CLERK                                               4150              
           MANAGER                                             8275              
           PRESIDENT                                           5000              
           SALESMAN                                            5600              
                           7566                                6000              
                           7698                                6550              
                           7782                                1300              
 
    DEPTNO JOB              MGR YEAR Q MONTH     WEEKDAY   SUM(SAL)              
---------- --------- ---------- ---- - --------- --------- --------              
                           7788                                1100              
                           7839                                8275              
                           7902                                 800              
                                                               5000              
                                1980                            800              
                                1981                          22825              
                                1982                           4300              
                                1983                           1100              
                                     1                         5250              
                                     2                         8275              
                                     3                         2750              
 
    DEPTNO JOB              MGR YEAR Q MONTH     WEEKDAY   SUM(SAL)              
---------- --------- ---------- ---- - --------- --------- --------              
                                     4                        12750              
                                       April                   2975              
                                       December                7750              
                                       February                2850              
 
                                       January                 2400              
                                       June                    2450              
                                       May                     2850              
                                       November                5000              
                                       September               2750              
                                                 Friday        4450              
                                                 Monday        1250              
 
    DEPTNO JOB              MGR YEAR Q MONTH     WEEKDAY   SUM(SAL)              
---------- --------- ---------- ---- - --------- --------- --------              
                                                 Saturday      1300              
                                                 Sunday        1250              
                                                 Thursday      9925              
                                                 Tuesday       8950              
                                                 Wednesday     1900              
 
38 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT  
  2  Deptno,  
  3  Job,  
  4  MGR, 
  5  TO_CHAR(HireDate, 'YYYY') Year, 
  6  TO_CHAR(HireDate, 'Q') Quarter, 
  7  TO_CHAR(HireDate, 'Month') Month, 
  8  TO_CHAR(HireDate, 'Day') WeekDay, 
  9  SUM(Sal) 
 10  FROM Emp 
 11  GROUP BY 
 12  GROUPING SETS 
 13  ( 
 14   Deptno,  
 15   Job, 
 16   MGR, 
 17   TO_CHAR(HireDate, 'YYYY'), 
 18   TO_CHAR(HireDate, 'Q'), 
 19   TO_CHAR(HireDate, 'Month'), 
 20   TO_CHAR(HireDate, 'Day') 
 21  ) 
 22* HAVING GROUPING(&ColumnName) = 0 
SQL> SET VERIFY OFF 
SQL> / 
Enter value for columnname: Deptno 
 
    DEPTNO JOB              MGR YEAR Q MONTH     WEEKDAY   SUM(SAL)              
---------- --------- ---------- ---- - --------- --------- --------              
        10                                                     8750              
        20                                                    10875              
        30                                                     9400              
 
SQL> / 
Enter value for columnname: Job 
 
    DEPTNO JOB              MGR YEAR Q MONTH     WEEKDAY   SUM(SAL)              
 
---------- --------- ---------- ---- - --------- --------- --------              
           ANALYST                                             6000              
           CLERK                                               4150              
           MANAGER                                             8275              
           PRESIDENT                                           5000              
           SALESMAN                                            5600              
 
SQL> / 
Enter value for columnname: MGR 
 
    DEPTNO JOB              MGR YEAR Q MONTH     WEEKDAY   SUM(SAL)              
---------- --------- ---------- ---- - --------- --------- --------              
                           7566                                6000              
                           7698                                6550              
                           7782                                1300              
                           7788                                1100              
                           7839                                8275              
                           7902                                 800              
                                                               5000              
 
7 rows selected. 
 
SQL> / 
Enter value for columnname: TO_CHAR(HireDate, 'YYYY') 
 
    DEPTNO JOB              MGR YEAR Q MONTH     WEEKDAY   SUM(SAL)              
---------- --------- ---------- ---- - --------- --------- --------              
                                1980                            800              
                                1981                          22825              
                                1982                           4300              
                                1983                           1100              
 
SQL> SET AUTOTRACE ON EXPLAIN 
SQL> / 
Enter value for columnname: Deptno 
 
    DEPTNO JOB              MGR YEAR Q MONTH     WEEKDAY   SUM(SAL)              
---------- --------- ---------- ---- - --------- --------- --------              
        10                                                     8750              
        20                                                    10875              
        30                                                     9400              
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=36           
          4)                                                                     
                                                                                 
   1    0   FILTER                                                               
   2    1     SORT (GROUP BY) (Cost=4 Card=14 Bytes=364)                         
   3    2       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 B           
          ytes=364)                                                              
                                                                                 
 
 
 
SQL> ED 
 
Wrote file afiedt.buf 
 
  1  SELECT  
  2  Deptno,  
  3  Job,  
  4  MGR, 
  5  TO_CHAR(HireDate, 'YYYY') Year, 
  6  TO_CHAR(HireDate, 'Q') Quarter, 
  7  TO_CHAR(HireDate, 'Month') Month, 
  8  TO_CHAR(HireDate, 'Day') WeekDay, 
  9  SUM(Sal) 
 10  FROM Emp 
 11  GROUP BY 
 12  GROUPING SETS 
 13  ( 
 14   Deptno,  
 15   Job, 
 16   MGR, 
 17   TO_CHAR(HireDate, 'YYYY'), 
 18   TO_CHAR(HireDate, 'Q'), 
 19   TO_CHAR(HireDate, 'Month'), 
 20   TO_CHAR(HireDate, 'Day') 
 21* ) 
 22  / 
 
    DEPTNO JOB              MGR YEAR Q MONTH     WEEKDAY   SUM(SAL)              
---------- --------- ---------- ---- - --------- --------- --------              
        10                                                     8750              
        20                                                    10875              
        30                                                     9400              
           ANALYST                                             6000              
           CLERK                                               4150              
           MANAGER                                             8275              
           PRESIDENT                                           5000              
           SALESMAN                                            5600              
                           7566                                6000              
                           7698                                6550              
                           7782                                1300              
 
    DEPTNO JOB              MGR YEAR Q MONTH     WEEKDAY   SUM(SAL)              
---------- --------- ---------- ---- - --------- --------- --------              
                           7788                                1100              
                           7839                                8275              
                           7902                                 800              
                                                               5000              
                                1980                            800              
                                1981                          22825              
                                1982                           4300              
                                1983                           1100              
                                     1                         5250              
                                     2                         8275              
                                     3                         2750              
 
    DEPTNO JOB              MGR YEAR Q MONTH     WEEKDAY   SUM(SAL)              
---------- --------- ---------- ---- - --------- --------- --------              
                                     4                        12750              
                                       April                   2975              
 
                                       December                7750              
                                       February                2850              
                                       January                 2400              
                                       June                    2450              
                                       May                     2850              
                                       November                5000              
                                       September               2750              
                                                 Friday        4450              
                                                 Monday        1250              
 
    DEPTNO JOB              MGR YEAR Q MONTH     WEEKDAY   SUM(SAL)              
---------- --------- ---------- ---- - --------- --------- --------              
                                                 Saturday      1300              
                                                 Sunday        1250              
                                                 Thursday      9925              
                                                 Tuesday       8950              
                                                 Wednesday     1900              
 
38 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=26 Card=14 Bytes=8           
          82)                                                                    
                                                                                 
   1    0   TEMP TABLE TRANSFORMATION                                            
   2    1     LOAD AS SELECT                                                     
   3    2       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 B           
          ytes=756)                                                              
                                                                                 
   4    1     LOAD AS SELECT                                                     
   5    4       SORT (GROUP BY) (Cost=3 Card=1 Bytes=26)                         
   6    5         TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6604_14C83BF'            
          (TABLE (TEMP)) (Cost=2 Card=1 Bytes=26)                                
                                                                                 
   7    1     LOAD AS SELECT                                                     
   8    7       SORT (GROUP BY) (Cost=3 Card=1 Bytes=19)                         
   9    8         TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6604_14C83BF'            
          (TABLE (TEMP)) (Cost=2 Card=1 Bytes=19)                                
                                                                                 
  10    1     LOAD AS SELECT                                                     
  11   10       SORT (GROUP BY) (Cost=3 Card=1 Bytes=26)                         
  12   11         TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6604_14C83BF'            
          (TABLE (TEMP)) (Cost=2 Card=1 Bytes=26)                                
                                                                                 
  13    1     LOAD AS SELECT                                                     
  14   13       SORT (GROUP BY) (Cost=3 Card=1 Bytes=17)                         
  15   14         TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6604_14C83BF'            
          (TABLE (TEMP)) (Cost=2 Card=1 Bytes=17)                                
                                                                                 
  16    1     LOAD AS SELECT                                                     
  17   16       SORT (GROUP BY) (Cost=3 Card=1 Bytes=15)                         
  18   17         TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6604_14C83BF'            
          (TABLE (TEMP)) (Cost=2 Card=1 Bytes=15)                                
                                                                                 
  19    1     LOAD AS SELECT                                                     
 
  20   19       SORT (GROUP BY) (Cost=3 Card=1 Bytes=19)                         
  21   20         TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6604_14C83BF'            
          (TABLE (TEMP)) (Cost=2 Card=1 Bytes=19)                                
                                                                                 
  22    1     LOAD AS SELECT                                                     
  23   22       SORT (GROUP BY) (Cost=3 Card=1 Bytes=19)                         
  24   23         TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6604_14C83BF'            
          (TABLE (TEMP)) (Cost=2 Card=1 Bytes=19)                                
                                                                                 
  25    1     VIEW (Cost=2 Card=1 Bytes=63)                                      
  26   25       TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6605_14C83BF' (T           
          ABLE (TEMP)) (Cost=2 Card=1 Bytes=63)                                  
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> SET AUTOTRACE OFF EXPLAIN 
SQL> cl scr 
 
SQL> SELECT Ename, Deptno, 
  2  CASE Deptno 
  3   WHEN 10 THEN 'ACCOUNTING' 
  4   WHEN 20 THEN 'RESEARCH' 
  5   WHEN 30 THEN 'SALES' 
  6   WHEN 40 THEN 'OPERATIONS' 
  7   ELSE 'NOT FOUND' 
  8  END 
  9  FROM Emp; 
 
ENAME          DEPTNO CASEDEPTNO                                                 
---------- ---------- ----------                                                 
KING               10 ACCOUNTING                                                 
BLAKE              30 SALES                                                      
CLARK              10 ACCOUNTING                                                 
JONES              20 RESEARCH                                                   
MARTIN             30 SALES                                                      
ALLEN              30 SALES                                                      
TURNER             30 SALES                                                      
JAMES              30 SALES                                                      
WARD               30 SALES                                                      
FORD               20 RESEARCH                                                   
SMITH              20 RESEARCH                                                   
 
ENAME          DEPTNO CASEDEPTNO                                                 
---------- ---------- ----------                                                 
SCOTT              20 RESEARCH                                                   
ADAMS              20 RESEARCH                                                   
MILLER             10 ACCOUNTING                                                 
 
14 rows selected. 
 
SQL> SELECT Ename, Deptno, 
  2  DECODE(Deptno 
  3   , 10 , 'ACCOUNTING' 
  4   , 20 , 'RESEARCH' 
 
  5   , 30 , 'SALES' 
  6   , 40 , 'OPERATIONS' 
  7   , 'NOT FOUND') Dept 
  8  FROM Emp; 
 
ENAME          DEPTNO DEPT                                                       
---------- ---------- ----------                                                 
KING               10 ACCOUNTING                                                 
BLAKE              30 SALES                                                      
CLARK              10 ACCOUNTING                                                 
JONES              20 RESEARCH                                                   
MARTIN             30 SALES                                                      
ALLEN              30 SALES                                                      
TURNER             30 SALES                                                      
JAMES              30 SALES                                                      
WARD               30 SALES                                                      
FORD               20 RESEARCH                                                   
SMITH              20 RESEARCH                                                   
 
ENAME          DEPTNO DEPT                                                       
---------- ---------- ----------                                                 
SCOTT              20 RESEARCH                                                   
ADAMS              20 RESEARCH                                                   
MILLER             10 ACCOUNTING                                                 
 
14 rows selected. 
 
SQL> SELECT Ename, Deptno, 
  2  CASE 
  3  WHEN Deptno = 10 THEN 'ACCOUNTING' 
  4  WHEN Deptno = 20 THEN 'RESEARCH' 
  5  WHEN Deptno = 30 THEN 'SALES' 
  6  WHEN Deptno = 40 THEN 'OPERATIONS' 
  7  ELSE 'Not Specified' 
  8  END 
  9  FROM Emp; 
 
ENAME          DEPTNO CASEWHENDEPTN                                              
---------- ---------- -------------                                              
KING               10 ACCOUNTING                                                 
BLAKE              30 SALES                                                      
CLARK              10 ACCOUNTING                                                 
JONES              20 RESEARCH                                                   
MARTIN             30 SALES                                                      
ALLEN              30 SALES                                                      
TURNER             30 SALES                                                      
JAMES              30 SALES                                                      
WARD               30 SALES                                                      
FORD               20 RESEARCH                                                   
SMITH              20 RESEARCH                                                   
 
ENAME          DEPTNO CASEWHENDEPTN                                              
---------- ---------- -------------                                              
SCOTT              20 RESEARCH                                                   
ADAMS              20 RESEARCH                                                   
MILLER             10 ACCOUNTING                                                 
 
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT Ename, Sal, 
  2  CASE 
  3  WHEN Sal >= 800 AND Sal <= 2000 
  4  THEN 'LOWEST PAY' 
  5  WHEN Sal >= 2001 AND Sal <= 4000 
  6  THEN 'MODERATE PAY' 
  7  ELSE 'HIGH PAY'  END 
  8  FROM Emp; 
 
ENAME             SAL CASEWHENSAL>                                               
---------- ---------- ------------                                               
KING             5000 HIGH PAY                                                   
BLAKE            2850 MODERATE PAY                                               
CLARK            2450 MODERATE PAY                                               
JONES            2975 MODERATE PAY                                               
MARTIN           1250 LOWEST PAY                                                 
ALLEN            1600 LOWEST PAY                                                 
TURNER           1500 LOWEST PAY                                                 
JAMES             950 LOWEST PAY                                                 
WARD             1250 LOWEST PAY                                                 
FORD             3000 MODERATE PAY                                               
SMITH             800 LOWEST PAY                                                 
 
ENAME             SAL CASEWHENSAL>                                               
---------- ---------- ------------                                               
SCOTT            3000 MODERATE PAY                                               
ADAMS            1100 LOWEST PAY                                                 
MILLER           1300 LOWEST PAY                                                 
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Sal, 
  2  CASE 
  3  WHEN Sal BETWEEN 800 AND 2000 
  4  THEN 'LOWEST PAY' 
  5  WHEN Sal BETWEEN 2001 AND 4000 
  6  THEN 'MODERATE PAY' 
  7  ELSE 'HIGH PAY'  END 
  8* FROM Emp 
SQL> / 
 
ENAME             SAL CASEWHENSALB                                               
---------- ---------- ------------                                               
KING             5000 HIGH PAY                                                   
BLAKE            2850 MODERATE PAY                                               
CLARK            2450 MODERATE PAY                                               
JONES            2975 MODERATE PAY                                               
MARTIN           1250 LOWEST PAY                                                 
ALLEN            1600 LOWEST PAY                                                 
TURNER           1500 LOWEST PAY                                                 
 
JAMES             950 LOWEST PAY                                                 
WARD             1250 LOWEST PAY                                                 
FORD             3000 MODERATE PAY                                               
SMITH             800 LOWEST PAY                                                 
 
ENAME             SAL CASEWHENSALB                                               
---------- ---------- ------------                                               
SCOTT            3000 MODERATE PAY                                               
ADAMS            1100 LOWEST PAY                                                 
MILLER           1300 LOWEST PAY                                                 
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Sal, 
  2  CASE 
  3  WHEN Sal BETWEEN 800 AND 2000 AND Job IN('SALESMAN', 'ANALYST') 
  4  THEN 'LOWEST PAY' 
  5  WHEN Sal BETWEEN 2001 AND 4000 
  6  THEN 'MODERATE PAY' 
  7  ELSE 'HIGH PAY'  END 
  8* FROM Emp 
SQL> / 
 
ENAME             SAL CASEWHENSALB                                               
---------- ---------- ------------                                               
KING             5000 HIGH PAY                                                   
BLAKE            2850 MODERATE PAY                                               
CLARK            2450 MODERATE PAY                                               
JONES            2975 MODERATE PAY                                               
MARTIN           1250 LOWEST PAY                                                 
ALLEN            1600 LOWEST PAY                                                 
TURNER           1500 LOWEST PAY                                                 
JAMES             950 HIGH PAY                                                   
WARD             1250 LOWEST PAY                                                 
FORD             3000 MODERATE PAY                                               
SMITH             800 HIGH PAY                                                   
 
ENAME             SAL CASEWHENSALB                                               
---------- ---------- ------------                                               
SCOTT            3000 MODERATE PAY                                               
ADAMS            1100 HIGH PAY                                                   
MILLER           1300 HIGH PAY                                                   
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Sal, Job, 
  2  CASE 
  3  WHEN Sal BETWEEN 800 AND 2000 AND Job IN('SALESMAN', 'ANALYST') 
  4  THEN 'LOWEST PAY' 
  5  WHEN Sal BETWEEN 2001 AND 4000 
  6  THEN 'MODERATE PAY' 
 
  7  ELSE 'HIGH PAY'  END 
  8* FROM Emp 
SQL> / 
 
ENAME             SAL JOB       CASEWHENSALB                                     
---------- ---------- --------- ------------                                     
KING             5000 PRESIDENT HIGH PAY                                         
BLAKE            2850 MANAGER   MODERATE PAY                                     
CLARK            2450 MANAGER   MODERATE PAY                                     
JONES            2975 MANAGER   MODERATE PAY                                     
MARTIN           1250 SALESMAN  LOWEST PAY                                       
ALLEN            1600 SALESMAN  LOWEST PAY                                       
TURNER           1500 SALESMAN  LOWEST PAY                                       
JAMES             950 CLERK     HIGH PAY                                         
WARD             1250 SALESMAN  LOWEST PAY                                       
FORD             3000 ANALYST   MODERATE PAY                                     
SMITH             800 CLERK     HIGH PAY                                         
 
ENAME             SAL JOB       CASEWHENSALB                                     
---------- ---------- --------- ------------                                     
SCOTT            3000 ANALYST   MODERATE PAY                                     
ADAMS            1100 CLERK     HIGH PAY                                         
MILLER           1300 CLERK     HIGH PAY                                         
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Sal, Job, 
  2  CASE 
  3  WHEN Sal BETWEEN 800 AND 2000 AND Job IN('SALESMAN', 'ANALYST') 
  4  THEN 'LOWEST PAY' 
  5  WHEN Sal BETWEEN 2001 AND 4000 
  6  THEN 'MODERATE PAY' 
  7  ELSE 'HIGH PAY'  END 
  8* FROM Emp 
SQL> cl scr 
 
SQL> CONN SYSTEM 
Connected. 
SQL> GRANT QUERY REWRITE 
  2  TO SCOTT; 
 
Grant succeeded. 
 
SQL> GRANT 
  2  CREATE MATERIALIZED VIEW 
  3  TO SCOTT; 
 
Grant succeeded. 
 
SQL> GRANT ALTER SESSION 
  2  TO SCOTT; 
 
Grant succeeded. 
 
 
SQL> CONN SCOTT/TIGER 
Connected. 
SQL> ALTER SESSION 
  2  SET QUERY_REWRITE_ENABLED = TRUE; 
 
Session altered. 
 
SQL> cl scr 
 
SQL> SELECT Deptno , Job, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY Deptno, Job; 
 
    DEPTNO JOB       SUM(SAL)                                                    
---------- --------- --------                                                    
        10 CLERK         1300                                                    
        10 MANAGER       2450                                                    
        10 PRESIDENT     5000                                                    
        20 CLERK         1900                                                    
        20 ANALYST       6000                                                    
        20 MANAGER       2975                                                    
        30 CLERK          950                                                    
        30 MANAGER       2850                                                    
        30 SALESMAN      5600                                                    
 
9 rows selected. 
 
SQL> CREATE OR REPLACE VIEW DeptJobSalSum 
  2  AS 
  3  SELECT Deptno, Job, SUM(Sal) SalSum 
  4  FROM Emp 
  5  GROUP BY Deptno, Job; 
 
View created. 
 
SQL> SET AUTOTRACE ON EXPLAIN 
SQL> SELECT Deptno, Job, SUM(Sal) SalSum 
  2  FROM Emp 
  3  GROUP BY Deptno, Job; 
 
    DEPTNO JOB           SALSUM                                                  
---------- --------- ----------                                                  
        10 CLERK           1300                                                  
        10 MANAGER         2450                                                  
        10 PRESIDENT       5000                                                  
        20 CLERK           1900                                                  
        20 ANALYST         6000                                                  
        20 MANAGER         2975                                                  
        30 CLERK            950                                                  
        30 MANAGER         2850                                                  
        30 SALESMAN        5600                                                  
 
9 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
 
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=44           
          8)                                                                     
                                                                                 
   1    0   SORT (GROUP BY) (Cost=4 Card=14 Bytes=448)                           
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
          es=448)                                                                
                                                                                 
 
 
 
SQL> SELECT * FROM DeptJobSalSum; 
 
    DEPTNO JOB           SALSUM                                                  
---------- --------- ----------                                                  
        10 CLERK           1300                                                  
        10 MANAGER         2450                                                  
        10 PRESIDENT       5000                                                  
        20 CLERK           1900                                                  
        20 ANALYST         6000                                                  
        20 MANAGER         2975                                                  
        30 CLERK            950                                                  
        30 MANAGER         2850                                                  
        30 SALESMAN        5600                                                  
 
9 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=44           
          8)                                                                     
                                                                                 
   1    0   SORT (GROUP BY) (Cost=4 Card=14 Bytes=448)                           
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
          es=448)                                                                
                                                                                 
 
 
 
SQL> SELECT Deptno, SUM(SALSUM) 
  2  FROM DeptJobSalSum 
  3  GROUP BY Deptno; 
 
    DEPTNO SUM(SALSUM)                                                           
---------- -----------                                                           
        10        8750                                                           
        20       10875                                                           
        30        9400                                                           
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=36           
          4)                                                                     
                                                                                 
   1    0   SORT (GROUP BY NOSORT) (Cost=4 Card=14 Bytes=364)                    
   2    1     VIEW OF 'DEPTJOBSALSUM' (VIEW) (Cost=4 Card=14 Bytes=364           
 
          )                                                                      
                                                                                 
   3    2       SORT (GROUP BY) (Cost=4 Card=14 Bytes=448)                       
   4    3         TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14           
           Bytes=448)                                                            
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> CREATE MATERIALIZED VIEW 
  2  EMP_SUM 
  3  ENABLE QUERY REWRITE 
  4  AS 
  5  SELECT Deptno , Job, SUM(Sal) 
  6  FROM Emp 
  7  GROUP BY Deptno, Job; 
 
Materialized view created. 
 
SQL> SELECT Deptno , Job, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY Deptno, Job; 
 
    DEPTNO JOB       SUM(SAL)                                                    
---------- --------- --------                                                    
        10 CLERK         1300                                                    
        10 MANAGER       2450                                                    
        10 PRESIDENT     5000                                                    
        20 CLERK         1900                                                    
        20 ANALYST       6000                                                    
        20 MANAGER       2975                                                    
        30 CLERK          950                                                    
        30 MANAGER       2850                                                    
        30 SALESMAN      5600                                                    
 
9 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=9 Bytes=288           
          )                                                                      
                                                                                 
   1    0   MAT_VIEW REWRITE ACCESS (FULL) OF 'EMP_SUM' (MAT_VIEW REWR           
          ITE) (Cost=3 Card=9 Bytes=288)                                         
                                                                                 
 
 
 
SQL> SELECT Deptno, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY Deptno; 
 
    DEPTNO SUM(SAL)                                                              
---------- --------                                                              
 
        10     8750                                                              
        20    10875                                                              
        30     9400                                                              
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=9 Bytes=234           
          )                                                                      
                                                                                 
   1    0   SORT (GROUP BY) (Cost=4 Card=9 Bytes=234)                            
   2    1     MAT_VIEW REWRITE ACCESS (FULL) OF 'EMP_SUM' (MAT_VIEW RE           
          WRITE) (Cost=3 Card=9 Bytes=234)                                       
                                                                                 
 
 
 
SQL> SELECT Job, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY Deptno; 
SELECT Job, SUM(Sal) 
       * 
ERROR at line 1: 
ORA-00979: not a GROUP BY expression  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Job, SUM(Sal) 
  2  FROM Emp 
  3* GROUP BY Job 
SQL> / 
 
JOB       SUM(SAL)                                                               
--------- --------                                                               
ANALYST       6000                                                               
CLERK         4150                                                               
MANAGER       8275                                                               
PRESIDENT     5000                                                               
SALESMAN      5600                                                               
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=9 Bytes=171           
          )                                                                      
                                                                                 
   1    0   SORT (GROUP BY) (Cost=4 Card=9 Bytes=171)                            
   2    1     MAT_VIEW REWRITE ACCESS (FULL) OF 'EMP_SUM' (MAT_VIEW RE           
          WRITE) (Cost=3 Card=9 Bytes=171)                                       
                                                                                 
 
 
 
SQL> SELECT SUM(Sal) 
  2  FROM Emp; 
 
 
SUM(SAL)                                                                         
--------                                                                         
   29025                                                                         
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=13)           
   1    0   SORT (AGGREGATE)                                                     
   2    1     MAT_VIEW REWRITE ACCESS (FULL) OF 'EMP_SUM' (MAT_VIEW RE           
          WRITE) (Cost=3 Card=9 Bytes=117)                                       
                                                                                 
 
 
 
SQL> SELECT Deptno, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY ROLLUP(Deptno); 
 
    DEPTNO SUM(SAL)                                                              
---------- --------                                                              
        10     8750                                                              
        20    10875                                                              
        30     9400                                                              
              29025                                                              
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=9 Bytes=234           
          )                                                                      
                                                                                 
   1    0   SORT (GROUP BY ROLLUP) (Cost=4 Card=9 Bytes=234)                     
   2    1     MAT_VIEW REWRITE ACCESS (FULL) OF 'EMP_SUM' (MAT_VIEW RE           
          WRITE) (Cost=3 Card=9 Bytes=234)                                       
                                                                                 
 
 
 
SQL> SELECT Job, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY ROLLUP(Job); 
 
JOB       SUM(SAL)                                                               
--------- --------                                                               
ANALYST       6000                                                               
CLERK         4150                                                               
MANAGER       8275                                                               
PRESIDENT     5000                                                               
SALESMAN      5600                                                               
             29025                                                               
 
6 rows selected. 
 
 
Execution Plan 
 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=9 Bytes=171           
          )                                                                      
                                                                                 
   1    0   SORT (GROUP BY ROLLUP) (Cost=4 Card=9 Bytes=171)                     
   2    1     MAT_VIEW REWRITE ACCESS (FULL) OF 'EMP_SUM' (MAT_VIEW RE           
          WRITE) (Cost=3 Card=9 Bytes=171)                                       
                                                                                 
 
 
 
SQL> SELECT Deptno, Job, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY ROLLUP(Deptno, Job); 
 
    DEPTNO JOB       SUM(SAL)                                                    
---------- --------- --------                                                    
        10 CLERK         1300                                                    
        10 MANAGER       2450                                                    
        10 PRESIDENT     5000                                                    
        10               8750                                                    
        20 CLERK         1900                                                    
        20 ANALYST       6000                                                    
        20 MANAGER       2975                                                    
        20              10875                                                    
        30 CLERK          950                                                    
        30 MANAGER       2850                                                    
        30 SALESMAN      5600                                                    
 
    DEPTNO JOB       SUM(SAL)                                                    
---------- --------- --------                                                    
        30               9400                                                    
                        29025                                                    
 
13 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=9 Bytes=288           
          )                                                                      
                                                                                 
   1    0   SORT (GROUP BY ROLLUP) (Cost=4 Card=9 Bytes=288)                     
   2    1     MAT_VIEW REWRITE ACCESS (FULL) OF 'EMP_SUM' (MAT_VIEW RE           
          WRITE) (Cost=3 Card=9 Bytes=288)                                       
                                                                                 
 
 
 
SQL> SELECT Deptno, Job, SUm(Sal) 
  2  FROm Emp 
  3  GROUP BY CUBE(Deptno, Job) 
  4  ORDER BY Deptno; 
 
    DEPTNO JOB       SUM(SAL)                                                    
---------- --------- --------                                                    
        10 CLERK         1300                                                    
 
        10 MANAGER       2450                                                    
        10 PRESIDENT     5000                                                    
        10               8750                                                    
        20 ANALYST       6000                                                    
        20 CLERK         1900                                                    
        20 MANAGER       2975                                                    
        20              10875                                                    
        30 CLERK          950                                                    
        30 MANAGER       2850                                                    
        30 SALESMAN      5600                                                    
 
    DEPTNO JOB       SUM(SAL)                                                    
---------- --------- --------                                                    
        30               9400                                                    
           ANALYST       6000                                                    
           CLERK         4150                                                    
           MANAGER       8275                                                    
           PRESIDENT     5000                                                    
           SALESMAN      5600                                                    
                        29025                                                    
 
18 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=9 Bytes=288           
          )                                                                      
                                                                                 
   1    0   SORT (GROUP BY) (Cost=4 Card=9 Bytes=288)                            
   2    1     GENERATE (CUBE) (Cost=4 Card=9 Bytes=288)                          
   3    2       SORT (GROUP BY) (Cost=4 Card=9 Bytes=288)                        
   4    3         MAT_VIEW REWRITE ACCESS (FULL) OF 'EMP_SUM' (MAT_VIE           
          W REWRITE) (Cost=3 Card=9 Bytes=288)                                   
                                                                                 
 
 
 
SQL> SELECT Deptno, Job, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY Deptno, ROLLUP(Job); 
 
    DEPTNO JOB       SUM(SAL)                                                    
---------- --------- --------                                                    
        10 CLERK         1300                                                    
        10 MANAGER       2450                                                    
        10 PRESIDENT     5000                                                    
        10               8750                                                    
        20 CLERK         1900                                                    
        20 ANALYST       6000                                                    
        20 MANAGER       2975                                                    
        20              10875                                                    
        30 CLERK          950                                                    
        30 MANAGER       2850                                                    
        30 SALESMAN      5600                                                    
 
    DEPTNO JOB       SUM(SAL)                                                    
 
---------- --------- --------                                                    
        30               9400                                                    
 
12 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=9 Bytes=288           
          )                                                                      
                                                                                 
   1    0   SORT (GROUP BY ROLLUP) (Cost=4 Card=9 Bytes=288)                     
   2    1     MAT_VIEW REWRITE ACCESS (FULL) OF 'EMP_SUM' (MAT_VIEW RE           
          WRITE) (Cost=3 Card=9 Bytes=288)                                       
                                                                                 
 
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Deptno, Job, SUM(Sal) 
  2  FROM Emp 
  3* GROUP BY Job, ROLLUP(Deptno) 
SQL> / 
 
    DEPTNO JOB       SUM(SAL)                                                    
---------- --------- --------                                                    
        10 CLERK         1300                                                    
        20 CLERK         1900                                                    
        30 CLERK          950                                                    
           CLERK         4150                                                    
        20 ANALYST       6000                                                    
           ANALYST       6000                                                    
        10 MANAGER       2450                                                    
        20 MANAGER       2975                                                    
        30 MANAGER       2850                                                    
           MANAGER       8275                                                    
        30 SALESMAN      5600                                                    
 
    DEPTNO JOB       SUM(SAL)                                                    
---------- --------- --------                                                    
           SALESMAN      5600                                                    
        10 PRESIDENT     5000                                                    
           PRESIDENT     5000                                                    
 
14 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=9 Bytes=288           
          )                                                                      
                                                                                 
   1    0   SORT (GROUP BY ROLLUP) (Cost=4 Card=9 Bytes=288)                     
   2    1     MAT_VIEW REWRITE ACCESS (FULL) OF 'EMP_SUM' (MAT_VIEW RE           
          WRITE) (Cost=3 Card=9 Bytes=288)                                       
 
                                                                                 
 
 
 
SQL> SLECT Deptno, Job, SUM(Sal) 
SP2-0734: unknown command beginning "SLECT Dept..." - rest of line ignored. 
SQL> SELECT Deptno, Job, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY 
  4  GROUPING SETS(Deptno, Job); 
 
    DEPTNO JOB       SUM(SAL)                                                    
---------- --------- --------                                                    
        10               8750                                                    
        20              10875                                                    
        30               9400                                                    
           ANALYST       6000                                                    
           CLERK         4150                                                    
           MANAGER       8275                                                    
           PRESIDENT     5000                                                    
           SALESMAN      5600                                                    
 
8 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=9 Bytes=28           
          8)                                                                     
                                                                                 
   1    0   TEMP TABLE TRANSFORMATION                                            
   2    1     LOAD AS SELECT                                                     
   3    2       MAT_VIEW ACCESS (FULL) OF 'EMP_SUM' (MAT_VIEW) (Cost=3           
           Card=9 Bytes=288)                                                     
                                                                                 
   4    1     LOAD AS SELECT                                                     
   5    4       SORT (GROUP BY) (Cost=3 Card=1 Bytes=26)                         
   6    5         TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D660A_14C83BF'            
          (TABLE (TEMP)) (Cost=2 Card=1 Bytes=26)                                
                                                                                 
   7    1     LOAD AS SELECT                                                     
   8    7       SORT (GROUP BY) (Cost=3 Card=1 Bytes=19)                         
   9    8         TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D660A_14C83BF'            
          (TABLE (TEMP)) (Cost=2 Card=1 Bytes=19)                                
                                                                                 
  10    1     VIEW (Cost=2 Card=1 Bytes=32)                                      
  11   10       TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D660B_14C83BF' (T           
          ABLE (TEMP)) (Cost=2 Card=1 Bytes=32)                                  
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> ED 
Wrote file afiedt.buf 
 
 
  1  SELECT  
  2  Deptno,  
  3  Job, 
  4  MGR, 
  5  TO_CHAR(HireDate, 'YYYY') Year, 
  6  TO_CHAR(HireDate, 'Q') Quarter, 
  7  TO_CHAR(HireDate, 'Month') Month, 
  8  TO_CHAR(HireDate, 'Day') WeekDay, 
  9  SUM(Sal) 
 10  FROM Emp 
 11  GROUP BY 
 12  GROUPING SETS 
 13  ( 
 14   Deptno,  
 15   Job, 
 16   MGR, 
 17   TO_CHAR(HireDate, 'YYYY'), 
 18   TO_CHAR(HireDate, 'Q'), 
 19   TO_CHAR(HireDate, 'Month'), 
 20   TO_CHAR(HireDate, 'Day') 
 21*  ) 
SQL> / 
 
    DEPTNO JOB              MGR YEAR Q MONTH     WEEKDAY   SUM(SAL)              
---------- --------- ---------- ---- - --------- --------- --------              
        10                                                     8750              
        20                                                    10875              
        30                                                     9400              
           ANALYST                                             6000              
           CLERK                                               4150              
           MANAGER                                             8275              
           PRESIDENT                                           5000              
           SALESMAN                                            5600              
                           7566                                6000              
                           7698                                6550              
                           7782                                1300              
 
    DEPTNO JOB              MGR YEAR Q MONTH     WEEKDAY   SUM(SAL)              
---------- --------- ---------- ---- - --------- --------- --------              
                           7788                                1100              
                           7839                                8275              
                           7902                                 800              
                                                               5000              
                                1980                            800              
                                1981                          22825              
                                1982                           4300              
                                1983                           1100              
                                     1                         5250              
                                     2                         8275              
                                     3                         2750              
 
    DEPTNO JOB              MGR YEAR Q MONTH     WEEKDAY   SUM(SAL)              
---------- --------- ---------- ---- - --------- --------- --------              
                                     4                        12750              
                                       April                   2975              
                                       December                7750              
                                       February                2850              
 
                                       January                 2400              
                                       June                    2450              
                                       May                     2850              
                                       November                5000              
                                       September               2750              
                                                 Friday        4450              
                                                 Monday        1250              
 
    DEPTNO JOB              MGR YEAR Q MONTH     WEEKDAY   SUM(SAL)              
---------- --------- ---------- ---- - --------- --------- --------              
                                                 Saturday      1300              
                                                 Sunday        1250              
                                                 Thursday      9925              
                                                 Tuesday       8950              
                                                 Wednesday     1900              
 
38 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=26 Card=14 Bytes=8           
          82)                                                                    
                                                                                 
   1    0   TEMP TABLE TRANSFORMATION                                            
   2    1     LOAD AS SELECT                                                     
   3    2       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 B           
          ytes=756)                                                              
                                                                                 
   4    1     LOAD AS SELECT                                                     
   5    4       SORT (GROUP BY) (Cost=3 Card=1 Bytes=26)                         
   6    5         TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6610_14C83BF'            
          (TABLE (TEMP)) (Cost=2 Card=1 Bytes=26)                                
                                                                                 
   7    1     LOAD AS SELECT                                                     
   8    7       SORT (GROUP BY) (Cost=3 Card=1 Bytes=19)                         
   9    8         TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6610_14C83BF'            
          (TABLE (TEMP)) (Cost=2 Card=1 Bytes=19)                                
                                                                                 
  10    1     LOAD AS SELECT                                                     
  11   10       SORT (GROUP BY) (Cost=3 Card=1 Bytes=26)                         
  12   11         TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6610_14C83BF'            
          (TABLE (TEMP)) (Cost=2 Card=1 Bytes=26)                                
                                                                                 
  13    1     LOAD AS SELECT                                                     
  14   13       SORT (GROUP BY) (Cost=3 Card=1 Bytes=17)                         
  15   14         TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6610_14C83BF'            
          (TABLE (TEMP)) (Cost=2 Card=1 Bytes=17)                                
                                                                                 
  16    1     LOAD AS SELECT                                                     
  17   16       SORT (GROUP BY) (Cost=3 Card=1 Bytes=15)                         
  18   17         TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6610_14C83BF'            
          (TABLE (TEMP)) (Cost=2 Card=1 Bytes=15)                                
                                                                                 
  19    1     LOAD AS SELECT                                                     
  20   19       SORT (GROUP BY) (Cost=3 Card=1 Bytes=19)                         
  21   20         TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6610_14C83BF'            
 
          (TABLE (TEMP)) (Cost=2 Card=1 Bytes=19)                                
                                                                                 
  22    1     LOAD AS SELECT                                                     
  23   22       SORT (GROUP BY) (Cost=3 Card=1 Bytes=19)                         
  24   23         TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6610_14C83BF'            
          (TABLE (TEMP)) (Cost=2 Card=1 Bytes=19)                                
                                                                                 
  25    1     VIEW (Cost=2 Card=1 Bytes=63)                                      
  26   25       TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6611_14C83BF' (T           
          ABLE (TEMP)) (Cost=2 Card=1 Bytes=63)                                  
                                                                                 
 
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE MATERIALIZED VIEW SubTotals 
  2  ENABLE QUERY REWRITE 
  3  AS 
  4  SELECT  
  5  Deptno,  
  6  Job, 
  7  MGR, 
  8  TO_CHAR(HireDate, 'YYYY') Year, 
  9  TO_CHAR(HireDate, 'Q') Quarter, 
 10  TO_CHAR(HireDate, 'Month') Month, 
 11  TO_CHAR(HireDate, 'Day') WeekDay, 
 12  SUM(Sal) 
 13  FROM Emp 
 14  GROUP BY 
 15  GROUPING SETS 
 16  ( 
 17   Deptno,  
 18   Job, 
 19   MGR, 
 20   TO_CHAR(HireDate, 'YYYY'), 
 21   TO_CHAR(HireDate, 'Q'), 
 22   TO_CHAR(HireDate, 'Month'), 
 23   TO_CHAR(HireDate, 'Day') 
 24*  ) 
SQL> / 
 
Materialized view created. 
 
SQL> SELECT 
  2  Deptno, 
  3  Job, 
  4  MGR, 
  5  TO_CHAR(HireDate, 'YYYY') Year, 
  6  TO_CHAR(HireDate, 'Q') Quarter, 
  7  TO_CHAR(HireDate, 'Month') Month, 
  8  TO_CHAR(HireDate, 'Day') WeekDay, 
  9  SUM(Sal) 
 10  FROM Emp 
 11  GROUP BY 
 12  GROUPING SETS 
 
 13  ( 
 14   Deptno, 
 15   Job, 
 16   MGR, 
 17   TO_CHAR(HireDate, 'YYYY'), 
 18   TO_CHAR(HireDate, 'Q'), 
 19   TO_CHAR(HireDate, 'Month'), 
 20   TO_CHAR(HireDate, 'Day') 
 21   ) 
 22  / 
 
    DEPTNO JOB              MGR YEAR Q MONTH     WEEKDAY   SUM(SAL)              
---------- --------- ---------- ---- - --------- --------- --------              
        10                                                     8750              
        20                                                    10875              
        30                                                     9400              
           ANALYST                                             6000              
           CLERK                                               4150              
           MANAGER                                             8275              
           PRESIDENT                                           5000              
           SALESMAN                                            5600              
                           7566                                6000              
                           7698                                6550              
                           7782                                1300              
 
    DEPTNO JOB              MGR YEAR Q MONTH     WEEKDAY   SUM(SAL)              
---------- --------- ---------- ---- - --------- --------- --------              
                           7788                                1100              
                           7839                                8275              
                           7902                                 800              
                                                               5000              
                                1980                            800              
                                1981                          22825              
                                1982                           4300              
                                1983                           1100              
                                     1                         5250              
                                     2                         8275              
                                     3                         2750              
 
    DEPTNO JOB              MGR YEAR Q MONTH     WEEKDAY   SUM(SAL)              
---------- --------- ---------- ---- - --------- --------- --------              
                                     4                        12750              
                                       April                   2975              
                                       December                7750              
                                       February                2850              
                                       January                 2400              
                                       June                    2450              
                                       May                     2850              
                                       November                5000              
                                       September               2750              
                                                 Friday        4450              
                                                 Monday        1250              
 
    DEPTNO JOB              MGR YEAR Q MONTH     WEEKDAY   SUM(SAL)              
---------- --------- ---------- ---- - --------- --------- --------              
                                                 Saturday      1300              
                                                 Sunday        1250              
 
                                                 Thursday      9925              
                                                 Tuesday       8950              
                                                 Wednesday     1900              
 
38 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=38 Bytes=23           
          94)                                                                    
                                                                                 
   1    0   MAT_VIEW REWRITE ACCESS (FULL) OF 'SUBTOTALS' (MAT_VIEW RE           
          WRITE) (Cost=3 Card=38 Bytes=2394)                                     
                                                                                 
 
 
 
SQL> SPOOL OFF 
SQL> cl scr 
 
SQL> SELECT 
  2  Deptno, 
  3  GROUPING(Deptno) GrpDeptnoBit, 
  4  Job, 
  5  GROUPING(Job) GrpJobBit, 
  6  SUM(Sal) 
  7  FROM Emp 
  8  GROUP BY ROLLUP(Deptno, Job); 
 
    DEPTNO GRPDEPTNOBIT JOB        GRPJOBBIT   SUM(SAL)                          
---------- ------------ --------- ---------- ----------                          
        10            0 CLERK              0       1300                          
        10            0 MANAGER            0       2450                          
        10            0 PRESIDENT          0       5000                          
        10            0                    1       8750                          
        20            0 CLERK              0       1900                          
        20            0 ANALYST            0       6000                          
        20            0 MANAGER            0       2975                          
        20            0                    1      10875                          
        30            0 CLERK              0        950                          
        30            0 MANAGER            0       2850                          
        30            0 SALESMAN           0       5600                          
 
    DEPTNO GRPDEPTNOBIT JOB        GRPJOBBIT   SUM(SAL)                          
---------- ------------ --------- ---------- ----------                          
        30            0                    1       9400                          
                      1                    1      29025                          
 
13 rows selected. 
 
SQL> COLUMN GRPDEPTNOBIT FORMAT 99 
SQL> COLUMN GRPJobBIT FORMAT 99 
SQL> COLUMN Deptno FORMAT 99 
SQL> cl scr 
 
SQL> R 
 
  1  SELECT 
  2  Deptno, 
  3  GROUPING(Deptno) GrpDeptnoBit, 
  4  Job, 
  5  GROUPING(Job) GrpJobBit, 
  6  SUM(Sal) 
  7  FROM Emp 
  8* GROUP BY ROLLUP(Deptno, Job) 
 
DEPTNO GRPDEPTNOBIT JOB       GRPJOBBIT   SUM(SAL)                               
------ ------------ --------- --------- ----------                               
    10            0 CLERK             0       1300                               
    10            0 MANAGER           0       2450                               
    10            0 PRESIDENT         0       5000                               
    10            0                   1       8750                               
    20            0 CLERK             0       1900                               
    20            0 ANALYST           0       6000                               
    20            0 MANAGER           0       2975                               
    20            0                   1      10875                               
    30            0 CLERK             0        950                               
    30            0 MANAGER           0       2850                               
    30            0 SALESMAN          0       5600                               
 
DEPTNO GRPDEPTNOBIT JOB       GRPJOBBIT   SUM(SAL)                               
------ ------------ --------- --------- ----------                               
    30            0                   1       9400                               
                  1                   1      29025                               
 
13 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 
  2  Deptno, 
  3  GROUPING(Deptno) GrpDeptnoBit, 
  4  Job, 
  5  GROUPING(Job) GrpJobBit, 
  6  GROUPIN_ID(Deptno, Job) GrpVal, 
  7  SUM(Sal) SalSum 
  8  FROM Emp 
  9* GROUP BY ROLLUP(Deptno, Job) 
SQL> COLUMN SalSum FORMAt 99999 
SQL> COLUMN GrpVal FORMAt 99 
SQL> / 
GROUPIN_ID(Deptno, Job) GrpVal, 
* 
ERROR at line 6: 
ORA-00904: "GROUPIN_ID": invalid identifier  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 
  2  Deptno, 
  3  GROUPING(Deptno) GrpDeptnoBit, 
 
  4  Job, 
  5  GROUPING(Job) GrpJobBit, 
  6  GROUPING_ID(Deptno, Job) GrpVal, 
  7  SUM(Sal) SalSum 
  8  FROM Emp 
  9* GROUP BY ROLLUP(Deptno, Job) 
SQL> / 
 
DEPTNO GRPDEPTNOBIT JOB       GRPJOBBIT GRPVAL SALSUM                            
------ ------------ --------- --------- ------ ------                            
    10            0 CLERK             0      0   1300                            
    10            0 MANAGER           0      0   2450                            
    10            0 PRESIDENT         0      0   5000                            
    10            0                   1      1   8750                            
    20            0 CLERK             0      0   1900                            
    20            0 ANALYST           0      0   6000                            
    20            0 MANAGER           0      0   2975                            
    20            0                   1      1  10875                            
    30            0 CLERK             0      0    950                            
    30            0 MANAGER           0      0   2850                            
    30            0 SALESMAN          0      0   5600                            
 
DEPTNO GRPDEPTNOBIT JOB       GRPJOBBIT GRPVAL SALSUM                            
------ ------------ --------- --------- ------ ------                            
    30            0                   1      1   9400                            
                  1                   1      3  29025                            
 
13 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 
  2  Deptno, 
  3  Job, 
  4  GROUPING_ID(Deptno, Job) GrpVal, 
  5  SUM(Sal) SalSum 
  6  FROM Emp 
  7* GROUP BY ROLLUP(Deptno, Job) 
SQL> / 
 
DEPTNO JOB       GRPVAL SALSUM                                                   
------ --------- ------ ------                                                   
    10 CLERK          0   1300                                                   
    10 MANAGER        0   2450                                                   
    10 PRESIDENT      0   5000                                                   
    10                1   8750                                                   
    20 CLERK          0   1900                                                   
    20 ANALYST        0   6000                                                   
    20 MANAGER        0   2975                                                   
    20                1  10875                                                   
    30 CLERK          0    950                                                   
    30 MANAGER        0   2850                                                   
    30 SALESMAN       0   5600                                                   
 
DEPTNO JOB       GRPVAL SALSUM                                                   
------ --------- ------ ------                                                   
 
    30                1   9400                                                   
                      3  29025                                                   
 
13 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 
  2  Deptno, 
  3  Job, 
  4  SUM(Sal) SalSum 
  5  FROM Emp 
  6  GROUP BY ROLLUP(Deptno, Job) 
  7  HAVING 
  8* GROUPING_ID(Deptno, Job) IN(&GrpVal1, &GrpVal2, &GrpVal3)  
SQL> / 
Enter value for grpval1: 0 
Enter value for grpval2: 0 
Enter value for grpval3: 0 
old   8: GROUPING_ID(Deptno, Job) IN(&GrpVal1, &GrpVal2, &GrpVal3)  
new   8: GROUPING_ID(Deptno, Job) IN(0, 0, 0)  
 
DEPTNO JOB       SALSUM                                                          
------ --------- ------                                                          
    10 CLERK       1300                                                          
    10 MANAGER     2450                                                          
    10 PRESIDENT   5000                                                          
    20 CLERK       1900                                                          
    20 ANALYST     6000                                                          
    20 MANAGER     2975                                                          
    30 CLERK        950                                                          
    30 MANAGER     2850                                                          
    30 SALESMAN    5600                                                          
 
9 rows selected. 
 
SQL> / 
Enter value for grpval1: 0 
Enter value for grpval2: 1 
Enter value for grpval3: 1 
old   8: GROUPING_ID(Deptno, Job) IN(&GrpVal1, &GrpVal2, &GrpVal3)  
new   8: GROUPING_ID(Deptno, Job) IN(0, 1, 1)  
 
DEPTNO JOB       SALSUM                                                          
------ --------- ------                                                          
    10 CLERK       1300                                                          
    10 MANAGER     2450                                                          
    10 PRESIDENT   5000                                                          
    10             8750                                                          
    20 CLERK       1900                                                          
    20 ANALYST     6000                                                          
    20 MANAGER     2975                                                          
    20            10875                                                          
    30 CLERK        950                                                          
    30 MANAGER     2850                                                          
    30 SALESMAN    5600                                                          
 
 
DEPTNO JOB       SALSUM                                                          
------ --------- ------                                                          
    30             9400                                                          
 
12 rows selected. 
 
SQL> / 
Enter value for grpval1: 1 
Enter value for grpval2: 1 
Enter value for grpval3: 1 
old   8: GROUPING_ID(Deptno, Job) IN(&GrpVal1, &GrpVal2, &GrpVal3)  
new   8: GROUPING_ID(Deptno, Job) IN(1, 1, 1)  
 
DEPTNO JOB       SALSUM                                                          
------ --------- ------                                                          
    10             8750                                                          
    20            10875                                                          
    30             9400                                                          
 
SQL> / 
Enter value for grpval1: 1 
Enter value for grpval2: 1 
Enter value for grpval3: 3 
old   8: GROUPING_ID(Deptno, Job) IN(&GrpVal1, &GrpVal2, &GrpVal3)  
new   8: GROUPING_ID(Deptno, Job) IN(1, 1, 3)  
 
DEPTNO JOB       SALSUM                                                          
------ --------- ------                                                          
    10             8750                                                          
    20            10875                                                          
    30             9400                                                          
                  29025                                                          
 
SQL> / 
Enter value for grpval1: 3 
Enter value for grpval2: 3 
Enter value for grpval3: 3 
old   8: GROUPING_ID(Deptno, Job) IN(&GrpVal1, &GrpVal2, &GrpVal3)  
new   8: GROUPING_ID(Deptno, Job) IN(3, 3, 3)  
 
DEPTNO JOB       SALSUM                                                          
------ --------- ------                                                          
                  29025                                                          
 
SQL> SET AUTOTRACE ON EXPLAIN 
SQL> SELECT 
  2  Deptno, 
  3  Job, 
  4  GROUPING_ID(Deptno, Job) GrpVal, 
  5  SUM(Sal) SalSum 
  6  FROM Emp 
  7  GROUP BY ROLLUP(Deptno, Job); 
 
DEPTNO JOB       GRPVAL SALSUM                                                   
------ --------- ------ ------                                                   
    10 CLERK          0   1300                                                   
 
    10 MANAGER        0   2450                                                   
    10 PRESIDENT      0   5000                                                   
    10                1   8750                                                   
    20 CLERK          0   1900                                                   
    20 ANALYST        0   6000                                                   
    20 MANAGER        0   2975                                                   
    20                1  10875                                                   
    30 CLERK          0    950                                                   
    30 MANAGER        0   2850                                                   
    30 SALESMAN       0   5600                                                   
 
DEPTNO JOB       GRPVAL SALSUM                                                   
------ --------- ------ ------                                                   
    30                1   9400                                                   
                      3  29025                                                   
 
13 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=44           
          8)                                                                     
                                                                                 
   1    0   SORT (GROUP BY ROLLUP) (Cost=4 Card=14 Bytes=448)                    
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
          es=448)                                                                
                                                                                 
 
 
 
SQL> SELECT 
  2  Deptno, 
  3  Job, 
  4  SUM(Sal) SalSum 
  5  FROM Emp 
  6  GROUP BY ROLLUP(Deptno, Job) 
  7  HAVING 
  8  GROUPING_ID(Deptno, Job) IN(&GrpVal1, &GrpVal2, &GrpVal3) 
  9  / 
Enter value for grpval1: 1 
Enter value for grpval2: 1 
Enter value for grpval3: 3 
old   8: GROUPING_ID(Deptno, Job) IN(&GrpVal1, &GrpVal2, &GrpVal3) 
new   8: GROUPING_ID(Deptno, Job) IN(1, 1, 3) 
 
DEPTNO JOB       SALSUM                                                          
------ --------- ------                                                          
    10             8750                                                          
    20            10875                                                          
    30             9400                                                          
                  29025                                                          
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=36           
 
          4)                                                                     
                                                                                 
   1    0   FILTER                                                               
   2    1     SORT (GROUP BY ROLLUP) (Cost=4 Card=14 Bytes=364)                  
   3    2       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 B           
          ytes=364)                                                              
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> SELECT Deptno, Job, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY Deptno, ROLLUP(Deptno, Job); 
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
    10 CLERK           1300                                                      
    10 MANAGER         2450                                                      
    10 PRESIDENT       5000                                                      
    20 CLERK           1900                                                      
    20 ANALYST         6000                                                      
    20 MANAGER         2975                                                      
    30 CLERK            950                                                      
    30 MANAGER         2850                                                      
    30 SALESMAN        5600                                                      
    10                 8750                                                      
    20                10875                                                      
 
DEPTNO JOB         SUM(SAL)                                                      
------ --------- ----------                                                      
    30                 9400                                                      
    10                 8750                                                      
    20                10875                                                      
    30                 9400                                                      
 
15 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=10 Card=14 Bytes=4           
          48)                                                                    
                                                                                 
   1    0   TEMP TABLE TRANSFORMATION                                            
   2    1     MULTI-TABLE INSERT                                                 
   3    2       DIRECT LOAD INTO OF 'SYS_TEMP_0FD9D6602_14E5DF5'                 
   4    2       DIRECT LOAD INTO OF 'SYS_TEMP_0FD9D6603_14E5DF5'                 
   5    4         SORT (GROUP BY ROLLUP) (Cost=4 Card=14 Bytes=448)              
   6    5           TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=           
          14 Bytes=448)                                                          
                                                                                 
   7    1     VIEW (Cost=6 Card=3 Bytes=96)                                      
   8    7       VIEW (Cost=6 Card=3 Bytes=96)                                    
   9    8         UNION-ALL                                                      
  10    9           TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6603_14E5DF5           
 
          ' (TABLE (TEMP)) (Cost=2 Card=1 Bytes=32)                              
                                                                                 
  11    9           TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6602_14E5DF5           
          ' (TABLE (TEMP)) (Cost=2 Card=1 Bytes=32)                              
                                                                                 
  12    9           TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6602_14E5DF5           
          ' (TABLE (TEMP)) (Cost=2 Card=1 Bytes=32)                              
                                                                                 
 
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Deptno, Job, GROUP_ID() GrpID, SUM(Sal) 
  2  FROM Emp 
  3* GROUP BY Deptno, ROLLUP(Deptno, Job) 
SQL> / 
 
DEPTNO JOB            GRPID   SUM(SAL)                                           
------ --------- ---------- ----------                                           
    10 CLERK              0       1300                                           
    10 MANAGER            0       2450                                           
    10 PRESIDENT          0       5000                                           
    20 CLERK              0       1900                                           
    20 ANALYST            0       6000                                           
    20 MANAGER            0       2975                                           
    30 CLERK              0        950                                           
    30 MANAGER            0       2850                                           
    30 SALESMAN           0       5600                                           
    10                    0       8750                                           
    20                    0      10875                                           
 
DEPTNO JOB            GRPID   SUM(SAL)                                           
------ --------- ---------- ----------                                           
    30                    0       9400                                           
    10                    1       8750                                           
    20                    1      10875                                           
    30                    1       9400                                           
 
15 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=10 Card=14 Bytes=4           
          90)                                                                    
                                                                                 
   1    0   TEMP TABLE TRANSFORMATION                                            
   2    1     MULTI-TABLE INSERT                                                 
   3    2       DIRECT LOAD INTO OF 'SYS_TEMP_0FD9D6606_14E5DF5'                 
   4    2       DIRECT LOAD INTO OF 'SYS_TEMP_0FD9D6607_14E5DF5'                 
   5    4         SORT (GROUP BY ROLLUP) (Cost=4 Card=14 Bytes=448)              
   6    5           TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=           
          14 Bytes=448)                                                          
                                                                                 
   7    1     VIEW (Cost=6 Card=3 Bytes=105)                                     
 
   8    7       VIEW (Cost=6 Card=3 Bytes=105)                                   
   9    8         UNION-ALL                                                      
  10    9           TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6607_14E5DF5           
          ' (TABLE (TEMP)) (Cost=2 Card=1 Bytes=32)                              
                                                                                 
  11    9           TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_14E5DF5           
          ' (TABLE (TEMP)) (Cost=2 Card=1 Bytes=32)                              
                                                                                 
  12    9           TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_14E5DF5           
          ' (TABLE (TEMP)) (Cost=2 Card=1 Bytes=32)                              
                                                                                 
 
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Deptno, Job, GROUP_ID() GrpID, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY Deptno, ROLLUP(Deptno, Job) 
  4* HAVING GROUP_ID() = 0 
SQL> / 
 
DEPTNO JOB            GRPID   SUM(SAL)                                           
------ --------- ---------- ----------                                           
    10 CLERK              0       1300                                           
    10 MANAGER            0       2450                                           
    10 PRESIDENT          0       5000                                           
    20 CLERK              0       1900                                           
    20 ANALYST            0       6000                                           
    20 MANAGER            0       2975                                           
    30 CLERK              0        950                                           
    30 MANAGER            0       2850                                           
    30 SALESMAN           0       5600                                           
    10                    0       8750                                           
    20                    0      10875                                           
 
DEPTNO JOB            GRPID   SUM(SAL)                                           
------ --------- ---------- ----------                                           
    30                    0       9400                                           
 
12 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=10 Card=14 Bytes=4           
          90)                                                                    
                                                                                 
   1    0   TEMP TABLE TRANSFORMATION                                            
   2    1     MULTI-TABLE INSERT                                                 
   3    2       DIRECT LOAD INTO OF 'SYS_TEMP_0FD9D660A_14E5DF5'                 
   4    2       DIRECT LOAD INTO OF 'SYS_TEMP_0FD9D660B_14E5DF5'                 
   5    4         SORT (GROUP BY ROLLUP) (Cost=4 Card=14 Bytes=448)              
   6    5           TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=           
          14 Bytes=448)                                                          
                                                                                 
 
   7    1     VIEW (Cost=6 Card=3 Bytes=105)                                     
   8    7       VIEW (Cost=6 Card=3 Bytes=105)                                   
   9    8         UNION-ALL                                                      
  10    9           TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D660B_14E5DF5           
          ' (TABLE (TEMP)) (Cost=2 Card=1 Bytes=32)                              
                                                                                 
  11    9           TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D660A_14E5DF5           
          ' (TABLE (TEMP)) (Cost=2 Card=1 Bytes=32)                              
                                                                                 
  12    9           FILTER                                                       
  13   12             TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D660A_14E5D           
          F5' (TABLE (TEMP)) (Cost=2 Card=1 Bytes=32)                            
                                                                                 
 
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Deptno, Job, GROUP_ID() GrpID, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY Deptno, ROLLUP(Deptno, Job) 
  4* HAVING GROUP_ID() = 1 
SQL> / 
 
DEPTNO JOB            GRPID   SUM(SAL)                                           
------ --------- ---------- ----------                                           
    10                    1       8750                                           
    20                    1      10875                                           
    30                    1       9400                                           
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=10 Card=14 Bytes=4           
          90)                                                                    
                                                                                 
   1    0   TEMP TABLE TRANSFORMATION                                            
   2    1     MULTI-TABLE INSERT                                                 
   3    2       DIRECT LOAD INTO OF 'SYS_TEMP_0FD9D660E_14E5DF5'                 
   4    2       DIRECT LOAD INTO OF 'SYS_TEMP_0FD9D660F_14E5DF5'                 
   5    4         SORT (GROUP BY ROLLUP) (Cost=4 Card=14 Bytes=448)              
   6    5           TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=           
          14 Bytes=448)                                                          
                                                                                 
   7    1     VIEW (Cost=6 Card=3 Bytes=105)                                     
   8    7       VIEW (Cost=6 Card=3 Bytes=105)                                   
   9    8         UNION-ALL                                                      
  10    9           FILTER                                                       
  11   10             TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D660F_14E5D           
          F5' (TABLE (TEMP)) (Cost=2 Card=1 Bytes=32)                            
                                                                                 
  12    9           FILTER                                                       
  13   12             TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D660E_14E5D           
          F5' (TABLE (TEMP)) (Cost=2 Card=1 Bytes=32)                            
                                                                                 
  14    9           TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D660E_14E5DF5           
 
          ' (TABLE (TEMP)) (Cost=2 Card=1 Bytes=32)                              
                                                                                 
 
 
 
SQL> SET AUTOTRACE OFF EXPLAIN 
SQL> cl scr 
 
SQL> SPOOL OFF 
SQL> cl scr 
 
SQL> SELECT Ename, Sal 
  2  FROM Emp 
  3  ORDER BY Sal DESC; 
 
ENAME             SAL                                                            
---------- ----------                                                            
KING             5000                                                            
FORD             3000                                                            
SCOTT            3000                                                            
JONES            2975                                                            
BLAKE            2850                                                            
CLARK            2450                                                            
ALLEN            1600                                                            
TURNER           1500                                                            
MILLER           1300                                                            
MARTIN           1250                                                            
WARD             1250                                                            
 
ENAME             SAL                                                            
---------- ----------                                                            
ADAMS            1100                                                            
JAMES             950                                                            
SMITH             800                                                            
 
14 rows selected. 
 
SQL> SELECT EName, Deptno,  Sal, 
  2  RANK() 
  3  OVER(ORDER BY Sal) EmpRank 
  4  FROM  Emp 
  5  GROUP BY Deptno,  EName, Sal 
  6  ORDER BY Emprank 
  7   
SQL> SELECT EName, Deptno,  Sal 
  2  FROM  Emp; 
 
ENAME          DEPTNO        SAL                                                 
---------- ---------- ----------                                                 
KING               10       5000                                                 
BLAKE              30       2850                                                 
CLARK              10       2450                                                 
JONES              20       2975                                                 
MARTIN             30       1250                                                 
ALLEN              30       1600                                                 
TURNER             30       1500                                                 
JAMES              30        950                                                 
 
WARD               30       1250                                                 
FORD               20       3000                                                 
SMITH              20        800                                                 
 
ENAME          DEPTNO        SAL                                                 
---------- ---------- ----------                                                 
SCOTT              20       3000                                                 
ADAMS              20       1100                                                 
MILLER             10       1300                                                 
 
14 rows selected. 
 
SQL> SELECT EName, Deptno,  Sal, 
  2  RANK() 
  3  OVER(ORDER BY Sal) EmpRank 
  4  FROM  Emp 
  5  / 
 
ENAME          DEPTNO        SAL    EMPRANK                                      
---------- ---------- ---------- ----------                                      
SMITH              20        800          1                                      
JAMES              30        950          2                                      
ADAMS              20       1100          3                                      
MARTIN             30       1250          4                                      
WARD               30       1250          4                                      
MILLER             10       1300          6                                      
TURNER             30       1500          7                                      
ALLEN              30       1600          8                                      
CLARK              10       2450          9                                      
BLAKE              30       2850         10                                      
JONES              20       2975         11                                      
 
ENAME          DEPTNO        SAL    EMPRANK                                      
---------- ---------- ---------- ----------                                      
FORD               20       3000         12                                      
SCOTT              20       3000         12                                      
KING               10       5000         14                                      
 
14 rows selected. 
 
SQL> SELECT EName, Deptno,  Sal, 
  2  RANK() 
  3  OVER(ORDER BY Sal) EmpRank 
  4  FROM  Emp 
  5  GROUP BY Deptno,  EName, Sal 
  6  ORDER BY Emprank; 
 
ENAME          DEPTNO        SAL    EMPRANK                                      
---------- ---------- ---------- ----------                                      
SMITH              20        800          1                                      
JAMES              30        950          2                                      
ADAMS              20       1100          3                                      
MARTIN             30       1250          4                                      
WARD               30       1250          4                                      
MILLER             10       1300          6                                      
TURNER             30       1500          7                                      
ALLEN              30       1600          8                                      
 
CLARK              10       2450          9                                      
BLAKE              30       2850         10                                      
JONES              20       2975         11                                      
 
ENAME          DEPTNO        SAL    EMPRANK                                      
---------- ---------- ---------- ----------                                      
FORD               20       3000         12                                      
SCOTT              20       3000         12                                      
KING               10       5000         14                                      
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT EName, Deptno,  Sal, 
  2  RANK() 
  3  OVER(ORDER BY Sal) EmpRank 
  4  FROM  Emp 
  5  GROUP BY Deptno,  EName, Sal 
  6* ORDER BY Emprank DESC 
SQL> / 
 
ENAME          DEPTNO        SAL    EMPRANK                                      
---------- ---------- ---------- ----------                                      
KING               10       5000         14                                      
FORD               20       3000         12                                      
SCOTT              20       3000         12                                      
JONES              20       2975         11                                      
BLAKE              30       2850         10                                      
CLARK              10       2450          9                                      
ALLEN              30       1600          8                                      
TURNER             30       1500          7                                      
MILLER             10       1300          6                                      
MARTIN             30       1250          4                                      
WARD               30       1250          4                                      
 
ENAME          DEPTNO        SAL    EMPRANK                                      
---------- ---------- ---------- ----------                                      
ADAMS              20       1100          3                                      
JAMES              30        950          2                                      
SMITH              20        800          1                                      
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT EName, Deptno,  Sal, 
  2  RANK() 
  3  OVER(ORDER BY Sal DESC) EmpRank 
  4  FROM  Emp 
  5  GROUP BY Deptno,  EName, Sal 
  6* ORDER BY Emprank 
SQL> / 
 
ENAME          DEPTNO        SAL    EMPRANK                                      
 
---------- ---------- ---------- ----------                                      
KING               10       5000          1                                      
FORD               20       3000          2                                      
SCOTT              20       3000          2                                      
JONES              20       2975          4                                      
BLAKE              30       2850          5                                      
CLARK              10       2450          6                                      
ALLEN              30       1600          7                                      
TURNER             30       1500          8                                      
MILLER             10       1300          9                                      
MARTIN             30       1250         10                                      
WARD               30       1250         10                                      
 
ENAME          DEPTNO        SAL    EMPRANK                                      
---------- ---------- ---------- ----------                                      
ADAMS              20       1100         12                                      
JAMES              30        950         13                                      
SMITH              20        800         14                                      
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT EName, Deptno,  Sal, 
  2  RANK() 
  3  OVER(ORDER BY Sal DESC) EmpRank 
  4  FROM  Emp 
  5  GROUP BY Deptno,  EName, Sal 
  6* ORDER BY Emprank DESC  
SQL> / 
 
ENAME          DEPTNO        SAL    EMPRANK                                      
---------- ---------- ---------- ----------                                      
SMITH              20        800         14                                      
JAMES              30        950         13                                      
ADAMS              20       1100         12                                      
MARTIN             30       1250         10                                      
WARD               30       1250         10                                      
MILLER             10       1300          9                                      
TURNER             30       1500          8                                      
ALLEN              30       1600          7                                      
CLARK              10       2450          6                                      
BLAKE              30       2850          5                                      
JONES              20       2975          4                                      
 
ENAME          DEPTNO        SAL    EMPRANK                                      
---------- ---------- ---------- ----------                                      
FORD               20       3000          2                                      
SCOTT              20       3000          2                                      
KING               10       5000          1                                      
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
 
  1  SELECT EName, Deptno, 
  2  RANK() 
  3  OVER(ORDER BY Sal DESC) EmpRank 
  4  FROM  Emp 
  5  GROUP BY Deptno,  EName, Sal 
  6* ORDER BY Emprank DESC  
SQL> / 
 
ENAME          DEPTNO    EMPRANK                                                 
---------- ---------- ----------                                                 
SMITH              20         14                                                 
JAMES              30         13                                                 
ADAMS              20         12                                                 
MARTIN             30         10                                                 
WARD               30         10                                                 
MILLER             10          9                                                 
TURNER             30          8                                                 
ALLEN              30          7                                                 
CLARK              10          6                                                 
BLAKE              30          5                                                 
JONES              20          4                                                 
 
ENAME          DEPTNO    EMPRANK                                                 
---------- ---------- ----------                                                 
FORD               20          2                                                 
SCOTT              20          2                                                 
KING               10          1                                                 
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT   EName, Deptno, Sal, 
  2  DENSE_RANK() 
  3  OVER(ORDER BY Sal DESC) EmpRank 
  4  FROM  Emp 
  5  GROUP BY Deptno,  EName, Sal 
  6  ORDER BY EmpRank; 
 
ENAME          DEPTNO        SAL    EMPRANK                                      
---------- ---------- ---------- ----------                                      
KING               10       5000          1                                      
FORD               20       3000          2                                      
SCOTT              20       3000          2                                      
JONES              20       2975          3                                      
BLAKE              30       2850          4                                      
CLARK              10       2450          5                                      
ALLEN              30       1600          6                                      
TURNER             30       1500          7                                      
MILLER             10       1300          8                                      
MARTIN             30       1250          9                                      
WARD               30       1250          9                                      
 
ENAME          DEPTNO        SAL    EMPRANK                                      
---------- ---------- ---------- ----------                                      
ADAMS              20       1100         10                                      
JAMES              30        950         11                                      
 
SMITH              20        800         12                                      
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT ROWNUM, E1.* 
  2  FROM (SELECT   EName, Deptno, Sal, 
  3    DENSE_RANK() 
  4    OVER(ORDER BY Sal DESC) EmpRank 
  5    FROM  Emp 
  6    GROUP BY Deptno,  EName, Sal 
  7    ORDER BY EmpRank) E1 
  8  ORDER BY ROWNUM; 
 
    ROWNUM ENAME          DEPTNO        SAL    EMPRANK                           
---------- ---------- ---------- ---------- ----------                           
         1 KING               10       5000          1                           
         2 FORD               20       3000          2                           
         3 SCOTT              20       3000          2                           
         4 JONES              20       2975          3                           
         5 BLAKE              30       2850          4                           
         6 CLARK              10       2450          5                           
         7 ALLEN              30       1600          6                           
         8 TURNER             30       1500          7                           
         9 MILLER             10       1300          8                           
        10 MARTIN             30       1250          9                           
        11 WARD               30       1250          9                           
 
    ROWNUM ENAME          DEPTNO        SAL    EMPRANK                           
---------- ---------- ---------- ---------- ----------                           
        12 ADAMS              20       1100         10                           
        13 JAMES              30        950         11                           
        14 SMITH              20        800         12                           
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT 
  2  DENSE_RANK() 
  3  OVER(ORDER BY Ename) RollNo, 
  4  EName, Deptno, Sal 
  5  FROM  Emp 
  6  GROUP BY Deptno,  EName, Sal 
  7  ORDER BY RollNo; 
 
    ROLLNO ENAME          DEPTNO        SAL                                      
---------- ---------- ---------- ----------                                      
         1 ADAMS              20       1100                                      
         2 ALLEN              30       1600                                      
         3 BLAKE              30       2850                                      
         4 CLARK              10       2450                                      
         5 FORD               20       3000                                      
         6 JAMES              30        950                                      
         7 JONES              20       2975                                      
         8 KING               10       5000                                      
 
         9 MARTIN             30       1250                                      
        10 MILLER             10       1300                                      
        11 SCOTT              20       3000                                      
 
    ROLLNO ENAME          DEPTNO        SAL                                      
---------- ---------- ---------- ----------                                      
        12 SMITH              20        800                                      
        13 TURNER             30       1500                                      
        14 WARD               30       1250                                      
 
14 rows selected. 
 
SQL> INSERT INTO Emp(Empno, Ename, Deptno) 
  2  VALUES(1234, 'ALLEN', 30); 
 
1 row created. 
 
SQL> INSERT INTO Emp(Empno, Ename, Deptno) 
  2  VALUES(1235, 'ALLEN', 30); 
 
1 row created. 
 
SQL> INSERT INTO Emp(Empno, Ename, Deptno) 
  2  VALUES(1236, 'ALLEN', 30); 
 
1 row created. 
 
SQL> SELECT 
  2  DENSE_RANK() 
  3  OVER(ORDER BY Ename) RollNo, 
  4  EName, Deptno, Sal 
  5  FROM  Emp 
  6  GROUP BY Deptno,  EName, Sal 
  7  ORDER BY RollNo; 
 
    ROLLNO ENAME          DEPTNO        SAL                                      
---------- ---------- ---------- ----------                                      
         1 ADAMS              20       1100                                      
         2 ALLEN              30       1600                                      
         2 ALLEN              30                                                 
         3 BLAKE              30       2850                                      
         4 CLARK              10       2450                                      
         5 FORD               20       3000                                      
         6 JAMES              30        950                                      
         7 JONES              20       2975                                      
         8 KING               10       5000                                      
         9 MARTIN             30       1250                                      
        10 MILLER             10       1300                                      
 
    ROLLNO ENAME          DEPTNO        SAL                                      
---------- ---------- ---------- ----------                                      
        11 SCOTT              20       3000                                      
        12 SMITH              20        800                                      
        13 TURNER             30       1500                                      
        14 WARD               30       1250                                      
 
15 rows selected. 
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 
  2  DENSE_RANK() 
  3  OVER(ORDER BY Ename) RollNo, 
  4  EName, Deptno, Sal 
  5  FROM  Emp 
  6* ORDER BY RollNo 
SQL> / 
 
    ROLLNO ENAME          DEPTNO        SAL                                      
---------- ---------- ---------- ----------                                      
         1 ADAMS              20       1100                                      
         2 ALLEN              30       1600                                      
         2 ALLEN              30                                                 
         2 ALLEN              30                                                 
         2 ALLEN              30                                                 
         3 BLAKE              30       2850                                      
         4 CLARK              10       2450                                      
         5 FORD               20       3000                                      
         6 JAMES              30        950                                      
         7 JONES              20       2975                                      
         8 KING               10       5000                                      
 
    ROLLNO ENAME          DEPTNO        SAL                                      
---------- ---------- ---------- ----------                                      
         9 MARTIN             30       1250                                      
        10 MILLER             10       1300                                      
        11 SCOTT              20       3000                                      
        12 SMITH              20        800                                      
        13 TURNER             30       1500                                      
        14 WARD               30       1250                                      
 
17 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 
  2  DENSE_RANK() 
  3  OVER(ORDER BY Ename, Empno) RollNo, 
  4  EName, Deptno, Sal 
  5  FROM  Emp 
  6* ORDER BY RollNo 
SQL> / 
 
    ROLLNO ENAME          DEPTNO        SAL                                      
---------- ---------- ---------- ----------                                      
         1 ADAMS              20       1100                                      
         2 ALLEN              30                                                 
         3 ALLEN              30                                                 
         4 ALLEN              30                                                 
         5 ALLEN              30       1600                                      
         6 BLAKE              30       2850                                      
         7 CLARK              10       2450                                      
 
         8 FORD               20       3000                                      
         9 JAMES              30        950                                      
        10 JONES              20       2975                                      
        11 KING               10       5000                                      
 
    ROLLNO ENAME          DEPTNO        SAL                                      
---------- ---------- ---------- ----------                                      
        12 MARTIN             30       1250                                      
        13 MILLER             10       1300                                      
        14 SCOTT              20       3000                                      
        15 SMITH              20        800                                      
        16 TURNER             30       1500                                      
        17 WARD               30       1250                                      
 
17 rows selected. 
 
SQL> ROLLBACK; 
 
Rollback complete. 
 
SQL> cl scr 
 
SQL> SELECT 
  2  DENSE_RANK() 
  3  OVER(ORDER BY Ename) RollNo, 
  4  EName, Sal, 
  5  DENSE_RANK() 
  6  OVER(ORDER BY Sal DESC) RankSal, 
  7  HireDate, 
  8  DENSE_RANK() 
  9  OVER(ORDER BY HireDate) SeniorRank, 
 10  DENSE_RANK() 
 11  OVER(ORDER BY HireDate DESC) JuniorRank 
 12  FROM  Emp 
 13  ORDER BY RollNo; 
 
    ROLLNO ENAME             SAL    RANKSAL HIREDATE  SENIORRANK JUNIORRANK      
---------- ---------- ---------- ---------- --------- ---------- ----------      
         1 ADAMS            1100         10 12-JAN-83         13          1      
         2 ALLEN            1600          6 20-FEB-81          2         12      
         3 BLAKE            2850          4 01-MAY-81          5          9      
         4 CLARK            2450          5 09-JUN-81          6          8      
         5 FORD             3000          2 03-DEC-81         10          4      
         6 JAMES             950         11 03-DEC-81         10          4      
         7 JONES            2975          3 02-APR-81          4         10      
         8 KING             5000          1 17-NOV-81          9          5      
         9 MARTIN           1250          9 28-SEP-81          8          6      
        10 MILLER           1300          8 23-JAN-82         11          3      
        11 SCOTT            3000          2 09-DEC-82         12          2      
 
    ROLLNO ENAME             SAL    RANKSAL HIREDATE  SENIORRANK JUNIORRANK      
---------- ---------- ---------- ---------- --------- ---------- ----------      
        12 SMITH             800         12 17-DEC-80          1         13      
        13 TURNER           1500          7 08-SEP-81          7          7      
        14 WARD             1250          9 22-FEB-81          3         11      
 
14 rows selected. 
 
 
SQL> SELECT * FROm Emp;cl scr 
  2   
SQL>  
SQL> cl scr 
 
SQL> SELECT  EName, Deptno, Sal, 
  2  RANK() 
  3  OVER(PARTITION BY DeptNo 
  4   ORDER BY Sal DESC)    "TOP Sal" 
  5  FROM   Emp 
  6  ORDER BY Deptno, Sal DESC; 
 
ENAME          DEPTNO        SAL    TOP Sal                                      
---------- ---------- ---------- ----------                                      
KING               10       5000          1                                      
CLARK              10       2450          2                                      
MILLER             10       1300          3                                      
FORD               20       3000          1                                      
SCOTT              20       3000          1                                      
JONES              20       2975          3                                      
ADAMS              20       1100          4                                      
SMITH              20        800          5                                      
BLAKE              30       2850          1                                      
ALLEN              30       1600          2                                      
TURNER             30       1500          3                                      
 
ENAME          DEPTNO        SAL    TOP Sal                                      
---------- ---------- ---------- ----------                                      
MARTIN             30       1250          4                                      
WARD               30       1250          4                                      
JAMES              30        950          6                                      
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT   EName, Deptno, Sal, 
  2    DENSE_RANK() 
  3    OVER(ORDER BY Sal DESC) EmpRank 
  4    FROM  Emp 
  5    GROUP BY Deptno,  EName, Sal 
  6    ORDER BY EmpRank; 
 
ENAME          DEPTNO        SAL    EMPRANK                                      
---------- ---------- ---------- ----------                                      
KING               10       5000          1                                      
FORD               20       3000          2                                      
SCOTT              20       3000          2                                      
JONES              20       2975          3                                      
BLAKE              30       2850          4                                      
CLARK              10       2450          5                                      
ALLEN              30       1600          6                                      
TURNER             30       1500          7                                      
MILLER             10       1300          8                                      
MARTIN             30       1250          9                                      
WARD               30       1250          9                                      
 
 
ENAME          DEPTNO        SAL    EMPRANK                                      
---------- ---------- ---------- ----------                                      
ADAMS              20       1100         10                                      
JAMES              30        950         11                                      
SMITH              20        800         12                                      
 
14 rows selected. 
 
SQL> SELECT ROWNUM, E1.* 
  2  FROM (SELECT   EName, Deptno, Sal, 
  3    DENSE_RANK() 
  4    OVER(ORDER BY Sal DESC) EmpRank 
  5    FROM  Emp 
  6    GROUP BY Deptno,  EName, Sal 
  7    ORDER BY EmpRank) E1 
  8  WHERE E1.EmpRank <= 5 
  9  ORDER BY ROWNUM; 
 
    ROWNUM ENAME          DEPTNO        SAL    EMPRANK                           
---------- ---------- ---------- ---------- ----------                           
         1 KING               10       5000          1                           
         2 FORD               20       3000          2                           
         3 SCOTT              20       3000          2                           
         4 JONES              20       2975          3                           
         5 BLAKE              30       2850          4                           
         6 CLARK              10       2450          5                           
 
6 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT ROWNUM, E1.* 
  2  FROM (SELECT   EName, Deptno, Sal, 
  3   DENSE_RANK() 
  4   OVER(ORDER BY Sal DESC) EmpRank 
  5   FROM  Emp 
  6   GROUP BY Deptno,  EName, Sal 
  7   ORDER BY EmpRank) E1 
  8  WHERE E1.EmpRank &GOperator &GValue 
  9* ORDER BY ROWNUM 
SQL> / 
Enter value for goperator: = 
Enter value for gvalue: 1 
 
    ROWNUM ENAME          DEPTNO        SAL    EMPRANK                           
---------- ---------- ---------- ---------- ----------                           
         1 KING               10       5000          1                           
 
SQL> / 
Enter value for goperator: 1 
Enter value for gvalue:  
WHERE E1.EmpRank 1  
                 * 
ERROR at line 8: 
ORA-00920: invalid relational operator  
 
 
 
SQL> / 
Enter value for goperator: = 
Enter value for gvalue: 6 
 
    ROWNUM ENAME          DEPTNO        SAL    EMPRANK                           
---------- ---------- ---------- ---------- ----------                           
         1 ALLEN              30       1600          6                           
 
SQL> / 
Enter value for goperator: = 
Enter value for gvalue: 2 
 
    ROWNUM ENAME          DEPTNO        SAL    EMPRANK                           
---------- ---------- ---------- ---------- ----------                           
         1 FORD               20       3000          2                           
         2 SCOTT              20       3000          2                           
 
SQL> / 
Enter value for goperator: < 
Enter value for gvalue: 5 
 
    ROWNUM ENAME          DEPTNO        SAL    EMPRANK                           
---------- ---------- ---------- ---------- ----------                           
         1 KING               10       5000          1                           
         2 FORD               20       3000          2                           
         3 SCOTT              20       3000          2                           
         4 JONES              20       2975          3                           
         5 BLAKE              30       2850          4                           
 
SQL> / 
Enter value for goperator: > 
Enter value for gvalue: 5 
 
    ROWNUM ENAME          DEPTNO        SAL    EMPRANK                           
---------- ---------- ---------- ---------- ----------                           
         1 ALLEN              30       1600          6                           
         2 TURNER             30       1500          7                           
         3 MILLER             10       1300          8                           
         4 MARTIN             30       1250          9                           
         5 WARD               30       1250          9                           
         6 ADAMS              20       1100         10                           
         7 JAMES              30        950         11                           
         8 SMITH              20        800         12                           
 
8 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT ROWNUM, E1.* 
  2  FROM (SELECT   EName, Deptno, Sal, 
  3   DENSE_RANK() 
  4   OVER(ORDER BY Sal DESC) EmpRank 
  5   FROM  Emp 
  6   GROUP BY Deptno,  EName, Sal 
 
  7   ORDER BY EmpRank) E1 
  8  WHERE E1.EmpRank BETWEEN &GVal1 AND &GVal2 
  9* ORDER BY ROWNUM 
SQL> / 
Enter value for gval1: 1 
Enter value for gval2: 4 
 
    ROWNUM ENAME          DEPTNO        SAL    EMPRANK                           
---------- ---------- ---------- ---------- ----------                           
         1 KING               10       5000          1                           
         2 FORD               20       3000          2                           
         3 SCOTT              20       3000          2                           
         4 JONES              20       2975          3                           
         5 BLAKE              30       2850          4                           
 
SQL> / 
Enter value for gval1: 5 
Enter value for gval2: 9 
 
    ROWNUM ENAME          DEPTNO        SAL    EMPRANK                           
---------- ---------- ---------- ---------- ----------                           
         1 CLARK              10       2450          5                           
         2 ALLEN              30       1600          6                           
         3 TURNER             30       1500          7                           
         4 MILLER             10       1300          8                           
         5 MARTIN             30       1250          9                           
         6 WARD               30       1250          9                           
 
6 rows selected. 
 
SQL> / 
Enter value for gval1: 10 
Enter value for gval2: 14 
 
    ROWNUM ENAME          DEPTNO        SAL    EMPRANK                           
---------- ---------- ---------- ---------- ----------                           
         1 ADAMS              20       1100         10                           
         2 JAMES              30        950         11                           
         3 SMITH              20        800         12                           
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT ROWNUM, E1.* 
  2  FROM (SELECT   EName, Deptno, Sal, 
  3   DENSE_RANK() 
  4   OVER(ORDER BY Sal DESC) EmpRank 
  5   FROM  Emp 
  6   GROUP BY Deptno,  EName, Sal 
  7   ORDER BY EmpRank) E1 
  8  WHERE E1.EmpRank IN(&GVal1, &GVal2, &GVal3) 
  9* ORDER BY ROWNUM 
SQL> / 
Enter value for gval1: 1 
Enter value for gval2: 5 
Enter value for gval3: 8 
 
 
    ROWNUM ENAME          DEPTNO        SAL    EMPRANK                           
---------- ---------- ---------- ---------- ----------                           
         1 KING               10       5000          1                           
         2 CLARK              10       2450          5                           
         3 MILLER             10       1300          8                           
 
SQL> cl scr 
 
SQL> SELECT 
  2  TO_CHAR(HireDate, 'YYYY') "Year", 
  3  SUM(Sal), 
  4  DENSE_RANK() 
  5  OVER(ORDER BY  SUM(Sal) DESC) YearRank 
  6  FROM  Emp 
  7  GROUP BY TO_CHAR(HireDate, 'YYYY') 
  8  ORDER BY YearRank; 
 
Year   SUM(SAL)   YEARRANK                                                       
---- ---------- ----------                                                       
1981      22825          1                                                       
1982       4300          2                                                       
1983       1100          3                                                       
1980        800          4                                                       
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 
  2  TO_CHAR(HireDate, 'YYYY') "Year", 
  3  SUM(Sal), 
  4  DENSE_RANK() 
  5  OVER(ORDER BY  SUM(Sal) DESC) YearRank 
  6  FROM  Emp 
  7  --GROUP BY TO_CHAR(HireDate, 'YYYY') 
  8* ORDER BY YearRank 
SQL> / 
TO_CHAR(HireDate, 'YYYY') "Year", 
        * 
ERROR at line 2: 
ORA-00937: not a single-group group function  
 
 
SQL> cl scr 
 
SQL> SELECT   Deptno, TO_CHAR(HireDate, 'YYYY') "Year", SUM(Sal), 
  2  DENSE_RANK() 
  3  OVER(ORDER BY  SUM(Sal) DESC) YearRank 
  4  FROM  Emp 
  5  GROUP BY Deptno, TO_CHAR(HireDate, 'YYYY') 
  6  ORDER BY YearRank; 
 
    DEPTNO Year   SUM(SAL)   YEARRANK                                            
---------- ---- ---------- ----------                                            
        30 1981       9400          1                                            
        10 1981       7450          2                                            
        20 1981       5975          3                                            
        20 1982       3000          4                                            
 
        10 1982       1300          5                                            
        20 1983       1100          6                                            
        20 1980        800          7                                            
 
7 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT Ename, Deptno, Sal, 
  2  SUM(Sal) 
  3  OVER(ORDER BY Sal DESC) "Run Sum" 
  4  FROM Emp; 
 
ENAME          DEPTNO        SAL    Run Sum                                      
---------- ---------- ---------- ----------                                      
KING               10       5000       5000                                      
FORD               20       3000      11000                                      
SCOTT              20       3000      11000                                      
JONES              20       2975      13975                                      
BLAKE              30       2850      16825                                      
CLARK              10       2450      19275                                      
ALLEN              30       1600      20875                                      
TURNER             30       1500      22375                                      
MILLER             10       1300      23675                                      
MARTIN             30       1250      26175                                      
WARD               30       1250      26175                                      
 
ENAME          DEPTNO        SAL    Run Sum                                      
---------- ---------- ---------- ----------                                      
ADAMS              20       1100      27275                                      
JAMES              30        950      28225                                      
SMITH              20        800      29025                                      
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
  2  SUM(Sal) 
  3  OVER(ORDER BY Sal DESC, Ename) "Run Sum" 
  4* FROM Emp 
SQL> / 
 
ENAME          DEPTNO        SAL    Run Sum                                      
---------- ---------- ---------- ----------                                      
KING               10       5000       5000                                      
FORD               20       3000       8000                                      
SCOTT              20       3000      11000                                      
JONES              20       2975      13975                                      
BLAKE              30       2850      16825                                      
CLARK              10       2450      19275                                      
ALLEN              30       1600      20875                                      
TURNER             30       1500      22375                                      
MILLER             10       1300      23675                                      
MARTIN             30       1250      24925                                      
WARD               30       1250      26175                                      
 
 
ENAME          DEPTNO        SAL    Run Sum                                      
---------- ---------- ---------- ----------                                      
ADAMS              20       1100      27275                                      
JAMES              30        950      28225                                      
SMITH              20        800      29025                                      
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
  2  AVG(Sal) 
  3  OVER(ORDER BY Sal DESC, Ename) "Mov Avg" 
  4* FROM Emp 
SQL> / 
 
ENAME          DEPTNO        SAL    Mov Avg                                      
---------- ---------- ---------- ----------                                      
KING               10       5000       5000                                      
FORD               20       3000       4000                                      
SCOTT              20       3000 3666.66667                                      
JONES              20       2975    3493.75                                      
BLAKE              30       2850       3365                                      
CLARK              10       2450     3212.5                                      
ALLEN              30       1600 2982.14286                                      
TURNER             30       1500   2796.875                                      
MILLER             10       1300 2630.55556                                      
MARTIN             30       1250     2492.5                                      
WARD               30       1250 2379.54545                                      
 
ENAME          DEPTNO        SAL    Mov Avg                                      
---------- ---------- ---------- ----------                                      
ADAMS              20       1100 2272.91667                                      
JAMES              30        950 2171.15385                                      
SMITH              20        800 2073.21429                                      
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno,  
  2  TO_CHAR(Sal, '9G999D99') Sal, 
  3  TO_CHAR(SUM(Sal) 
  4  OVER(ORDER BY Sal DESC, Ename), '99G999D99') "Run Sum", 
  5  TO_CHAR(AVG(Sal) 
  6  OVER(ORDER BY Sal DESC, Ename), '99G999D99') "Mov Avg" 
  7* FROM Emp 
SQL> / 
 
ENAME          DEPTNO SAL       Run Sum    Mov Avg                               
---------- ---------- --------- ---------- ----------                            
KING               10  5,000.00   5,000.00   5,000.00                            
FORD               20  3,000.00   8,000.00   4,000.00                            
SCOTT              20  3,000.00  11,000.00   3,666.67                            
 
JONES              20  2,975.00  13,975.00   3,493.75                            
BLAKE              30  2,850.00  16,825.00   3,365.00                            
CLARK              10  2,450.00  19,275.00   3,212.50                            
ALLEN              30  1,600.00  20,875.00   2,982.14                            
TURNER             30  1,500.00  22,375.00   2,796.88                            
MILLER             10  1,300.00  23,675.00   2,630.56                            
MARTIN             30  1,250.00  24,925.00   2,492.50                            
WARD               30  1,250.00  26,175.00   2,379.55                            
 
ENAME          DEPTNO SAL       Run Sum    Mov Avg                               
---------- ---------- --------- ---------- ----------                            
ADAMS              20  1,100.00  27,275.00   2,272.92                            
JAMES              30    950.00  28,225.00   2,171.15                            
SMITH              20    800.00  29,025.00   2,073.21                            
 
14 rows selected. 
 
SQL> SPOOL OFF 
SQL> cl scr 
 
SQL> SET VERIFY OFF 
SQL> cl scr 
 
SQL> SELECT Ename, Deptno, Job, Sal, 
  2  RANK() 
  3  OVER(ORDER BY Sal DESC) Rank 
  4  FROM Emp; 
 
ENAME          DEPTNO JOB              SAL       RANK                            
---------- ---------- --------- ---------- ----------                            
KING               10 PRESIDENT       5000          1                            
FORD               20 ANALYST         3000          2                            
SCOTT              20 ANALYST         3000          2                            
JONES              20 MANAGER         2975          4                            
BLAKE              30 MANAGER         2850          5                            
CLARK              10 MANAGER         2450          6                            
ALLEN              30 SALESMAN        1600          7                            
TURNER             30 SALESMAN        1500          8                            
MILLER             10 CLERK           1300          9                            
MARTIN             30 SALESMAN        1250         10                            
WARD               30 SALESMAN        1250         10                            
 
ENAME          DEPTNO JOB              SAL       RANK                            
---------- ---------- --------- ---------- ----------                            
ADAMS              20 CLERK           1100         12                            
JAMES              30 CLERK            950         13                            
SMITH              20 CLERK            800         14                            
 
14 rows selected. 
 
SQL> COLUMN Deptno FORMAT 99 
SQL> COLUMN Sal FORMAT 99999 
SQL> / 
 
ENAME      DEPTNO JOB          SAL       RANK                                    
---------- ------ --------- ------ ----------                                    
KING           10 PRESIDENT   5000          1                                    
 
FORD           20 ANALYST     3000          2                                    
SCOTT          20 ANALYST     3000          2                                    
JONES          20 MANAGER     2975          4                                    
BLAKE          30 MANAGER     2850          5                                    
CLARK          10 MANAGER     2450          6                                    
ALLEN          30 SALESMAN    1600          7                                    
TURNER         30 SALESMAN    1500          8                                    
MILLER         10 CLERK       1300          9                                    
MARTIN         30 SALESMAN    1250         10                                    
WARD           30 SALESMAN    1250         10                                    
 
ENAME      DEPTNO JOB          SAL       RANK                                    
---------- ------ --------- ------ ----------                                    
ADAMS          20 CLERK       1100         12                                    
JAMES          30 CLERK        950         13                                    
SMITH          20 CLERK        800         14                                    
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Job, Sal, 
  2  RANK() 
  3  OVER( 
  4   PARTITION BY Deptno 
  5   ORDER BY Sal DESC) Rank 
  6* FROM Emp 
SQL> / 
 
ENAME      DEPTNO JOB          SAL       RANK                                    
---------- ------ --------- ------ ----------                                    
KING           10 PRESIDENT   5000          1                                    
CLARK          10 MANAGER     2450          2                                    
MILLER         10 CLERK       1300          3                                    
FORD           20 ANALYST     3000          1                                    
SCOTT          20 ANALYST     3000          1                                    
JONES          20 MANAGER     2975          3                                    
ADAMS          20 CLERK       1100          4                                    
SMITH          20 CLERK        800          5                                    
BLAKE          30 MANAGER     2850          1                                    
ALLEN          30 SALESMAN    1600          2                                    
TURNER         30 SALESMAN    1500          3                                    
 
ENAME      DEPTNO JOB          SAL       RANK                                    
---------- ------ --------- ------ ----------                                    
MARTIN         30 SALESMAN    1250          4                                    
WARD           30 SALESMAN    1250          4                                    
JAMES          30 CLERK        950          6                                    
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Job, Sal, 
  2  SUM(Sal) 
 
  3  OVER( 
  4   ORDER BY Sal DESC, Ename) "Run Sal" 
  5* FROM Emp 
SQL> / 
 
ENAME      DEPTNO JOB          SAL    Run Sal                                    
---------- ------ --------- ------ ----------                                    
KING           10 PRESIDENT   5000       5000                                    
FORD           20 ANALYST     3000       8000                                    
SCOTT          20 ANALYST     3000      11000                                    
JONES          20 MANAGER     2975      13975                                    
BLAKE          30 MANAGER     2850      16825                                    
CLARK          10 MANAGER     2450      19275                                    
ALLEN          30 SALESMAN    1600      20875                                    
TURNER         30 SALESMAN    1500      22375                                    
MILLER         10 CLERK       1300      23675                                    
MARTIN         30 SALESMAN    1250      24925                                    
WARD           30 SALESMAN    1250      26175                                    
 
ENAME      DEPTNO JOB          SAL    Run Sal                                    
---------- ------ --------- ------ ----------                                    
ADAMS          20 CLERK       1100      27275                                    
JAMES          30 CLERK        950      28225                                    
SMITH          20 CLERK        800      29025                                    
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Job, Sal, 
  2  SUM(Sal) 
  3  OVER( 
  4   PARTITION BY Deptno 
  5   ORDER BY Sal DESC, Ename) "Run Sal" 
  6* FROM Emp 
SQL> / 
 
ENAME      DEPTNO JOB          SAL    Run Sal                                    
---------- ------ --------- ------ ----------                                    
KING           10 PRESIDENT   5000       5000                                    
CLARK          10 MANAGER     2450       7450                                    
MILLER         10 CLERK       1300       8750                                    
FORD           20 ANALYST     3000       3000                                    
SCOTT          20 ANALYST     3000       6000                                    
JONES          20 MANAGER     2975       8975                                    
ADAMS          20 CLERK       1100      10075                                    
SMITH          20 CLERK        800      10875                                    
BLAKE          30 MANAGER     2850       2850                                    
ALLEN          30 SALESMAN    1600       4450                                    
TURNER         30 SALESMAN    1500       5950                                    
 
ENAME      DEPTNO JOB          SAL    Run Sal                                    
---------- ------ --------- ------ ----------                                    
MARTIN         30 SALESMAN    1250       7200                                    
WARD           30 SALESMAN    1250       8450                                    
JAMES          30 CLERK        950       9400                                    
 
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Job, Sal, 
  2  SUM(Sal) 
  3  OVER( 
  4   PARTITION BY Job 
  5   ORDER BY Sal DESC, Ename) "Run Sal" 
  6* FROM Emp 
SQL> / 
 
ENAME      DEPTNO JOB          SAL    Run Sal                                    
---------- ------ --------- ------ ----------                                    
FORD           20 ANALYST     3000       3000                                    
SCOTT          20 ANALYST     3000       6000                                    
MILLER         10 CLERK       1300       1300                                    
ADAMS          20 CLERK       1100       2400                                    
JAMES          30 CLERK        950       3350                                    
SMITH          20 CLERK        800       4150                                    
JONES          20 MANAGER     2975       2975                                    
BLAKE          30 MANAGER     2850       5825                                    
CLARK          10 MANAGER     2450       8275                                    
KING           10 PRESIDENT   5000       5000                                    
ALLEN          30 SALESMAN    1600       1600                                    
 
ENAME      DEPTNO JOB          SAL    Run Sal                                    
---------- ------ --------- ------ ----------                                    
TURNER         30 SALESMAN    1500       3100                                    
MARTIN         30 SALESMAN    1250       4350                                    
WARD           30 SALESMAN    1250       5600                                    
 
14 rows selected. 
 
SQL> SELECT Deptno, SUM(Sal) SalSum 
  2  FROM Emp 
  3  GROUP BY ROLLUP(Deptno); 
 
DEPTNO     SALSUM                                                                
------ ----------                                                                
    10       8750                                                                
    20      10875                                                                
    30       9400                                                                
            29025                                                                
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Deptno, SUM(Sal) SalSum 
  2  FROM Emp 
  3  WHERE Deptno = &GDeptno 
  4* GROUP BY ROLLUP(Deptno) 
SQL> / 
Enter value for gdeptno: 10 
 
 
DEPTNO     SALSUM                                                                
------ ----------                                                                
    10       8750                                                                
             8750                                                                
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Job, Sal, 
  2  SUM(Sal) 
  3  OVER( 
  4     PARTITION BY Job 
  5     ORDER BY Sal DESC, Ename) "Run Sal", 
  6  AVG(Sal) 
  7  OVER( 
  8     PARTITION BY Job 
  9     ORDER BY Sal DESC, Ename) "Mov Avg Sal" 
 10* FROM Emp 
SQL> / 
 
ENAME      DEPTNO JOB          SAL    Run Sal Mov Avg Sal                        
---------- ------ --------- ------ ---------- -----------                        
FORD           20 ANALYST     3000       3000        3000                        
SCOTT          20 ANALYST     3000       6000        3000                        
MILLER         10 CLERK       1300       1300        1300                        
ADAMS          20 CLERK       1100       2400        1200                        
JAMES          30 CLERK        950       3350  1116.66667                        
SMITH          20 CLERK        800       4150      1037.5                        
JONES          20 MANAGER     2975       2975        2975                        
BLAKE          30 MANAGER     2850       5825      2912.5                        
CLARK          10 MANAGER     2450       8275  2758.33333                        
KING           10 PRESIDENT   5000       5000        5000                        
ALLEN          30 SALESMAN    1600       1600        1600                        
 
ENAME      DEPTNO JOB          SAL    Run Sal Mov Avg Sal                        
---------- ------ --------- ------ ---------- -----------                        
TURNER         30 SALESMAN    1500       3100        1550                        
MARTIN         30 SALESMAN    1250       4350        1450                        
WARD           30 SALESMAN    1250       5600        1400                        
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Job, Sal, 
  2  SUM(Sal) 
  3  OVER( 
  4     PARTITION BY Deptno 
  5     ORDER BY Sal DESC, Ename) "Run Sal", 
  6  AVG(Sal) 
  7  OVER( 
  8     PARTITION BY Deptno 
  9     ORDER BY Sal DESC, Ename) "Mov Avg Sal" 
 10* FROM Emp 
SQL> / 
 
 
ENAME      DEPTNO JOB          SAL    Run Sal Mov Avg Sal                        
---------- ------ --------- ------ ---------- -----------                        
KING           10 PRESIDENT   5000       5000        5000                        
CLARK          10 MANAGER     2450       7450        3725                        
MILLER         10 CLERK       1300       8750  2916.66667                        
FORD           20 ANALYST     3000       3000        3000                        
SCOTT          20 ANALYST     3000       6000        3000                        
JONES          20 MANAGER     2975       8975  2991.66667                        
ADAMS          20 CLERK       1100      10075     2518.75                        
SMITH          20 CLERK        800      10875        2175                        
BLAKE          30 MANAGER     2850       2850        2850                        
ALLEN          30 SALESMAN    1600       4450        2225                        
TURNER         30 SALESMAN    1500       5950  1983.33333                        
 
ENAME      DEPTNO JOB          SAL    Run Sal Mov Avg Sal                        
---------- ------ --------- ------ ---------- -----------                        
MARTIN         30 SALESMAN    1250       7200        1800                        
WARD           30 SALESMAN    1250       8450        1690                        
JAMES          30 CLERK        950       9400  1566.66667                        
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT * 
  2  FROM(SELECT Ename, Deptno, Sal, 
  3    DENSE_RANK() 
  4    OVER(PARTITION BY DeptNo 
  5    ORDER BY Sal DESC)  "TOP Sal" 
  6    FROM Emp) 
  7  WHERE "TOP Sal" <=3 
  8  ORDER BY DeptNo, Sal DESC; 
 
ENAME      DEPTNO    SAL    TOP Sal                                              
---------- ------ ------ ----------                                              
KING           10   5000          1                                              
CLARK          10   2450          2                                              
MILLER         10   1300          3                                              
FORD           20   3000          1                                              
SCOTT          20   3000          1                                              
JONES          20   2975          2                                              
ADAMS          20   1100          3                                              
BLAKE          30   2850          1                                              
ALLEN          30   1600          2                                              
TURNER         30   1500          3                                              
 
10 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT * 
  2  FROM(SELECT Ename, Deptno, Sal, 
  3   DENSE_RANK() 
  4   OVER(PARTITION BY DeptNo 
  5   ORDER BY Sal DESC)  "TOP Sal" 
  6   FROM Emp) 
 
  7  WHERE "TOP Sal" = 3 
  8* ORDER BY DeptNo, Sal DESC 
SQL> / 
 
ENAME      DEPTNO    SAL    TOP Sal                                              
---------- ------ ------ ----------                                              
MILLER         10   1300          3                                              
ADAMS          20   1100          3                                              
TURNER         30   1500          3                                              
 
SQL> cl scr 
 
SQL> SELECT Ename, Deptno, Sal, 
  2  SUM(Sal) 
  3  OVER(ORDER BY Sal DESC) "Run Sal" 
  4  FROM Emp; 
 
ENAME      DEPTNO    SAL    Run Sal                                              
---------- ------ ------ ----------                                              
KING           10   5000       5000                                              
FORD           20   3000      11000                                              
SCOTT          20   3000      11000                                              
JONES          20   2975      13975                                              
BLAKE          30   2850      16825                                              
CLARK          10   2450      19275                                              
ALLEN          30   1600      20875                                              
TURNER         30   1500      22375                                              
MILLER         10   1300      23675                                              
MARTIN         30   1250      26175                                              
WARD           30   1250      26175                                              
 
ENAME      DEPTNO    SAL    Run Sal                                              
---------- ------ ------ ----------                                              
ADAMS          20   1100      27275                                              
JAMES          30    950      28225                                              
SMITH          20    800      29025                                              
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
  2  SUM(Sal) 
  3  OVER(ORDER BY Sal DESC, Ename) "Run Sal" 
  4* FROM Emp 
SQL> / 
 
ENAME      DEPTNO    SAL    Run Sal                                              
---------- ------ ------ ----------                                              
KING           10   5000       5000                                              
FORD           20   3000       8000                                              
SCOTT          20   3000      11000                                              
JONES          20   2975      13975                                              
BLAKE          30   2850      16825                                              
CLARK          10   2450      19275                                              
ALLEN          30   1600      20875                                              
 
TURNER         30   1500      22375                                              
MILLER         10   1300      23675                                              
MARTIN         30   1250      24925                                              
WARD           30   1250      26175                                              
 
ENAME      DEPTNO    SAL    Run Sal                                              
---------- ------ ------ ----------                                              
ADAMS          20   1100      27275                                              
JAMES          30    950      28225                                              
SMITH          20    800      29025                                              
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
  2  SUM(Sal) 
  3  OVER( 
  4   PARTITION BY Deptno 
  5   ORDER BY Sal DESC, Ename) "Run Sal" 
  6* FROM Emp 
SQL> / 
 
ENAME      DEPTNO    SAL    Run Sal                                              
---------- ------ ------ ----------                                              
KING           10   5000       5000                                              
CLARK          10   2450       7450                                              
MILLER         10   1300       8750                                              
FORD           20   3000       3000                                              
SCOTT          20   3000       6000                                              
JONES          20   2975       8975                                              
ADAMS          20   1100      10075                                              
SMITH          20    800      10875                                              
BLAKE          30   2850       2850                                              
ALLEN          30   1600       4450                                              
TURNER         30   1500       5950                                              
 
ENAME      DEPTNO    SAL    Run Sal                                              
---------- ------ ------ ----------                                              
MARTIN         30   1250       7200                                              
WARD           30   1250       8450                                              
JAMES          30    950       9400                                              
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
  2  SUM(Sal) 
  3  OVER( 
  4   PARTITION BY Deptno 
  5   ORDER BY Sal DESC, Ename) "Run Sal" 
  6* FROM Emp 
SQL> / 
 
 
ENAME      DEPTNO    SAL    Run Sal                                              
---------- ------ ------ ----------                                              
KING           10   5000       5000                                              
CLARK          10   2450       7450                                              
MILLER         10   1300       8750                                              
FORD           20   3000       3000                                              
SCOTT          20   3000       6000                                              
JONES          20   2975       8975                                              
ADAMS          20   1100      10075                                              
SMITH          20    800      10875                                              
BLAKE          30   2850       2850                                              
ALLEN          30   1600       4450                                              
TURNER         30   1500       5950                                              
 
ENAME      DEPTNO    SAL    Run Sal                                              
---------- ------ ------ ----------                                              
MARTIN         30   1250       7200                                              
WARD           30   1250       8450                                              
JAMES          30    950       9400                                              
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT DeptNo, Ename, Sal, 
  2  SUM(SAL) 
  3  OVER( 
  4   PARTITION BY DeptNo 
  5   ORDER BY EName 
  6   ROWS 2 PRECEDING 
  7   ) "Sliding Total" 
  8  FROM Emp 
  9  ORDER BY DeptNo, EName; 
 
DEPTNO ENAME         SAL Sliding Total                                           
------ ---------- ------ -------------                                           
    10 CLARK        2450          2450                                           
    10 KING         5000          7450                                           
    10 MILLER       1300          8750                                           
    20 ADAMS        1100          1100                                           
    20 FORD         3000          4100                                           
    20 JONES        2975          7075                                           
    20 SCOTT        3000          8975                                           
    20 SMITH         800          6775                                           
    30 ALLEN        1600          1600                                           
    30 BLAKE        2850          4450                                           
    30 JAMES         950          5400                                           
 
DEPTNO ENAME         SAL Sliding Total                                           
------ ---------- ------ -------------                                           
    30 MARTIN       1250          5050                                           
    30 TURNER       1500          3700                                           
    30 WARD         1250          4000                                           
 
14 rows selected. 
 
SQL> cl scr 
 
 
SQL> SELECT Ename, 
  2  HireDate, 
  3  COUNT(*) 
  4  OVER(ORDER BY HireDate ASC 
  5    RANGE 100 PRECEDING) HireCnt 
  6  FROM Emp 
  7  ORDER BY HireDate ASC; 
 
ENAME      HIREDATE     HIRECNT                                                  
---------- --------- ----------                                                  
SMITH      17-DEC-80          1                                                  
ALLEN      20-FEB-81          2                                                  
WARD       22-FEB-81          3                                                  
JONES      02-APR-81          3                                                  
BLAKE      01-MAY-81          4                                                  
CLARK      09-JUN-81          3                                                  
TURNER     08-SEP-81          2                                                  
MARTIN     28-SEP-81          2                                                  
KING       17-NOV-81          3                                                  
JAMES      03-DEC-81          5                                                  
FORD       03-DEC-81          5                                                  
 
ENAME      HIREDATE     HIRECNT                                                  
---------- --------- ----------                                                  
MILLER     23-JAN-82          4                                                  
SCOTT      09-DEC-82          1                                                  
ADAMS      12-JAN-83          2                                                  
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, 
  2  HireDate, HireDate - 100 "100Days", 
  3  COUNT(*) 
  4  OVER(ORDER BY HireDate ASC 
  5    RANGE 100 PRECEDING) HireCnt 
  6  FROM Emp 
  7* ORDER BY HireDate ASC 
SQL> / 
 
ENAME      HIREDATE  100Days      HIRECNT                                        
---------- --------- --------- ----------                                        
SMITH      17-DEC-80 08-SEP-80          1                                        
ALLEN      20-FEB-81 12-NOV-80          2                                        
WARD       22-FEB-81 14-NOV-80          3                                        
JONES      02-APR-81 23-DEC-80          3                                        
BLAKE      01-MAY-81 21-JAN-81          4                                        
CLARK      09-JUN-81 01-MAR-81          3                                        
TURNER     08-SEP-81 31-MAY-81          2                                        
MARTIN     28-SEP-81 20-JUN-81          2                                        
KING       17-NOV-81 09-AUG-81          3                                        
JAMES      03-DEC-81 25-AUG-81          5                                        
FORD       03-DEC-81 25-AUG-81          5                                        
 
 
ENAME      HIREDATE  100Days      HIRECNT                                        
---------- --------- --------- ----------                                        
MILLER     23-JAN-82 15-OCT-81          4                                        
SCOTT      09-DEC-82 31-AUG-82          1                                        
ADAMS      12-JAN-83 04-OCT-82          2                                        
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT Ename, HireDate, Sal, 
  2  TO_CHAR(ROUND(AVG(Sal) 
  3  OVER(ORDER BY HireDate ASC 
  4    RANGE 100 PRECEDING) , 2), '99G999D99') AvgSal 
  5  FROM Emp 
  6  ORDER BY HireDate ASC; 
 
ENAME      HIREDATE     SAL AVGSAL                                               
---------- --------- ------ ----------                                           
SMITH      17-DEC-80    800     800.00                                           
ALLEN      20-FEB-81   1600   1,200.00                                           
WARD       22-FEB-81   1250   1,216.67                                           
JONES      02-APR-81   2975   1,941.67                                           
BLAKE      01-MAY-81   2850   2,168.75                                           
CLARK      09-JUN-81   2450   2,758.33                                           
TURNER     08-SEP-81   1500   1,975.00                                           
MARTIN     28-SEP-81   1250   1,375.00                                           
KING       17-NOV-81   5000   2,583.33                                           
JAMES      03-DEC-81    950   2,340.00                                           
FORD       03-DEC-81   3000   2,340.00                                           
 
ENAME      HIREDATE     SAL AVGSAL                                               
---------- --------- ------ ----------                                           
MILLER     23-JAN-82   1300   2,562.50                                           
SCOTT      09-DEC-82   3000   3,000.00                                           
ADAMS      12-JAN-83   1100   2,050.00                                           
 
14 rows selected. 
 
SQL> SPOOL OFF 
SQL> cl scr 
 
SQL> SELECT  Ename, HireDate, Sal, 
  2  LAG(Sal, 1, 0) 
  3  OVER(ORDER BY HireDate) PreSal 
  4  FROM  Emp; 
 
ENAME      HIREDATE         SAL     PRESAL                                       
---------- --------- ---------- ----------                                       
SMITH      17-DEC-80        800          0                                       
ALLEN      20-FEB-81       1600        800                                       
WARD       22-FEB-81       1250       1600                                       
JONES      02-APR-81       2975       1250                                       
BLAKE      01-MAY-81       2850       2975                                       
CLARK      09-JUN-81       2450       2850                                       
TURNER     08-SEP-81       1500       2450                                       
MARTIN     28-SEP-81       1250       1500                                       
 
KING       17-NOV-81       5000       1250                                       
JAMES      03-DEC-81        950       5000                                       
FORD       03-DEC-81       3000        950                                       
 
ENAME      HIREDATE         SAL     PRESAL                                       
---------- --------- ---------- ----------                                       
MILLER     23-JAN-82       1300       3000                                       
SCOTT      09-DEC-82       3000       1300                                       
ADAMS      12-JAN-83       1100       3000                                       
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT  Ename, HireDate, Sal, 
  2  LAG(Sal, 1) 
  3  OVER(ORDER BY HireDate) PreSal 
  4* FROM  Emp 
SQL> / 
 
ENAME      HIREDATE         SAL     PRESAL                                       
---------- --------- ---------- ----------                                       
SMITH      17-DEC-80        800                                                  
ALLEN      20-FEB-81       1600        800                                       
WARD       22-FEB-81       1250       1600                                       
JONES      02-APR-81       2975       1250                                       
BLAKE      01-MAY-81       2850       2975                                       
CLARK      09-JUN-81       2450       2850                                       
TURNER     08-SEP-81       1500       2450                                       
MARTIN     28-SEP-81       1250       1500                                       
KING       17-NOV-81       5000       1250                                       
JAMES      03-DEC-81        950       5000                                       
FORD       03-DEC-81       3000        950                                       
 
ENAME      HIREDATE         SAL     PRESAL                                       
---------- --------- ---------- ----------                                       
MILLER     23-JAN-82       1300       3000                                       
SCOTT      09-DEC-82       3000       1300                                       
ADAMS      12-JAN-83       1100       3000                                       
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT  Ename, HireDate, Sal, 
  2  LAG(Sal, 1) 
  3  OVER(ORDER BY HireDate) PreSal1, 
  4  LAG(Sal, 2) 
  5  OVER(ORDER BY HireDate) PreSal2, 
  6  LAG(Sal, 3) 
  7  OVER(ORDER BY HireDate) PreSal3, 
  8  LAG(Sal, 4) 
  9  OVER(ORDER BY HireDate) PreSal4, 
 10  LAG(Sal, 5) 
 11  OVER(ORDER BY HireDate) PreSal5 
 
 12* FROM  Emp 
SQL> / 
 
ENAME      HIREDATE         SAL    PRESAL1    PRESAL2    PRESAL3    PRESAL4      
---------- --------- ---------- ---------- ---------- ---------- ----------      
   PRESAL5                                                                       
----------                                                                       
SMITH      17-DEC-80        800                                                  
                                                                                 
                                                                                 
ALLEN      20-FEB-81       1600        800                                       
                                                                                 
                                                                                 
WARD       22-FEB-81       1250       1600        800                            
                                                                                 
                                                                                 
 
ENAME      HIREDATE         SAL    PRESAL1    PRESAL2    PRESAL3    PRESAL4      
---------- --------- ---------- ---------- ---------- ---------- ----------      
   PRESAL5                                                                       
----------                                                                       
JONES      02-APR-81       2975       1250       1600        800                 
                                                                                 
                                                                                 
BLAKE      01-MAY-81       2850       2975       1250       1600        800      
                                                                                 
                                                                                 
CLARK      09-JUN-81       2450       2850       2975       1250       1600      
       800                                                                       
                                                                                 
 
ENAME      HIREDATE         SAL    PRESAL1    PRESAL2    PRESAL3    PRESAL4      
---------- --------- ---------- ---------- ---------- ---------- ----------      
   PRESAL5                                                                       
----------                                                                       
TURNER     08-SEP-81       1500       2450       2850       2975       1250      
      1600                                                                       
                                                                                 
MARTIN     28-SEP-81       1250       1500       2450       2850       2975      
      1250                                                                       
                                                                                 
KING       17-NOV-81       5000       1250       1500       2450       2850      
      2975                                                                       
                                                                                 
 
ENAME      HIREDATE         SAL    PRESAL1    PRESAL2    PRESAL3    PRESAL4      
---------- --------- ---------- ---------- ---------- ---------- ----------      
   PRESAL5                                                                       
----------                                                                       
JAMES      03-DEC-81        950       5000       1250       1500       2450      
      2850                                                                       
                                                                                 
FORD       03-DEC-81       3000        950       5000       1250       1500      
      2450                                                                       
                                                                                 
MILLER     23-JAN-82       1300       3000        950       5000       1250      
      1500                                                                       
 
                                                                                 
 
ENAME      HIREDATE         SAL    PRESAL1    PRESAL2    PRESAL3    PRESAL4      
---------- --------- ---------- ---------- ---------- ---------- ----------      
   PRESAL5                                                                       
----------                                                                       
SCOTT      09-DEC-82       3000       1300       3000        950       5000      
      1250                                                                       
                                                                                 
ADAMS      12-JAN-83       1100       3000       1300       3000        950      
      5000                                                                       
                                                                                 
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT  Ename, HireDate, Sal, 
  2  LAG(Sal, 1) 
  3  OVER(ORDER BY HireDate) PreSal1, 
  4  LAG(Sal, 2) 
  5  OVER(ORDER BY HireDate) PreSal2, 
  6  LAG(Sal, 3) 
  7  OVER(ORDER BY HireDate) PreSal3, 
  8  LAG(Sal, 4) 
  9  OVER(ORDER BY HireDate) PreSal4 
 10* FROM  Emp 
SQL> / 
 
ENAME      HIREDATE         SAL    PRESAL1    PRESAL2    PRESAL3    PRESAL4      
---------- --------- ---------- ---------- ---------- ---------- ----------      
SMITH      17-DEC-80        800                                                  
ALLEN      20-FEB-81       1600        800                                       
WARD       22-FEB-81       1250       1600        800                            
JONES      02-APR-81       2975       1250       1600        800                 
BLAKE      01-MAY-81       2850       2975       1250       1600        800      
CLARK      09-JUN-81       2450       2850       2975       1250       1600      
TURNER     08-SEP-81       1500       2450       2850       2975       1250      
MARTIN     28-SEP-81       1250       1500       2450       2850       2975      
KING       17-NOV-81       5000       1250       1500       2450       2850      
JAMES      03-DEC-81        950       5000       1250       1500       2450      
FORD       03-DEC-81       3000        950       5000       1250       1500      
 
ENAME      HIREDATE         SAL    PRESAL1    PRESAL2    PRESAL3    PRESAL4      
---------- --------- ---------- ---------- ---------- ---------- ----------      
MILLER     23-JAN-82       1300       3000        950       5000       1250      
SCOTT      09-DEC-82       3000       1300       3000        950       5000      
ADAMS      12-JAN-83       1100       3000       1300       3000        950      
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT  Ename, HireDate, Sal, 
  2  LAG(Sal, &GOffset) 
 
  3  OVER(ORDER BY HireDate) PreSal 
  4* FROM  Emp 
SQL> / 
Enter value for goffset: 1 
old   2: LAG(Sal, &GOffset) 
new   2: LAG(Sal, 1) 
 
ENAME      HIREDATE         SAL     PRESAL                                       
---------- --------- ---------- ----------                                       
SMITH      17-DEC-80        800                                                  
ALLEN      20-FEB-81       1600        800                                       
WARD       22-FEB-81       1250       1600                                       
JONES      02-APR-81       2975       1250                                       
BLAKE      01-MAY-81       2850       2975                                       
CLARK      09-JUN-81       2450       2850                                       
TURNER     08-SEP-81       1500       2450                                       
MARTIN     28-SEP-81       1250       1500                                       
KING       17-NOV-81       5000       1250                                       
JAMES      03-DEC-81        950       5000                                       
FORD       03-DEC-81       3000        950                                       
 
ENAME      HIREDATE         SAL     PRESAL                                       
---------- --------- ---------- ----------                                       
MILLER     23-JAN-82       1300       3000                                       
SCOTT      09-DEC-82       3000       1300                                       
ADAMS      12-JAN-83       1100       3000                                       
 
14 rows selected. 
 
SQL> / 
Enter value for goffset: 2 
old   2: LAG(Sal, &GOffset) 
new   2: LAG(Sal, 2) 
 
ENAME      HIREDATE         SAL     PRESAL                                       
---------- --------- ---------- ----------                                       
SMITH      17-DEC-80        800                                                  
ALLEN      20-FEB-81       1600                                                  
WARD       22-FEB-81       1250        800                                       
JONES      02-APR-81       2975       1600                                       
BLAKE      01-MAY-81       2850       1250                                       
CLARK      09-JUN-81       2450       2975                                       
TURNER     08-SEP-81       1500       2850                                       
MARTIN     28-SEP-81       1250       2450                                       
KING       17-NOV-81       5000       1500                                       
JAMES      03-DEC-81        950       1250                                       
FORD       03-DEC-81       3000       5000                                       
 
ENAME      HIREDATE         SAL     PRESAL                                       
---------- --------- ---------- ----------                                       
MILLER     23-JAN-82       1300        950                                       
SCOTT      09-DEC-82       3000       3000                                       
ADAMS      12-JAN-83       1100       1300                                       
 
14 rows selected. 
 
SQL> ED 
 
Wrote file afiedt.buf 
 
  1  SELECT  Ename, HireDate, Sal, 
  2  LAG(Sal, &GOffset) 
  3  OVER(ORDER BY HireDate) PreSal 
  4  FROM  Emp 
  5* WHERE Ename = &GEname 
SQL> SET VERIFY OFF 
SQL> / 
Enter value for goffset: 1 
Enter value for gename: KING 
WHERE Ename = KING 
              * 
ERROR at line 5: 
ORA-00904: "KING": invalid identifier  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT  Ename, HireDate, Sal, 
  2  LAG(Sal, &GOffset) 
  3  OVER(ORDER BY HireDate) PreSal 
  4  FROM  Emp 
  5* WHERE Ename = UPPER('&GEname') 
SQL> / 
Enter value for goffset: 1 
Enter value for gename: king 
 
ENAME      HIREDATE         SAL     PRESAL                                       
---------- --------- ---------- ----------                                       
KING       17-NOV-81       5000                                                  
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT  Ename, HireDate, Sal, 
  2  LAG(Sal, &GOffset) 
  3  OVER(ORDER BY HireDate) PreSal 
  4  FROM  Emp 
  5* WHERE Deptno = 10 
SQL> / 
Enter value for goffset: 1 
 
ENAME      HIREDATE         SAL     PRESAL                                       
---------- --------- ---------- ----------                                       
CLARK      09-JUN-81       2450                                                  
KING       17-NOV-81       5000       2450                                       
MILLER     23-JAN-82       1300       5000                                       
 
SQL> cl scr 
 
SQL> SELECT  Ename, HireDate, Sal, 
  2  LEAD(Sal, 1, 0) 
  3  OVER(ORDER BY HireDate) NextSal 
  4  FROM  Emp; 
 
 
ENAME      HIREDATE         SAL    NEXTSAL                                       
---------- --------- ---------- ----------                                       
SMITH      17-DEC-80        800       1600                                       
ALLEN      20-FEB-81       1600       1250                                       
WARD       22-FEB-81       1250       2975                                       
JONES      02-APR-81       2975       2850                                       
BLAKE      01-MAY-81       2850       2450                                       
CLARK      09-JUN-81       2450       1500                                       
TURNER     08-SEP-81       1500       1250                                       
MARTIN     28-SEP-81       1250       5000                                       
KING       17-NOV-81       5000        950                                       
JAMES      03-DEC-81        950       3000                                       
FORD       03-DEC-81       3000       1300                                       
 
ENAME      HIREDATE         SAL    NEXTSAL                                       
---------- --------- ---------- ----------                                       
MILLER     23-JAN-82       1300       3000                                       
SCOTT      09-DEC-82       3000       1100                                       
ADAMS      12-JAN-83       1100          0                                       
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT  Ename, HireDate,  
  2  LAG(Sal, 1, 0) 
  3  OVER(ORDER BY HireDate) PreSal, 
  4  Sal CurrSal, 
  5  LEAD(Sal, 1, 0) 
  6  OVER(ORDER BY HireDate) NextSal 
  7* FROM  Emp 
SQL> / 
 
ENAME      HIREDATE      PRESAL    CURRSAL    NEXTSAL                            
---------- --------- ---------- ---------- ----------                            
SMITH      17-DEC-80          0        800       1600                            
ALLEN      20-FEB-81        800       1600       1250                            
WARD       22-FEB-81       1600       1250       2975                            
JONES      02-APR-81       1250       2975       2850                            
BLAKE      01-MAY-81       2975       2850       2450                            
CLARK      09-JUN-81       2850       2450       1500                            
TURNER     08-SEP-81       2450       1500       1250                            
MARTIN     28-SEP-81       1500       1250       5000                            
KING       17-NOV-81       1250       5000        950                            
JAMES      03-DEC-81       5000        950       3000                            
FORD       03-DEC-81        950       3000       1300                            
 
ENAME      HIREDATE      PRESAL    CURRSAL    NEXTSAL                            
---------- --------- ---------- ---------- ----------                            
MILLER     23-JAN-82       3000       1300       3000                            
SCOTT      09-DEC-82       1300       3000       1100                            
ADAMS      12-JAN-83       3000       1100          0                            
 
14 rows selected. 
 
SQL> ED 
 
Wrote file afiedt.buf 
 
  1  SELECT  Ename, HireDate, 
  2  LAG(Sal, 2, 0) 
  3  OVER(ORDER BY HireDate) PreSal2, 
  4  LAG(Sal, 1, 0) 
  5  OVER(ORDER BY HireDate) PreSal1, 
  6  Sal CurrSal, 
  7  LEAD(Sal, 1, 0) 
  8  OVER(ORDER BY HireDate) NextSal1 
  9  LEAD(Sal, 2, 0) 
 10  OVER(ORDER BY HireDate) NextSal2 
 11* FROM  Emp 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT  Ename, HireDate, 
  2  LAG(Sal, 2, 0) 
  3  OVER(ORDER BY HireDate) PreSal2, 
  4  LAG(Sal, 1, 0) 
  5  OVER(ORDER BY HireDate) PreSal1, 
  6  Sal CurrSal, 
  7  LEAD(Sal, 1, 0) 
  8  OVER(ORDER BY HireDate) NextSal1, 
  9  LEAD(Sal, 2, 0) 
 10  OVER(ORDER BY HireDate) NextSal2 
 11* FROM  Emp 
SQL> / 
 
ENAME      HIREDATE     PRESAL2    PRESAL1    CURRSAL   NEXTSAL1   NEXTSAL2      
---------- --------- ---------- ---------- ---------- ---------- ----------      
SMITH      17-DEC-80          0          0        800       1600       1250      
ALLEN      20-FEB-81          0        800       1600       1250       2975      
WARD       22-FEB-81        800       1600       1250       2975       2850      
JONES      02-APR-81       1600       1250       2975       2850       2450      
BLAKE      01-MAY-81       1250       2975       2850       2450       1500      
CLARK      09-JUN-81       2975       2850       2450       1500       1250      
TURNER     08-SEP-81       2850       2450       1500       1250       5000      
MARTIN     28-SEP-81       2450       1500       1250       5000        950      
KING       17-NOV-81       1500       1250       5000        950       3000      
JAMES      03-DEC-81       1250       5000        950       3000       1300      
FORD       03-DEC-81       5000        950       3000       1300       3000      
 
ENAME      HIREDATE     PRESAL2    PRESAL1    CURRSAL   NEXTSAL1   NEXTSAL2      
---------- --------- ---------- ---------- ---------- ---------- ----------      
MILLER     23-JAN-82        950       3000       1300       3000       1100      
SCOTT      09-DEC-82       3000       1300       3000       1100          0      
ADAMS      12-JAN-83       1300       3000       1100          0          0      
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT  Ename, HireDate, Sal, 
  2  Sal - LAG(Sal, 1, 0) 
  3  OVER(ORDER BY HireDate) DiffPreSal 
  4  FROM  Emp; 
 
 
ENAME      HIREDATE         SAL DIFFPRESAL                                       
---------- --------- ---------- ----------                                       
SMITH      17-DEC-80        800        800                                       
ALLEN      20-FEB-81       1600        800                                       
WARD       22-FEB-81       1250       -350                                       
JONES      02-APR-81       2975       1725                                       
BLAKE      01-MAY-81       2850       -125                                       
CLARK      09-JUN-81       2450       -400                                       
TURNER     08-SEP-81       1500       -950                                       
MARTIN     28-SEP-81       1250       -250                                       
KING       17-NOV-81       5000       3750                                       
JAMES      03-DEC-81        950      -4050                                       
FORD       03-DEC-81       3000       2050                                       
 
ENAME      HIREDATE         SAL DIFFPRESAL                                       
---------- --------- ---------- ----------                                       
MILLER     23-JAN-82       1300      -1700                                       
SCOTT      09-DEC-82       3000       1700                                       
ADAMS      12-JAN-83       1100      -1900                                       
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT  Ename, HireDate, Sal, 
  2  Sal - LEAD(Sal, 1, 0) 
  3  OVER(ORDER BY HireDate) DiffNextSal 
  4  FROM  Emp; 
 
ENAME      HIREDATE         SAL DIFFNEXTSAL                                      
---------- --------- ---------- -----------                                      
SMITH      17-DEC-80        800        -800                                      
ALLEN      20-FEB-81       1600         350                                      
WARD       22-FEB-81       1250       -1725                                      
JONES      02-APR-81       2975         125                                      
BLAKE      01-MAY-81       2850         400                                      
CLARK      09-JUN-81       2450         950                                      
TURNER     08-SEP-81       1500         250                                      
MARTIN     28-SEP-81       1250       -3750                                      
KING       17-NOV-81       5000        4050                                      
JAMES      03-DEC-81        950       -2050                                      
FORD       03-DEC-81       3000        1700                                      
 
ENAME      HIREDATE         SAL DIFFNEXTSAL                                      
---------- --------- ---------- -----------                                      
MILLER     23-JAN-82       1300       -1700                                      
SCOTT      09-DEC-82       3000        1900                                      
ADAMS      12-JAN-83       1100        1100                                      
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> COLUMN Remarks FORMAT A30 
SQL> SELECT E1.Ename, E1.HireDate, E1.Sal, 
ABS(E1.DiffNExtSal)||DECODE(SIGN(E1.DiffNextSal), 
 
  2         1, ' More Than Next Salary.', 
  3         -1, ' Less Than Next Salary.', 
  4         ' Salaries Equal.') Remarks 
  5  FROM (SELECT  Ename, HireDate, Sal, 
  6    Sal - LEAD(Sal, 1, 0) 
  7    OVER(ORDER BY HireDate) DiffNextSal 
  8    FROM  Emp) E1; 
 
ENAME      HIREDATE         SAL REMARKS                                          
---------- --------- ---------- ------------------------------                   
SMITH      17-DEC-80        800 800 Less Than Next Salary.                       
ALLEN      20-FEB-81       1600 350 More Than Next Salary.                       
WARD       22-FEB-81       1250 1725 Less Than Next Salary.                      
JONES      02-APR-81       2975 125 More Than Next Salary.                       
BLAKE      01-MAY-81       2850 400 More Than Next Salary.                       
CLARK      09-JUN-81       2450 950 More Than Next Salary.                       
TURNER     08-SEP-81       1500 250 More Than Next Salary.                       
MARTIN     28-SEP-81       1250 3750 Less Than Next Salary.                      
KING       17-NOV-81       5000 4050 More Than Next Salary.                      
JAMES      03-DEC-81        950 2050 Less Than Next Salary.                      
FORD       03-DEC-81       3000 1700 More Than Next Salary.                      
 
ENAME      HIREDATE         SAL REMARKS                                          
---------- --------- ---------- ------------------------------                   
MILLER     23-JAN-82       1300 1700 Less Than Next Salary.                      
SCOTT      09-DEC-82       3000 1900 More Than Next Salary.                      
ADAMS      12-JAN-83       1100 1100 More Than Next Salary.                      
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> COLUMN Remarks FORMAT A40 
SQL> SELECT E1.Deptno, E1.DeptSalSum, 
  2  ABS(E1.DeptSalSum - NextSal)|| 
  3  DECODE(NVL(SIGN(E1.DeptSalSum - NextSal), 0), 
  4             1, ' More Budget Than Next Department', 
  5             -1, ' Less Budget Than Next Department', 
  6             0, ' Terminating Department') Remarks 
  7  FROM (SELECT  Deptno, SUM(Sal) DeptSalSum, 
  8    LEAD(SUM(Sal), 1, NULL) 
  9    OVER(ORDER BY Deptno) NextSal 
 10    FROM  Emp 
 11    GROUP BY Deptno) E1; 
 
    DEPTNO DEPTSALSUM REMARKS                                                    
---------- ---------- ----------------------------------------                   
        10       8750 2125 Less Budget Than Next Department                      
        20      10875 1475 More Budget Than Next Department                      
        30       9400  Terminating Department                                    
 
SQL> cl scr 
 
SQL> BREAK ON Deptno SKIP 1 
SQL> COLUMN DaysDiff FORMAT A40 
SQL> COLUMN DEPTNO FORMAT 99 
SQL> COLUMN ENAME FORMAT A10 
 
SQL> SELECT Deptno, Ename, HireDate, 
  2  LAG(HireDate, 1, NULL) 
  3  OVER(PARTITION BY Deptno 
  4              ORDER BY HireDate, Ename)   Last_Hire, 
  5  NVL(HireDate - LAG(HireDate, 1, Null) 
  6           OVER (PARTITION BY Deptno 
  7     ORDER BY HireDate, Ename), 0)||' Days of Difference.'  DaysDiff 
  8  FROM  Emp 
  9  ORDER BY DeptNo, HireDate; 
 
DEPTNO ENAME      HIREDATE  LAST_HIRE DAYSDIFF                                   
------ ---------- --------- --------- ----------------------------------------   
    10 CLARK      09-JUN-81           0 Days of Difference.                      
       KING       17-NOV-81 09-JUN-81 161 Days of Difference.                    
       MILLER     23-JAN-82 17-NOV-81 67 Days of Difference.                     
                                                                                 
    20 SMITH      17-DEC-80           0 Days of Difference.                      
       JONES      02-APR-81 17-DEC-80 106 Days of Difference.                    
       FORD       03-DEC-81 02-APR-81 245 Days of Difference.                    
       SCOTT      09-DEC-82 03-DEC-81 371 Days of Difference.                    
       ADAMS      12-JAN-83 09-DEC-82 34 Days of Difference.                     
                                                                                 
    30 ALLEN      20-FEB-81           0 Days of Difference.                      
 
DEPTNO ENAME      HIREDATE  LAST_HIRE DAYSDIFF                                   
------ ---------- --------- --------- ----------------------------------------   
    30 WARD       22-FEB-81 20-FEB-81 2 Days of Difference.                      
       BLAKE      01-MAY-81 22-FEB-81 68 Days of Difference.                     
       TURNER     08-SEP-81 01-MAY-81 130 Days of Difference.                    
       MARTIN     28-SEP-81 08-SEP-81 20 Days of Difference.                     
       JAMES      03-DEC-81 28-SEP-81 66 Days of Difference.                     
                                                                                 
 
14 rows selected. 
 
SQL> BREAK ON Deptno DUP 
SQL> cl scr 
 
SQL> SELECT Ename, Deptno, Sal, 
  2  FIRST_VALUE(Ename) 
  3  OVER(PARTITION BY DeptNo 
  4  ORDER BY Sal DESC)  Max_Sal_Name 
  5  FROM   Emp ORDER BY Deptno, Sal DESC, Ename DESC; 
 
ENAME      DEPTNO        SAL MAX_SAL_NA                                          
---------- ------ ---------- ----------                                          
KING           10       5000 KING                                                
CLARK          10       2450 KING                                                
MILLER         10       1300 KING                                                
SCOTT          20       3000 FORD                                                
FORD           20       3000 FORD                                                
JONES          20       2975 FORD                                                
ADAMS          20       1100 FORD                                                
SMITH          20        800 FORD                                                
BLAKE          30       2850 BLAKE                                               
ALLEN          30       1600 BLAKE                                               
TURNER         30       1500 BLAKE                                               
 
 
ENAME      DEPTNO        SAL MAX_SAL_NA                                          
---------- ------ ---------- ----------                                          
WARD           30       1250 BLAKE                                               
MARTIN         30       1250 BLAKE                                               
JAMES          30        950 BLAKE                                               
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
  2  FIRST_VALUE(Ename) 
  3  OVER(PARTITION BY DeptNo 
  4  ORDER BY Sal DESC)  Max_Sal_Name 
  5  FROM   Emp  
  6* ORDER BY Deptno, Sal DESC 
SQL> / 
 
ENAME      DEPTNO        SAL MAX_SAL_NA                                          
---------- ------ ---------- ----------                                          
KING           10       5000 KING                                                
CLARK          10       2450 KING                                                
MILLER         10       1300 KING                                                
FORD           20       3000 FORD                                                
SCOTT          20       3000 FORD                                                
JONES          20       2975 FORD                                                
ADAMS          20       1100 FORD                                                
SMITH          20        800 FORD                                                
BLAKE          30       2850 BLAKE                                               
ALLEN          30       1600 BLAKE                                               
TURNER         30       1500 BLAKE                                               
 
ENAME      DEPTNO        SAL MAX_SAL_NA                                          
---------- ------ ---------- ----------                                          
MARTIN         30       1250 BLAKE                                               
WARD           30       1250 BLAKE                                               
JAMES          30        950 BLAKE                                               
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
  2  LAST_VALUE(Ename) 
  3  OVER(PARTITION BY DeptNo 
  4  ORDER BY Sal DESC)  Last 
  5  FROM   Emp  
  6* ORDER BY Deptno, Sal DESC 
SQL> / 
 
ENAME      DEPTNO        SAL LAST                                                
---------- ------ ---------- ----------                                          
KING           10       5000 KING                                                
CLARK          10       2450 CLARK                                               
 
MILLER         10       1300 MILLER                                              
FORD           20       3000 SCOTT                                               
SCOTT          20       3000 SCOTT                                               
JONES          20       2975 JONES                                               
ADAMS          20       1100 ADAMS                                               
SMITH          20        800 SMITH                                               
BLAKE          30       2850 BLAKE                                               
ALLEN          30       1600 ALLEN                                               
TURNER         30       1500 TURNER                                              
 
ENAME      DEPTNO        SAL LAST                                                
---------- ------ ---------- ----------                                          
MARTIN         30       1250 WARD                                                
WARD           30       1250 WARD                                                
JAMES          30        950 JAMES                                               
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
  2  FIRST_VALUE(Ename) 
  3  OVER(PARTITION BY DeptNo 
  4  ORDER BY Sal DESC)  Last 
  5  FROM   Emp  
  6* ORDER BY Deptno, Sal DESC 
SQL> / 
 
ENAME      DEPTNO        SAL LAST                                                
---------- ------ ---------- ----------                                          
KING           10       5000 KING                                                
CLARK          10       2450 KING                                                
MILLER         10       1300 KING                                                
FORD           20       3000 FORD                                                
SCOTT          20       3000 FORD                                                
JONES          20       2975 FORD                                                
ADAMS          20       1100 FORD                                                
SMITH          20        800 FORD                                                
BLAKE          30       2850 BLAKE                                               
ALLEN          30       1600 BLAKE                                               
TURNER         30       1500 BLAKE                                               
 
ENAME      DEPTNO        SAL LAST                                                
---------- ------ ---------- ----------                                          
MARTIN         30       1250 BLAKE                                               
WARD           30       1250 BLAKE                                               
JAMES          30        950 BLAKE                                               
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
  2  FIRST_VALUE(Ename) 
  3  OVER(PARTITION BY DeptNo 
 
  4  ORDER BY Sal DESC)  First 
  5  FROM   Emp  
  6* ORDER BY Deptno, Sal DESC 
SQL> / 
 
ENAME      DEPTNO        SAL FIRST                                               
---------- ------ ---------- ----------                                          
KING           10       5000 KING                                                
CLARK          10       2450 KING                                                
MILLER         10       1300 KING                                                
FORD           20       3000 FORD                                                
SCOTT          20       3000 FORD                                                
JONES          20       2975 FORD                                                
ADAMS          20       1100 FORD                                                
SMITH          20        800 FORD                                                
BLAKE          30       2850 BLAKE                                               
ALLEN          30       1600 BLAKE                                               
TURNER         30       1500 BLAKE                                               
 
ENAME      DEPTNO        SAL FIRST                                               
---------- ------ ---------- ----------                                          
MARTIN         30       1250 BLAKE                                               
WARD           30       1250 BLAKE                                               
JAMES          30        950 BLAKE                                               
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
  2  LAST_VALUE(Ename) 
  3  OVER(PARTITION BY DeptNo 
  4  ORDER BY Sal DESC)  Last 
  5  FROM   Emp  
  6* ORDER BY Deptno, Sal DESC 
SQL> / 
 
ENAME      DEPTNO        SAL LAST                                                
---------- ------ ---------- ----------                                          
KING           10       5000 KING                                                
CLARK          10       2450 CLARK                                               
MILLER         10       1300 MILLER                                              
FORD           20       3000 SCOTT                                               
SCOTT          20       3000 SCOTT                                               
JONES          20       2975 JONES                                               
ADAMS          20       1100 ADAMS                                               
SMITH          20        800 SMITH                                               
BLAKE          30       2850 BLAKE                                               
ALLEN          30       1600 ALLEN                                               
TURNER         30       1500 TURNER                                              
 
ENAME      DEPTNO        SAL LAST                                                
---------- ------ ---------- ----------                                          
MARTIN         30       1250 WARD                                                
WARD           30       1250 WARD                                                
JAMES          30        950 JAMES                                               
 
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
  2  LAST_VALUE(Ename) 
  3  OVER(PARTITION BY DeptNo 
  4  ORDER BY Deptno)  Last 
  5  FROM   Emp  
  6* ORDER BY Deptno 
SQL> / 
 
ENAME      DEPTNO        SAL LAST                                                
---------- ------ ---------- ----------                                          
CLARK          10       2450 MILLER                                              
KING           10       5000 MILLER                                              
MILLER         10       1300 MILLER                                              
ADAMS          20       1100 SMITH                                               
FORD           20       3000 SMITH                                               
JONES          20       2975 SMITH                                               
SCOTT          20       3000 SMITH                                               
SMITH          20        800 SMITH                                               
ALLEN          30       1600 WARD                                                
BLAKE          30       2850 WARD                                                
JAMES          30        950 WARD                                                
 
ENAME      DEPTNO        SAL LAST                                                
---------- ------ ---------- ----------                                          
MARTIN         30       1250 WARD                                                
TURNER         30       1500 WARD                                                
WARD           30       1250 WARD                                                
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT Ename, Deptno, Sal, 
  2  FIRST_VALUE(Ename) 
  3  OVER(ORDER BY Sal ASC 
  4  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)  Min_Sal_Name 
  5  FROM (SELECT  * 
  6    FROM  Emp 
  7    WHERE Deptno = 30); 
 
ENAME      DEPTNO        SAL MIN_SAL_NA                                          
---------- ------ ---------- ----------                                          
JAMES          30        950 JAMES                                               
MARTIN         30       1250 JAMES                                               
WARD           30       1250 JAMES                                               
TURNER         30       1500 JAMES                                               
ALLEN          30       1600 JAMES                                               
BLAKE          30       2850 JAMES                                               
 
6 rows selected. 
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
  2  LAST_VALUE(Ename) 
  3  OVER(ORDER BY Sal ASC 
  4  )  Min_Sal_Name 
  5  FROM (SELECT  * 
  6    FROM  Emp 
  7*   WHERE Deptno = 30) 
SQL> / 
 
ENAME      DEPTNO        SAL MIN_SAL_NA                                          
---------- ------ ---------- ----------                                          
JAMES          30        950 JAMES                                               
MARTIN         30       1250 WARD                                                
WARD           30       1250 WARD                                                
TURNER         30       1500 TURNER                                              
ALLEN          30       1600 ALLEN                                               
BLAKE          30       2850 BLAKE                                               
 
6 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
  2  LAST_VALUE(Ename) 
  3  OVER(ORDER BY Sal ASC 
  4  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)  Min_Sal_Name 
  5  FROM (SELECT  * 
  6    FROM  Emp 
  7*   WHERE Deptno = 30) 
SQL> / 
 
ENAME      DEPTNO        SAL MIN_SAL_NA                                          
---------- ------ ---------- ----------                                          
JAMES          30        950 BLAKE                                               
MARTIN         30       1250 BLAKE                                               
WARD           30       1250 BLAKE                                               
TURNER         30       1500 BLAKE                                               
ALLEN          30       1600 BLAKE                                               
BLAKE          30       2850 BLAKE                                               
 
6 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
  2  LAST_VALUE(Ename) 
  3  OVER(ORDER BY Sal ASC 
  4  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)  Min_Sal_Name 
  5* FROM Emp 
  6  / 
 
ENAME      DEPTNO        SAL MIN_SAL_NA                                          
 
---------- ------ ---------- ----------                                          
SMITH          20        800 KING                                                
JAMES          30        950 KING                                                
ADAMS          20       1100 KING                                                
MARTIN         30       1250 KING                                                
WARD           30       1250 KING                                                
MILLER         10       1300 KING                                                
TURNER         30       1500 KING                                                
ALLEN          30       1600 KING                                                
CLARK          10       2450 KING                                                
BLAKE          30       2850 KING                                                
JONES          20       2975 KING                                                
 
ENAME      DEPTNO        SAL MIN_SAL_NA                                          
---------- ------ ---------- ----------                                          
FORD           20       3000 KING                                                
SCOTT          20       3000 KING                                                
KING           10       5000 KING                                                
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
  2  FIRSt_VALUE(Ename) 
  3  OVER(ORDER BY Sal ASC 
  4  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)  Min_Sal_Name 
  5* FROM Emp 
SQL> / 
 
ENAME      DEPTNO        SAL MIN_SAL_NA                                          
---------- ------ ---------- ----------                                          
SMITH          20        800 SMITH                                               
JAMES          30        950 SMITH                                               
ADAMS          20       1100 SMITH                                               
MARTIN         30       1250 SMITH                                               
WARD           30       1250 SMITH                                               
MILLER         10       1300 SMITH                                               
TURNER         30       1500 SMITH                                               
ALLEN          30       1600 SMITH                                               
CLARK          10       2450 SMITH                                               
BLAKE          30       2850 SMITH                                               
JONES          20       2975 SMITH                                               
 
ENAME      DEPTNO        SAL MIN_SAL_NA                                          
---------- ------ ---------- ----------                                          
FORD           20       3000 SMITH                                               
SCOTT          20       3000 SMITH                                               
KING           10       5000 SMITH                                               
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
 
  2  FIRST_VALUE(Ename) 
  3  OVER( 
  4  PARTITION BY Deptno 
  5  ORDER BY Sal ASC 
  6  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)  Min_Sal_Name 
  7* FROM Emp 
SQL> / 
 
ENAME      DEPTNO        SAL MIN_SAL_NA                                          
---------- ------ ---------- ----------                                          
MILLER         10       1300 MILLER                                              
CLARK          10       2450 MILLER                                              
KING           10       5000 MILLER                                              
SMITH          20        800 SMITH                                               
ADAMS          20       1100 SMITH                                               
JONES          20       2975 SMITH                                               
FORD           20       3000 SMITH                                               
SCOTT          20       3000 SMITH                                               
JAMES          30        950 JAMES                                               
MARTIN         30       1250 JAMES                                               
WARD           30       1250 JAMES                                               
 
ENAME      DEPTNO        SAL MIN_SAL_NA                                          
---------- ------ ---------- ----------                                          
TURNER         30       1500 JAMES                                               
ALLEN          30       1600 JAMES                                               
BLAKE          30       2850 JAMES                                               
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
  2  FIRST_VALUE(Ename) 
  3  OVER( 
  4  PARTITION BY Deptno 
  5  ORDER BY Sal ASC 
  6  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)  Min_Sal_Name, 
  7  LAST_VALUE(Ename) 
  8  OVER( 
  9  PARTITION BY Deptno 
 10  ORDER BY Sal ASC 
 11  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)  Max_Sal_Name 
 12* FROM Emp 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
  2  FIRST_VALUE(Ename) 
  3  OVER( 
  4  PARTITION BY Deptno 
  5  ORDER BY Sal ASC 
  6  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)  Min_Sal_Name, 
  7  LAST_VALUE(Ename) 
  8  OVER( 
  9  PARTITION BY Deptno 
 
 10  ORDER BY Sal ASC 
 11  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)  Max_Sal_Name 
 12* FROM Emp 
SQL> / 
 
ENAME      DEPTNO        SAL MIN_SAL_NA MAX_SAL_NA                               
---------- ------ ---------- ---------- ----------                               
MILLER         10       1300 MILLER     KING                                     
CLARK          10       2450 MILLER     KING                                     
KING           10       5000 MILLER     KING                                     
SMITH          20        800 SMITH      SCOTT                                    
ADAMS          20       1100 SMITH      SCOTT                                    
JONES          20       2975 SMITH      SCOTT                                    
FORD           20       3000 SMITH      SCOTT                                    
SCOTT          20       3000 SMITH      SCOTT                                    
JAMES          30        950 JAMES      BLAKE                                    
MARTIN         30       1250 JAMES      BLAKE                                    
WARD           30       1250 JAMES      BLAKE                                    
 
ENAME      DEPTNO        SAL MIN_SAL_NA MAX_SAL_NA                               
---------- ------ ---------- ---------- ----------                               
TURNER         30       1500 JAMES      BLAKE                                    
ALLEN          30       1600 JAMES      BLAKE                                    
BLAKE          30       2850 JAMES      BLAKE                                    
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
  2  SUM(Sal) 
  3  OVER( 
  4  ORDER BY Sal ASC 
  5  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 
  6* FROM Emp 
SQL> / 
 
ENAME      DEPTNO        SAL                                                     
---------- ------ ----------                                                     
SUM(SAL)OVER(ORDERBYSALASCROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)    
-----------------------------------------------------------------------------    
SMITH          20        800                                                     
                                                                        29025    
                                                                                 
JAMES          30        950                                                     
                                                                        29025    
                                                                                 
ADAMS          20       1100                                                     
                                                                        29025    
                                                                                 
 
ENAME      DEPTNO        SAL                                                     
---------- ------ ----------                                                     
SUM(SAL)OVER(ORDERBYSALASCROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)    
-----------------------------------------------------------------------------    
MARTIN         30       1250                                                     
 
                                                                        29025    
                                                                                 
WARD           30       1250                                                     
                                                                        29025    
                                                                                 
MILLER         10       1300                                                     
                                                                        29025    
                                                                                 
 
ENAME      DEPTNO        SAL                                                     
---------- ------ ----------                                                     
SUM(SAL)OVER(ORDERBYSALASCROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)    
-----------------------------------------------------------------------------    
TURNER         30       1500                                                     
                                                                        29025    
                                                                                 
ALLEN          30       1600                                                     
                                                                        29025    
                                                                                 
CLARK          10       2450                                                     
                                                                        29025    
                                                                                 
 
ENAME      DEPTNO        SAL                                                     
---------- ------ ----------                                                     
SUM(SAL)OVER(ORDERBYSALASCROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)    
-----------------------------------------------------------------------------    
BLAKE          30       2850                                                     
                                                                        29025    
                                                                                 
JONES          20       2975                                                     
                                                                        29025    
                                                                                 
FORD           20       3000                                                     
                                                                        29025    
                                                                                 
 
ENAME      DEPTNO        SAL                                                     
---------- ------ ----------                                                     
SUM(SAL)OVER(ORDERBYSALASCROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)    
-----------------------------------------------------------------------------    
SCOTT          20       3000                                                     
                                                                        29025    
                                                                                 
KING           10       5000                                                     
                                                                        29025    
                                                                                 
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
  2  SUM(Sal) 
  3  OVER( 
  4  ORDER BY Sal ASC 
 
  5  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) "Slide Sal" 
  6* FROM Emp 
SQL> / 
 
ENAME      DEPTNO        SAL  Slide Sal                                          
---------- ------ ---------- ----------                                          
SMITH          20        800      29025                                          
JAMES          30        950      29025                                          
ADAMS          20       1100      29025                                          
MARTIN         30       1250      29025                                          
WARD           30       1250      29025                                          
MILLER         10       1300      29025                                          
TURNER         30       1500      29025                                          
ALLEN          30       1600      29025                                          
CLARK          10       2450      29025                                          
BLAKE          30       2850      29025                                          
JONES          20       2975      29025                                          
 
ENAME      DEPTNO        SAL  Slide Sal                                          
---------- ------ ---------- ----------                                          
FORD           20       3000      29025                                          
SCOTT          20       3000      29025                                          
KING           10       5000      29025                                          
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
  2  SUM(Sal) 
  3  OVER( 
  4  ORDER BY Sal ASC 
  5  ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) "Slide Sal" 
  6* FROM Emp 
SQL> / 
 
ENAME      DEPTNO        SAL  Slide Sal                                          
---------- ------ ---------- ----------                                          
SMITH          20        800      29025                                          
JAMES          30        950      29025                                          
ADAMS          20       1100      28225                                          
MARTIN         30       1250      27275                                          
WARD           30       1250      26175                                          
MILLER         10       1300      24925                                          
TURNER         30       1500      23675                                          
ALLEN          30       1600      22375                                          
CLARK          10       2450      20875                                          
BLAKE          30       2850      19275                                          
JONES          20       2975      16825                                          
 
ENAME      DEPTNO        SAL  Slide Sal                                          
---------- ------ ---------- ----------                                          
FORD           20       3000      13975                                          
SCOTT          20       3000      11000                                          
KING           10       5000       8000                                          
 
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
  2  SUM(Sal) 
  3  OVER( 
  4  ORDER BY Sal ASC 
  5  ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING) "Slide Sal" 
  6* FROM Emp 
SQL> / 
 
ENAME      DEPTNO        SAL  Slide Sal                                          
---------- ------ ---------- ----------                                          
SMITH          20        800      29025                                          
JAMES          30        950      29025                                          
ADAMS          20       1100      29025                                          
MARTIN         30       1250      28225                                          
WARD           30       1250      27275                                          
MILLER         10       1300      26175                                          
TURNER         30       1500      24925                                          
ALLEN          30       1600      23675                                          
CLARK          10       2450      22375                                          
BLAKE          30       2850      20875                                          
JONES          20       2975      19275                                          
 
ENAME      DEPTNO        SAL  Slide Sal                                          
---------- ------ ---------- ----------                                          
FORD           20       3000      16825                                          
SCOTT          20       3000      13975                                          
KING           10       5000      11000                                          
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
  2  SUM(Sal) 
  3  OVER( 
  4  ORDER BY Sal ASC 
  5  ROWS BETWEEN 0 PRECEDING AND UNBOUNDED FOLLOWING) "Slide Sal" 
  6* FROM Emp 
SQL> / 
 
ENAME      DEPTNO        SAL  Slide Sal                                          
---------- ------ ---------- ----------                                          
SMITH          20        800      29025                                          
JAMES          30        950      28225                                          
ADAMS          20       1100      27275                                          
MARTIN         30       1250      26175                                          
WARD           30       1250      24925                                          
MILLER         10       1300      23675                                          
TURNER         30       1500      22375                                          
ALLEN          30       1600      20875                                          
CLARK          10       2450      19275                                          
 
BLAKE          30       2850      16825                                          
JONES          20       2975      13975                                          
 
ENAME      DEPTNO        SAL  Slide Sal                                          
---------- ------ ---------- ----------                                          
FORD           20       3000      11000                                          
SCOTT          20       3000       8000                                          
KING           10       5000       5000                                          
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
  2  SUM(Sal) 
  3  OVER( 
  4  ORDER BY Sal ASC 
  5  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) "Slide Sal" 
  6* FROM Emp 
SQL> / 
 
ENAME      DEPTNO        SAL  Slide Sal                                          
---------- ------ ---------- ----------                                          
SMITH          20        800       1750                                          
JAMES          30        950       2850                                          
ADAMS          20       1100       3300                                          
MARTIN         30       1250       3600                                          
WARD           30       1250       3800                                          
MILLER         10       1300       4050                                          
TURNER         30       1500       4400                                          
ALLEN          30       1600       5550                                          
CLARK          10       2450       6900                                          
BLAKE          30       2850       8275                                          
JONES          20       2975       8825                                          
 
ENAME      DEPTNO        SAL  Slide Sal                                          
---------- ------ ---------- ----------                                          
FORD           20       3000       8975                                          
SCOTT          20       3000      11000                                          
KING           10       5000       8000                                          
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, 
  2  SUM(Sal) 
  3  OVER( 
  4  ORDER BY Sal ASC 
  5  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) "Center Sum", 
  6  TO_CHAR(AVG(Sal) 
  7  OVER( 
  8  ORDER BY Sal ASC 
  9  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), '9G999D99') "Center Avg" 
 10* FROM Emp 
 
SQL> / 
 
ENAME      DEPTNO        SAL Center Sum Center Av                                
---------- ------ ---------- ---------- ---------                                
SMITH          20        800       1750    875.00                                
JAMES          30        950       2850    950.00                                
ADAMS          20       1100       3300  1,100.00                                
MARTIN         30       1250       3600  1,200.00                                
WARD           30       1250       3800  1,266.67                                
MILLER         10       1300       4050  1,350.00                                
TURNER         30       1500       4400  1,466.67                                
ALLEN          30       1600       5550  1,850.00                                
CLARK          10       2450       6900  2,300.00                                
BLAKE          30       2850       8275  2,758.33                                
JONES          20       2975       8825  2,941.67                                
 
ENAME      DEPTNO        SAL Center Sum Center Av                                
---------- ------ ---------- ---------- ---------                                
FORD           20       3000       8975  2,991.67                                
SCOTT          20       3000      11000  3,666.67                                
KING           10       5000       8000  4,000.00                                
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT 
  2  ROW_NUMBER() 
  3  OVER(ORDER BY Sal DESC 
  4  NULLS LAST) RowNo, 
  5  Ename, Deptno 
  6  FROM  Emp; 
 
     ROWNO ENAME      DEPTNO                                                     
---------- ---------- ------                                                     
         1 KING           10                                                     
         2 FORD           20                                                     
         3 SCOTT          20                                                     
         4 JONES          20                                                     
         5 BLAKE          30                                                     
         6 CLARK          10                                                     
         7 ALLEN          30                                                     
         8 TURNER         30                                                     
         9 MILLER         10                                                     
        10 MARTIN         30                                                     
        11 WARD           30                                                     
 
     ROWNO ENAME      DEPTNO                                                     
---------- ---------- ------                                                     
        12 ADAMS          20                                                     
        13 JAMES          30                                                     
        14 SMITH          20                                                     
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
 
  1  SELECT 
  2  ROW_NUMBER() 
  3  OVER( 
  4  PARTITION BY Deptno 
  5  ORDER BY Sal DESC 
  6  NULLS LAST) RowNo, 
  7  Ename, Deptno 
  8* FROM  Emp 
SQL> / 
 
     ROWNO ENAME      DEPTNO                                                     
---------- ---------- ------                                                     
         1 KING           10                                                     
         2 CLARK          10                                                     
         3 MILLER         10                                                     
         1 FORD           20                                                     
         2 SCOTT          20                                                     
         3 JONES          20                                                     
         4 ADAMS          20                                                     
         5 SMITH          20                                                     
         1 BLAKE          30                                                     
         2 ALLEN          30                                                     
         3 TURNER         30                                                     
 
     ROWNO ENAME      DEPTNO                                                     
---------- ---------- ------                                                     
         4 MARTIN         30                                                     
         5 WARD           30                                                     
         6 JAMES          30                                                     
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT Deptno, Ename, 
  2    ROW_NUMBER() 
  3    OVER (PARTITION BY Deptno 
  4    ORDER BY Sal DESC NULLS LAST)  SeqNo 
  5    FROM  Emp; 
 
DEPTNO ENAME           SEQNO                                                     
------ ---------- ----------                                                     
    10 KING                1                                                     
    10 CLARK               2                                                     
    10 MILLER              3                                                     
    20 FORD                1                                                     
    20 SCOTT               2                                                     
    20 JONES               3                                                     
    20 ADAMS               4                                                     
    20 SMITH               5                                                     
    30 BLAKE               1                                                     
    30 ALLEN               2                                                     
    30 TURNER              3                                                     
 
DEPTNO ENAME           SEQNO                                                     
------ ---------- ----------                                                     
 
    30 MARTIN              4                                                     
    30 WARD                5                                                     
    30 JAMES               6                                                     
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT E1.* 
  2  FROM ( 
  3   SELECT Deptno, Ename, 
  4   ROW_NUMBER() 
  5   OVER (PARTITION BY Deptno 
  6   ORDER BY Sal DESC NULLS LAST)  SeqNo 
  7   FROM  Emp 
  8   ) E1 
  9* WHERE E1.Seqno <= 3 
SQL> / 
 
DEPTNO ENAME           SEQNO                                                     
------ ---------- ----------                                                     
    10 KING                1                                                     
    10 CLARK               2                                                     
    10 MILLER              3                                                     
    20 FORD                1                                                     
    20 SCOTT               2                                                     
    20 JONES               3                                                     
    30 BLAKE               1                                                     
    30 ALLEN               2                                                     
    30 TURNER              3                                                     
 
9 rows selected. 
 
SQL> SELECT Deptno, 
  2  MAX(DECODE(Seqno, 1, Ename, NULL)) First, 
  3  MAX(DECODE(Seqno, 2, Ename, NULL)) Second, 
  4  MAX(DECODE(Seqno, 3, Ename, NULL)) Third 
  5  FROM  (SELECT Deptno, Ename, 
  6    ROW_NUMBER() 
  7    OVER (PARTITION BY Deptno 
  8    ORDER BY Sal DESC NULLS LAST)  SeqNo 
  9    FROM  Emp) 
 10  WHERE SeqNo <= 3 
 11  GROUP BY Deptno; 
 
DEPTNO FIRST      SECOND     THIRD                                               
------ ---------- ---------- ----------                                          
    10 KING       CLARK      MILLER                                              
    20 FORD       SCOTT      JONES                                               
    30 BLAKE      ALLEN      TURNER                                              
 
SQL> SELECT Deptno, 
  2  MAX(DECODE(Seqno, 1, Ename, NULL)) First, 
  3  MAX(DECODE(Seqno, 2, Ename, NULL)) Second, 
  4  MAX(DECODE(Seqno, 3, Ename, NULL)) Third 
  5  FROM  (SELECT Deptno, Ename, 
 
  6    ROW_NUMBER() 
  7    OVER (PARTITION BY Deptno 
  8    ORDER BY HireDate NULLS LAST)  SeqNo 
  9    FROM  Emp) 
 10  WHERE SeqNo <= 3 
 11  GROUP BY Deptno; 
 
DEPTNO FIRST      SECOND     THIRD                                               
------ ---------- ---------- ----------                                          
    10 CLARK      KING       MILLER                                              
    20 SMITH      JONES      FORD                                                
    30 ALLEN      WARD       BLAKE                                               
 
SQL> cl scr 
 
SQL> SELECT Deptno, Ename, 
  2    ROW_NUMBER() 
  3    OVER (ORDER BY HireDate NULLS LAST)  SeqNo 
  4    FROM  Emp; 
 
DEPTNO ENAME           SEQNO                                                     
------ ---------- ----------                                                     
    20 SMITH               1                                                     
    30 ALLEN               2                                                     
    30 WARD                3                                                     
    20 JONES               4                                                     
    30 BLAKE               5                                                     
    10 CLARK               6                                                     
    30 TURNER              7                                                     
    30 MARTIN              8                                                     
    10 KING                9                                                     
    30 JAMES              10                                                     
    20 FORD               11                                                     
 
DEPTNO ENAME           SEQNO                                                     
------ ---------- ----------                                                     
    10 MILLER             12                                                     
    20 SCOTT              13                                                     
    20 ADAMS              14                                                     
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT E1.* 
  2  FROM ( 
  3   SELECT Deptno, Ename, 
  4   ROW_NUMBER() 
  5   OVER (ORDER BY HireDate NULLS LAST)  SeqNo 
  6   FROM  Emp 
  7   ) E1 
  8* WHERE E1.Seqno <= 3 
SQL> / 
 
DEPTNO ENAME           SEQNO                                                     
------ ---------- ----------                                                     
 
    20 SMITH               1                                                     
    30 ALLEN               2                                                     
    30 WARD                3                                                     
 
SQL> SELECT Deptno, 
  2  NVL(MAX(DECODE(Seqno, 1, Ename, NULL)), 'N.A.') First, 
  3  NVL(MAX(DECODE(Seqno, 2, Ename, NULL)), 'N.A') Second, 
  4  NVL(MAX(DECODE(Seqno, 3, Ename, NULL)), 'N.A') Third 
  5  FROM  (SELECT Deptno, Ename, 
  6    ROW_NUMBER() 
  7    OVER (ORDER BY HireDate NULLS LAST)  SeqNo 
  8    FROM  Emp) 
  9  WHERE SeqNo <= 4 OR Deptno IN(10, 20, 30) 
 10  GROUP BY Deptno; 
 
DEPTNO FIRST      SECOND     THIRD                                               
------ ---------- ---------- ----------                                          
    10 N.A.       N.A        N.A                                                 
    20 SMITH      N.A        N.A                                                 
    30 N.A.       ALLEN      WARD                                                
 
SQL> cl scr 
 
SQL> SELECT  Deptno, 
  2  DECODE (SeqNo, 1, Ename, NULL)   First, 
  3  DECODE (SeqNo, 2, Ename, NULL)  Second, 
  4  DECODE (SeqNo, 3, Ename, NULL)  Third 
  5  FROM  (SELECT  Deptno,  Ename, 
  6    ROW_NUMBER() 
  7    OVER (PARTITION BY Deptno 
  8    ORDER BY Sal DESC NULLS LAST)  SeqNo 
  9    FROM  Emp) 
 10  WHERE SeqNo <= 3; 
 
DEPTNO FIRST      SECOND     THIRD                                               
------ ---------- ---------- ----------                                          
    10 KING                                                                      
    10            CLARK                                                          
    10                       MILLER                                              
    20 FORD                                                                      
    20            SCOTT                                                          
    20                       JONES                                               
    30 BLAKE                                                                     
    30            ALLEN                                                          
    30                       TURNER                                              
 
9 rows selected. 
 
SQL> SELECT Deptno, 
  2  MAX(DECODE(Seqno, 1, Ename, NULL)) First, 
  3  MAX(DECODE(Seqno, 2, Ename, NULL)) Second, 
  4  MAX(DECODE(Seqno, 3, Ename, NULL)) Third 
  5  FROM  (SELECT Deptno, Ename, 
  6    ROW_NUMBER() 
  7    OVER (PARTITION BY Deptno 
  8    ORDER BY Sal DESC NULLS LAST)  SeqNo 
  9    FROM  Emp) 
 
 10  WHERE SeqNo <= 3 
 11  GROUP BY Deptno; 
 
DEPTNO FIRST      SECOND     THIRD                                               
------ ---------- ---------- ----------                                          
    10 KING       CLARK      MILLER                                              
    20 FORD       SCOTT      JONES                                               
    30 BLAKE      ALLEN      TURNER                                              
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Deptno, 
  2  MAX(DECODE(Seqno, 1, Ename, NULL)) First, 
  3  MAX(DECODE(Seqno, 2, Ename, NULL)) Second, 
  4  MAX(DECODE(Seqno, 3, Ename, NULL)) Third, 
  5  SUM(Sal) 
  6  FROM  (SELECT Deptno, Ename, Sal 
  7    ROW_NUMBER() 
  8    OVER (PARTITION BY Deptno 
  9    ORDER BY Sal DESC NULLS LAST)  SeqNo 
 10    FROM  Emp) 
 11  WHERE SeqNo <= 3 
 12* GROUP BY Deptno 
SQL> / 
  ROW_NUMBER() 
            * 
ERROR at line 7: 
ORA-00923: FROM keyword not found where expected  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Deptno, 
  2  MAX(DECODE(Seqno, 1, Ename, NULL)) First, 
  3  MAX(DECODE(Seqno, 2, Ename, NULL)) Second, 
  4  MAX(DECODE(Seqno, 3, Ename, NULL)) Third, 
  5  SUM(Sal) 
  6  FROM  (SELECT Deptno, Ename, Sal, 
  7    ROW_NUMBER() 
  8    OVER (PARTITION BY Deptno 
  9    ORDER BY Sal DESC NULLS LAST)  SeqNo 
 10    FROM  Emp) 
 11  WHERE SeqNo <= 3 
 12* GROUP BY Deptno 
SQL> / 
 
DEPTNO FIRST      SECOND     THIRD        SUM(SAL)                               
------ ---------- ---------- ---------- ----------                               
    10 KING       CLARK      MILLER           8750                               
    20 FORD       SCOTT      JONES            8975                               
    30 BLAKE      ALLEN      TURNER           5950                               
 
SQL> ED 
Wrote file afiedt.buf 
 
 
  1  SELECT Deptno, 
  2  DECODE(Seqno, 1, Ename, NULL) First, 
  3  DECODE(Seqno, 2, Ename, NULL) Second, 
  4  DECODE(Seqno, 3, Ename, NULL) Third, 
  5  Sal 
  6  FROM  (SELECT Deptno, Ename, Sal, 
  7    ROW_NUMBER() 
  8    OVER (PARTITION BY Deptno 
  9    ORDER BY Sal DESC NULLS LAST)  SeqNo 
 10    FROM  Emp) 
 11  WHERE SeqNo <= 3 
 12* GROUP BY Deptno 
SQL> / 
DECODE(Seqno, 1, Ename, NULL) First, 
       * 
ERROR at line 2: 
ORA-00979: not a GROUP BY expression  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Deptno, 
  2  DECODE(Seqno, 1, Ename, NULL) First, 
  3  DECODE(Seqno, 2, Ename, NULL) Second, 
  4  DECODE(Seqno, 3, Ename, NULL) Third, 
  5  Sal 
  6  FROM  (SELECT Deptno, Ename, Sal, 
  7    ROW_NUMBER() 
  8    OVER (PARTITION BY Deptno 
  9    ORDER BY Sal DESC NULLS LAST)  SeqNo 
 10    FROM  Emp) 
 11* WHERE SeqNo <= 3 
SQL> / 
 
DEPTNO FIRST      SECOND     THIRD             SAL                               
------ ---------- ---------- ---------- ----------                               
    10 KING                                   5000                               
    10            CLARK                       2450                               
    10                       MILLER           1300                               
    20 FORD                                   3000                               
    20            SCOTT                       3000                               
    20                       JONES            2975                               
    30 BLAKE                                  2850                               
    30            ALLEN                       1600                               
    30                       TURNER           1500                               
 
9 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT 
  2  Empno, 
  3  Deptno, 
  4  HireDate, 
  5  FIRST_VALUE(HireDate) 
  6  OVER ( 
 
  7    PARTITION BY Deptno 
  8    ORDER BY HireDate 
  9    ) FirstDate, 
 10  HireDate - FIRST_VALUE(HireDate) 
 11     OVER ( 
 12      PARTITION BY Deptno 
 13      ORDER BY HireDate 
 14      ) NDays 
 15  FROM Emp 
 16  ORDER BY Deptno, NDays; 
 
     EMPNO DEPTNO HIREDATE  FIRSTDATE      NDAYS                                 
---------- ------ --------- --------- ----------                                 
      7782     10 09-JUN-81 09-JUN-81          0                                 
      7839     10 17-NOV-81 09-JUN-81        161                                 
      7934     10 23-JAN-82 09-JUN-81        228                                 
      7369     20 17-DEC-80 17-DEC-80          0                                 
      7566     20 02-APR-81 17-DEC-80        106                                 
      7902     20 03-DEC-81 17-DEC-80        351                                 
      7788     20 09-DEC-82 17-DEC-80        722                                 
      7876     20 12-JAN-83 17-DEC-80        756                                 
      7499     30 20-FEB-81 20-FEB-81          0                                 
      7521     30 22-FEB-81 20-FEB-81          2                                 
      7698     30 01-MAY-81 20-FEB-81         70                                 
 
     EMPNO DEPTNO HIREDATE  FIRSTDATE      NDAYS                                 
---------- ------ --------- --------- ----------                                 
      7844     30 08-SEP-81 20-FEB-81        200                                 
      7654     30 28-SEP-81 20-FEB-81        220                                 
      7900     30 03-DEC-81 20-FEB-81        286                                 
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT 
  2  Empno, 
  3  Deptno, 
  4  HireDate, 
  5  LAST_VALUE(Hiredate) 
  6  OVER ( 
  7   PARTITION BY Deptno 
  8   ORDER BY HireDate 
  9   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 
 10   ) LastDate, 
 11  LAST_VALUE(Hiredate) 
 12  OVER ( 
 13   PARTITION BY Deptno 
 14   ORDER BY Hiredate 
 15   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 
 16   ) - HireDate NDays 
 17  FROM Emp 
 18  ORDER BY Deptno, NDays DESC 
 19  / 
 
     EMPNO DEPTNO HIREDATE  LASTDATE       NDAYS                                 
---------- ------ --------- --------- ----------                                 
 
      7782     10 09-JUN-81 23-JAN-82        228                                 
      7839     10 17-NOV-81 23-JAN-82         67                                 
      7934     10 23-JAN-82 23-JAN-82          0                                 
      7369     20 17-DEC-80 12-JAN-83        756                                 
      7566     20 02-APR-81 12-JAN-83        650                                 
      7902     20 03-DEC-81 12-JAN-83        405                                 
      7788     20 09-DEC-82 12-JAN-83         34                                 
      7876     20 12-JAN-83 12-JAN-83          0                                 
      7499     30 20-FEB-81 03-DEC-81        286                                 
      7521     30 22-FEB-81 03-DEC-81        284                                 
      7698     30 01-MAY-81 03-DEC-81        216                                 
 
     EMPNO DEPTNO HIREDATE  LASTDATE       NDAYS                                 
---------- ------ --------- --------- ----------                                 
      7844     30 08-SEP-81 03-DEC-81         86                                 
      7654     30 28-SEP-81 03-DEC-81         66                                 
      7900     30 03-DEC-81 03-DEC-81          0                                 
 
14 rows selected. 
 
SQL> SPOOL OFF 
SQL> SELECT Deptno, 
  2  NVL(MAX(DECODE(Seqno, 1, Ename, NULL)), 'N.A.') First, 
  3  NVL(MAX(DECODE(Seqno, 2, Ename, NULL)), 'N.A') Second, 
  4  NVL(MAX(DECODE(Seqno, 3, Ename, NULL)), 'N.A') Third 
  5  FROM  (SELECT Deptno, Ename, 
  6    ROW_NUMBER() 
  7    OVER (ORDER BY HireDate NULLS LAST)  SeqNo 
  8    FROM  Emp) 
  9  WHERE SeqNo <= 4 OR Deptno IN(10, 20, 30) 
 10  GROUP BY Deptno; 
 
    DEPTNO FIRST      SECOND     THIRD                                           
---------- ---------- ---------- ----------                                      
        10 N.A.       N.A        N.A                                             
        20 SMITH      N.A        N.A                                             
        30 N.A.       ALLEN      WARD                                            
 
SQL> cl scr 
 
SQL> SELECT  Deptno, 
  2  DECODE (SeqNo, 1, Ename, NULL)   First, 
  3  DECODE (SeqNo, 2, Ename, NULL)  Second, 
  4  DECODE (SeqNo, 3, Ename, NULL)  Third 
  5  FROM  (SELECT  Deptno,  Ename, 
  6    ROW_NUMBER() 
  7    OVER (PARTITION BY Deptno 
  8    ORDER BY Sal DESC NULLS LAST)  SeqNo 
  9    FROM  Emp) 
 10  WHERE SeqNo <= 3; 
 
    DEPTNO FIRST      SECOND     THIRD                                           
---------- ---------- ---------- ----------                                      
        10 KING                                                                  
        10            CLARK                                                      
        10                       MILLER                                          
        20 FORD                                                                  
 
        20            SCOTT                                                      
        20                       JONES                                           
        30 BLAKE                                                                 
        30            ALLEN                                                      
        30                       TURNER                                          
 
9 rows selected. 
 
SQL> SELECT Deptno, 
  2  MAX(DECODE(Seqno, 1, Ename, NULL)) First, 
  3  MAX(DECODE(Seqno, 2, Ename, NULL)) Second, 
  4  MAX(DECODE(Seqno, 3, Ename, NULL)) Third 
  5  FROM  (SELECT Deptno, Ename, 
  6    ROW_NUMBER() 
  7    OVER (PARTITION BY Deptno 
  8    ORDER BY Sal DESC NULLS LAST)  SeqNo 
  9    FROM  Emp) 
 10  WHERE SeqNo <= 3 
 11  GROUP BY Deptno; 
 
    DEPTNO FIRST      SECOND     THIRD                                           
---------- ---------- ---------- ----------                                      
        10 KING       CLARK      MILLER                                          
        20 FORD       SCOTT      JONES                                           
        30 BLAKE      ALLEN      TURNER                                          
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Deptno, 
  2  MAX(DECODE(Seqno, 1, Ename, NULL)) First, 
  3  MAX(DECODE(Seqno, 2, Ename, NULL)) Second, 
  4  MAX(DECODE(Seqno, 3, Ename, NULL)) Third, 
  5  Sal 
  6  FROM  (SELECT Deptno, Ename, Sal 
  7    ROW_NUMBER() 
  8    OVER (PARTITION BY Deptno 
  9    ORDER BY Sal DESC NULLS LAST)  SeqNo 
 10    FROM  Emp) 
 11  WHERE SeqNo <= 3 
 12* GROUP BY Deptno 
SQL> / 
  ROW_NUMBER() 
            * 
ERROR at line 7: 
ORA-00923: FROM keyword not found where expected  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Deptno, 
  2  MAX(DECODE(Seqno, 1, Ename, NULL)) First, 
  3  MAX(DECODE(Seqno, 2, Ename, NULL)) Second, 
  4  MAX(DECODE(Seqno, 3, Ename, NULL)) Third, 
  5  Sal 
  6  FROM  (SELECT Deptno, Ename, Sal, 
 
  7    ROW_NUMBER() 
  8    OVER (PARTITION BY Deptno 
  9    ORDER BY Sal DESC NULLS LAST)  SeqNo 
 10    FROM  Emp) 
 11  WHERE SeqNo <= 3 
 12* GROUP BY Deptno 
SQL> / 
Sal 
* 
ERROR at line 5: 
ORA-00979: not a GROUP BY expression  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Deptno, 
  2  MAX(DECODE(Seqno, 1, Ename, NULL)) First, 
  3  MAX(DECODE(Seqno, 2, Ename, NULL)) Second, 
  4  MAX(DECODE(Seqno, 3, Ename, NULL)) Third, 
  5  SUM(Sal) SalSum 
  6  FROM  (SELECT Deptno, Ename, Sal, 
  7    ROW_NUMBER() 
  8    OVER (PARTITION BY Deptno 
  9    ORDER BY Sal DESC NULLS LAST)  SeqNo 
 10    FROM  Emp) 
 11  WHERE SeqNo <= 3 
 12* GROUP BY Deptno 
SQL> / 
 
    DEPTNO FIRST      SECOND     THIRD          SALSUM                           
---------- ---------- ---------- ---------- ----------                           
        10 KING       CLARK      MILLER           8750                           
        20 FORD       SCOTT      JONES            8975                           
        30 BLAKE      ALLEN      TURNER           5950                           
 
SQL> cl scr 
 
SQL> SELECT  Deptno, 
  2  DECODE (SeqNo, 1, Ename, NULL)   First, 
  3  DECODE (SeqNo, 2, Ename, NULL)  Second, 
  4  DECODE (SeqNo, 3, Ename, NULL)  Third, 
  5  Sal 
  6  FROM  (SELECT  Deptno,  Ename, Sal 
  7    ROW_NUMBER() 
  8    OVER (PARTITION BY Deptno 
  9    ORDER BY Sal DESC NULLS LAST)  SeqNo 
 10    FROM  Emp) 
 11  WHERE SeqNo <= 3; 
  ROW_NUMBER() 
            * 
ERROR at line 7: 
ORA-00923: FROM keyword not found where expected  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
 
  1  SELECT  Deptno, 
  2  DECODE (SeqNo, 1, Ename, NULL)   First, 
  3  DECODE (SeqNo, 2, Ename, NULL)  Second, 
  4  DECODE (SeqNo, 3, Ename, NULL)  Third, 
  5  Sal 
  6  FROM  (SELECT  Deptno,  Ename, Sal, 
  7    ROW_NUMBER() 
  8    OVER (PARTITION BY Deptno 
  9    ORDER BY Sal DESC NULLS LAST)  SeqNo 
 10    FROM  Emp) 
 11* WHERE SeqNo <= 3 
SQL> / 
 
    DEPTNO FIRST      SECOND     THIRD             SAL                           
---------- ---------- ---------- ---------- ----------                           
        10 KING                                   5000                           
        10            CLARK                       2450                           
        10                       MILLER           1300                           
        20 FORD                                   3000                           
        20            SCOTT                       3000                           
        20                       JONES            2975                           
        30 BLAKE                                  2850                           
        30            ALLEN                       1600                           
        30                       TURNER           1500                           
 
9 rows selected. 
 
SQL> SELECT  Deptno, 
  2  NVL(DECODE(SeqNo, 1, Ename, NULL), '***')   First, 
  3  NVL(DECODE(SeqNo, 2, Ename, NULL), '***')  Second, 
  4  NVL(DECODE(SeqNo, 3, Ename, NULL), '***')  Third 
  5  FROM  (SELECT  Deptno,  Ename, 
  6    ROW_NUMBER() 
  7    OVER (PARTITION BY Deptno 
  8    ORDER BY Sal DESC NULLS LAST)  SeqNo 
  9    FROM  Emp) 
 10  WHERE SeqNo <= 3; 
 
    DEPTNO FIRST      SECOND     THIRD                                           
---------- ---------- ---------- ----------                                      
        10 KING       ***        ***                                             
        10 ***        CLARK      ***                                             
        10 ***        ***        MILLER                                          
        20 FORD       ***        ***                                             
        20 ***        SCOTT      ***                                             
        20 ***        ***        JONES                                           
        30 BLAKE      ***        ***                                             
        30 ***        ALLEN      ***                                             
        30 ***        ***        TURNER                                          
 
9 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT 
  2   DECODE(RN, 
 
  3     1, Ename, 
  4     2, Ename, 
  5     3, Ename, 
  6      'Rest' 
  7     ), 
  8   SUM(SAL) 
  9  FROM ( 
 10   SELECT 
 11   ROW_NUMBER() 
 12   OVER(ORDER BY Sal DESC) RN, 
 13   Ename, 
 14   SAL 
 15               FROM EMP 
 16             ) 
 17  GROUP BY 
 18  DECODE(RN, 
 19    1, ENAME, 
 20    2, ENAME, 
 21    3, ENAME, 
 22     'Rest' 
 23    ) 
 24  ORDER BY 2; 
 
DECODE(RN,   SUM(SAL)                                                            
---------- ----------                                                            
FORD             3000                                                            
SCOTT            3000                                                            
KING             5000                                                            
Rest            18025                                                            
 
SQL> cl scr 
 
SQL> SELECT 
  2   NumRow, 
  3   Deptno, 
  4   Sal, 
  5   MAX(Total) 
  6   OVER() Rest 
  7   FROM ( 
  8     SELECT 
  9        NumRow, 
 10        Deptno, 
 11        Sal, 
 12        SUM(Sal) 
 13     OVER (ORDER BY NumRow 
 14       RANGE BETWEEN 3 FOLLOWING AND 
 15       UNBOUNDED FOLLOWING) Total 
 16   FROM 
 17   (SELECT 
 18              Empno, 
 19              Deptno, 
 20              Sal, 
 21              SUM(Sal) OVER (ORDER BY Empno) Cumulative, 
 22              DENSE_RANK() OVER (ORDER BY Sal DESC) NumRow 
 23        FROM Emp) B) 
 24  WHERE NumRow <= 3; 
 
 
    NUMROW     DEPTNO        SAL       REST                                      
---------- ---------- ---------- ----------                                      
         1         10       5000      15050                                      
         2         20       3000      15050                                      
         2         20       3000      15050                                      
         3         20       2975      15050                                      
 
SQL> cl scr 
 
SQL> SELECT Ename, Job, Comm 
  2  FROM Emp 
  3  WHERE Job = 'CLERK'; 
 
ENAME      JOB             COMM                                                  
---------- --------- ----------                                                  
JAMES      CLERK                                                                 
SMITH      CLERK                                                                 
ADAMS      CLERK                                                                 
MILLER     CLERK                            

Go Back