Monday, August 16, 2010

Oracle 11g: General Functions

select last_name,
       salary,
       commission_pct      
from employees;

Results:
LAST_NAME                 SALARY                 COMMISSION_PCT        
------------------------- ---------------------- ----------------------
King                      24000                                        
Kochhar                   17000                                        
De Haan                   17000                                        
Hunold                    9000                                         
Ernst                     6000                                         
Austin                    4800                                         
Pataballa                 4800                                         
Lorentz                   4200                                         
Greenberg                 12000                                        
Faviet                    9000                                         
Chen                      8200                                         
Sciarra                   7700                                         
Urman                     7800                                         
Popp                      6900                                         
Raphaely                  11000                                        
Khoo                      3100                                         
Baida                     2900                                         
Tobias                    2800                                         
Himuro                    2600                                         
Colmenares                2500                                         
Weiss                     8000                                         
Fripp                     8200                                         
Kaufling                  7900                                         
Vollman                   6500                                         
Mourgos                   5800                                         
Nayer                     3200                                         
Mikkilineni               2700                                         
Landry                    2400                                         
Markle                    2200                                         
Bissot                    3300                                         
Atkinson                  2800                                         
Marlow                    2500                                         
Olson                     2100                                         
Mallin                    3300                                         
Rogers                    2900                                         
Gee                       2400                                         
Philtanker                2200                                         
Ladwig                    3600                                         
Stiles                    3200                                         
Seo                       2700                                         
Patel                     2500                                         
Rajs                      3500                                         
Davies                    3100                                         
Matos                     2600                                         
Vargas                    2500                                         
Russell                   14000                  0.4                   
Partners                  13500                  0.3                   
Errazuriz                 12000                  0.3                   
Cambrault                 11000                  0.3                   
Zlotkey                   10500                  0.2                   
Tucker                    10000                  0.3                   
Bernstein                 9500                   0.25                  
Hall                      9000                   0.25                  
Olsen                     8000                   0.2                   
Cambrault                 7500                   0.2                   
Tuvault                   7000                   0.15                  
King                      10000                  0.35                  
Sully                     9500                   0.35                  
McEwen                    9000                   0.35                  
Smith                     8000                   0.3                   
Doran                     7500                   0.3                   
Sewall                    7000                   0.25                  
Vishney                   10500                  0.25                  
Greene                    9500                   0.15                  
Marvins                   7200                   0.1                   
Lee                       6800                   0.1                   
Ande                      6400                   0.1                   
Banda                     6200                   0.1                   
Ozer                      11500                  0.25                  
Bloom                     10000                  0.2                   
Fox                       9600                   0.2                   
Smith                     7400                   0.15                  
Bates                     7300                   0.15                  
Kumar                     6100                   0.1                   
Abel                      11000                  0.3                   
Hutton                    8800                   0.25                  
Taylor                    8600                   0.2                   
Livingston                8400                   0.2                   
Grant                     7000                   0.15                  
Johnson                   6200                   0.1                   
Taylor                    3200                                         
Fleaur                    3100                                         
Sullivan                  2500                                         
Geoni                     2800                                         
Sarchand                  4200                                         
Bull                      4100                                         
Dellinger                 3400                                         
Cabrio                    3000                                         
Chung                     3800                                         
Dilly                     3600                                         
Gates                     2900                                         
Perkins                   2500                                         
Bell                      4000                                         
Everett                   3900                                         
McCain                    3200                                         
Jones                     2800                                         
Walsh                     3100                                         
Feeney                    3000                                         
OConnell                  2600                                         
Grant                     2600                                         
Whalen                    4400                                         
Hartstein                 13000                                        
Fay                       6000                                         
Mavris                    6500                                         
Baer                      10000                                        
Higgins                   12000                                        
Gietz                     8300                                         

107 rows selected



===>  SQL Queries using NVL [Convert null value to an actual value]
select last_name,
       salary,
       NVL(commission_pct,0),
       (salary*12) + (salary*12*NVL(commission_pct,0)) AN_SAL
