SQL> select lower(last_name), upper(last_name), initcap(last_name)
2 from employees
3 where employee_id<120;
LOWER(LAST_NAME) UPPER(LAST_NAME) INITCAP(LAST_NAME)
------------------------- ------------------------- -------------------------
king KING King
kochhar KOCHHAR Kochhar
de haan DE HAAN De Haan
hunold HUNOLD Hunold
ernst ERNST Ernst
austin AUSTIN Austin
pataballa PATABALLA Pataballa
lorentz LORENTZ Lorentz
greenberg GREENBERG Greenberg
faviet FAVIET Faviet
chen CHEN Chen
LOWER(LAST_NAME) UPPER(LAST_NAME) INITCAP(LAST_NAME)
------------------------- ------------------------- -------------------------
sciarra SCIARRA Sciarra
urman URMAN Urman
popp POPP Popp
raphaely RAPHAELY Raphaely
khoo KHOO Khoo
baida BAIDA Baida
tobias TOBIAS Tobias
himuro HIMURO Himuro
colmenares COLMENARES Colmenares
20 rows selected.
SQL> select concat(last_name, first_name)
2 from employees
3 where employee_id<110;
CONCAT(LAST_NAME,FIRST_NAME)
---------------------------------------------
KingSteven
KochharNeena
De HaanLex
HunoldAlexander
ErnstBruce
AustinDavid
PataballaValli
LorentzDiana
GreenbergNancy
FavietDaniel
10 rows selected.
SQL> define_editor=vi
SQL> ed
Wrote file afiedt.buf
1 select concat(concat(last_name,' '), first_name)
2 from employees
3* where employee_id<110
SQL> /
CONCAT(CONCAT(LAST_NAME,''),FIRST_NAME)
----------------------------------------------
King Steven
Kochhar Neena
De Haan Lex
Hunold Alexander
Ernst Bruce
Austin David
Pataballa Valli
Lorentz Diana
Greenberg Nancy
Faviet Daniel
10 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select substr( concat(concat(last_name,' '), first_name),3,6)
2 from employees
3* where employee_id<110
SQL> /
SUBSTR
------
ng Ste
chhar
Haan
nold A
nst Br
stin D
taball
rentz
eenber
viet D
10 rows selected.
SQL> select last_name, substr(last_name,2,4)
2 from employees
3 where employee_id<110;
LAST_NAME SUBS
------------------------- ----
King ing
Kochhar ochh
De Haan e Ha
Hunold unol
Ernst rnst
Austin usti
Pataballa atab
Lorentz oren
Greenberg reen
Faviet avie
10 rows selected.
SQL> select last_name, length(last_name)
2 from employees
3 where employee_id<110;
LAST_NAME LENGTH(LAST_NAME)
------------------------- -----------------
King 4
Kochhar 7
De Haan 7
Hunold 6
Ernst 5
Austin 6
Pataballa 9
Lorentz 7
Greenberg 9
Faviet 6
10 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select last_name, length(last_name)
2 from employees
3* where length(last_name)>5
SQL> /
LAST_NAME LENGTH(LAST_NAME)
------------------------- -----------------
Atkinson 8
Austin 6
Bernstein 9
Bissot 6
Cabrio 6
Cambrault 9
Cambrault 9
Colmenares 10
Davies 6
De Haan 7
Dellinger 9
LAST_NAME LENGTH(LAST_NAME)
------------------------- -----------------
Errazuriz 9
Everett 7
Faviet 6
Feeney 6
Fleaur 6
Greenberg 9
Greene 6
Hartstein 9
Higgins 7
Himuro 6
Hunold 6
LAST_NAME LENGTH(LAST_NAME)
------------------------- -----------------
Hutton 6
Johnson 7
Kaufling 8
Kochhar 7
Ladwig 6
Landry 6
Livingston 10
Lorentz 7
Mallin 6
Markle 6
Marlow 6
LAST_NAME LENGTH(LAST_NAME)
------------------------- -----------------
Marvins 7
Mavris 6
McCain 6
McEwen 6
Mikkilineni 11
Mourgos 7
OConnell 8
Partners 8
Pataballa 9
Perkins 7
Philtanker 10
LAST_NAME LENGTH(LAST_NAME)
------------------------- -----------------
Raphaely 8
Rogers 6
Russell 7
Sarchand 8
Sciarra 7
Sewall 6
Stiles 6
Sullivan 8
Taylor 6
Taylor 6
Tobias 6
LAST_NAME LENGTH(LAST_NAME)
------------------------- -----------------
Tucker 6
Tuvault 7
Vargas 6
Vishney 7
Vollman 7
Whalen 6
Zlotkey 7
62 rows selected.
SQL> select last_name, instr(last_name, 'a')
2 from employees
3 where employee_id<110;
LAST_NAME INSTR(LAST_NAME,'A')
------------------------- --------------------
King 0
Kochhar 6
De Haan 5
Hunold 0
Ernst 0
Austin 0
Pataballa 2
Lorentz 0
Greenberg 0
Faviet 2
10 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select last_name, instr(last_name, 'a',1,2)
2 from employees
3* where employee_id<110
SQL> /
LAST_NAME INSTR(LAST_NAME,'A',1,2)
------------------------- ------------------------
King 0
Kochhar 0
De Haan 6
Hunold 0
Ernst 0
Austin 0
Pataballa 4
Lorentz 0
Greenberg 0
Faviet 0
10 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select last_name, instr(last_name, 'a',1,2)
2 from employees
3* where employee_id<110
SQL> select last_name, lpad(last_name, 10, '*')
2 from employees
3 where employee_id<110;
LAST_NAME LPAD(LAST_
------------------------- ----------
King ******King
Kochhar ***Kochhar
De Haan ***De Haan
Hunold ****Hunold
Ernst *****Ernst
Austin ****Austin
Pataballa *Pataballa
Lorentz ***Lorentz
Greenberg *Greenberg
Faviet ****Faviet
10 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select last_name, rpad(last_name, 10, '*')
2 from employees
3* where employee_id<110
SQL> /
LAST_NAME RPAD(LAST_
------------------------- ----------
King King******
Kochhar Kochhar***
De Haan De Haan***
Hunold Hunold****
Ernst Ernst*****
Austin Austin****
Pataballa Pataballa*
Lorentz Lorentz***
Greenberg Greenberg*
Faviet Faviet****
10 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select last_name, rpad(last_name||first_name, 10, '*')
2 from employees
3* where employee_id<110
SQL> /
LAST_NAME RPAD(LAST_
------------------------- ----------
King KingSteven
Kochhar KochharNee
De Haan De HaanLex
Hunold HunoldAlex
Ernst ErnstBruce
Austin AustinDavi
Pataballa PataballaV
Lorentz LorentzDia
Greenberg GreenbergN
Faviet FavietDani
10 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select last_name, rpad(last_name||first_name, 10, '*')
2 from employees
3* where employee_id<110
SQL> select trim('A' from last_name), last_name
2 from employees
3 where employee_id<110;
TRIM('A'FROMLAST_NAME) LAST_NAME
------------------------- -------------------------
King King
Kochhar Kochhar
De Haan De Haan
Hunold Hunold
Ernst Ernst
ustin Austin
Pataballa Pataballa
Lorentz Lorentz
Greenberg Greenberg
Faviet Faviet
10 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select trim('A' from upper(last_name)), last_name
2 from employees
3* where employee_id<110
SQL> /
TRIM('A'FROMUPPER(LAST_NA LAST_NAME
------------------------- -------------------------
KING King
KOCHHAR Kochhar
DE HAAN De Haan
HUNOLD Hunold
ERNST Ernst
USTIN Austin
PATABALL Pataballa
LORENTZ Lorentz
GREENBERG Greenberg
FAVIET Faviet
10 rows selected.
SQL> select replace(last_name, 'a','ank'), last_name
2 from employees
3 where last_name<110;
where last_name<110
*
ERROR at line 3:
ORA-01722: invalid number
SQL> ed
Wrote file afiedt.buf
1 select replace(last_name, 'a','ank'), last_name
2 from employees
3* where employee_id<110
SQL> /
REPLACE(LAST_NAME,'A','ANK')
---------------------------------------------------------------------------
LAST_NAME
-------------------------
King
King
Kochhankr
Kochhar
De Hankankn
De Haan
REPLACE(LAST_NAME,'A','ANK')
---------------------------------------------------------------------------
LAST_NAME
-------------------------
Hunold
Hunold
Ernst
Ernst
Austin
Austin
REPLACE(LAST_NAME,'A','ANK')
---------------------------------------------------------------------------
LAST_NAME
-------------------------
Panktankbankllank
Pataballa
Lorentz
Lorentz
Greenberg
Greenberg
REPLACE(LAST_NAME,'A','ANK')
---------------------------------------------------------------------------
LAST_NAME
-------------------------
Fankviet
Faviet
10 rows selected.
SQL> select round(45.936,2), trunc(45.936,2), mod(12000,39)
2 from dual;
ROUND(45.936,2) TRUNC(45.936,2) MOD(12000,39)
--------------- --------------- -------------
45.94 45.93 27
SQL> ed
Wrote file afiedt.buf
1 select round(45.936,-1), trunc(45.936,-1), mod(12000,39)
2* from dual
SQL> /
ROUND(45.936,-1) TRUNC(45.936,-1) MOD(12000,39)
---------------- ---------------- -------------
50 40 27
SQL> ed
Wrote file afiedt.buf
1 select round(45.936,-2), trunc(45.936,-2), mod(12000,39)
2* from dual
SQL> /
ROUND(45.936,-2) TRUNC(45.936,-2) MOD(12000,39)
---------------- ---------------- -------------
0 0 27
SQL> ed
Wrote file afiedt.buf
1 select round(145.936,-2), trunc(145.936,-2), mod(12000,39)
2* from dual
SQL> /
ROUND(145.936,-2) TRUNC(145.936,-2) MOD(12000,39)
----------------- ----------------- -------------
100 100 27
SQL> ed
Wrote file afiedt.buf
1 select round(165.936,-2), trunc(165.936,-2), mod(12000,39)
2* from dual
SQL> /
ROUND(165.936,-2) TRUNC(165.936,-2) MOD(12000,39)
----------------- ----------------- -------------
200 100 27
SQL> ed
Wrote file afiedt.buf
1 select round(165.936,0), trunc(165.936,0), mod(12000,39)
2* from dual
SQL> /
ROUND(165.936,0) TRUNC(165.936,0) MOD(12000,39)
---------------- ---------------- -------------
166 165 27
SQL> select sysdate from dual;
SYSDATE
---------
30-AUG-13
SQL> alter session set nls_date_format='DD-MON-RR HH24:MI:SS';
Session altered.
SQL> select sysdate from dual;
SYSDATE
------------------
30-AUG-13 18:08:58
SQL> select sysdate+6 from dual;
SYSDATE+6
------------------
05-SEP-13 18:09:39
SQL> select sysdate-'12-JAN-13' FROM DUAL;
select sysdate-'12-JAN-13' FROM DUAL
*
ERROR at line 1:
ORA-01722: invalid number
SQL> ed
Wrote file afiedt.buf
1* select sysdate-to_date('12-JAN-13','DD-MON-RR') FROM DUAL
SQL> /
SYSDATE-TO_DATE('12-JAN-13','DD-MON-RR')
----------------------------------------
230.757998
SQL> select sysdate, sysdate+6/24 from dual;
SYSDATE SYSDATE+6/24
------------------ ------------------
30-AUG-13 18:12:16 31-AUG-13 00:12:16
SQL> select sysdate-hire_date "Experience"
2 from dual;
select sysdate-hire_date "Experience"
*
ERROR at line 1:
ORA-00904: "HIRE_DATE": invalid identifier
SQL> ed
Wrote file afiedt.buf
1 select sysdate-hire_date "Experience"
2* from employees
SQL> /
Experience
----------
5184.7594
4978.7594
9479.7594
6404.7594
5857.7594
7024.7594
7024.7594
7024.7594
7024.7594
9571.7594
8744.7594
Experience
----------
7534.7594
8640.7594
8137.7594
5910.7594
5685.7594
5318.7594
6953.7594
6954.7594
5815.7594
5813.7594
5655.7594
Experience
----------
5015.7594
6841.7594
6679.7594
5728.7594
5881.7594
5402.7594
5134.7594
6252.7594
5986.7594
6696.7594
5803.7594
Experience
----------
5036.7594
5889.7594
5450.7594
5342.7594
4923.7594
5854.7594
5783.7594
6039.7594
5256.7594
6286.7594
5483.7594
Experience
----------
5010.7594
4954.7594
6622.7594
5787.7594
5678.7594
5625.7594
6527.7594
6057.7594
5647.7594
5531.7594
6177.7594
Experience
----------
6081.7594
6017.7594
5068.7594
4962.7594
6056.7594
6003.7594
5854.7594
5632.7594
5378.7594
5029.7594
6422.7594
Experience
----------
6388.7594
6238.7594
6017.7594
5737.7594
5414.7594
5771.7594
5278.7594
4967.7594
4937.7594
4907.7594
4879.7594
Experience
----------
6016.7594
5639.7594
5697.7594
5302.7594
5273.7594
4879.7594
6320.7594
6008.7594
5638.7594
5608.7594
5212.7594
Experience
----------
4987.7594
5697.7594
5667.7594
5184.7594
4957.7594
6425.7594
6035.7594
5546.7594
5318.7594
5921.7594
5861.7594
Experience
----------
5529.7594
5003.7594
6417.7594
6024.7594
5539.7594
5280.7594
5607.7594
5578.7594
107 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select months_between(sysdate,hire_date) "Experience"
2 from employees
3* where employee_id<110
SQL> /
Experience
----------
314.443892
287.31486
247.572924
283.895505
267.31486
194.185827
186.830989
174.766473
228.443892
228.47615
10 rows selected.
SQL> select add_months(sysdate, 6), sysdate
2 from dual;
ADD_MONTHS(SYSDATE SYSDATE
------------------ ------------------
28-FEB-14 18:16:01 30-AUG-13 18:16:01
SQL> select next_day(sysdate,'SATURDAY')
2 from dual;
NEXT_DAY(SYSDATE,'
------------------
31-AUG-13 18:16:42
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
------------------
31-AUG-13 18:17:23
SQL> select round(to_date('22-JUL-12','DD-MON-RR'),'MONTH')
2 FROM DUAL;
ROUND(TO_DATE('22-
------------------
01-AUG-12 00:00:00
SQL> ed
Wrote file afiedt.buf
1 select round(to_date('22-JUL-12','DD-MON-RR'),'YEAR')
2* FROM DUAL
SQL> /
ROUND(TO_DATE('22-
------------------
01-JAN-13 00:00:00
SQL> ed
Wrote file afiedt.buf
1 select trunc(to_date('22-JUL-12','DD-MON-RR'),'YEAR')
2* FROM DUAL
SQL> /
TRUNC(TO_DATE('22-
------------------
01-JAN-12 00:00:00
SQL> ed
Wrote file afiedt.buf
1 select trunc(to_date('22-JUL-12','DD-MON-RR'),'MONTH')
2* FROM DUAL
SQL> /
TRUNC(TO_DATE('22-
------------------
01-JUL-12 00:00:00
SQL> select to_char(sysdate,'DD-MONTH-YYYY')
2 FROM DUAL;
TO_CHAR(SYSDATE,'
-----------------
30-AUGUST -2013
SQL> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'DDSP-MONTH-YYYY')
2* FROM DUAL
SQL> /
TO_CHAR(SYSDATE,'DDSP-MONTH
---------------------------
THIRTY-AUGUST -2013
SQL> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'DDSPTH-MONTH-YYYY')
2* FROM DUAL
SQL> /
TO_CHAR(SYSDATE,'DDSPTH-MONTH
-----------------------------
THIRTIETH-AUGUST -2013
SQL> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'DDSPTH-MONTH-YEAR')
2* FROM DUAL
SQL> /
TO_CHAR(SYSDATE,'DDSPTH-MONTH-YEAR')
-------------------------------------------------------------------
THIRTIETH-AUGUST -TWENTY THIRTEEN
SQL> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'DDSPTH-MONTH-YYYYSP')
2* FROM DUAL
SQL> /
TO_CHAR(SYSDATE,'DDSPTH-MONTH-YYYYSP')
-------------------------------------------------------------------
THIRTIETH-AUGUST -TWO THOUSAND THIRTEEN
SQL> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'DDSPTH-MONTH-YYYYSP DAY')
2* FROM DUAL
SQL> /
TO_CHAR(SYSDATE,'DDSPTH-MONTH-YYYYSPDAY')
-----------------------------------------------------------------------------
THIRTIETH-AUGUST -TWO THOUSAND THIRTEEN FRIDAY
SQL> select to_char(sysdate,'J')
2 FROM DUAL;
TO_CHAR
-------
2456535
SQL> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'J')/365.25
2* FROM DUAL
SQL> /
TO_CHAR(SYSDATE,'J')/365.25
---------------------------
6725.62628
SQL> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'SSSSS')
2* FROM DUAL
SQL> /
TO_CH
-----
66675
SQL> ed
Wrote file afiedt.buf
1 select to_number(sysdate,'SSSSS')
2* FROM DUAL
SQL> /
select to_number(sysdate,'SSSSS')
*
ERROR at line 1:
ORA-01481: invalid number format model
SQL> ed
Wrote file afiedt.buf
1 select to_char(17895,'99G999D99')
2* FROM DUAL
SQL> /
TO_CHAR(17
----------
17,895.00
SQL> ed
Wrote file afiedt.buf
1 select to_char(17895,'99G999D99')
2* FROM DUAL
SQL> select to_char(salary,'L999G999D99')
2 FROM EMPLOYEES
3 WHERE EMPLOYEE_ID<105;
TO_CHAR(SALARY,'L999G
---------------------
$24,000.00
$17,000.00
$17,000.00
$9,000.00
$6,000.00
SQL> select '$12,456.66'-34 from dual;
select '$12,456.66'-34 from dual
*
ERROR at line 1:
ORA-01722: invalid number
SQL> ed
Wrote file afiedt.buf
1* select to_number('$12,456.66','L99G999D99')-34 from dual
SQL> /
TO_NUMBER('$12,456.66','L99G999D99')-34
---------------------------------------
12422.66
SQL> select salary*commission_pct from employees
2 where employee_id<105;
SALARY*COMMISSION_PCT
---------------------
null
null
null
null
null
SQL> ed
Wrote file afiedt.buf
1 select salary*commission_pct, salary, commission_pct from employees
2* where employee_id<105
SQL> /
SALARY*COMMISSION_PCT SALARY COMMISSION_PCT
--------------------- ---------- --------------
null 24000 null
null 17000 null
null 17000 null
null 9000 null
null 6000 null
SQL> ed
Wrote file afiedt.buf
1 select salary*nvl(commission_pct,0), salary, commission_pct from employees
2* where employee_id<105
SQL> /
SALARY*NVL(COMMISSION_PCT,0) SALARY COMMISSION_PCT
---------------------------- ---------- --------------
0 24000 null
0 17000 null
0 17000 null
0 9000 null
0 6000 null
SQL> ed
Wrote file afiedt.buf
1 select salary+salary*nvl(commission_pct,0), salary, commission_pct from employees
2* where employee_id<105
SQL> /
SALARY+SALARY*NVL(COMMISSION_PCT,0) SALARY COMMISSION_PCT
----------------------------------- ---------- --------------
24000 24000 null
17000 17000 null
17000 17000 null
9000 9000 null
6000 6000 null
SQL> ed
Wrote file afiedt.buf
1 select salary+salary*nvl(commission_pct,0), salary, commission_pct from employees
2* where employee_id<110
SQL> /
SALARY+SALARY*NVL(COMMISSION_PCT,0) SALARY COMMISSION_PCT
----------------------------------- ---------- --------------
24000 24000 null
17000 17000 null
17000 17000 null
9000 9000 null
6000 6000 null
4800 4800 null
4800 4800 null
4200 4200 null
12000 12000 null
9000 9000 null
10 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select salary+salary*nvl(commission_pct,0), salary, commission_pct from employees
2* where employee_id<110
SQL> ed
Wrote file afiedt.buf
1 select nvl2(commission_pct, salary+salary*commission_pct, salary), salary, commission_pct from employees
2* where employee_id>195
SQL> /
NVL2(COMMISSION_PCT,SALARY+SALARY*COMMISSION_PCT,SALARY) SALARY
-------------------------------------------------------- ----------
COMMISSION_PCT
--------------
3100 3100
null
3000 3000
null
2600 2600
null
NVL2(COMMISSION_PCT,SALARY+SALARY*COMMISSION_PCT,SALARY) SALARY
-------------------------------------------------------- ----------
COMMISSION_PCT
--------------
2600 2600
null
4400 4400
null
13000 13000
null
NVL2(COMMISSION_PCT,SALARY+SALARY*COMMISSION_PCT,SALARY) SALARY
-------------------------------------------------------- ----------
COMMISSION_PCT
--------------
6000 6000
null
6500 6500
null
10000 10000
null
NVL2(COMMISSION_PCT,SALARY+SALARY*COMMISSION_PCT,SALARY) SALARY
-------------------------------------------------------- ----------
COMMISSION_PCT
--------------
12000 12000
null
8300 8300
null
11 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select nvl2(commission_pct, salary+salary*commission_pct, salary) total, salary, commission_pct from employees
2* where employee_id>195
SQL> /
TOTAL SALARY COMMISSION_PCT
---------- ---------- --------------
3100 3100 null
3000 3000 null
2600 2600 null
2600 2600 null
4400 4400 null
13000 13000 null
6000 6000 null
6500 6500 null
10000 10000 null
12000 12000 null
8300 8300 null
11 rows selected.
SQL> select nullif(length(first_name),length(last_name))
2 from employees;
NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME))
--------------------------------------------
5
6
5
5
7
6
4
9
5
5
5
NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME))
--------------------------------------------
8
6
7
6
7
null
null
5
null
3
5
NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME))
--------------------------------------------
8
6
null
7
null
null
null
5
4
6
4
NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME))
--------------------------------------------
7
2
6
7
7
9
5
8
5
7
null
NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME))
--------------------------------------------
3
9
null
null
null
5
9
7
6
5
7
NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME))
--------------------------------------------
null
5
5
4
5
5
null
5
6
7
5
NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME))
--------------------------------------------
null
5
5
5
null
6
11
2
null
5
5
NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME))
--------------------------------------------
6
null
5
null
6
3
7
4
7
6
4
NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME))
--------------------------------------------
null
7
7
7
6
7
8
7
5
5
6
NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME))
--------------------------------------------
11
5
5
6
null
7
8
5
107 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select nullif(length(first_name),length(last_name)) comparison, length(first_name), length(last_name)
2* from employees
SQL> /
COMPARISON LENGTH(FIRST_NAME) LENGTH(LAST_NAME)
---------- ------------------ -----------------
5 5 4
6 6 4
5 5 8
5 5 6
7 7 4
6 6 5
4 4 5
9 9 5
5 5 4
5 5 9
5 5 6
COMPARISON LENGTH(FIRST_NAME) LENGTH(LAST_NAME)
---------- ------------------ -----------------
8 8 5
6 6 4
7 7 6
6 6 9
7 7 9
null 4 4
null 5 5
5 5 10
null 6 6
3 3 7
5 5 9
COMPARISON LENGTH(FIRST_NAME) LENGTH(LAST_NAME)
---------- ------------------ -----------------
8 8 5
6 6 5
null 5 5
7 7 9
null 7 7
null 6 6
null 3 3
5 5 6
4 4 6
6 6 3
4 4 5
COMPARISON LENGTH(FIRST_NAME) LENGTH(LAST_NAME)
---------- ------------------ -----------------
7 7 5
2 2 3
6 6 5
7 7 5
7 7 5
9 9 5
5 5 9
8 8 6
5 5 4
7 7 9
null 7 7
COMPARISON LENGTH(FIRST_NAME) LENGTH(LAST_NAME)
---------- ------------------ -----------------
3 3 6
9 9 6
null 6 6
null 7 7
null 5 5
5 5 8
9 9 4
7 7 4
6 6 4
5 5 7
7 7 5
COMPARISON LENGTH(FIRST_NAME) LENGTH(LAST_NAME)
---------- ------------------ -----------------
null 6 6
5 5 6
5 5 3
4 4 10
5 5 7
5 5 6
null 6 6
5 5 6
6 6 7
7 7 5
5 5 6
COMPARISON LENGTH(FIRST_NAME) LENGTH(LAST_NAME)
---------- ------------------ -----------------
null 6 6
5 5 6
5 5 11
5 5 7
null 5 5
6 6 8
11 11 5
2 2 5
null 4 4
5 5 8
5 5 9
COMPARISON LENGTH(FIRST_NAME) LENGTH(LAST_NAME)
---------- ------------------ -----------------
6 6 5
null 7 7
5 5 10
null 4 4
6 6 4
3 3 8
7 7 6
4 4 7
7 7 8
6 6 7
4 4 3
COMPARISON LENGTH(FIRST_NAME) LENGTH(LAST_NAME)
---------- ------------------ -----------------
null 6 6
7 7 5
7 7 5
7 7 6
6 6 8
7 7 5
8 8 6
7 7 6
5 5 6
5 5 6
6 6 7
COMPARISON LENGTH(FIRST_NAME) LENGTH(LAST_NAME)
---------- ------------------ -----------------
11 11 5
5 5 6
5 5 7
6 6 7
null 5 5
7 7 5
8 8 6
5 5 7
107 rows selected.
SQL> select coalesce(manager_id, commission_pct, -1) from employees
2 where employee_id between 130 and 150;
COALESCE(MANAGER_ID,COMMISSION_PCT,-1)
--------------------------------------
121
121
121
122
122
122
122
123
123
123
123
COALESCE(MANAGER_ID,COMMISSION_PCT,-1)
--------------------------------------
124
124
124
124
100
100
100
100
100
145
21 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select coalesce(manager_id, commission_pct, -1) from employees
2* where employee_id between 160 and 180
SQL> /
COALESCE(MANAGER_ID,COMMISSION_PCT,-1)
--------------------------------------
146
146
147
147
147
147
147
147
148
148
148
COALESCE(MANAGER_ID,COMMISSION_PCT,-1)
--------------------------------------
148
148
148
149
149
149
149
149
149
120
21 rows selected.
SQL> select last_name, case job_id when 'SA_REP' then salary*1.10
2 when 'IT_PROG' then salary*1.15
3 else salary*1.20 end Increment
4 from employees
5 where employee_id<110;
else salary*1.20 end Increment
*
ERROR at line 3:
ORA-00923: FROM keyword not found where expected
SQL> ed
Wrote file afiedt.buf
1 select job_id, salary ,
2 case job_id
3 when 'SA_REP' then salary*1.10
4 when 'IT_PROG' then salary*1.15
5 else salary*1.20 end "Increment"
6 from employees
7* where employee_id<110
SQL> /
JOB_ID SALARY Increment
---------- ---------- ----------
AD_PRES 24000 28800
AD_VP 17000 20400
AD_VP 17000 20400
IT_PROG 9000 10350
IT_PROG 6000 6900
IT_PROG 4800 5520
IT_PROG 4800 5520
IT_PROG 4200 4830
FI_MGR 12000 14400
FI_ACCOUNT 9000 10800
10 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select job_id, salary ,
2 decode(job_id, 'SA_REP',salary*1.10,'IT_PROG',salary*1.15,salary*1.20) "Increment"
3 from employees
4* where employee_id<110
SQL> /
JOB_ID SALARY Increment
---------- ---------- ----------
AD_PRES 24000 28800
AD_VP 17000 20400
AD_VP 17000 20400
IT_PROG 9000 10350
IT_PROG 6000 6900
IT_PROG 4800 5520
IT_PROG 4800 5520
IT_PROG 4200 4830
FI_MGR 12000 14400
FI_ACCOUNT 9000 10800
10 rows selected.
SQL> spool off
2 from employees
3 where employee_id<120;
LOWER(LAST_NAME) UPPER(LAST_NAME) INITCAP(LAST_NAME)
------------------------- ------------------------- -------------------------
king KING King
kochhar KOCHHAR Kochhar
de haan DE HAAN De Haan
hunold HUNOLD Hunold
ernst ERNST Ernst
austin AUSTIN Austin
pataballa PATABALLA Pataballa
lorentz LORENTZ Lorentz
greenberg GREENBERG Greenberg
faviet FAVIET Faviet
chen CHEN Chen
LOWER(LAST_NAME) UPPER(LAST_NAME) INITCAP(LAST_NAME)
------------------------- ------------------------- -------------------------
sciarra SCIARRA Sciarra
urman URMAN Urman
popp POPP Popp
raphaely RAPHAELY Raphaely
khoo KHOO Khoo
baida BAIDA Baida
tobias TOBIAS Tobias
himuro HIMURO Himuro
colmenares COLMENARES Colmenares
20 rows selected.
SQL> select concat(last_name, first_name)
2 from employees
3 where employee_id<110;
CONCAT(LAST_NAME,FIRST_NAME)
---------------------------------------------
KingSteven
KochharNeena
De HaanLex
HunoldAlexander
ErnstBruce
AustinDavid
PataballaValli
LorentzDiana
GreenbergNancy
FavietDaniel
10 rows selected.
SQL> define_editor=vi
SQL> ed
Wrote file afiedt.buf
1 select concat(concat(last_name,' '), first_name)
2 from employees
3* where employee_id<110
SQL> /
CONCAT(CONCAT(LAST_NAME,''),FIRST_NAME)
----------------------------------------------
King Steven
Kochhar Neena
De Haan Lex
Hunold Alexander
Ernst Bruce
Austin David
Pataballa Valli
Lorentz Diana
Greenberg Nancy
Faviet Daniel
10 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select substr( concat(concat(last_name,' '), first_name),3,6)
2 from employees
3* where employee_id<110
SQL> /
SUBSTR
------
ng Ste
chhar
Haan
nold A
nst Br
stin D
taball
rentz
eenber
viet D
10 rows selected.
SQL> select last_name, substr(last_name,2,4)
2 from employees
3 where employee_id<110;
LAST_NAME SUBS
------------------------- ----
King ing
Kochhar ochh
De Haan e Ha
Hunold unol
Ernst rnst
Austin usti
Pataballa atab
Lorentz oren
Greenberg reen
Faviet avie
10 rows selected.
SQL> select last_name, length(last_name)
2 from employees
3 where employee_id<110;
LAST_NAME LENGTH(LAST_NAME)
------------------------- -----------------
King 4
Kochhar 7
De Haan 7
Hunold 6
Ernst 5
Austin 6
Pataballa 9
Lorentz 7
Greenberg 9
Faviet 6
10 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select last_name, length(last_name)
2 from employees
3* where length(last_name)>5
SQL> /
LAST_NAME LENGTH(LAST_NAME)
------------------------- -----------------
Atkinson 8
Austin 6
Bernstein 9
Bissot 6
Cabrio 6
Cambrault 9
Cambrault 9
Colmenares 10
Davies 6
De Haan 7
Dellinger 9
LAST_NAME LENGTH(LAST_NAME)
------------------------- -----------------
Errazuriz 9
Everett 7
Faviet 6
Feeney 6
Fleaur 6
Greenberg 9
Greene 6
Hartstein 9
Higgins 7
Himuro 6
Hunold 6
LAST_NAME LENGTH(LAST_NAME)
------------------------- -----------------
Hutton 6
Johnson 7
Kaufling 8
Kochhar 7
Ladwig 6
Landry 6
Livingston 10
Lorentz 7
Mallin 6
Markle 6
Marlow 6
LAST_NAME LENGTH(LAST_NAME)
------------------------- -----------------
Marvins 7
Mavris 6
McCain 6
McEwen 6
Mikkilineni 11
Mourgos 7
OConnell 8
Partners 8
Pataballa 9
Perkins 7
Philtanker 10
LAST_NAME LENGTH(LAST_NAME)
------------------------- -----------------
Raphaely 8
Rogers 6
Russell 7
Sarchand 8
Sciarra 7
Sewall 6
Stiles 6
Sullivan 8
Taylor 6
Taylor 6
Tobias 6
LAST_NAME LENGTH(LAST_NAME)
------------------------- -----------------
Tucker 6
Tuvault 7
Vargas 6
Vishney 7
Vollman 7
Whalen 6
Zlotkey 7
62 rows selected.
SQL> select last_name, instr(last_name, 'a')
2 from employees
3 where employee_id<110;
LAST_NAME INSTR(LAST_NAME,'A')
------------------------- --------------------
King 0
Kochhar 6
De Haan 5
Hunold 0
Ernst 0
Austin 0
Pataballa 2
Lorentz 0
Greenberg 0
Faviet 2
10 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select last_name, instr(last_name, 'a',1,2)
2 from employees
3* where employee_id<110
SQL> /
LAST_NAME INSTR(LAST_NAME,'A',1,2)
------------------------- ------------------------
King 0
Kochhar 0
De Haan 6
Hunold 0
Ernst 0
Austin 0
Pataballa 4
Lorentz 0
Greenberg 0
Faviet 0
10 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select last_name, instr(last_name, 'a',1,2)
2 from employees
3* where employee_id<110
SQL> select last_name, lpad(last_name, 10, '*')
2 from employees
3 where employee_id<110;
LAST_NAME LPAD(LAST_
------------------------- ----------
King ******King
Kochhar ***Kochhar
De Haan ***De Haan
Hunold ****Hunold
Ernst *****Ernst
Austin ****Austin
Pataballa *Pataballa
Lorentz ***Lorentz
Greenberg *Greenberg
Faviet ****Faviet
10 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select last_name, rpad(last_name, 10, '*')
2 from employees
3* where employee_id<110
SQL> /
LAST_NAME RPAD(LAST_
------------------------- ----------
King King******
Kochhar Kochhar***
De Haan De Haan***
Hunold Hunold****
Ernst Ernst*****
Austin Austin****
Pataballa Pataballa*
Lorentz Lorentz***
Greenberg Greenberg*
Faviet Faviet****
10 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select last_name, rpad(last_name||first_name, 10, '*')
2 from employees
3* where employee_id<110
SQL> /
LAST_NAME RPAD(LAST_
------------------------- ----------
King KingSteven
Kochhar KochharNee
De Haan De HaanLex
Hunold HunoldAlex
Ernst ErnstBruce
Austin AustinDavi
Pataballa PataballaV
Lorentz LorentzDia
Greenberg GreenbergN
Faviet FavietDani
10 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select last_name, rpad(last_name||first_name, 10, '*')
2 from employees
3* where employee_id<110
SQL> select trim('A' from last_name), last_name
2 from employees
3 where employee_id<110;
TRIM('A'FROMLAST_NAME) LAST_NAME
------------------------- -------------------------
King King
Kochhar Kochhar
De Haan De Haan
Hunold Hunold
Ernst Ernst
ustin Austin
Pataballa Pataballa
Lorentz Lorentz
Greenberg Greenberg
Faviet Faviet
10 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select trim('A' from upper(last_name)), last_name
2 from employees
3* where employee_id<110
SQL> /
TRIM('A'FROMUPPER(LAST_NA LAST_NAME
------------------------- -------------------------
KING King
KOCHHAR Kochhar
DE HAAN De Haan
HUNOLD Hunold
ERNST Ernst
USTIN Austin
PATABALL Pataballa
LORENTZ Lorentz
GREENBERG Greenberg
FAVIET Faviet
10 rows selected.
SQL> select replace(last_name, 'a','ank'), last_name
2 from employees
3 where last_name<110;
where last_name<110
*
ERROR at line 3:
ORA-01722: invalid number
SQL> ed
Wrote file afiedt.buf
1 select replace(last_name, 'a','ank'), last_name
2 from employees
3* where employee_id<110
SQL> /
REPLACE(LAST_NAME,'A','ANK')
---------------------------------------------------------------------------
LAST_NAME
-------------------------
King
King
Kochhankr
Kochhar
De Hankankn
De Haan
REPLACE(LAST_NAME,'A','ANK')
---------------------------------------------------------------------------
LAST_NAME
-------------------------
Hunold
Hunold
Ernst
Ernst
Austin
Austin
REPLACE(LAST_NAME,'A','ANK')
---------------------------------------------------------------------------
LAST_NAME
-------------------------
Panktankbankllank
Pataballa
Lorentz
Lorentz
Greenberg
Greenberg
REPLACE(LAST_NAME,'A','ANK')
---------------------------------------------------------------------------
LAST_NAME
-------------------------
Fankviet
Faviet
10 rows selected.
SQL> select round(45.936,2), trunc(45.936,2), mod(12000,39)
2 from dual;
ROUND(45.936,2) TRUNC(45.936,2) MOD(12000,39)
--------------- --------------- -------------
45.94 45.93 27
SQL> ed
Wrote file afiedt.buf
1 select round(45.936,-1), trunc(45.936,-1), mod(12000,39)
2* from dual
SQL> /
ROUND(45.936,-1) TRUNC(45.936,-1) MOD(12000,39)
---------------- ---------------- -------------
50 40 27
SQL> ed
Wrote file afiedt.buf
1 select round(45.936,-2), trunc(45.936,-2), mod(12000,39)
2* from dual
SQL> /
ROUND(45.936,-2) TRUNC(45.936,-2) MOD(12000,39)
---------------- ---------------- -------------
0 0 27
SQL> ed
Wrote file afiedt.buf
1 select round(145.936,-2), trunc(145.936,-2), mod(12000,39)
2* from dual
SQL> /
ROUND(145.936,-2) TRUNC(145.936,-2) MOD(12000,39)
----------------- ----------------- -------------
100 100 27
SQL> ed
Wrote file afiedt.buf
1 select round(165.936,-2), trunc(165.936,-2), mod(12000,39)
2* from dual
SQL> /
ROUND(165.936,-2) TRUNC(165.936,-2) MOD(12000,39)
----------------- ----------------- -------------
200 100 27
SQL> ed
Wrote file afiedt.buf
1 select round(165.936,0), trunc(165.936,0), mod(12000,39)
2* from dual
SQL> /
ROUND(165.936,0) TRUNC(165.936,0) MOD(12000,39)
---------------- ---------------- -------------
166 165 27
SQL> select sysdate from dual;
SYSDATE
---------
30-AUG-13
SQL> alter session set nls_date_format='DD-MON-RR HH24:MI:SS';
Session altered.
SQL> select sysdate from dual;
SYSDATE
------------------
30-AUG-13 18:08:58
SQL> select sysdate+6 from dual;
SYSDATE+6
------------------
05-SEP-13 18:09:39
SQL> select sysdate-'12-JAN-13' FROM DUAL;
select sysdate-'12-JAN-13' FROM DUAL
*
ERROR at line 1:
ORA-01722: invalid number
SQL> ed
Wrote file afiedt.buf
1* select sysdate-to_date('12-JAN-13','DD-MON-RR') FROM DUAL
SQL> /
SYSDATE-TO_DATE('12-JAN-13','DD-MON-RR')
----------------------------------------
230.757998
SQL> select sysdate, sysdate+6/24 from dual;
SYSDATE SYSDATE+6/24
------------------ ------------------
30-AUG-13 18:12:16 31-AUG-13 00:12:16
SQL> select sysdate-hire_date "Experience"
2 from dual;
select sysdate-hire_date "Experience"
*
ERROR at line 1:
ORA-00904: "HIRE_DATE": invalid identifier
SQL> ed
Wrote file afiedt.buf
1 select sysdate-hire_date "Experience"
2* from employees
SQL> /
Experience
----------
5184.7594
4978.7594
9479.7594
6404.7594
5857.7594
7024.7594
7024.7594
7024.7594
7024.7594
9571.7594
8744.7594
Experience
----------
7534.7594
8640.7594
8137.7594
5910.7594
5685.7594
5318.7594
6953.7594
6954.7594
5815.7594
5813.7594
5655.7594
Experience
----------
5015.7594
6841.7594
6679.7594
5728.7594
5881.7594
5402.7594
5134.7594
6252.7594
5986.7594
6696.7594
5803.7594
Experience
----------
5036.7594
5889.7594
5450.7594
5342.7594
4923.7594
5854.7594
5783.7594
6039.7594
5256.7594
6286.7594
5483.7594
Experience
----------
5010.7594
4954.7594
6622.7594
5787.7594
5678.7594
5625.7594
6527.7594
6057.7594
5647.7594
5531.7594
6177.7594
Experience
----------
6081.7594
6017.7594
5068.7594
4962.7594
6056.7594
6003.7594
5854.7594
5632.7594
5378.7594
5029.7594
6422.7594
Experience
----------
6388.7594
6238.7594
6017.7594
5737.7594
5414.7594
5771.7594
5278.7594
4967.7594
4937.7594
4907.7594
4879.7594
Experience
----------
6016.7594
5639.7594
5697.7594
5302.7594
5273.7594
4879.7594
6320.7594
6008.7594
5638.7594
5608.7594
5212.7594
Experience
----------
4987.7594
5697.7594
5667.7594
5184.7594
4957.7594
6425.7594
6035.7594
5546.7594
5318.7594
5921.7594
5861.7594
Experience
----------
5529.7594
5003.7594
6417.7594
6024.7594
5539.7594
5280.7594
5607.7594
5578.7594
107 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select months_between(sysdate,hire_date) "Experience"
2 from employees
3* where employee_id<110
SQL> /
Experience
----------
314.443892
287.31486
247.572924
283.895505
267.31486
194.185827
186.830989
174.766473
228.443892
228.47615
10 rows selected.
SQL> select add_months(sysdate, 6), sysdate
2 from dual;
ADD_MONTHS(SYSDATE SYSDATE
------------------ ------------------
28-FEB-14 18:16:01 30-AUG-13 18:16:01
SQL> select next_day(sysdate,'SATURDAY')
2 from dual;
NEXT_DAY(SYSDATE,'
------------------
31-AUG-13 18:16:42
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
------------------
31-AUG-13 18:17:23
SQL> select round(to_date('22-JUL-12','DD-MON-RR'),'MONTH')
2 FROM DUAL;
ROUND(TO_DATE('22-
------------------
01-AUG-12 00:00:00
SQL> ed
Wrote file afiedt.buf
1 select round(to_date('22-JUL-12','DD-MON-RR'),'YEAR')
2* FROM DUAL
SQL> /
ROUND(TO_DATE('22-
------------------
01-JAN-13 00:00:00
SQL> ed
Wrote file afiedt.buf
1 select trunc(to_date('22-JUL-12','DD-MON-RR'),'YEAR')
2* FROM DUAL
SQL> /
TRUNC(TO_DATE('22-
------------------
01-JAN-12 00:00:00
SQL> ed
Wrote file afiedt.buf
1 select trunc(to_date('22-JUL-12','DD-MON-RR'),'MONTH')
2* FROM DUAL
SQL> /
TRUNC(TO_DATE('22-
------------------
01-JUL-12 00:00:00
SQL> select to_char(sysdate,'DD-MONTH-YYYY')
2 FROM DUAL;
TO_CHAR(SYSDATE,'
-----------------
30-AUGUST -2013
SQL> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'DDSP-MONTH-YYYY')
2* FROM DUAL
SQL> /
TO_CHAR(SYSDATE,'DDSP-MONTH
---------------------------
THIRTY-AUGUST -2013
SQL> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'DDSPTH-MONTH-YYYY')
2* FROM DUAL
SQL> /
TO_CHAR(SYSDATE,'DDSPTH-MONTH
-----------------------------
THIRTIETH-AUGUST -2013
SQL> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'DDSPTH-MONTH-YEAR')
2* FROM DUAL
SQL> /
TO_CHAR(SYSDATE,'DDSPTH-MONTH-YEAR')
-------------------------------------------------------------------
THIRTIETH-AUGUST -TWENTY THIRTEEN
SQL> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'DDSPTH-MONTH-YYYYSP')
2* FROM DUAL
SQL> /
TO_CHAR(SYSDATE,'DDSPTH-MONTH-YYYYSP')
-------------------------------------------------------------------
THIRTIETH-AUGUST -TWO THOUSAND THIRTEEN
SQL> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'DDSPTH-MONTH-YYYYSP DAY')
2* FROM DUAL
SQL> /
TO_CHAR(SYSDATE,'DDSPTH-MONTH-YYYYSPDAY')
-----------------------------------------------------------------------------
THIRTIETH-AUGUST -TWO THOUSAND THIRTEEN FRIDAY
SQL> select to_char(sysdate,'J')
2 FROM DUAL;
TO_CHAR
-------
2456535
SQL> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'J')/365.25
2* FROM DUAL
SQL> /
TO_CHAR(SYSDATE,'J')/365.25
---------------------------
6725.62628
SQL> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'SSSSS')
2* FROM DUAL
SQL> /
TO_CH
-----
66675
SQL> ed
Wrote file afiedt.buf
1 select to_number(sysdate,'SSSSS')
2* FROM DUAL
SQL> /
select to_number(sysdate,'SSSSS')
*
ERROR at line 1:
ORA-01481: invalid number format model
SQL> ed
Wrote file afiedt.buf
1 select to_char(17895,'99G999D99')
2* FROM DUAL
SQL> /
TO_CHAR(17
----------
17,895.00
SQL> ed
Wrote file afiedt.buf
1 select to_char(17895,'99G999D99')
2* FROM DUAL
SQL> select to_char(salary,'L999G999D99')
2 FROM EMPLOYEES
3 WHERE EMPLOYEE_ID<105;
TO_CHAR(SALARY,'L999G
---------------------
$24,000.00
$17,000.00
$17,000.00
$9,000.00
$6,000.00
SQL> select '$12,456.66'-34 from dual;
select '$12,456.66'-34 from dual
*
ERROR at line 1:
ORA-01722: invalid number
SQL> ed
Wrote file afiedt.buf
1* select to_number('$12,456.66','L99G999D99')-34 from dual
SQL> /
TO_NUMBER('$12,456.66','L99G999D99')-34
---------------------------------------
12422.66
SQL> select salary*commission_pct from employees
2 where employee_id<105;
SALARY*COMMISSION_PCT
---------------------
null
null
null
null
null
SQL> ed
Wrote file afiedt.buf
1 select salary*commission_pct, salary, commission_pct from employees
2* where employee_id<105
SQL> /
SALARY*COMMISSION_PCT SALARY COMMISSION_PCT
--------------------- ---------- --------------
null 24000 null
null 17000 null
null 17000 null
null 9000 null
null 6000 null
SQL> ed
Wrote file afiedt.buf
1 select salary*nvl(commission_pct,0), salary, commission_pct from employees
2* where employee_id<105
SQL> /
SALARY*NVL(COMMISSION_PCT,0) SALARY COMMISSION_PCT
---------------------------- ---------- --------------
0 24000 null
0 17000 null
0 17000 null
0 9000 null
0 6000 null
SQL> ed
Wrote file afiedt.buf
1 select salary+salary*nvl(commission_pct,0), salary, commission_pct from employees
2* where employee_id<105
SQL> /
SALARY+SALARY*NVL(COMMISSION_PCT,0) SALARY COMMISSION_PCT
----------------------------------- ---------- --------------
24000 24000 null
17000 17000 null
17000 17000 null
9000 9000 null
6000 6000 null
SQL> ed
Wrote file afiedt.buf
1 select salary+salary*nvl(commission_pct,0), salary, commission_pct from employees
2* where employee_id<110
SQL> /
SALARY+SALARY*NVL(COMMISSION_PCT,0) SALARY COMMISSION_PCT
----------------------------------- ---------- --------------
24000 24000 null
17000 17000 null
17000 17000 null
9000 9000 null
6000 6000 null
4800 4800 null
4800 4800 null
4200 4200 null
12000 12000 null
9000 9000 null
10 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select salary+salary*nvl(commission_pct,0), salary, commission_pct from employees
2* where employee_id<110
SQL> ed
Wrote file afiedt.buf
1 select nvl2(commission_pct, salary+salary*commission_pct, salary), salary, commission_pct from employees
2* where employee_id>195
SQL> /
NVL2(COMMISSION_PCT,SALARY+SALARY*COMMISSION_PCT,SALARY) SALARY
-------------------------------------------------------- ----------
COMMISSION_PCT
--------------
3100 3100
null
3000 3000
null
2600 2600
null
NVL2(COMMISSION_PCT,SALARY+SALARY*COMMISSION_PCT,SALARY) SALARY
-------------------------------------------------------- ----------
COMMISSION_PCT
--------------
2600 2600
null
4400 4400
null
13000 13000
null
NVL2(COMMISSION_PCT,SALARY+SALARY*COMMISSION_PCT,SALARY) SALARY
-------------------------------------------------------- ----------
COMMISSION_PCT
--------------
6000 6000
null
6500 6500
null
10000 10000
null
NVL2(COMMISSION_PCT,SALARY+SALARY*COMMISSION_PCT,SALARY) SALARY
-------------------------------------------------------- ----------
COMMISSION_PCT
--------------
12000 12000
null
8300 8300
null
11 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select nvl2(commission_pct, salary+salary*commission_pct, salary) total, salary, commission_pct from employees
2* where employee_id>195
SQL> /
TOTAL SALARY COMMISSION_PCT
---------- ---------- --------------
3100 3100 null
3000 3000 null
2600 2600 null
2600 2600 null
4400 4400 null
13000 13000 null
6000 6000 null
6500 6500 null
10000 10000 null
12000 12000 null
8300 8300 null
11 rows selected.
SQL> select nullif(length(first_name),length(last_name))
2 from employees;
NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME))
--------------------------------------------
5
6
5
5
7
6
4
9
5
5
5
NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME))
--------------------------------------------
8
6
7
6
7
null
null
5
null
3
5
NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME))
--------------------------------------------
8
6
null
7
null
null
null
5
4
6
4
NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME))
--------------------------------------------
7
2
6
7
7
9
5
8
5
7
null
NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME))
--------------------------------------------
3
9
null
null
null
5
9
7
6
5
7
NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME))
--------------------------------------------
null
5
5
4
5
5
null
5
6
7
5
NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME))
--------------------------------------------
null
5
5
5
null
6
11
2
null
5
5
NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME))
--------------------------------------------
6
null
5
null
6
3
7
4
7
6
4
NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME))
--------------------------------------------
null
7
7
7
6
7
8
7
5
5
6
NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME))
--------------------------------------------
11
5
5
6
null
7
8
5
107 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select nullif(length(first_name),length(last_name)) comparison, length(first_name), length(last_name)
2* from employees
SQL> /
COMPARISON LENGTH(FIRST_NAME) LENGTH(LAST_NAME)
---------- ------------------ -----------------
5 5 4
6 6 4
5 5 8
5 5 6
7 7 4
6 6 5
4 4 5
9 9 5
5 5 4
5 5 9
5 5 6
COMPARISON LENGTH(FIRST_NAME) LENGTH(LAST_NAME)
---------- ------------------ -----------------
8 8 5
6 6 4
7 7 6
6 6 9
7 7 9
null 4 4
null 5 5
5 5 10
null 6 6
3 3 7
5 5 9
COMPARISON LENGTH(FIRST_NAME) LENGTH(LAST_NAME)
---------- ------------------ -----------------
8 8 5
6 6 5
null 5 5
7 7 9
null 7 7
null 6 6
null 3 3
5 5 6
4 4 6
6 6 3
4 4 5
COMPARISON LENGTH(FIRST_NAME) LENGTH(LAST_NAME)
---------- ------------------ -----------------
7 7 5
2 2 3
6 6 5
7 7 5
7 7 5
9 9 5
5 5 9
8 8 6
5 5 4
7 7 9
null 7 7
COMPARISON LENGTH(FIRST_NAME) LENGTH(LAST_NAME)
---------- ------------------ -----------------
3 3 6
9 9 6
null 6 6
null 7 7
null 5 5
5 5 8
9 9 4
7 7 4
6 6 4
5 5 7
7 7 5
COMPARISON LENGTH(FIRST_NAME) LENGTH(LAST_NAME)
---------- ------------------ -----------------
null 6 6
5 5 6
5 5 3
4 4 10
5 5 7
5 5 6
null 6 6
5 5 6
6 6 7
7 7 5
5 5 6
COMPARISON LENGTH(FIRST_NAME) LENGTH(LAST_NAME)
---------- ------------------ -----------------
null 6 6
5 5 6
5 5 11
5 5 7
null 5 5
6 6 8
11 11 5
2 2 5
null 4 4
5 5 8
5 5 9
COMPARISON LENGTH(FIRST_NAME) LENGTH(LAST_NAME)
---------- ------------------ -----------------
6 6 5
null 7 7
5 5 10
null 4 4
6 6 4
3 3 8
7 7 6
4 4 7
7 7 8
6 6 7
4 4 3
COMPARISON LENGTH(FIRST_NAME) LENGTH(LAST_NAME)
---------- ------------------ -----------------
null 6 6
7 7 5
7 7 5
7 7 6
6 6 8
7 7 5
8 8 6
7 7 6
5 5 6
5 5 6
6 6 7
COMPARISON LENGTH(FIRST_NAME) LENGTH(LAST_NAME)
---------- ------------------ -----------------
11 11 5
5 5 6
5 5 7
6 6 7
null 5 5
7 7 5
8 8 6
5 5 7
107 rows selected.
SQL> select coalesce(manager_id, commission_pct, -1) from employees
2 where employee_id between 130 and 150;
COALESCE(MANAGER_ID,COMMISSION_PCT,-1)
--------------------------------------
121
121
121
122
122
122
122
123
123
123
123
COALESCE(MANAGER_ID,COMMISSION_PCT,-1)
--------------------------------------
124
124
124
124
100
100
100
100
100
145
21 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select coalesce(manager_id, commission_pct, -1) from employees
2* where employee_id between 160 and 180
SQL> /
COALESCE(MANAGER_ID,COMMISSION_PCT,-1)
--------------------------------------
146
146
147
147
147
147
147
147
148
148
148
COALESCE(MANAGER_ID,COMMISSION_PCT,-1)
--------------------------------------
148
148
148
149
149
149
149
149
149
120
21 rows selected.
SQL> select last_name, case job_id when 'SA_REP' then salary*1.10
2 when 'IT_PROG' then salary*1.15
3 else salary*1.20 end Increment
4 from employees
5 where employee_id<110;
else salary*1.20 end Increment
*
ERROR at line 3:
ORA-00923: FROM keyword not found where expected
SQL> ed
Wrote file afiedt.buf
1 select job_id, salary ,
2 case job_id
3 when 'SA_REP' then salary*1.10
4 when 'IT_PROG' then salary*1.15
5 else salary*1.20 end "Increment"
6 from employees
7* where employee_id<110
SQL> /
JOB_ID SALARY Increment
---------- ---------- ----------
AD_PRES 24000 28800
AD_VP 17000 20400
AD_VP 17000 20400
IT_PROG 9000 10350
IT_PROG 6000 6900
IT_PROG 4800 5520
IT_PROG 4800 5520
IT_PROG 4200 4830
FI_MGR 12000 14400
FI_ACCOUNT 9000 10800
10 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select job_id, salary ,
2 decode(job_id, 'SA_REP',salary*1.10,'IT_PROG',salary*1.15,salary*1.20) "Increment"
3 from employees
4* where employee_id<110
SQL> /
JOB_ID SALARY Increment
---------- ---------- ----------
AD_PRES 24000 28800
AD_VP 17000 20400
AD_VP 17000 20400
IT_PROG 9000 10350
IT_PROG 6000 6900
IT_PROG 4800 5520
IT_PROG 4800 5520
IT_PROG 4200 4830
FI_MGR 12000 14400
FI_ACCOUNT 9000 10800
10 rows selected.
SQL> spool off
