[SQL]데이터 유형 변환 및 IF문 구현(TO_CHAR, TO_DATE, NVL, DECODE, CASE WHEN)

- 이전 과정의 연습문제를 확인하고 싶으면 다음을 참고하시기 바랍니다.

[SQL]초보자를 위한 SQL 200제 문제풀이 모음

 

030 문자형으로 데이터 유형 변환하기 (TO_CHAR)

숫자형 데이터 유형을 문자형으로 변환하거나 날짜형 데이터 유형을 문자형으로 변환할 때 TO_CHAR 함수를 사용합니다. 

숫자형, 날짜형   →   TO_CHAR  → 문자형

날짜를 문자로 변환해서 출력하면 날짜에서 년, 월, 일, 요일 등을 추출해서 출력할 수 있습니다. 날짜를 문자로 출력할 때 사용할 수 있는 날짜 포맷은 이전 글에서 좀더 자세히 확인할 수 있습니다.

[SQL] PART1 - 02 초보자를 위한 SQL 200제 문제풀이 (005-009)

 

예를 들어 KING 사원의 입사일과, 입사 연도, 달, 일, 요일을 출력하는 경우 아래와 같이 작성할 수 있습니다.

SELECT HIREDATE, TO_CHAR(HIREDATE, 'YYYY') AS YEAR, TO_CHAR(HIREDATE, 'MM') AS MONTH,
		TO_CHAR(HIREDATE, 'DD') AS DAY, TO_CHAR(HIREDATE, 'DAY') AS DAY_OF_WEEK
FROM EMP
WHERE ENAME = 'KING';

날짜 컬럼에서 연도/월/일/시간/분/초를 추출하기 위해 EXTRACT 함수를 사용하는 방법도 있습니다.

SELECT ENAME AS 이름, EXTRACT(year from HIREDATE) AS 년도,
			EXTRACT(month from HIREDATE) AS 달,
			EXTRACT(day from HIREDATE) AS 요일
FROM EMP;

숫자형을 출력할 때 표시할 단위를 표시하여 입력합니다. 예를 들어 123456789라는 숫자를 천 단위, 백만 단위를 구분할 수 있는 콤마를 붙여 출력할 경우 다음과 같이 표기합니다.

-- TO_CHAR(숫자형 데이터, 형식)

SELECT TO_CHAR(123456789, '999,999,999')
FROM DUAL;

숫자 9는 자릿수이고 이 자리에 0~9까지 어떠한 숫자가 와도 관계 없음을 의미합니다. 콤마(,)는 천 단위를 나타내는 표시 합니다. 또한 알파벳 L을 사용하면 화폐 단위(\)를 붙여 출력할 수 있습니다.

 

Q. 이름이 SCOTT인 사원의 이름과 입사한 요일을 출력하고, SCOTT의 월급에 천단위를 구분할 수 있는 콤마(,)를 붙여 출력하시오

1. 입사한 요일 출력

입사한 요일은 입사날짜 정보가 담겨있는 HIREDATE로 부터 DAY' 날짜 포맷을 통해 출력할 수 있다.

TO_CHAR(HIREDATE, 'DAY')

 

2. 월급에 단위를 붙여 출력

월급 정보가 담겨있는 SAL 로 부터 천 단위를 구분할 형식 '999,999' 형식을 통해 출력할 수 있다.

TO_CHAR(SAL, '999,999')

 

SELECT ENAME, TO_CHAR(HIREDATE, 'DAY') AS DAY, TO_CHAR(SAL, '999,999') AS SAL
FROM EMP
WHERE ENAME='SCOTT';

 

Q. 1981년도에 입사한 사원의 이름과 입사일을 출력하시오

1. 1981년도에 입사한 사원에 대한 정보를 출력하므로 조건문 WHERE에서 입사 연도가 1981 인 사람만으로 제한한다. (입사 연도 정보는 HIREDATE에 있다.)

* WHERE TO_CHAR(HIREDATE,'YYYY') = '1981';

* 날짜형을 문자열로 변환했기 때문에 숫자형 데이터와 비교하는 것이 아닌 문자형 데이터와 비교한다

SELECT ENAME, HIREDATE
FROM EMP
WHERE TO_CHAR(HIREDATE,'YYYY') = '1981';

 

Q. 월급이 1500 이상인 사원의 이름과, 월급에 천단위를 구분할 수 있는 콤마(,)와 화폐단위를 붙여 출력하시오

1. 출력 조건이 월급(SAL)이 1500 이상인 사람이므로 WHERE 절에는 'SAL >=1500'와 같이 조건을 제시한다.

2. 월급에 천단위를 구분할 수 있는 콤마와 화폐단위를 붙여 출력하므로 숫자형 데이터의 형식과 함께 'L'을 붙여 작성한다.

