1.null값 처리함수
1)nvl :null값을 포함하는 컬럼을 특정한 값으로 전환하는 함수
-- 형식 : nvl(null값을 포함하고 있는 컬럼이나 식, 전환하고자 하는 목표값)
(문제) emp 테이블에서 사원의 이름, 직책, 커미션(수당) 을 나타내시오.
단, 커미션이 없는 사원은 3%의 커미션을 적용하시오.
-- nvl(A, B) A가 null 이면 B, null 이 아니면 A
select ename, deptno, sal*nvl(comm, 3)/100 커미션 from emp;
>>출력
| 김철수 20 6 이찬수 30 780 박종수 30 1625 임채호 20 14.91 나대호 30 4550 박지성 30 14.55 구자철 10 13.35 송기성 20 15 김철호 10 24 성명준 30 0 황인태 20 6.3 박민성 30 8.85 박진성 20 18 최철호 10 6.9 |
-- nvl2(A, B, C) 가 null 이 아니면 B, null 이면 C
2)nvl2 : null 값인 경우와 아닌 경우 모두 특정 값으로 변환하는 함수
-- 형식 : nvl2(null값을 포함하고 있는 컬럼이나 식, null 이 아닌 경우의 값, null인 경우의 값)
(문제) emp 테이블에서 커미션이 원래 있는 직원은 5%를 적용하고 없던 직원은 3%를 적용하여
특별보너스를 계산하시오
select ename, deptno, sal*nvl2(comm, 5, 3)/100 커미션 from emp;
=>출력
|
김철수 20 6 |
3) nullif : 두 값을 비교하여 같으면 null, 다르면 첫번째 값을 반환하는 함수
-- 형식 : nullif(비교값1, 비교값2)
(문제) emp 테이블에서 이름이 A로 시작하는 직원의 이름과 부서코드를 검색하시오.
select ename, deptno, LTRIM(ename, '김'), nullif(ename, LTRIM(ename, '김'))
from emp where nullif(ename, LTRIM(ename, '김')) is not null;
-- =
select * from emp where ename like '김%' ;
|
김철수 20 철수 김철수 |
4) coalesce(코얼레스, 더 큰 덩어리로 합치다) : 여러 개의 list중 null이 아닌
첫번째 값을 돌려주는 함수
-- 형식 : coalesce(값1, 값2, 값3, ....)
(문제) emp 테이블에서 커미션이 있으면 커미션을 , 만약 커미션이 없으면
해당 급여를 , 커미션과 급여가 다 없는(null) 인 경우는 임의로 20을 치환하여
사원의 이름 및 해당 치환값을 검색하시오.
select ename, comm, sal, coalesce(comm, sal , 20) 치환값 from emp;
>> 출력
|
ename comm sal 치환값 |
2.비교 처리 함수
1) decode :값을 비교하여 해당하는 값을 돌려주는 함수. 단, 비교시에는 정확히
같은 값(=)만 비교가 가능함
-- 형식 : decode(컬럼이나 값, 비교값1, 결과값1, 비교값2, 결과값2,
...... 기본치)
** 기본치는 컬럼이나 값이 비교값과 맞지 않을 때 갖는 값이며 생략되면
null 값을 가짐
(문제) emp 테이블에서 각 사원의 이름과 급여, 급여등급을 나타내시오.
단, 급여가 400만원 이상이면 A등급, 300만원 이상이면 B등급, 200만원 이상이면
C등급, 100만원 이상이면 D등급, 100만원 미만이면 E등급으로 나타내시오.
-- trunc 소수점 버리기
-- trunc(실수) => 실수값을 정수로 (버림)
-- decode(A, B,C, D, E) A와 B가 같으면 C 다르면 A와 D가 같으면 E
select ename, sal, decode(trunc(sal/1000), 0, 'E', 1, 'D', 2, 'C', 3, 'B', 'A') 급여등급 from emp;
=>
case 로 변경
select ename , sal,
case
when sal >=400 then 'A'
when sal >=300 then 'B'
when sal >=200 then 'C'
when sal >=100 then 'D'
else 'E'
end 급여등급
from emp;
=>출력
| 김철수 200 C 이찬수 260 C 박종수 325 B 임채호 497 A 나대호 325 B 박지성 485 A 구자철 445 A 송기성 500 A 김철호 800 A 성명준 350 B 황인태 210 C 박민성 295 C 박진성 600 A 최철호 230 C |
2) case : decode 함수를 보완하여 대소, like 등의 비교처리 가능한 함수
-- 형식 : case 컬럼이나 값 when 비교값 1 then 치환값1
[when 비교값2 then 치환값2 ....
when 비교값n then 치환값 n
else 기본치 ]
end
(문제) professor 테이블에서 각 교수의 이름과 직위, 급여총액(pay+bonus) 을 나타내시오.
단, 이번달 급여는 정교수이면 급여총액의 10%를 , 조교수이면 7%를, 전임강사이면
5%를 더하여 주고 급여가 많은 사람부터 나타내시오.
-- case when 조건 then 값 ...... else 기본값 end
select name, position,
case
when position ='정교수' then nvl((pay+bonus)*1.1, 0)
when position='조교수' then nvl((pay+bonus)*1.07, 0)
when position='전임강사' then nvl((pay+bonus)*1.05, 0)
else nvl(pay + bonus, 0)
end
"이번달 급여"
from professor order by 3 desc;
=>여기서 3은 3번째 컬럼을 의미한다.
=> 동등만 비교할 때에는 다음과 같이 변경할 수 있다
select name, position,
case position
when '정교수' then nvl((pay+bonus)*1.1, 0)
when '조교수' then nvl((pay+bonus)*1.07, 0)
when '전임강사' then nvl((pay+bonus)*1.05, 0)
else nvl(pay + bonus, 0)
end
"이번달 급여"
from professor order by 3 desc;
=> 출력
| NAME POSITION 이번달 급여 허은 조교수 342.4 최슬기 조교수 0 차범철 전임강사 0 주승재 정교수 638 조인형 정교수 715 전민 전임강사 0 양선희 전임강사 0 심슨 정교수 770 송도권 전임강사 0 박원범 조교수 385.2 박승곤 조교수 470.8 바비 정교수 638 나한열 조교수 406.6 김현정 전임강사 0 김영조 조교수 460.1 김도형 정교수 704 |
3.순위를 구하는 함수
1)rank : order by 를 포함한 query 문에서 특정 컬럼에 대한 순위를 구하는 함수
-- 형식 : rank() over([partition by 컬럼] order by 절)
partition by : 순위를 지정하기 위한 컬럼 그룹을 지정함
(문제) emp 테이블에서 전체 사원에 대하여 부서번호, 이름, 급여, 급여순위를 출력하시오.
select deptno, ename, sal, rank() over(order by sal desc) "급여 순위" from emp;
=>동률 rank 수를 무시하려면 dense_rank()
=> 출력
| deptno ename sal 급여순위 10 김철호 800 1 20 박진성 600 2 20 송기성 500 3 20 임채호 497 4 30 박지성 485 5 10 구자철 445 6 30 성명준 350 7 30 박종수 325 8 30 나대호 325 8 30 박민성 295 10 30 이찬수 260 11 10 최철호 230 12 20 황인태 210 13 20 김철수 200 14 |
(문제) emp 테이블에서 전체 사원에 대하여 부서번호, 이름, 급여, 해당부서 내 급여 순위를 출력하시오.
-- partition by 컬럼( 부서 내에서의 순위)
select deptno, ename, sal, rank() over(partition by deptno order by sal desc)
"부서내 급여 순위" from emp;
=>출력
| 10 김철호 800 1 10 구자철 445 2 10 최철호 230 3 20 박진성 600 1 20 송기성 500 2 20 임채호 497 3 20 황인태 210 4 20 김철수 200 5 30 박지성 485 1 30 성명준 350 2 30 박종수 325 3 30 나대호 325 3 30 박민성 295 5 30 이찬수 260 6 |
댓글 ( 4)
댓글 남기기