D+26 [06. SQL 함수 예제]

DBMS/Oracle

D+26 [06. SQL 함수 예제]

구일일구 2022. 8. 30. 15:23

DAUL 테이블

>DEPT 테이블로 1일을 초로 환산하는 산술 계산식 : 4개의 로우(행)
SELECT 24*60*60 FROM DEPT;

>DUAL 테이블은 한번만 출력하고 싶을 때 사용하는 테이블
SELECT 24*60*60 FROM DUAL;

>DESC(내림차순)로 DUAL이 가진 데이터 출력 : 한 개의 문자만을 저장할 수 있음
DESC DUAL;

>DUAL이 가진 테이블의 모든 내용 출력
SELECT * FROM DUAL;

단일 행 함수와 그룹 함수 비교

>30번 부서 소속 사원의 급여를 출력 : 단일 행 함수 - 행이 여러개
SELECT DEPTNO, SAL
FROM EMP
WHERE DEPTNO=30;

>30번 부서 소속 사원의 총 급여 출력 : 그룹 함수 - 행이 하나
SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING DEPTNO=30;

문자 함수

LOWER 함수

> 입력한 문자값을 소문자로 변환
SELECT 'DataBase', LOWER('DataBase')
FROM DUAL;

> 사원 테이블에서 부서번호가 10번인 사원명을 모두 소문자로 변환
SELECT ENAME,LOWER(ENAME)
FROM EMP
WHERE DEPTNO=10;

UPPER 함수

> 입력한 문자값을 대문자로 변환
SELECT 'DataBase', UPPER('DataBase')
FROM DUAL;

> 직급이 'MANAGER'인 사원을 검색 - 대문자로 변환하여 검색하기
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE JOB='manager';

SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE LOWER(JOB)='manager';

INITCAP 함수

> 입력한 문자값을 첫 글자만 대문자, 나머지는 소문자로 변환하기
SELECT INITCAP('data BASE PROGRAM') FROM DUAL;

> 사원 테이블의 10번 부서 소속의 사원이름의 이름 첫 글자만 대문자로
SELECT ENAME, INITCAP(ENAME)
FROM EMP
WHERE DEPTNO=10;

#문제#

#문제# 'Smith'란 이름을 갖는 사원의 사번, 이름, 급여, 커미션 출력(INITCAP, UPPER 사용)
SELECT EMPNO, INITCAP(ENAME), SAL, COMM
FROM EMP
WHERE ENAME = UPPER('Smith');

CONCAT 함수

> 두 문자를 연결하기
SELECT CONCAT ('Data', 'base')
FROM DUAL;

LENGTH/LENGTHB 함수

> 글자 개수 구하기
SELECT LENGTH('DataBase'), LENGTH('데이터베이스')
FROM DUAL;

>메모리에 차지하는 바이트 수 구하기 : 한글 1자에 3바이트씩
SELECT LENGTHB('DataBase'), LENGTHB('데이터베이스')
FROM DUAL;

>10번 부서 소속 사원들의 이름 길이 출력
SELECT LENGTH(ENAME)
FROM EMP
WHERE DEPTNO='10';

>직원 중 이름이 4글자인 직원의 이름을 소문자로 출력
SELECT LOWER(ENAME)
FROM EMP
WHERE LENGTH(ENAME)=4;

#문제#

#문제# 이름이 6글자 이상인 사원의 사번, 이름, 급여 출력
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE LENGTH(ENAME)>=6;

SUBSTR/SUBSTRB 함수

SUB(대상, 시작위치, 추출할개수)

> 시작위치 : 양수
SELECT SUBSTR('DataBase', 1, 3)
FROM DUAL;

> 시작위치 : 음수
> 시작위치의 인자 값이 음수이면 뒤에서부터 계산한다 
> 맨 마지막이 -1, 그 앞부터 -2, -3, -4, ...
SELECT SUBSTR('DataBase', -4, 3)
FROM DUAL;

>20번 부서 사원들 중의 입사 년도 알아내기
SELECT ENAME, HIREDATE, SUBSTR(HIREDATE, 1, 2)
FROM EMP
WHERE DEPTNO=20;

>20번 부서 사원들 중의 입사 년도가 81년도인 사원 알아내기
SELECT ENAME, HIREDATE, SUBSTR(HIREDATE, 1, 2)
FROM EMP
WHERE DEPTNO=20 AND SUBSTR(HIREDATE, 1, 2)=81;