SELECT ENAME, TO_CHAR(SAL. 'L999,999') AS SAL
FROM EMP
WHERE SAL >= 1500;

 

031 날짜형으로 데이터 유형 변환하기(TO_DATE)

문자를 날짜형으로 변환하여 출력할 때는 TO_DATE 함수를 사용합니다.

※날짜 형식에서 유의해야 할 점

날짜 데이터를 검색할 때는 접속한 세션의 날짜 형식을 확인해야 오류없이 검색할 수 있습니다. 현재 접속한 세션의 날짜 형식을 확인하는 다음과 같습니다. 

-- 날짜 형식 확인하기

SELECT *
FROM NLS_SESSION_PARAMETERS
WHERE PARAMETER = 'NLS_DATE_FORMAT';

만약 날짜 형식을 변경할 경우 다음과 같은 SQL을 작성합니다.

-- 날짜 형식 변경

ALTER SESSION SET NLS_DATE_FORMAT='날짜 형식';

세션이란 지금 사용자 ID로 오라클에 접속한 창을 의미합니다. ALTER SESSION 명령어로 변경한 파라미터 설정은 지급 접속한 세션에서만 유효합니다. 현재 사용하고 있는 ID 창에서 로그아웃하고 다시 새로 접속하게 되면 ALTER SESSION으로 설정한 파라미터 설정 값은 사라지게 됩니다. 그런데 나라마다, 또는 접속하는 세션마다 날짜 형식이 다를 수 있으므로 일관되게 날짜를 검색할 수 있는 방법이 필요합니다. 그래서 TO_DATE를 이용해 연도, 달, 일 등을 명시해 검색하면 접속한 세션의 날짜 형식과 관계없이 검색할 수 있습니다.

 

예를 들어 입사일이 1981년 8월 21일인 사원의 정보를 출력할 경우 다음과 같이 입력할 수 있습니다.

1) 접속한 세션의 날짜 형식이 'YY/MM/DD'인 경우

SELECT *
FROM EMP
WHERE HIREDATE = TO_DATE('81/08/21', 'RR/MM/DD');

2) 접속한 세션의 날짜 형식이 'RR/MM/DD'인 경우

SELECT *
FROM EMP
WHERE HIREDATE = '81/08/21';

EMP 테이블의 HIREDATE 경우 'RR/MM/DD'형식 이기 때문에 TO_DATE 함수를 쓰지 않고 검색할 수 있습니다. 이처럼 테이블의 날짜 형식과 접속한 세션의 날짜 형식을 맞춰 검색해야합니다.

 

Q. 81년 11월 17일에 입사한 사원의 이름과 입사일을 출력하라

1) 접속한 세션의 날짜 형식이 'YY/MM/DD'인 경우

SELECT ENAME, HIREDATE
FROM EMP
WHERE HIREDATE = TO_DATE('81/11/17', 'RR/MM/DD');

 

2) 접속한 세션의 날짜 형식이 'RR/MM/DD'인 경우

SELECT ENAME, HIREDATE
FROM EMP
WHERE HIREDATE = '81/11/17';

 

032 암시적 형 변환하기

1) 숫자형 = 문자형일 경우

SELECT ENAME, SAL
FROM EMP
WHERE SAL = '3000';

SAL 데이터는 숫자형 데이터 컬림인데 문자형은 '3000'과 비교하고 있습니다. 이와 같은 경우 오라클이 자동으로 숫자형 = 숫자형과 같이 암시적으로 형 변환을 하기 때문에 에러가 발생하지 않고 검색됩니다.

검색 결과

2) 문자형 = 숫자형

문자형을 숫자형과 비교하는 경우를 확인하기 위해 SAL을 일부러 문자형으로 만들어 보겠습니다.

CREATE TABLE EMP32
(ENAME VARCHAR2(10),
SAL VARCHAR2(10));

INSERT INTO EMP32 VALUES('SCOTT', '3000');
INSERT INTO EMP32 VALUES('SMITH', '1200');

EMP32 테이블의 SAL 데이터는 문자형으로 만들어 저장했습니다. 따라서 SAL에 대한 조건을 검색할 때는 문자형으로 검색했을때 정상적으로 검색됨을 확인할 수 있습니다.

SELECT ENAME, SAL
FROM EMP32
WHERE SAL = '3000';

여기서 만약 월급(SAL)을 숫자형과 비교하면 검색되는지 확인해 보겠습니다.

SELECT ENAME, SAL
FROM EMP32
WHERE SAL = 3000;

위에서 처럼 검색이 잘 되는 것을 확인할 수 있습니다. 검색이 되는 이유는 오라클이 내부적으로 숫자형=숫자형으로 비교하여 데이터를 검색했기 때문입니다. 오라클은 사용자가 수행한 SQL과는 달리 다음과 같이 쿼리를 변환해서 실행합니다.

