[오라클 SQL과 PL/SQL] decode 퀴즈 1, 2, case문 퀴즈

120 ~ 123p 내용

 

1. decode 퀴즈 1번

 

select name, jumin, decode(substr(jumin,7, 1), 1, 'MAN', 2, 'WOMAN') "Gender"
from student
where deptno1 = 101;

 

 

2. decode 퀴즈 2번

 

select name, tel, decode(substr(TEL, 1, instr(tel, ')')-1), '02', 'SEOUL', '031', 'GYEONGGI', '051', 'BUSAN', '052', 'ULSAN', '055', 'GYEONGNAM') loc
from student
where deptno1 = 101;

 

 

 

 

3. case문 퀴즈

 

select empno, ename, sal,
        case WHEN sal between 1 and 1000 then 'LEVEL1'
             WHEN SAL BETWEEN 1001 AND 2000 THEN 'LEVEL2'
             WHEN SAL BETWEEN 2001 AND 3000 THEN 'LEVEL3'
             WHEN SAL BETWEEN 3001 AND 4000 THEN 'LEVEL4'
             ELSE 'LEVEL5'
        END "LEVEL"
FROM EMP
ORDER BY SAL DESC;