from employees

Results:
LAST_NAME   SALARY                 NVL(COMMISSION_PCT,0)  AN_SAL                
------------------------- ---------------------- ---------------------- ------------
King                      24000                  0                      288000                
Kochhar                   17000                  0                      204000                
De Haan                   17000                  0                      204000                
Hunold                    9000                   0                      108000                
Ernst                     6000                   0                      72000                 
Austin                    4800                   0                      57600                 
Pataballa                 4800                   0                      57600                 
Lorentz                   4200                   0                      50400                 
Greenberg                 12000                  0                      144000                
Faviet                    9000                   0                      108000                
Chen                      8200                   0                      98400                 
Sciarra                   7700                   0                      92400                 
Urman                     7800                   0                      93600                 
Popp                      6900                   0                      82800                 
Raphaely                  11000                  0                      132000                
Khoo                      3100                   0                      37200                 
Baida                     2900                   0                      34800                 
Tobias                    2800                   0                      33600                 
Himuro                    2600                   0                      31200                 
Colmenares                2500                   0                      30000                 
Weiss                     8000                   0                      96000                 
Fripp                     8200                   0                      98400                 
Kaufling                  7900                   0                      94800                 
Vollman                   6500                   0                      78000                 
Mourgos                   5800                   0                      69600                 
Nayer                     3200                   0                      38400                 
Mikkilineni               2700                   0                      32400                 
Landry                    2400                   0                      28800                 
Markle                    2200                   0                      26400                 
Bissot                    3300                   0                      39600                 
Atkinson                  2800                   0                      33600                 
Marlow                    2500                   0                      30000                 
Olson                     2100                   0                      25200                 
Mallin                    3300                   0                      39600                 
Rogers                    2900                   0                      34800                 
Gee                       2400                   0                      28800                 
Philtanker                2200                   0                      26400                 
Ladwig                    3600                   0                      43200                 
Stiles                    3200                   0                      38400                 
Seo                       2700                   0                      32400                 
Patel                     2500                   0                      30000                 
Rajs                      3500                   0                      42000                 
Davies                    3100                   0                      37200                 
Matos                     2600                   0                      31200                 
Vargas                    2500                   0                      30000                 
Russell                   14000                  0.4                    235200                
Partners                  13500                  0.3                    210600                
Errazuriz                 12000                  0.3                    187200                
Cambrault                 11000                  0.3                    171600                
Zlotkey                   10500                  0.2                    151200                
Tucker                    10000                  0.3                    156000                
Bernstein                 9500                   0.25                   142500                
Hall                      9000                   0.25                   135000                
Olsen                     8000                   0.2                    115200                
Cambrault                 7500                   0.2                    108000                
Tuvault                   7000                   0.15                   96600                 
King                      10000                  0.35                   162000                
Sully                     9500                   0.35                   153900                
McEwen                    9000                   0.35                   145800                
Smith                     8000                   0.3                    124800                
Doran                     7500                   0.3                    117000                
Sewall                    7000                   0.25                   105000                
Vishney                   10500                  0.25                   157500                
Greene                    9500                   0.15                   131100                
Marvins                   7200                   0.1                    95040                 
Lee                       6800                   0.1                    89760                 
Ande                      6400                   0.1                    84480                 
Banda                     6200                   0.1                    81840                 
Ozer                      11500                  0.25                   172500                
Bloom                     10000                  0.2                    144000                
Fox                       9600                   0.2                    138240                
Smith                     7400                   0.15                   102120                
Bates                     7300                   0.15                   100740                
Kumar                     6100                   0.1                    80520                 
Abel                      11000                  0.3                    171600                
Hutton                    8800                   0.25                   132000                
Taylor                    8600                   0.2                    123840                
Livingston                8400                   0.2                    120960                
Grant                     7000                   0.15                   96600                 
Johnson                   6200                   0.1                    81840                 
Taylor                    3200                   0                      38400                 
Fleaur                    3100                   0                      37200                 
Sullivan                  2500                   0                      30000                 
Geoni                     2800                   0                      33600                 
Sarchand                  4200                   0                      50400                 
Bull                      4100                   0                      49200                 
Dellinger                 3400                   0                      40800                 
Cabrio                    3000                   0                      36000                 
Chung                     3800                   0                      45600                 
Dilly                     3600                   0                      43200                 
Gates                     2900                   0                      34800                 
Perkins                   2500                   0                      30000                 
Bell                      4000                   0                      48000                 
Everett                   3900                   0                      46800                 
McCain                    3200                   0                      38400                 
Jones                     2800                   0                      33600                 
Walsh                     3100                   0                      37200                 
Feeney                    3000                   0                      36000                 
OConnell                  2600                   0                      31200                 
Grant                     2600                   0                      31200                 
Whalen                    4400                   0                      52800                 
Hartstein                 13000                  0                      156000                
Fay                       6000                   0                      72000                 
Mavris                    6500                   0                      78000                 
Baer                      10000                  0                      120000                
Higgins                   12000                  0                      144000                
Gietz                     8300                   0                      99600                 

