흐름제어 (decode, simpled case, searched case)
내년 급여인상 IT_PROG *1.2, ST_CLERK *1.1, AD_VP* 1.15, etc *1.05 뽑아보기
SELECT last_name, job_id, salary AS 올해급여,
DECODE(job_id, 'IT_PROG', salary * 1.2,
'ST_CLERK', salary * 1.1,
'AD_VP', salary * 1.15,
salary * 1.05) as 내년급여
FROM employees;
SELECT last_name, job_id, salary as 올해급여,
CASE job_id WHEN 'IT_PROG' THEN salary * 1.2
WHEN 'ST_CLERK' THEN salary * 1.1
WHEN 'AD_VP' THEN salary * 1.15
ELSE salary * 1.05
END as 내년급여
FROM employees;
SELECT last_name, job_id, salary AS 올해급여,
CASE WHEN job_id ='IT_PROG' THEN salary * 1.2
WHEN job_id ='ST_CLERK' THEN salary * 1.1
WHEN job_id ='AD_VP' THEN salary * 1.15
ELSE salary * 1.05
END AS 내년급여
FROM employees;
LAST_NAME JOB_ID 올해급여 내년급여 --------- ---------- ----- ----- King AD_PRES 24000 25200 Kochhar AD_VP 17000 19550 De_Haan AD_VP 17000 19550 Hunold IT_PROG 9000 10800 Ernst IT_PROG 6000 7200 Lorentz IT_PROG 4200 5040 Mourgos ST_MAN 5800 6090 Rajs ST_CLERK 3500 3850 Davies ST_CLERK 3100 3410 Matos ST_CLERK 2600 2860 Vargas ST_CLERK 2500 2750 Zlotkey SA_MAN 10500 11025 Abel SA_REP 11000 11550 Taylor SA_REP 8600 9030 Grant SA_REP 7000 7350 Whalen AD_ASST 4400 4620 Hartstein MK_MAN 13000 13650 Fay MK_REP 6000 6300 Higgins AC_MGR 12000 12600 Gietz AC_ACCOUNT 8300 8715
문제1. 사원번호 짝수면 백군, 홀수면 청군(last_name (백군))
SELECT last_name, employee_id, MOD(employee_id,2),
DECODE(MOD(employee_id,2), 0, last_name||'(백군)',
last_name||'(청군)') AS ans
FROM employees;
SELECT last_name, employee_id, MOD(employee_id,2),
CASE MOD(employee_id,2) WHEN 0 THEN last_name||'(백군)'
ELSE last_name||'(청군)'
END AS ans
FROM employees;
SELECT last_name, employee_id, MOD(employee_id,2),
CASE WHEN MOD(employee_id,2) = 0 THEN last_name||'(백군)'
ELSE last_name||'(청군)'
END AS ans
FROM employees;
EMPLOYEE_ID EMPLOYEE_ID MOD(EMPLOYEE_ID,2) ANS
----------- ----------- ------------------ -------------
100 100 0 King(백군)
101 101 1 Kochhar(청군)
102 102 0 De_Haan(백군)
103 103 1 Hunold(청군)
104 104 0 Ernst(백군)
107 107 1 Lorentz(청군)
124 124 0 Mourgos(백군)
141 141 1 Rajs(청군)
142 142 0 Davies(백군)
143 143 1 Matos(청군)
144 144 0 Vargas(백군)
149 149 1 Zlotkey(청군)
174 174 0 Abel(백군)
176 176 0 Taylor(백군)
178 178 0 Grant(백군)
200 200 0 Whalen(백군)
201 201 1 Hartstein(청군)
202 202 0 Fay(백군)
205 205 1 Higgins(청군)
206 206 0 Gietz(백군)
문제2 salary 등급(low:0~10000, Mid:10001~20000, High:20001~)
SELECT last_name, salary, CEIL(salary/10000),
DECODE(CEIL(salary/10000), 1, 'Low',
2, 'Mid',
'High') AS sal_grade
FROM employees;
LAST_NAME SALARY CEIL(SALARY/10000) SAL_GRADE --------- ------ ------------------ --------- King 24000 3 High Kochhar 17000 2 Mid De_Haan 17000 2 Mid Hunold 9000 1 Low Ernst 6000 1 Low Lorentz 4200 1 Low Mourgos 5800 1 Low Rajs 3500 1 Low Davies 3100 1 Low Matos 2600 1 Low Vargas 2500 1 Low Zlotkey 10500 2 Mid Abel 11000 2 Mid Taylor 8600 1 Low Grant 7000 1 Low Whalen 4400 1 Low Hartstein 13000 2 Mid Fay 6000 1 Low Higgins 12000 2 Mid Gietz 8300 1 Low
SELECT last_name, salary, TRUNC((salary-1)/10000),
DECODE(TRUNC((salary-1)/10000), 0, 'Low',
1, 'Mid',
'High') AS sal_grade
FROM employees;
LAST_NAME SALARY TRUNC((SALARY-1)/10000) SAL_GRADE --------- ------ ----------------------- --------- King 24000 2 High Kochhar 17000 1 Mid De_Haan 17000 1 Mid Hunold 9000 0 Low Ernst 6000 0 Low Lorentz 4200 0 Low Mourgos 5800 0 Low Rajs 3500 0 Low Davies 3100 0 Low Matos 2600 0 Low Vargas 2500 0 Low Zlotkey 10500 1 Mid Abel 11000 1 Mid Taylor 8600 0 Low Grant 7000 0 Low Whalen 4400 0 Low Hartstein 13000 1 Mid Fay 6000 0 Low Higgins 12000 1 Mid Gietz 8300 0 Low
cf.decode안에 decode
SELECT last_name, salary, CEIL(salary/10000),
DECODE(CEIL(salary/10000), 1, DECODE(salary, 2500, 'T^T', 'Low'),
2, 'Mid',
'High') AS sal_grade
FROM employees;
LAST_NAME SALARY CEIL(SALARY/10000) SAL_GRADE --------- ------ ------------------ --------- King 24000 3 High Kochhar 17000 2 Mid De_Haan 17000 2 Mid Hunold 9000 1 Low Ernst 6000 1 Low Lorentz 4200 1 Low Mourgos 5800 1 Low Rajs 3500 1 Low Davies 3100 1 Low Matos 2600 1 Low Vargas 2500 1 T^T Zlotkey 10500 2 Mid Abel 11000 2 Mid Taylor 8600 1 Low Grant 7000 1 Low Whalen 4400 1 Low Hartstein 13000 2 Mid Fay 6000 1 Low Higgins 12000 2 Mid Gietz 8300 1 Low
SELECT last_name, salary,
CASE CEIL(salary/10000) WHEN 1 THEN 'Low'
WHEN 2 THEN 'Mid'
ELSE 'High'
END AS sal_grade
FROM employees;
SELECT last_name, salary,
CASE WHEN 0<= salary AND salary<=10000 THEN 'Low'
WHEN 10000<salary AND salary<=20000 THEN 'Mid'
ELSE 'High'
END AS sal_galde
FROM employees;
LAST_NAME SALARY SAL_GALDE --------- ------ --------- King 24000 High Kochhar 17000 Mid De_Haan 17000 Mid Hunold 9000 Low Ernst 6000 Low Lorentz 4200 Low Mourgos 5800 Low Rajs 3500 Low Davies 3100 Low Matos 2600 Low Vargas 2500 Low Zlotkey 10500 Mid Abel 11000 Mid Taylor 8600 Low Grant 7000 Low Whalen 4400 Low Hartstein 13000 Mid Fay 6000 Low Higgins 12000 Mid Gietz 8300 Low
.
.
.
.
.