[오라클] 180123 SQL 쿼리문 NVL , CASE(WHEN , THEN)
HR@orcl> SELECT * FROM employees
2 WHERE salary > '10000' ;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
201 Michael Hartstein MHARTSTE 515.123.5555 17-FEB-96 MK_MAN 13000
100 20
205 Shelley Higgins SHIGGINS 515.123.8080 07-JUN-94 AC_MGR 12000
101 110
100 Steven King SKING 515.123.4567 17-JUN-87 AD_PRES 24000
90
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
101 Neena Kochhar NKOCHHAR 515.123.4568 21-SEP-89 AD_VP 17000
100 90
102 Lex De Haan LDEHAAN 515.123.4569 13-JAN-93 AD_VP 17000
100 90
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-JAN-00 SA_MAN 10500
.2 100 80
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
174 Ellen Abel EABEL 011.44.1644.429267 11-MAY-96 SA_REP 11000
.3 149 80
7 rows selected.
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, last_name, hire_date
2 FROM employees
3* WHERE hire_date > TO_DATE('1999/01/01','yyyy/mm/dd')
HR@orcl> /
EMPLOYEE_ID LAST_NAME HIRE_DATE
----------- ------------------------- ---------
107 Lorentz 07-FEB-99
124 Mourgos 16-NOV-99
149 Zlotkey 29-JAN-00
178 Grant 24-MAY-99
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, last_name, hire_date
2 FROM employees
3* WHERE hire_date > TO_DATE('99/01/01','yy/mm/dd')
HR@orcl> /
no rows selected
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, last_name, hire_date
2 FROM employees
3* WHERE hire_date > TO_DATE('99/01/01','rr/mm/dd')
HR@orcl> /
EMPLOYEE_ID LAST_NAME HIRE_DATE
----------- ------------------------- ---------
107 Lorentz 07-FEB-99
124 Mourgos 16-NOV-99
149 Zlotkey 29-JAN-00
178 Grant 24-MAY-99
HR@orcl> show verify
verify ON
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, salary, commission_pct,
2 salary+salary*commission_pct AS incomm
3* FROM employees
HR@orcl> /
EMPLOYEE_ID SALARY COMMISSION_PCT INCOMM
----------- ---------- -------------- ----------
200 4400
201 13000
202 6000
205 12000
206 8300
100 24000
101 17000
102 17000
103 9000
104 6000
107 4200
EMPLOYEE_ID SALARY COMMISSION_PCT INCOMM
----------- ---------- -------------- ----------
124 5800
141 3500
142 3100
143 2600
144 2500
149 10500 .2 12600
174 11000 .3 14300
176 8600 .2 10320
178 7000 .15 8050
20 rows selected.
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, salary, commission_pct,
2 salary+salary*NVL(commission_pct,0) AS incomm
3* FROM employees
HR@orcl> /
EMPLOYEE_ID SALARY COMMISSION_PCT INCOMM
----------- ---------- -------------- ----------
200 4400 4400
201 13000 13000
202 6000 6000
205 12000 12000
206 8300 8300
100 24000 24000
101 17000 17000
102 17000 17000
103 9000 9000
104 6000 6000
107 4200 4200
EMPLOYEE_ID SALARY COMMISSION_PCT INCOMM
----------- ---------- -------------- ----------
124 5800 5800
141 3500 3500
142 3100 3100
143 2600 2600
144 2500 2500
149 10500 .2 12600
174 11000 .3 14300
176 8600 .2 10320
178 7000 .15 8050
20 rows selected.
HR@orcl> ed
Wrote file afiedt.buf
1* SELECT NULLIF(1,1),NULLIF(3,4) FROM dual
HR@orcl> /
NULLIF(1,1) NULLIF(3,4)
----------- -----------
3
HR@orcl> SELECT employee_id,
2 salary+salary*NVL(commission_pct,0) AS incomm,
3 NVL2(commission_pct,'COMM','NOCOMM') AS bigo
4 FROM employees;
EMPLOYEE_ID INCOMM BIGO
----------- ---------- ------
200 4400 NOCOMM
201 13000 NOCOMM
202 6000 NOCOMM
205 12000 NOCOMM
206 8300 NOCOMM
100 24000 NOCOMM
101 17000 NOCOMM
102 17000 NOCOMM
103 9000 NOCOMM
104 6000 NOCOMM
107 4200 NOCOMM
EMPLOYEE_ID INCOMM BIGO
----------- ---------- ------
124 5800 NOCOMM
141 3500 NOCOMM
142 3100 NOCOMM
143 2600 NOCOMM
144 2500 NOCOMM
149 12600 COMM
174 14300 COMM
176 10320 COMM
178 8050 COMM
20 rows selected.
HR@orcl> SELECT employee_id,
2 NULLIF(salary, salary+salary*NVL(commission_pct,0))
3 FROM employees;
EMPLOYEE_ID NULLIF(SALARY,SALARY+SALARY*NVL(COMMISSION_PCT,0))
----------- --------------------------------------------------
200
201
202
205
206
100
101
102
103
104
107
EMPLOYEE_ID NULLIF(SALARY,SALARY+SALARY*NVL(COMMISSION_PCT,0))
----------- --------------------------------------------------
124
141
142
143
144
149 10500
174 11000
176 8600
178 7000
20 rows selected.
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, manager_id, salary, commission_pct,
2 COALESCE(commission_pct, manager_id, salary) result
3* FROM employees
HR@orcl> /
EMPLOYEE_ID MANAGER_ID SALARY COMMISSION_PCT RESULT
----------- ---------- ---------- -------------- ----------
200 101 4400 101
201 100 13000 100
202 201 6000 201
205 101 12000 101
206 205 8300 205
100 24000 24000
101 100 17000 100
102 100 17000 100
103 102 9000 102
104 103 6000 103
107 103 4200 103
EMPLOYEE_ID MANAGER_ID SALARY COMMISSION_PCT RESULT
----------- ---------- ---------- -------------- ----------
124 100 5800 100
141 124 3500 124
142 124 3100 124
143 124 2600 124
144 124 2500 124
149 100 10500 .2 .2
174 149 11000 .3 .3
176 149 8600 .2 .2
178 149 7000 .15 .15
20 rows selected.
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, last_name, NVL(TO_CHAR(manager_id), 'No Manager')
2* FROM employees
HR@orcl> /
EMPLOYEE_ID LAST_NAME NVL(TO_CHAR(MANAGER_ID),'NOMANAGER')
----------- ------------------------- ----------------------------------------
200 Whalen 101
201 Hartstein 100
202 Fay 201
205 Higgins 101
206 Gietz 205
100 King No Manager
101 Kochhar 100
102 De Haan 100
103 Hunold 102
104 Ernst 103
107 Lorentz 103
EMPLOYEE_ID LAST_NAME NVL(TO_CHAR(MANAGER_ID),'NOMANAGER')
----------- ------------------------- ----------------------------------------
124 Mourgos 100
141 Rajs 124
142 Davies 124
143 Matos 124
144 Vargas 124
149 Zlotkey 100
174 Abel 149
176 Taylor 149
178 Grant 149
20 rows selected.
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, last_name, NVL(TO_CHAR(manager_id), 'No Manager')
2* FROM employees
HR@orcl> SELECT last_name, job_id, salary,
2 CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
3 WHEN 'ST_CLERK' THEN 1.15*salary
4 WHEN 'SA_REP' THEN 1.20*salary
5 else salary END "REVISED_SALARY"
6 FROM employees;
LAST_NAME JOB_ID SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
Whalen AD_ASST 4400 4400
Hartstein MK_MAN 13000 13000
Fay MK_REP 6000 6000
Higgins AC_MGR 12000 12000
Gietz AC_ACCOUNT 8300 8300
King AD_PRES 24000 24000
Kochhar AD_VP 17000 17000
De Haan AD_VP 17000 17000
Hunold IT_PROG 9000 9900
Ernst IT_PROG 6000 6600
Lorentz IT_PROG 4200 4620
LAST_NAME JOB_ID SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
Mourgos ST_MAN 5800 5800
Rajs ST_CLERK 3500 4025
Davies ST_CLERK 3100 3565
Matos ST_CLERK 2600 2990
Vargas ST_CLERK 2500 2875
Zlotkey SA_MAN 10500 10500
Abel SA_REP 11000 13200
Taylor SA_REP 8600 10320
Grant SA_REP 7000 8400
20 rows selected.
HR@orcl> spool off