SELECT ENAME, SAL
FROM EMP32
WHERE SAL = TO_NUMBER('3000');

 

위와 같이 오라클이 내부적으로 실행하는 SQL을 확인하려면 SET AUTOT ON 이라는 명령어를 통해 SQL을 실행할 때 출혁되는 결과의 SQL을 실행 계획을 한 번에 보여달라는 SQLPLUS 명령어 입니다. 

SQL 워크시트 의 상단 아이콘들중에 3번째 아이콘을 클릭하면 현재 SQL의 Plan(실행계획)을 아래에 보여줍니다. 

filter predicates 라고 쓰여 있으며 그 밑에 TO_NUMBER(SAL)=3000 으로 표기되어있는 것을 확인할 수 있습니다.

 

033 NULL 값 대신 다른 데이터 출력하기(NVL1, NVL2)

NULL 값 대신에 다른 값을 출력하기 위해서는 NVL 함수를 사용합니다. 여기서 치환 값으로는 숫자형만 쓸 수 있습니다. 또한 실제로 데이터가 치환 값으로 변경되는 것은 아니고 출력되는 쿼리에서만 치환 값으로 출력됩니다.

SELECT NVL(NULL값 ,치환 값)
FROM DUAL;

 

Q. 사원의 이름과 커미션(COMM)을 출력하라. 단 커미션이 NULL인 사원들은 0으로 출력하시오

COMM이 NULL일 경우 0으로 치환하여 출력해야하므로 NVL(COMM,0)을 입력햐아한다.

SELECT ENAME, COMM, NVL(COMM,0)
FROM EMP;

 

Q.  직업이 SALESMAN 또는 ANALYST 인 사원의 월급과 커미션, 그리고 월급과 커미션의 합을 출력하시오. 단 커미션이 NULL인 경우 0으로 표시하여 출력하라

먼저 직업이 SALESMAN이거나 ANALYST이야 하므로 조건문 WHERE 절에 JOB = 'SALESMAN' OR JOB='ANALYST' 또는 JOB IN ('SALESMAN', 'ANALYST')를 입력한다. 

 

또한 월급과 커미션의 합을 출력할 때 커미션에는 NULL값이 있으므로 커미션을 0으로 치환 한 값과 월급을 더해야한다 →  SAL+NVL(COMM,0)

SELECT ENAME, SAL, COMM, SAL+NVL(COMM,0)
FROM EMP
WHERE JOB IN ('SALESMAN', 'ANALYST');

※ NVL 함수로 치환하지 않을 경우 월급+커미션은 숫자+NULL 값이 발생해 다른 결과가 발생한다.

-- NVL 함수없이 계산한 결과

SELECT ENAME, SAL, COMM, SAL+COMM
FROM EMP
WHERE JOB IN ('SALESMAN', 'ANALYST');

 

N O T E NVL2

NVL2의 경우 어떤 데이터가 NULL일때와 NULL이 아닐때를 각각 다른 값을 출력할 수 있게 하는 함수입니다.

SELECT NVL2(데이터, NULL이 아닌경우, NULL인 경우)
FROM DUAL;

예를 들어 커미션이 NULL 이 아닌 사원들은 SAL+COMM을, NULL인 사원들은 SAL을 출력하는 경우 다음과 같이 NVL2를 이용하여 출력할 수 있습니다.

SELECT ENAME, SAL, COMM NVL2(COMM, SAL+COMM, SAL)
FROM EMP
WHERE JOB IN ('SALESMAN','ANALYST');

 

034 IF문을 SQL로 구현하기(DECODE)

IF문을 SQL로 구현하기 위해서는 DECODE문을 사용합니다. 

SELECT DECODE(컬럼, 조건1, 조건1결과, 조건2, 조건2결과, 그 외 결과)
FROM TABLE;

제일먼저 조건에 사용할 컬럼을 입력하고, 그 뒤로부터는 조건과 조건을 만족하면 실행 될 결과를 순서대로 나열합니다. 마지막 끝에는 default 값을 입력합니다. 만약 default 값이 없는 경우 생략할 수 있습니다.

예를 들어 직업이 MANAGER인 경우 '매니저' , PRESIDENT 인 경우 '대표'라고 출력하고, 나머지는 '사원'이라고 출력하는 컬럼을 '직책'이라고 표현할 경우 다음과 같이 작성할 수 있습니다.

SELECT ENAME, JOB, DECODE(JOB,'MANAGER','매니저','PRESIDENT','대표','사원') AS 직책
FROM EMP;

 