> B가 붙으면 바이트 수를 기준으로 문자열 일부만 추출
> '데이터베이스'에서 4바이트 수부터 6바이트만큼의 문자를 추출
SELECT SUBSTRB('데이터베이스', 4, 6)
FROM DUAL;

#문제#

#문제# 82년도에 입사한 사원 알아내기
SELECT ENAME, JOB, HIREDATE, SUBSTR(HIREDATE, 1, 2) 
FROM EMP
WHERE SUBSTR(HIREDATE, 1, 2)=82;

#문제# 이름이 K로 끝나는 직원을 검색 : LIKE 연산자와 와일드카드 사용
SELECT ENAME, JOB, SAL
FROM EMP
WHERE ENAME LIKE '%K';

#문제# 이름이 K로 끝나는 직원을 검색 : SUBSTR 함수 사용
SELECT ENAME, JOB, SAL
FROM EMP
WHERE SUBSTR(ENAME,-1, 1)='K';

INSTR/INSTRB 함수

대상 문자열이나 칼럼에서 특정 문자가 나타나는 위치를 알려줌
INSTR(대상, 찾을 글자, 시작위치, 몇번째발견)

> 'DataBase'에서 B의 위치
SELECT INSTR('DataBase', 'B') FROM DUAL;

>30번 부서 소속 사원 이름에 E자가 어디에 위치하는지 알려주는 쿼리문
SELECT DEPTNO, ENAME, INSTR(ENAME, 'E')
FROM EMP
WHERE DEPTNO=30;

> B가 붙으면 바이트 수를 기준으로 문자의 위치를 찾는다.
> '데이터베이스'에서 '스'문자를 4바이트 위치부터 검색
SELECT INSTRB('데이터베이스', '스', 4, 1)
FROM DUAL;

#문제#

#문제# 이름의 세 번째 자리가 R로 끝나는 직원 검색 :LIKE 연산자 사용
SELECT EMPNO, ENAME
FROM EMP
WHERE ENAME LIKE '__R%';

#문제# 이름의 세 번째 자리가 R로 끝나는 직원 검색 :SUBSTR 함수 사용
SELECT EMPNO, ENAME
FROM EMP
WHERE SUBSTR(ENAME, 3, 1)='R';

#문제# 이름의 두 번째 자리에 A가 있는 사원의 사번, 이름, 직급 출력
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE INSTR(ENAME, 'A')=2;

LPAD/RPAD 함수

>LPAD : 문자열을 오른쪽 끝으로 보내고, 남은 자리는 특정 기호로 채움 
SELECT LPAD('DataBase', 20, '$')
FROM DUAL;

>RPAD : 문자열을 왼쪽 끝으로 보내고, 남은 자리는 특정 기호로 채움
SELECT RPAD('DataBase', 20, '$')
FROM DUAL;

TRIM 함수

특정 문자를 잘라낼 때 사용
특정 문자가 앞부분이거나 뒷부분일 때 잘라내고 남은 문자열을 반환
TRIM([LEADING, TRAILING, BOTH] [trim_character] [FROM] trim_source)
-> LEADING은 왼쪽 공백제거, TRAILING은 오른쪽 공백 제거, BOTH는 둘다 제거

> 문자열에서 'a' 앞뒤를 잘라낸다
SELECT TRIM('a' FROM 'aaaaDataBase programmingaaaa')
FROM DUAL;

> 이름이 SMITH인 사원의 이름과 'S'를 잘라낸 이름 'H'를 잘라낸 이름을 출력
SELECT ENAME, TRIM('S' FROM ENAME), TRIM('H' FROM ENAME)
FROM EMP
WHERE ENAME='SMITH';

> LT = ' ABCD '에서 왼쪽 공백을 제거 
  RT = ' ABCD '에서 오른쪽 공백 제거
  BOTH1 = ' ABCD '에서 양쪽 공백 제거 TRIM(BOTH 문자열) 사용하여
  BOTH2 = ' ABCD '에서 양쪽 공백 제거

>각각의 문자열과 결과값의 길이를 출력
SELECT 	TRIM(LEADING FROM ' ABCD ') AS LT,
        LENGTH(TRIM(LEADING FROM ' ABCD ')) AS LT_LEN,
        TRIM(TRAILING FROM ' ABCD ') AS RT,
        LENGTH(TRIM(TRAILING FROM ' ABCD ')) AS RT_LEN,
        TRIM(BOTH FROM ' ABCD ') AS BOTH1,
        LENGTH(TRIM(BOTH FROM ' ABCD ')) AS BOTH1_LEN,
        TRIM(' ABCD ') AS BOTH2,
        LENGTH(TRIM(' ABCD ')) AS BOTH2_LEN