107 rows selected

 ===>  SQL Queries NVL2
select last_name,
       salary,
       commission_pct,
       NVL2(commission_pct,'SAL+COMM','SAL') income
from employees where department_id IN (50,80)       ;

Results:
LAST_NAME                 SALARY                 COMMISSION_PCT         INCOME  
------------------------- ---------------------- ---------------------- --------
Weiss                     8000                                          SAL     
Fripp                     8200                                          SAL     
Kaufling                  7900                                          SAL     
Vollman                   6500                                          SAL     
Mourgos                   5800                                          SAL     
Nayer                     3200                                          SAL     
Mikkilineni               2700                                          SAL     
Landry                    2400                                          SAL     
Markle                    2200                                          SAL     
Bissot                    3300                                          SAL     
Atkinson                  2800                                          SAL     
Marlow                    2500                                          SAL     
Olson                     2100                                          SAL     
Mallin                    3300                                          SAL     
Rogers                    2900                                          SAL     
Gee                       2400                                          SAL     
Philtanker                2200                                          SAL     
Ladwig                    3600                                          SAL     
Stiles                    3200                                          SAL     
Seo                       2700                                          SAL     
Patel                     2500                                          SAL     
Rajs                      3500                                          SAL     
Davies                    3100                                          SAL     
Matos                     2600                                          SAL     
Vargas                    2500                                          SAL     
Russell                   14000                  0.4                    SAL+COMM
Partners                  13500                  0.3                    SAL+COMM
Errazuriz                 12000                  0.3                    SAL+COMM
Cambrault                 11000                  0.3                    SAL+COMM
Zlotkey                   10500                  0.2                    SAL+COMM
Tucker                    10000                  0.3                    SAL+COMM
Bernstein                 9500                   0.25                   SAL+COMM
Hall                      9000                   0.25                   SAL+COMM
Olsen                     8000                   0.2                    SAL+COMM
Cambrault                 7500                   0.2                    SAL+COMM
Tuvault                   7000                   0.15                   SAL+COMM
King                      10000                  0.35                   SAL+COMM
Sully                     9500                   0.35                   SAL+COMM
McEwen                    9000                   0.35                   SAL+COMM
Smith                     8000                   0.3                    SAL+COMM
Doran                     7500                   0.3                    SAL+COMM
Sewall                    7000                   0.25                   SAL+COMM
Vishney                   10500                  0.25                   SAL+COMM
Greene                    9500                   0.15                   SAL+COMM
Marvins                   7200                   0.1                    SAL+COMM
Lee                       6800                   0.1                    SAL+COMM
Ande                      6400                   0.1                    SAL+COMM
Banda                     6200                   0.1                    SAL+COMM
Ozer                      11500                  0.25                   SAL+COMM
Bloom                     10000                  0.2                    SAL+COMM
Fox                       9600                   0.2                    SAL+COMM
Smith                     7400                   0.15                   SAL+COMM
Bates                     7300                   0.15                   SAL+COMM
Kumar                     6100                   0.1                    SAL+COMM
Abel                      11000                  0.3                    SAL+COMM
Hutton                    8800                   0.25                   SAL+COMM
Taylor                    8600                   0.2                    SAL+COMM
Livingston                8400                   0.2                    SAL+COMM
Johnson                   6200                   0.1                    SAL+COMM
Taylor                    3200                                          SAL     
Fleaur                    3100                                          SAL     
Sullivan                  2500                                          SAL     
Geoni                     2800                                          SAL     
Sarchand                  4200                                          SAL     
Bull                      4100                                          SAL     
Dellinger                 3400                                          SAL     
Cabrio                    3000                                          SAL     
Chung                     3800                                          SAL     
Dilly                     3600                                          SAL     
Gates                     2900                                          SAL     
Perkins                   2500                                          SAL     
Bell                      4000                                          SAL     
Everett                   3900                                          SAL     
McCain                    3200                                          SAL     
Jones                     2800                                          SAL     
Walsh                     3100                                          SAL     
Feeney                    3000                                          SAL     
OConnell                  2600                                          SAL     
Grant                     2600                                          SAL     

