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
Monday, August 16, 2010
Oracle 11g: Nesting Functions
===> SQL Queries (Concat,Substr,Upper)
select last_name,
upper(concat(substr(last_name,1,8),'_US'))
from employees
where department_id=60
Results:
LAST_NAME UPPER(CONCAT(SUBSTR(LAST_NAME,1,8),'_US'))
------------------------- ------------------------------------------
Hunold HUNOLD_US
Ernst ERNST_US
Austin AUSTIN_US
Pataballa PATABALL_US
Lorentz LORENTZ_US
5 rows selected
select last_name,
upper(concat(substr(last_name,1,8),'_US'))
from employees
where department_id=60
Results:
LAST_NAME UPPER(CONCAT(SUBSTR(LAST_NAME,1,8),'_US'))
------------------------- ------------------------------------------
Hunold HUNOLD_US
Ernst ERNST_US
Austin AUSTIN_US
Pataballa PATABALL_US
Lorentz LORENTZ_US
5 rows selected
Oracle 11g: Conversion Functions
===> SQL Queries: TO_CHAR with Dates
select last_name,
to_char(hire_date,'fmDD Month YYYY') as hiredate
from employees
Results:
LAST_NAME HIREDATE
------------------------- -----------------
King 17 June 1987
Kochhar 21 September 1989
De Haan 13 January 1993
Hunold 3 January 1990
Ernst 21 May 1991
Austin 25 June 1997
Pataballa 5 February 1998
Lorentz 7 February 1999
Greenberg 17 August 1994
Faviet 16 August 1994
Chen 28 September 1997
Sciarra 30 September 1997
Urman 7 March 1998
Popp 7 December 1999
Raphaely 7 December 1994
Khoo 18 May 1995
Baida 24 December 1997
Tobias 24 July 1997
Himuro 15 November 1998
Colmenares 10 August 1999
Weiss 18 July 1996
Fripp 10 April 1997
Kaufling 1 May 1995
Vollman 10 October 1997
Mourgos 16 November 1999
Nayer 16 July 1997
Mikkilineni 28 September 1998
Landry 14 January 1999
Markle 8 March 2000
Bissot 20 August 1997
Atkinson 30 October 1997
Marlow 16 February 1997
Olson 10 April 1999
Mallin 14 June 1996
Rogers 26 August 1998
Gee 12 December 1999
Philtanker 6 February 2000
Ladwig 14 July 1995
Stiles 26 October 1997
Seo 12 February 1998
Patel 6 April 1998
Rajs 17 October 1995
Davies 29 January 1997
Matos 15 March 1998
Vargas 9 July 1998
Russell 1 October 1996
Partners 5 January 1997
Errazuriz 10 March 1997
Cambrault 15 October 1999
Zlotkey 29 January 2000
Tucker 30 January 1997
Bernstein 24 March 1997
Hall 20 August 1997
Olsen 30 March 1998
Cambrault 9 December 1998
Tuvault 23 November 1999
King 30 January 1996
Sully 4 March 1996
McEwen 1 August 1996
Smith 10 March 1997
Doran 15 December 1997
Sewall 3 November 1998
Vishney 11 November 1997
Greene 19 March 1999
Marvins 24 January 2000
Lee 23 February 2000
Ande 24 March 2000
Banda 21 April 2000
Ozer 11 March 1997
Bloom 23 March 1998
Fox 24 January 1998
Smith 23 February 1999
Bates 24 March 1999
Kumar 21 April 2000
Abel 11 May 1996
Hutton 19 March 1997
Taylor 24 March 1998
Livingston 23 April 1998
Grant 24 May 1999
Johnson 4 January 2000
Taylor 24 January 1998
Fleaur 23 February 1998
Sullivan 21 June 1999
Geoni 3 February 2000
Sarchand 27 January 1996
Bull 20 February 1997
Dellinger 24 June 1998
Cabrio 7 February 1999
Chung 14 June 1997
Dilly 13 August 1997
Gates 11 July 1998
Perkins 19 December 1999
Bell 4 February 1996
Everett 3 March 1997
McCain 1 July 1998
Jones 17 March 1999
Walsh 24 April 1998
Feeney 23 May 1998
OConnell 21 June 1999
Grant 13 January 2000
Whalen 17 September 1987
Hartstein 17 February 1996
Fay 17 August 1997
Mavris 7 June 1994
Baer 7 June 1994
Higgins 7 June 1994
Gietz 7 June 1994
107 rows selected
===> SQL Queries: TO_CHAR with Numbers
select to_char(salary,'$99,999.00') salary
from employees
where last_name = 'Ernst'
Results:
SALARY
-----------
$6,000.00
1 rows selected
===> SQL Queries: TO_DATE using RR
select last_name,TO_CHAR(hire_date, 'DD-Mon-YYYY')
from employees
where hire_date < TO_DATE('01-Jan-90','DD-Mon-RR')
Results:
LAST_NAME TO_CHAR(HIRE_DATE,'DD-MON-YYYY')
------------------------- --------------------------------
King 17-Jun-1987
Kochhar 21-Sep-1989
Whalen 17-Sep-1987
3 rows selected
select last_name,
to_char(hire_date,'fmDD Month YYYY') as hiredate
from employees
Results:
LAST_NAME HIREDATE
------------------------- -----------------
King 17 June 1987
Kochhar 21 September 1989
De Haan 13 January 1993
Hunold 3 January 1990
Ernst 21 May 1991
Austin 25 June 1997
Pataballa 5 February 1998
Lorentz 7 February 1999
Greenberg 17 August 1994
Faviet 16 August 1994
Chen 28 September 1997
Sciarra 30 September 1997
Urman 7 March 1998
Popp 7 December 1999
Raphaely 7 December 1994
Khoo 18 May 1995
Baida 24 December 1997
Tobias 24 July 1997
Himuro 15 November 1998
Colmenares 10 August 1999
Weiss 18 July 1996
Fripp 10 April 1997
Kaufling 1 May 1995
Vollman 10 October 1997
Mourgos 16 November 1999
Nayer 16 July 1997
Mikkilineni 28 September 1998
Landry 14 January 1999
Markle 8 March 2000
Bissot 20 August 1997
Atkinson 30 October 1997
Marlow 16 February 1997
Olson 10 April 1999
Mallin 14 June 1996
Rogers 26 August 1998
Gee 12 December 1999
Philtanker 6 February 2000
Ladwig 14 July 1995
Stiles 26 October 1997
Seo 12 February 1998
Patel 6 April 1998
Rajs 17 October 1995
Davies 29 January 1997
Matos 15 March 1998
Vargas 9 July 1998
Russell 1 October 1996
Partners 5 January 1997
Errazuriz 10 March 1997
Cambrault 15 October 1999
Zlotkey 29 January 2000
Tucker 30 January 1997
Bernstein 24 March 1997
Hall 20 August 1997
Olsen 30 March 1998
Cambrault 9 December 1998
Tuvault 23 November 1999
King 30 January 1996
Sully 4 March 1996
McEwen 1 August 1996
Smith 10 March 1997
Doran 15 December 1997
Sewall 3 November 1998
Vishney 11 November 1997
Greene 19 March 1999
Marvins 24 January 2000
Lee 23 February 2000
Ande 24 March 2000
Banda 21 April 2000
Ozer 11 March 1997
Bloom 23 March 1998
Fox 24 January 1998
Smith 23 February 1999
Bates 24 March 1999
Kumar 21 April 2000
Abel 11 May 1996
Hutton 19 March 1997
Taylor 24 March 1998
Livingston 23 April 1998
Grant 24 May 1999
Johnson 4 January 2000
Taylor 24 January 1998
Fleaur 23 February 1998
Sullivan 21 June 1999
Geoni 3 February 2000
Sarchand 27 January 1996
Bull 20 February 1997
Dellinger 24 June 1998
Cabrio 7 February 1999
Chung 14 June 1997
Dilly 13 August 1997
Gates 11 July 1998
Perkins 19 December 1999
Bell 4 February 1996
Everett 3 March 1997
McCain 1 July 1998
Jones 17 March 1999
Walsh 24 April 1998
Feeney 23 May 1998
OConnell 21 June 1999
Grant 13 January 2000
Whalen 17 September 1987
Hartstein 17 February 1996
Fay 17 August 1997
Mavris 7 June 1994
Baer 7 June 1994
Higgins 7 June 1994
Gietz 7 June 1994
107 rows selected
===> SQL Queries: TO_CHAR with Numbers
select to_char(salary,'$99,999.00') salary
from employees
where last_name = 'Ernst'
Results:
SALARY
-----------
$6,000.00
1 rows selected
===> SQL Queries: TO_DATE using RR
select last_name,TO_CHAR(hire_date, 'DD-Mon-YYYY')
from employees
where hire_date < TO_DATE('01-Jan-90','DD-Mon-RR')
Results:
LAST_NAME TO_CHAR(HIRE_DATE,'DD-MON-YYYY')
------------------------- --------------------------------
King 17-Jun-1987
Kochhar 21-Sep-1989
Whalen 17-Sep-1987
3 rows selected
Oracle 11g: Using Date Functions
select last_name, (sysdate-hire_date)/7 as weeks
from employees
where department_id=90
Results:
LAST_NAME WEEKS
------------------------- ----------------------
King 1208.871435185185185185185185185185185185
Kochhar 1090.728578042328042328042328042328042328
De Haan 917.871435185185185185185185185185185185
===> Other queries
select months_between('10-SEP-1980','13-AUG-1980')
from dual
SELECT ADD_MONTHS('10-SEP-1980',6)
FROM DUAL
SELECT NEXT_DAY('10-SEP-1980','FRIDAY')
FROM DUAL
SELECT LAST_DAY('10-SEP-1980')
FROM DUAL
RESULTS:
MONTHS_BETWEEN('10-SEP-1980','13-AUG-1980')
-------------------------------------------
0.9032258064516129032258064516129032258065
1 rows selected
ADD_MONTHS('10-SEP-1980',6)
-------------------------
10-MAR-81
1 rows selected
NEXT_DAY('10-SEP-1980','FRIDAY')
-------------------------
12-SEP-80
1 rows selected
LAST_DAY('10-SEP-1980')
-------------------------
30-SEP-80
1 rows selected
===> SQL Queries ROUND and TRUNC
select round(sysdate,'MONTH')
from dual;
select round(sysdate,'year')
from dual;
select trunc(sysdate,'month')
from dual;
select trunc(sysdate,'year')
from dual;
Note: Assuming sysdate = '16-AUG-2010'
Results:
ROUND(SYSDATE,'MONTH')
-------------------------
01-SEP-10
1 rows selected
ROUND(SYSDATE,'YEAR')
-------------------------
01-JAN-11
1 rows selected
TRUNC(SYSDATE,'MONTH')
-------------------------
01-AUG-10
1 rows selected
TRUNC(SYSDATE,'YEAR')
-------------------------
01-JAN-10
1 rows selected
from employees
where department_id=90
Results:
LAST_NAME WEEKS
------------------------- ----------------------
King 1208.871435185185185185185185185185185185
Kochhar 1090.728578042328042328042328042328042328
De Haan 917.871435185185185185185185185185185185
===> Other queries
select months_between('10-SEP-1980','13-AUG-1980')
from dual
SELECT ADD_MONTHS('10-SEP-1980',6)
FROM DUAL
SELECT NEXT_DAY('10-SEP-1980','FRIDAY')
FROM DUAL
SELECT LAST_DAY('10-SEP-1980')
FROM DUAL
RESULTS:
MONTHS_BETWEEN('10-SEP-1980','13-AUG-1980')
-------------------------------------------
0.9032258064516129032258064516129032258065
1 rows selected
ADD_MONTHS('10-SEP-1980',6)
-------------------------
10-MAR-81
1 rows selected
NEXT_DAY('10-SEP-1980','FRIDAY')
-------------------------
12-SEP-80
1 rows selected
LAST_DAY('10-SEP-1980')
-------------------------
30-SEP-80
1 rows selected
===> SQL Queries ROUND and TRUNC
select round(sysdate,'MONTH')
from dual;
select round(sysdate,'year')
from dual;
select trunc(sysdate,'month')
from dual;
select trunc(sysdate,'year')
from dual;
Note: Assuming sysdate = '16-AUG-2010'
Results:
ROUND(SYSDATE,'MONTH')
-------------------------
01-SEP-10
1 rows selected
ROUND(SYSDATE,'YEAR')
-------------------------
01-JAN-11
1 rows selected
TRUNC(SYSDATE,'MONTH')
-------------------------
01-AUG-10
1 rows selected
TRUNC(SYSDATE,'YEAR')
-------------------------
01-JAN-10
1 rows selected
Subscribe to:
Posts (Atom)