FROM DUAL;

숫자 함수

ABS 함수 / FLOOR 함수

>ABS 함수는 절대값을 반환
SELECT ABS(-5) FROM DUAL;

>FLOOR 함수는 소수점 아래 버림
SELECT FLOOR(34.513) FROM DUAL;

ROUND 함수

>특정 자릿수에서 반올림
SELECT ROUND(12.345, 2),
       ROUND(12.345, 0),
       ROUND(12.345),
       ROUND(12.345, -1) --1단위에서 반올림
FROM DUAL;

TRUNC 함수

>지정한 자릿수 이하를 버린 결과를 구해줌
SELECT TRUNC(12.345, 2),
       TRUNC(12.345, 0),
       TRUNC(12.345),
       TRUNC(12.345, -1)
FROM DUAL;

MOD 함수

> 나머지 값을 반환함
SELECT MOD(34, 2),
       MOD(34, 5),
       MOD(34, 7)
FROM DUAL;

#문제#

#문제# 사번이 짝수인 사원들의 사번, 이름, 직급 출력
SELECT EMPNO, ENAME, JOB 
FROM EMP
WHERE MOD(EMPNO, 2)=0;

날짜 함수

SYSDATE 함수

> 현재 날짜를 반환
SELECT SYSDATE FROM DUAL;

> 날짜형 데이터는 더하기나 빼기와 같은 연산을 할 수 있음
SELECT SYSDATE-1 어제,
       SYSDATE 오늘,
       SYSDATE+1 내일
FROM DUAL;

> 칼럼에 별칭 사용하기
SELECT DEPTNO 부서번호, DNAME 부서명, LOC 지역명
FROM DEPT;

MONTHS_BETWEEN 함수

> 날짜와 날짜 사이의 개월 수를 구하는 함수
> 부서번호가 10번인 사원들이 오늘 날짜와 고용 날짜 개월 수 구하기
SELECT SYSDATE, HIREDATE, MONTHS_BETWEEN(SYSDATE, HIREDATE)
FROM EMP
WHERE DEPTNO=10;

ADD_MONTHS 함수

> 특정 개월 수를 더한 날짜를 구하는 함수
> 입사일에서 3개월이 지난 날짜를 구하기
SELECT ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 3)
FROM EMP
WHERE DEPTNO=10;

NEXT_DAY 함수

> 특정 날짜를 기준으로 빨리 다가오는 요일을 날짜로 반환
> 오늘을 기준으로 최초로 다가오는 수요일은 언제인지
SELECT NEXT_DAY(SYSDATE, '수요일')
FROM DUAL;

>'수요일' 대신 '수'로 가능
SELECT NEXT_DAY(SYSDATE, '수')
FROM DUAL;

> 일요일은 1, 월요일은 2, ... 토요일은 7인 숫자로 가능
SELECT NEXT_DAY(SYSDATE, 4)
FROM DUAL;

> 요일이 영어일 경우, 언어를 AMERICAN으로 지정해야 함
ALTER SESSION SET NLS_LANGUAGE=AMERICAN;
SELECT NEXT_DAY(SYSDATE,'WEDNESDAY')
FROM DUAL;

> 요일이 한글일 경우, 언어를 KOREAN으로 지정해야 함
ALTER SESSION SET NLS_LANGUAGE=KOREAN;
SELECT NEXT_DAY(SYSDATE,'수요일')
FROM DUAL;

LAST_DAY 함수

> 해당 달의 마지막 날짜를 반환
SELECT SYSDATE, LAST_DAY(SYSDATE)
FROM DUAL;

ROUND 함수

> 함수에 포맷 모델을 지정하면 숫자 이외의 날짜에 대해서도 반올림 할 수 있음
> 입사일을 달 기준으로 반올림하기
SELECT HIREDATE, ROUND(HIREDATE, 'MONTH')
FROM EMP;

TRUNC 함수

> 날짜를 잘라내기(버림)
> 입사일을 달 기준으로 버리기
SELECT HIREDATE, TRUNC(HIREDATE, 'MONTH')
FROM EMP;

변환 함수

TO_CHAR 함수

6. 변환 함수 (1) TO_CHAR 함수 : 날짜를 문자로
> 날짜 형태의 데이터를 문자형으로 변환하는 함수
SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY-MM-DD')
FROM DUAL;