79 rows selected



===>SQL Queries NULLIF [NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression]



select first_name, length(first_name) "expr1",
       last_name,  length(last_name) "expr2",
       NULLIF(length(first_name),length(last_name)) result
from employees


Results:


FIRST_NAME   expr1         LAST_NAME         expr2                  RESULT                
-------------------- ---------------------- ------------------------- ------
Steven               6                      King                      4                      6                     
Neena                5                      Kochhar                   7                      5                     
Lex                  3                      De Haan                   7                      3                     
Alexander            9                      Hunold                    6                      9                     
Bruce                5                      Ernst                     5                                            
David                5                      Austin                    6                      5                     
Valli                5                      Pataballa                 9                      5                     
Diana                5                      Lorentz                   7                      5                     
Nancy                5                      Greenberg                 9                      5                     
Daniel               6                      Faviet                    6                                            
John                 4                      Chen                      4                                            
Ismael               6                      Sciarra                   7                      6                     
Jose Manuel          11                     Urman                     5                      11                    
Luis                 4                      Popp                      4                                            
Den                  3                      Raphaely                  8                      3                     
Alexander            9                      Khoo                      4                      9                     
Shelli               6                      Baida                     5                      6                     
Sigal                5                      Tobias                    6                      5                     
Guy                  3                      Himuro                    6                      3                     
Karen                5                      Colmenares                10                     5                     
Matthew              7                      Weiss                     5                      7                     
Adam                 4                      Fripp                     5                      4                     
Payam                5                      Kaufling                  8                      5                     
Shanta               6                      Vollman                   7                      6                     
Kevin                5                      Mourgos                   7                      5                     
Julia                5                      Nayer                     5                                            
Irene                5                      Mikkilineni               11                     5                     
James                5                      Landry                    6                      5                     
Steven               6                      Markle                    6                                            
Laura                5                      Bissot                    6                      5                     
Mozhe                5                      Atkinson                  8                      5                     
James                5                      Marlow                    6                      5                     
TJ                   2                      Olson                     5                      2                     
Jason                5                      Mallin                    6                      5                     
Michael              7                      Rogers                    6                      7                     
Ki                   2                      Gee                       3                      2                     
Hazel                5                      Philtanker                10                     5                     
Renske               6                      Ladwig                    6                                            
Stephen              7                      Stiles                    6                      7                     
John                 4                      Seo                       3                      4                     
Joshua               6                      Patel                     5                      6                     
Trenna               6                      Rajs                      4                      6                     
Curtis               6                      Davies                    6                                            
Randall              7                      Matos                     5                      7                     
Peter                5                      Vargas                    6                      5                     
John                 4                      Russell                   7                      4                     
Karen                5                      Partners                  8                      5                     
Alberto              7                      Errazuriz                 9                      7                     
Gerald               6                      Cambrault                 9                      6                     
Eleni                5                      Zlotkey                   7                      5                     
Peter                5                      Tucker                    6                      5                     
David                5                      Bernstein                 9                      5                     
Peter                5                      Hall                      4                      5                     
Christopher          11                     Olsen                     5                      11                    
Nanette              7                      Cambrault                 9                      7                     
Oliver               6                      Tuvault                   7                      6                     
Janette              7                      King                      4                      7                     
Patrick              7                      Sully                     5                      7                     
Allan                5                      McEwen                    6                      5                     
Lindsey              7                      Smith                     5                      7                     
Louise               6                      Doran                     5                      6                     
Sarath               6                      Sewall                    6                                            
Clara                5                      Vishney                   7                      5                     
Danielle             8                      Greene                    6                      8                     
Mattea               6                      Marvins                   7                      6                     
David                5                      Lee                       3                      5                     
Sundar               6                      Ande                      4                      6                     
Amit                 4                      Banda                     5                      4                     
Lisa                 4                      Ozer                      4                                            
Harrison             8                      Bloom                     5                      8                     
Tayler               6                      Fox                       3                      6                     
William              7                      Smith                     5                      7                     
Elizabeth            9                      Bates                     5                      9                     
Sundita              7                      Kumar                     5                      7                     
Ellen                5                      Abel                      4                      5                     
Alyssa               6                      Hutton                    6                                            
Jonathon             8                      Taylor                    6                      8                     
Jack                 4                      Livingston                10                     4                     
Kimberely            9                      Grant                     5                      9                     
Charles              7                      Johnson                   7                                            
Winston              7                      Taylor                    6                      7                     
Jean                 4                      Fleaur                    6                      4                     
Martha               6                      Sullivan                  8                      6                     
Girard               6                      Geoni                     5                      6                     
Nandita              7                      Sarchand                  8                      7                     
Alexis               6                      Bull                      4                      6                     
Julia                5                      Dellinger                 9                      5                     
Anthony              7                      Cabrio                    6                      7                     
Kelly                5                      Chung                     5                                            
Jennifer             8                      Dilly                     5                      8                     
Timothy              7                      Gates                     5                      7                     
Randall              7                      Perkins                   7                                            
Sarah                5                      Bell                      4                      5                     
Britney              7                      Everett                   7                                            
Samuel               6                      McCain                    6                                            
Vance                5                      Jones                     5                                            
Alana                5                      Walsh                     5                                            
Kevin                5                      Feeney                    6                      5                     
Donald               6                      OConnell                  8                      6                     
Douglas              7                      Grant                     5                      7                     
Jennifer             8                      Whalen                    6                      8                     
Michael              7                      Hartstein                 9                      7                     
Pat                  3                      Fay                       3                                            
Susan                5                      Mavris                    6                      5                     
Hermann              7                      Baer                      4                      7                     
Shelley              7                      Higgins                   7                                            
William              7                      Gietz                     5                      7                     

