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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment