SELECT 기초 (2)

CASE와 DECODE 함수

if의 개념과 많이 비슷하다.

SELECT name, rrn,
    CASE SUBSTR(rrn, 8, 1)
    WHEN '0' THEN '남자'
    WHEN '1' THEN '여자'
END "성별"
FROM emp;

name, rrn을 출력할건데 rrn의 문자열 8인덱스 위치부터 1번째 글자가 0이면 남자이고 1이면 여자이다.

그리고 이 항목들을 담을 성별 이라는 행을 만들겠다.

년도 때문에 주민 뒷자리가 3,4인 경우를 생각해서 코딩 안했기 때문에 NULL 떠버린다.

 

 

□ WITH문과 함께 써보자

WITH asd AS (
    SELECT name, sal, bonus, sal+bonus pay FROM emp
    )
    SELECT name,sal,bonus,pay,
    CASE
        WHEN pay >= 2500000 THEN pay*0.03
        WHEN pay >= 2000000 THEN pay*0.02
        ELSE 0
    END "보너스 추가 금액"
FROM asd;

asd라는 이름을 가진 서브 쿼리를 만든다.

 

그리고 asd의 name,sal,bonus,pay를 출력할거다.

 

근데 pay의 값들이 250만 이상이면 pay에 0.03을 곱하고 200만 이상이면 0.02를 곱한다.

 

만약 그 외의 경우에는 그냥 0 이다.

 

그리고 나온 결과 값들을 "보너스 추가 금액" 이라는 이름을 가진 행을 새로 만들어서 열에 넣어준다.

 

asd의 값들을 가져왔으니 FROM asd로 문장을 닫아준다.

 

 

DECODE 함수

검색값과 비교하여 같으면 해당 결과 반환
CASE 구문보다 처리 속도가 느리다

 

사용 예

DECODE(a, 'b', 1, 2)           : a(컬럼 또는 수식)가 'b'이면 1, 그렇지 않으면 2

DECODE(a, 'b', 1)              : a가 'b'이면 1, 그렇지 않으면 null

DECODE(a, 'b', 1, 'c', 2, 3)   : a가 'b'이면 1, a가 'c'이면 2, 그렇지 않으면 3

 

SELECT name, rrn,
DECODE(MOD(SUBSTR(rrn,8,1),2),0, '여자', 1, '남자')
FROM emp;

이런식으로 CASE문 대신 사용이 가능하다.

 

ORDER BY

정렬을 해주는 함수

크기 순으로 나열

ASC = 오름차순(기본값)

DESC = 내림차순

 

SELECT name, dept, sal FROM emp ORDER BY sal;   -- sal 오름차순
SELECT name, dept, sal FROM emp ORDER BY sal ASC;   -- sal 오름차순

특이한 점은 FROM 다음에 온다는 것

 

 

□ 부서(dept) 내림차순으로 출력하고 부서가 같으면 sal 내림차순

SELECT name, dept, sal FROM emp ORDER BY dept DESC, sal DESC;

 

□ 남자 중에서 출신도시(city) 오름차순 하고 출신도시가 같으면 sal 내림차순

SELECT name, rrn, city, dept, sal FROM emp
WHERE MOD(SUBSTR(rrn,8,1),2) = 1
ORDER BY city, sal DESC;

name, rrn, city, dept, sal을 출력할건데

확인을 할거야 남자가 1인지 아닌지를

확인을해서 남자인 경우에 출력을 하는데

도시 순으로 오름차순 정렬을 하고 도시가 겹치면 sal순으로 내림차순을 할거다.

 

DISTINCT : 중복적은 행은 한번만 출력을 한다.

동의어 : UNIQUE 같은 기능을 한다 그러나 보통 DISTINCT 를 많이 쓴다.

 

 

집합 연산자

UNION : 합 집합 (중복적인 행도 모두 출력)

MINUS : 차 집합

INTERSECT : 교집합

 

UNION

SELECT name, city, dept, sal FROM emp WHERE dept = '개발부'
	UNION ALL
SELECT name, dept, city, sal FROM emp WHERE city = '인천';

 

MINUS

SELECT name, city, dept, sal FROM emp WHERE dept = '개발부'
	MINUS
SELECT name, city, dept, sal FROM emp WHERE city = '인천';

 

INTERSECT 

SELECT name, city, dept, sal FROM emp WHERE dept = '개발부'
	INTERSECT 
SELECT name, city, dept, sal FROM emp WHERE city = '인천';

 

ROWNUM

앞에 순번을 표시해 주는 역할

 

SELECT ROWNUM, name, sal  FROM emp;

 

이걸 사용해서 행 수를 제한 할 수있다.

SELECT ROWNUM, name, sal  FROM emp  WHERE ROWNUM <= 10;

 

그러나

양수보다 큰 ROWNUM 값은 항상 거짓

SELECT ROWNUM, name, sal  FROM emp  WHERE ROWNUM > 1;

-- 오류

마찬가지로 

1보다 큰 양수와 ROWNUM 을 = 으로 비교하면 항상 거짓

SELECT ROWNUM, name, sal  FROM emp  WHERE ROWNUM = 10; 
-- 아무것도 출력 되지 않음

 

1과 ROWNUM 을 = 으로 비교하면 행이 존재하는 경우 처음 행 출력

SELECT ROWNUM, name, sal  FROM emp  WHERE ROWNUM = 1;

 

ORDER BY 절이 동일한 조회에서 ROWNUM 뒤에 오는 경우 ORDER BY에 의해 재정렬 됨

ROWNUM 을 사용하는 경우 ORDER BY 사용하지 말것

 SELECT name, sal  FROM emp WHERE ROWNUM < 11 ORDER BY sal;

 

페이징 처리 방법 (암기)

 

10번부터 20번까지 목록 가져오기

(내림차순 정렬)

SELECT * FROM (
    SELECT ROWNUM rnum, tb.* FROM(
        SELECT name, dept, pos ,sal FROM EMP
        ORDER BY sal DESC
    ) tb WHERE ROWNUM <= 20
)   WHERE rnum >= 10