107 rows selected

===>SQL Queries COALESCE [equivalent to ISNULL but behave differently]

ISNULL =  If one parameter is NOT NULL and the other one is NULL.  Result:  NOT NULL
COALESCE = If one parameter is NOT NULL and the other one is NULL.  Result:  NULL

select last_name,
       manager_id,
       commission_pct,
       COALESCE(manager_id,commission_pct,-1) comm
from employees
order by commission_pct

Results:
LAST_NAME     MANAGER_ID    COMMISSION_PCT       COMM                  
------------------------- ---------------------- ---------------------- ------
Lee                       147                    0.1                    147                   
Johnson                   149                    0.1                    149                   
Marvins                   147                    0.1                    147                   
Banda                     147                    0.1                    147                   
Kumar                     148                    0.1                    148                   
Ande                      147                    0.1                    147                   
Greene                    147                    0.15                   147                   
Grant                     149                    0.15                   149                   
Tuvault                   145                    0.15                   145                   
Bates                     148                    0.15                   148                   
Smith                     148                    0.15                   148                   
Taylor                    149                    0.2                    149                   
Bloom                     148                    0.2                    148                   
Fox                       148                    0.2                    148                   
Cambrault                 145                    0.2                    145                   
Livingston                149                    0.2                    149                   
Zlotkey                   100                    0.2                    100                   
Olsen                     145                    0.2                    145                   
Sewall                    146                    0.25                   146                   
Hall                      145                    0.25                   145                   
Bernstein                 145                    0.25                   145                   
Vishney                   147                    0.25                   147                   
Hutton                    149                    0.25                   149                   
Ozer                      148                    0.25                   148                   
Abel                      149                    0.3                    149                   
Smith                     146                    0.3                    146                   
Partners                  100                    0.3                    100                   
Errazuriz                 100                    0.3                    100                   
Tucker                    145                    0.3                    145                   
Cambrault                 100                    0.3                    100                   
Doran                     146                    0.3                    146                   
King                      146                    0.35                   146                   
Sully                     146                    0.35                   146                   
McEwen                    146                    0.35                   146                   
Russell                   100                    0.4                    100                   
King                                                                    -1                    
Kochhar                   100                                           100                   
De Haan                   100                                           100                   
Hunold                    102                                           102                   
Ernst                     103                                           103                   
Austin                    103                                           103                   
Pataballa                 103                                           103                   
Lorentz                   103                                           103                   
Greenberg                 101                                           101                   
Faviet                    108                                           108                   
Chen                      108                                           108                   
Sciarra                   108                                           108                   
Urman                     108                                           108                   
Popp                      108                                           108                   
Raphaely                  100                                           100                   
Khoo                      114                                           114                   
Baida                     114                                           114                   
Tobias                    114                                           114                   
Himuro                    114                                           114                   
Colmenares                114                                           114                   
Weiss                     100                                           100                   
Fripp                     100                                           100                   
Kaufling                  100                                           100                   
Vollman                   100                                           100                   
Mourgos                   100                                           100                   
Nayer                     120                                           120                   
Mikkilineni               120                                           120                   
Landry                    120                                           120                   
Markle                    120                                           120                   
Bissot                    121                                           121                   
Atkinson                  121                                           121                   
Marlow                    121                                           121                   
Olson                     121                                           121                   
Mallin                    122                                           122                   
Rogers                    122                                           122                   
Gee                       122                                           122                   
Philtanker                122                                           122                   
Ladwig                    123                                           123                   
Stiles                    123                                           123                   
Seo                       123                                           123                   
Patel                     123                                           123                   
Rajs                      124                                           124                   
Davies                    124                                           124                   
Matos                     124                                           124                   
Vargas                    124                                           124                   
Taylor                    120                                           120                   
Fleaur                    120                                           120                   
Sullivan                  120                                           120                   
Geoni                     120                                           120                   
Sarchand                  121                                           121                   
Bull                      121                                           121                   
Dellinger                 121                                           121                   
Cabrio                    121                                           121                   
Chung                     122                                           122                   
Dilly                     122                                           122                   
Gates                     122                                           122                   
Perkins                   122                                           122                   
Bell                      123                                           123                   
Everett                   123                                           123                   
McCain                    123                                           123                   
Jones                     123                                           123                   
Walsh                     124                                           124                   
Feeney                    124                                           124                   
OConnell                  124                                           124                   
Grant                     124                                           124                   
Whalen                    101                                           101                   
Hartstein                 100                                           100                   
Fay                       201                                           201                   
Mavris                    101                                           101                   
Baer                      101                                           101                   
Higgins                   101                                           101                   
Gietz                     205                                           205                   

107 rows selected

No comments: