5. NVL, DECODE, CASE

■ nvl 함수(189페이지)

 

문제94. 이름과 커미션을 출력하는데

커미션이 null 인 사원들은 0으로 출력하시오

select ename, nvl(comm, 0)

from emp;

 

문제95. 이름과 커미션을 출력하는데

커미션이 null 인 사원들은

no comm 이란 글씨로 출력되게 하시오

select ename, nvl(to_char(comm), 'no comm')

from emp;

 

 

**comm 의 null 값 자체가 숫자라서 to_char 해준거고

그래서 no comm 이랑 호환된건데

그럼

null 자체는 숫자로 인식.

 

*왜 where 절이 안먹히지

nvl 은 null화 시키라는 명령어지,[nvl(지정값,'변환값')]

특정 대상 지정이 아니다.

그래서 select 절에 써야 함

 

 

 

■ nvl2 함수

select ename, sal, comm, nvl2(comm, sal+comm, sal)

from emp;

 

nvl2(comm, sal+comm, sal)

comm을 기준으로

comm 이 null 이 아니면 = sal+comm

comm이 null 이면       = sal

 

 

 

 

-DECODE 조건부 함수

 

 

 

■ decode 함수*중요*많이씀*

 " if 문으로 나열해서 프로그래밍 해야

볼 수 있는 결과를 SQL 함수인

decode 하나로 프로그래밍 없이

간단하게 조회할 수 있는 함수"

 

예: 이름, 직업, 보너스를 출력하는데 직업이

SALESMAN 이면 보너스를 1000 을 출력하고

ANLAYST 이면   보너스를 2000을 출력하고

나머지 직업은  보너스를    0을 출력하시오

 

select ename, job,

       decode(job, 'SALESMAN', 1000, 

                                 'ANALYST', 2000, 0) as bonus

from emp;

                                                       

원래는 if 문 써서 프로그래밍 해야함.

근데 decode 써서 시간 단축함, 중요함.

 

 

문제96.  이름, 통신사, 통신요금을 출력하는데

          통신사가 sk 면 50000 이 출력되게 하고

          통신사가 lg 면  60000 이 출력되게 하고

         통신사가 kt 면  55000 이 출력되게 하시오

 

select ename, telecom,

              decode(lower(telecom), 'sk', 50000, 'lg', 60000, 'kt', 55000) 통신요금

from emp2;

 

 

 

문제97. 이름, 입사한 년도(4자리), 보너스를 출력하는데

입사한 년도가 1981년도면 보너스를 9000 으로 출력하고

나머지 년도는 다 0 으로 출력하시오

 

1. select ename, to_char( hiredate, 'rrrr'),

decode(to_char(hiredate, 'rrrr'), 1981, 9000, 0)보너스

from emp;

 

2. select ename, to_char(hiredate,'rrrr'),

decode(hiredate,'1981','9000',0)보너스

from emp;

 

*1은 맞음. 2는 틀림

decode 에서도 똑같은 값에 대한 걸 구하는거니까

당연히 to_char 까지 똑같이 써준다

 

 case when 으로 한 답

select ename, to_char(hiredate, 'rrrr'),

case when to_char(hiredate,'rrrr') = '1981' then 9000

      else 0 end as "보너스"

from emp; 

 

 

 

 

문제98. 이름, 나이, 등급을 출력하는데

나이가 30살 이상이면 A 등급

나이가 28살 이상이면 B 등급

나이가 25살 이상이면 C 등급

나머지 D 등급 으로 출력하시오

 

select ename, age

decode(age, >=30, 'A', >=28,'B', >=25, 'C', 'D')등급

from emp2;

 

안된다. decode 함수는 등호 비교만 가능하다.

 

 

답

select ename, age,

   case when age >=30 then 'A'

              when age >=28 then 'B'

              when age >=25 then 'C'

              else 'D' end as 등급

from emp2;

 

 ****else age 'D' end 라고 쓰면 안되는 이유

>>>>case when 절에 대한 조건에서 남은 else

를 뽑아서 end 해야 하기 때문에

새로운 age 를 쓰면 안된다.

 

 

-CASE 함수

※ case 문은 등호, 부등호 비교 둘다 가능하다

 

 

 

 

 

문제99. 이름, 월급과 보너스를 출력하는데

월급이 3000 이상인 사원들은 보너스를

자기 월급의 10% 로 출력되게 하고

월급이 2000 이상인 사원들은

보너스를 자기 월급의 30%로

출력되게 하고

월급이 2000 보다 작은 사원들은

 그냥 0을 출력하시오

 

select ename, sal,

case when sal >=3000 then  sal*0.1

     when sal>=2000 then sal* 0.3

     else 0 end as 보너스

     from emp;

 

 

문제100. decode 를 중첩해서 아래의 문제를 해결하시오

 

이름과 부서번호, 직업, 보너스를 출력하는데

부서번호가 20번이면서 직업이 CLERK 인 사원들은

보너스를 9000 으로 출력하고

부서번호가 20번 이면서 직업이 ANALYST 인 사원들은

보너스를 1200을 출력하고

나머지 사원들은 보너스를 0 으로 출력하시오

 

select ename, deptno, job,

decode(deptno, 20, decode( job, CLERK, 9000,

             ANALYST, 1200), 0) 보너스

             FROM EMP;

 

선생님답

select ename, deptno, job,

 decode(deptno, 20, decode(job, 'CLERK', 9000,                                                                                                                                               

                                                         'ANALYST', 1200), 0)보너스

from emp;

 

 

Decode(지정값, 조건, 결과, 조건2, 결과2, …., 그외값)

즉 홀수로 해야 끝나네.

 

임혜진 답

Select ename, deptno, job,

   Decode(decode(deptno, 20, job, 0), 'CLERK', 9000, 'ANALYST' , 1200, 0) 보너스

From emp ;