> 사원들의 입사일을 출력하되 요일까지 함께 출력하기
SELECT TO_CHAR(HIREDATE,'YY-MM-DD DAY')
FROM EMP;

> 요일을 약어로 표시하기
SELECT TO_CHAR(HIREDATE,'YY-MM-DD DY')
FROM EMP;

> 시간 표시
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD, HH24:MI:SS')
FROM DUAL;


6. 변환 함수 (2) TO_CHAR 함수 : 숫자를 문자로
>숫자형을 문자형으로 변환하는 함수
SELECT ENAME, SAL, TO_CHAR(SAL, 'L999,999')
FROM EMP;

SELECT ENAME, SAL, TO_CHAR(SAL, '999,999.999')
FROM EMP;

TO_DATE 함수

>문자형을 날짜형으로 변환
>1981년 2월 20일에 입사한 사원 검색
SELECT ENAME, HIREDATE
FROM EMP
WHERE HIREDATE=TO_DATE(19810220,'YYYYMMDD');

>올해 며칠이 지났는지 날짜 계산(잘못된 예)
SELECT SYSDATE-'2022/01/01'
FROM DUAL;

>올해 며칠이 지났는지 날짜 계산(옳은 예)
SELECT TRUNC(SYSDATE-TO_DATE('2022/01/01','YYYY/MM/DD'))
FROM DUAL;

TO_NUMBER 함수

>문자형을 숫자형으로 변환
>10,000 + 20,000 계산 (잘못된 예)
SELECT '10,000'+ '20,000'
FROM DUAL;

>10,000 + 20,000 계산 (옳은 예)
SELECT TO_NUMBER('10,000', '999,999') + TO_NUMBER('20,000', '999,999')
FROM DUAL;

일반 함수

NVL 함수

> 첫번째 인자값이 null이면 두번째 인자값으로 변경

> 커미션이 null 값을 가짐
SELECT ENAME,SAL,COMM,JOB
FROM EMP
ORDER BY JOB;
> null 값을 0으로 변경하기
SELECT ENAME,SAL,NVL(COMM, 0),JOB
FROM EMP
ORDER BY JOB;

> 연봉 계산하기위해 급여에 12 곱하고 커미션 더하기
> COMM이 null값을 가지기 때문에, 연봉이 제대로 안나옴
SELECT ENAME,SAL,COMM,SAL*12+COMM
FROM EMP
ORDER BY JOB;
>연봉을 구하기 위해서 커미션을 0으로 만들어주기
SELECT ENAME,SAL,COMM,SAL*12+NVL(COMM, 0)
FROM EMP
ORDER BY JOB;

#문제#

#문제# 상관이 없는 사원만 출력하되 MGR 칼럼 값 Null 대신 CEO로 출력하기
SELECT EMPNO, ENAME, JOB, NVL(TO_CHAR(MGR),'CEO')
FROM EMP
WHERE MGR IS NULL;

DECODE 함수

> SWITCH CASE 문과 같이 여러 가지 경우에 대해 선택할 수 있는 함수
> 부서번호에 따라서 부서이름 붙여주기
SELECT DEPTNO, DECODE(DEPTNO, 10, 'ACCOUNTING',
                              20, 'RESEARCH',
                              30, 'SALES',
                              40, 'OPERATIONS') AS DNAME
FROM EMP;

CASE 함수

> 조건에 따라 서로 다른 처리가 가능함
> 부서번호에 따라서 부서이름 붙여주기
SELECT ENAME,DEPTNO,
    CASE WHEN DEPTNO=10 THEN 'ACCOUNTING'
         WHEN DEPTNO=20 THEN 'RESEARCH'
         WHEN DEPTNO=30 THEN 'SALES'
         WHEN DEPTNO=40 THEN 'OPERATIONS'
    END DNAME
FROM EMP;

#문제#

#문제# 직급에 따라서 급여 인상하기 (사원번호, 사원명, 직급, 급여)
직급이 'ANALYST'-5%, 'SALESMAN'-10%, 'MANAGER'-15%, 'CLERK'-20%
SELECT EMPNO, ENAME, JOB, SAL,
    CASE WHEN JOB='ANALYST' THEN SAL*1.05
         WHEN JOB='SALESMAN' THEN SAL*1.1
         WHEN JOB='MANAGER' THEN SAL*1.5
         WHEN JOB='CLERK' THEN SAL*1.2
    END SALUP
FROM EMP;

저작자표시 비영리 변경금지