Q. 사원의 이름과 부서 번호를 출력하시오, 또한 부서 번호가 10번이면 300을, 부서 번호가 20번이면 400을, 나머지 부서 번호는 0을 출력하는 '보너스'를 출력하시오.

부서 번호에 대한 IF문을 작성하므로 IF문으로 표현하면 다음과 같습니다.

IF 문	설명
IF DEPTNO = 10 THEN 300	부서 번호가 10번이면(조건1) 300 출력(결과1)
ELSE IF DEPTNO = 20 THEN 400	그렇지 않고 부서 번호가 20이면(조건2) 400 출력(결과2)
ELSE 0	그렇지 않으면 0 출력(default)

위에 작성된 표현을 바탕으로 다음과 같이 DECODE문을 사용하여 나타낼 수 있습니다.

SELECT ENAME, DEPTNO, DECODE(DEPTNO, 10, 300, 20, 400,0) AS 보너스
FROM EMP;

 

Q. 사원 번호(EMPNO)와 사원 번호가 짝수인지 홀수인지를 출력하시오

사원 번호가 짝수인지 홀수인지 확인하려면 사원 번호를 2로 나누었을 때 나머지를 통해 분류 할 수 있다.

2로 나눈 후 나머지를 출력하는 함수 → MOD(EMPNO,2)

SELECT EMPNO, DECODE(MOD(EMPNO,2),0,'짝수','1','홀수') AS 홀짝
FROM EMP;

 

Q. 사원의 이름, 직업, 보너스를 출력하시오. 단 직업이 SALESMAN이면 보너스 5000을 출력하고, 나머지 직업은 보너스 2000을 출력하시오

SELECT ENAME, JOB, SAL, DECODE(JOB,'SALESMAN',5000,2000) AS 보너스
FROM EMP;

 

035 IF문을 SQL로 구현하기(CASE WHEN)

IF문을 SQL로 구현할 때 위에서 봤던 DECODE 함수 뿐만 아니라 CASE 함수를 사용해 구현할 수 있습니다. CASE 문이 DECODE와 다른 점은 DECODE 문은 등호(=) 만 비교가 가능하지만, CASE 함수는 등호 뿐만 아니라 부등호(>=, <=, >, <)를 사용해 비교가 가능합니다.

SELECT CASE WHEN 조건1 THEN 결과1
	    WHEN 조건2 THEN 결과2
	    ELSE 결과3 END

 

Q. 사원의 이름, 직업, 월급, 보너스를 출력하시오. 단 보너스는 월급이 3000이상이면 500을, 월급이 2000이상 3000미만이면 300을, 월급이 1000이상 2000미만이면 200을, 나머지는 0을 출력하시오.

월급이 3000이상 → 500, 월급이 2000이상 → 300, 월급이 1000이상  → 200, 나머지 → 0 으로 조건과 결과를 구분할 수 있다. 따라서 다음과 같이 SQL을 작성할 수 있다.

SELECT ENAME, JOB, SAL, 
                CASE WHEN SAL >= 3000 THEN 500
                     WHEN SAL >=2000 THEN 300
                     WHEN SAL >= 1000 THEN 200
                     ELSE 0 END AS BONUS
FROM EMP;

 

 

Q. 사원의 이름, 직업, 커미션, 보너스를 출력하시오. 단 보너스는 커미션이 NULL이면 500을, NULL이 아니면 0을 출력합니다.

NULL의 경우 크기 비교가 불가능 하므로 IS NULL 또는 IS NOT NULL 로 나타낸다.

SELECT ENAME, JOB, COMM, CASE WHEN COMM IS NULL THEN 500 
                              ELSE 0 END AS BONUS
FROM EMP;

 

Q. 사원의 이름, 직업, 보너스를 출력하시오. 단 보너스는 직업이 SALESMAN, ANALYST 이면 500을, CLERK, MANAGER이면 400을, 나머지 JOB은 0을 출력하시오.

이 문제의경우 직업이 SALESMAN 또는 ANALYST 경우를 조건으로 두었을때 'CASE WHEN JOB=SALESMAN OR JOB = ANALYST THEN 500'와 같이 논리연산자를 사용하여 나타낼 수도 있지만 IN 연산자를 사용하여 아래와 같이 표현할 수 있다.

SELECT ENAME, JOB,  CASE WHEN JOB = 'SALESMAN' OR JOB =  'ANALYST' THEN 500
                         WHEN JOB = 'CLERK' OR JOB =  'MANAGER' THEN 400 
                         ELSE 0 END AS BONUS
FROM EMP;

SELECT ENAME, JOB,  CASE WHEN JOB IN ('SALESMAN', 'ANALYST') THEN 500
                         WHEN JOB IN ('CLERK', 'MANAGER') THEN 400 
                         ELSE 0 END AS BONUS
FROM EMP;