SQL 함수 실습 코드

참고도서: 모두의 SQL(길벗) 4장 http://www.yes24.com/Product/Goods/64434562?OzSrank=1 

모두의 SQL

 

---- 모두의 SQL 4장: 함수로 데이터를 쉽게 가공하기 ----

EMPLOYEES 테이블 사용

--- 1) 문자 타입 함수
-- LOWER, UPPER, INITCAP: 대소문자로 변환
SELECT LAST_NAME, LOWER(LAST_NAME) AS LOWER적용,
       UPPER(LAST_NAME) AS UPPER적용, EMAIL,
       INITCAP(EMAIL) AS INITCAP적용
FROM EMPLOYEES; -- LOWER: 소문자로 / UPPER: 대문자로 / INITCAP: 첫글자만 대문자로

SELECT SALARY, LOWER(SALARY),
       HIRE_DATE, LOWER(HIRE_DATE)
FROM EMPLOYEES; -- 문자형과 날짜형도 사용 가능한 함수

-- SUBSTR() 함수: 지정한 길이만큼 문자열 추출
SELECT JOB_ID, SUBSTR(JOB_ID, 1, 2) 적용결과 FROM EMPLOYEES;
SELECT JOB_ID, SUBSTR(JOB_ID, 4, 2) 적용결과 FROM EMPLOYEES;

-- 자체실습
SELECT HIRE_DATE, SUBSTR(HIRE_DATE,1,2) AS 연, SUBSTR(HIRE_DATE,4,2) AS 월,
       SUBSTR(HIRE_DATE,7,2) AS 일 FROM EMPLOYEES;

-- REPLACE() 함수: 특정 문자를 찾아 바꾸기
SELECT JOB_ID, REPLACE(JOB_ID, 'ACCOUNT', 'ACCNT') 적용결과 FROM EMPLOYEES;

-- LPAD, RPAD: 특정 문자로 자릿수 채우기
SELECT FIRST_NAME, LPAD(FIRST_NAME, 12, '*') LPAD적용결과 FROM EMPLOYEES;

SELECT FIRST_NAME, LPAD(FIRST_NAME, 12, '*'),
       SALARY, LPAD(SALARY, 12, '*') FROM EMPLOYEES;
       
SELECT PHONE_NUMBER, RPAD(SUBSTR(PHONE_NUMBER,1,8),12,'*') AS MASKING
FROM EMPLOYEES;

-- LTRIM, RTRIM: 특정 문자 삭제하기
-- 맨 왼쪽의 F 문자 삭제, 맨 오른쪽의 T 문자 삭제
SELECT JOB_ID, LTRIM(JOB_ID, 'F') LTRIM적용결과,
       RTRIM(JOB_ID, 'T') RTRIM적용결과 FROM EMPLOYEES;

-- TRIM: 공백 제거하기
SELECT * FROM DUAL;  -- 더미 테이블 활용
SELECT 1+1 FROM DUAL;

SELECT 'start' || TRIM('    - space -    ') || 'end' AS 제거된_공백
FROM dual;

--- 2) 숫자 타입 함수
-- ROUND: 숫자 반올림하기
SELECT ROUND(555.555, -3) FROM DUAL;

SELECT SALARY, SALARY/30 일급, ROUND(SALARY/30) 적용결과0, 
       ROUND(SALARY/30, 1) 적용결과1, ROUND(SALARY/30, -1) 적용결과MINUS1
FROM EMPLOYEES;

-- TRUNC: 숫자 절삭하기
SELECT SALARY, SALARY/30 일급, TRUNC(SALARY/30) 적용결과0, 
       TRUNC(SALARY/30, 1) 적용결과1, TRUNC(SALARY/30, -1) 적용결과MINUS1
FROM EMPLOYEES;

-- FLOOR, CEIL: 정수로 내림, 올림
select floor(999.999), ceil(0.00001) from dual;

--- 3) 날짜 타입 함수
SELECT HIRE_DATE, (HIRE_DATE + 25) AS A, (HIRE_DATE - 10) AS B,
       ROUND(((SYSDATE - HIRE_DATE)/7)) AS TWN
FROM EMPLOYEES
WHERE ROUND(((SYSDATE - HIRE_DATE)/7)) = 934;  -- 표현식을 적어야 WHERE절 사용 가능


-- MONTHS_BETWEEN: 두 날짜 사이의 개월 수 계산하기
SELECT SYSDATE, HIRE_DATE, FLOOR(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) 적용결과
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100;

-- ADD_MONTHS: 월에 날짜 더하기
SELECT HIRE_DATE, ADD_MONTHS(HIRE_DATE, 3) 더하기_적용결과,
       ADD_MONTHS(HIRE_DATE, -3) 빼기_적용결과
FROM EMPLOYEES
WHERE EMPLOYEE_ID BETWEEN 100 AND 106;

-- NEXT_DAY: 돌아오는 요일의 날짜 계산하기
-- LAST_DAY: 돌아오는 월의 마지막 날짜 계산하기
SELECT HIRE_DATE, LAST_DAY(HIRE_DATE) AS 적용결과,
       SUBSTR(LAST_DAY(HIRE_DATE), 7, 2) AS 일
FROM EMPLOYEES
WHERE SUBSTR(LAST_DAY(HIRE_DATE), 7, 2) = 30;

-- ROUND, TRUNC: 날짜를 반올림/절삭하기
SELECT HIRE_DATE,
       ROUND(HIRE_DATE, 'MONTH') AS 적용결과_ROUND_M,
       ROUND(HIRE_DATE, 'YEAR') AS 적용결과_ROUND_Y,
       TRUNC(HIRE_DATE, 'MONTH') AS 적용결과_TRUNC_M,
       TRUNC(HIRE_DATE, 'YEAR') AS 적용결과_TRUNC_Y
FROM EMPLOYEES
WHERE EMPLOYEE_ID BETWEEN 100 AND 106;

--- 4) 변환 함수
-- 자동 데이터 타입 변환
SELECT 1 + '02' FROM DUAL;

SELECT * FROM EMPLOYEES WHERE SALARY = '0024000';

-- 수동 데이터 타입 변환 (p.111~116)
SELECT TO_CHAR(SYSDATE, 'DY"요일"') FROM DUAL;  -- DY"요일" == DAY
SELECT TO_CHAR(SYSDATE, 'DAY') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'HH24"시" MI"분" SS"초"') FROM DUAL;

SELECT HIRE_DATE, TO_CHAR(HIRE_DATE, 'day') 
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'day') = '목요일';

-- 교재 p.103: to_char() 함수 미사용으로 인한 생략
SELECT TO_CHAR(SYSDATE, 'YY/MM/DD/HH24:MI') 오늘날짜,
       SYSDATE+1 더하기1, SYSDATE-1 빼기1,
       TO_DATE('20210513')-TO_DATE('20210512') 날짜빼기,
       SYSDATE + 13/24 시간더하기
FROM DUAL;

-- 숫자 값을 지정한 형식으로 변환
-- 지정 형식 9: 9로 출력 자릿수 지정
SELECT TO_CHAR(10/3, '999.999') FROM DUAL;

-- TO_NUMBER: 숫자 타입의 문자열을 숫자 데이터 타입으로 변환
SELECT TO_NUMBER('123') FROM DUAL;

--- 5) 일반 함수
-- NVL: NULL값 처리하기 - NVL(열 이름, 치환 값)
SELECT COMMISSION_PCT, COMMISSION_PCT+10 AS A,
       NVL(COMMISSION_PCT, 0.1) AS B,
       (NVL(COMMISSION_PCT, 0.1)+10) AS C
FROM EMPLOYEES;

SELECT (COMMISSION_PCT)+10 AS "PCT+10" FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;

SELECT SALARY * COMMISSION_PCT
FROM EMPLOYEES
ORDER BY COMMISSION_PCT;

SELECT SALARY * NVL(COMMISSION_PCT, 1)
FROM EMPLOYEES
ORDER BY COMMISSION_PCT;

-- DECODE: 조건 논리 처리하기
-- DECODE(열 이름, 조건 값, 치환 값, 기본값)
-- 예제: department_id가 60인 경우 급여를 10% 인상하고 '10% 인상'을 출력
SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_ID, SALARY 원래급여, 
       DECODE(DEPARTMENT_ID, 60, SALARY*1.1, SALARY) 조정된급여,
        (CASE DEPARTMENT_ID
            WHEN 60 THEN (SALARY * 1.1)
            ELSE SALARY
        END) AS 조정된급여2,
       DECODE(DEPARTMENT_ID, 60, '10% 인상', '미인상') 인상여부
FROM EMPLOYEES
WHERE (CASE DEPARTMENT_ID WHEN 60 THEN (SALARY * 1.1) ELSE SALARY END) = 4400;


-- CASE 표현식: 복잡한 조건 논리 처리하기
SELECT FIRST_NAME, SALARY,
    (CASE
        WHEN (SALARY >= 9000) THEN 'HIGH'
        WHEN (SALARY >= 6000) THEN 'MIDDLE'
        ELSE 'LOW'
    END) AS RATE
FROM EMPLOYEES;

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY,
        CASE
            WHEN SALARY >= 9000 THEN '상위급여'
            WHEN SALARY BETWEEN 6000 AND 8999 THEN '중위급여'
            ELSE '하위급여'
        END AS 급여등급
FROM EMPLOYEES
WHERE JOB_ID = 'IT_PROG';

-- RANK, DENSE_RANK, ROW_NUMBER: 데이터 값에 순위 매기기
SELECT EMPLOYEE_ID, SALARY,
       RANK()       OVER(ORDER BY SALARY DESC) RANK_급여,
       DENSE_RANK() OVER(ORDER BY SALARY DESC) DENSE_RANK_급여,
       ROW_NUMBER() OVER(ORDER BY SALARY DESC) ROW_NUMBER_급여
FROM EMPLOYEES;

SELECT EMPLOYEE_ID, HIRE_DATE,
       RANK()       OVER(ORDER BY HIRE_DATE ASC) RANK_채용일,
       DENSE_RANK() OVER(ORDER BY HIRE_DATE ASC) DENSE_RANK_채용일,
       ROW_NUMBER() OVER(ORDER BY HIRE_DATE ASC) ROW_NUMBER_채용일
FROM EMPLOYEES;

--- 6) 그룹 함수
-- COUNT: 지정한 열의 행 개수를 세는 함수
SELECT COUNT(*) FROM EMPLOYEES;   -- 전체 행 수
SELECT COUNT(SALARY) salary행수 FROM EMPLOYEES;
SELECT COUNT(COMMISSION_PCT), COUNT(*) FROM EMPLOYEES;

-- SUM, AVG: 열의 합계와 평균을 구하는 함수
SELECT SUM(SALARY) 합계, ROUND(AVG(SALARY)) 평균, 
       ROUND(SUM(SALARY)/COUNT(SALARY)) 계산된평균
FROM EMPLOYEES;

SELECT ROUND(AVG(COMMISSION_PCT),3), ROUND(AVG(NVL(COMMISSION_PCT, 0)),3),
       ROUND((SUM(COMMISSION_PCT) / COUNT(*)),3)
FROM EMPLOYEES;

-- MAX, MIN: 최댓값, 최솟값 출력
SELECT MAX(SALARY) 최댓값, MIN(SALARY) 최솟값, 
       MAX(FIRST_NAME) 최대문자값, MIN(FIRST_NAME) 최소문자값
FROM EMPLOYEES;

--- 7) GROUP BY: 그룹으로 묶기
-- UNION을 사용하여 묶는 경우
SELECT SUM(SALARY), AVG(SALARY) FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
UNION
SELECT SUM(SALARY), AVG(SALARY) FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30;

-- SALARY의 합계, 평균 등을 출력
SELECT DEPARTMENT_ID, SUM(SALARY), FLOOR(AVG(SALARY))
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID
ORDER BY 1;


-- HIRE_DATE의 요일을 기준으로 그룹화
SELECT TO_CHAR(HIRE_DATE, 'DAY'), SUM(SALARY) 
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE, 'DAY');

-- 연도별(4자리 연도 YYYY)로 그룹화
SELECT TO_CHAR(HIRE_DATE, 'YYYY'), ROUND(AVG(SALARY)) 
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE, 'YYYY');

-- CASE 문을 활용한 GROUP BY
SELECT (CASE
            WHEN (SALARY >= 10000) THEN 'HIGH'
            WHEN (SALARY >= 6000) THEN 'MIDDLE'
            ELSE 'LOW'
        END) AS SALARY_RATE, SUM(SALARY)
FROM EMPLOYEES
GROUP BY (CASE 
            WHEN (SALARY >= 10000) THEN 'HIGH' 
            WHEN (SALARY >= 6000) THEN 'MIDDLE' 
            ELSE 'LOW' 
        END);

-- 예제 4-21
SELECT JOB_ID 직무, SUM(SALARY) 직무별_총급여, AVG(SALARY) 직무별_평균급여
FROM EMPLOYEES
WHERE EMPLOYEE_ID >= 10
GROUP BY JOB_ID
ORDER BY 직무별_총급여 DESC, 직무별_평균급여;

-- GROUP BY 대그룹, 중그룹, 소그룹의 형태로 응용
SELECT JOB_ID job_id_대그룹, 
    MANAGER_ID manager_id_중그룹, 
    SUM(SALARY) 그룹핑_총급여,
    CEIL(AVG(SALARY)) 그룹핑_평균급여
FROM EMPLOYEES
WHERE EMPLOYEE_ID >= 10
GROUP BY JOB_ID, MANAGER_ID
ORDER BY 그룹핑_총급여 DESC, 그룹핑_평균급여;

--- 8) HAVING: 연산된 그룹 함수 결과에 조건 적용하기
SELECT JOB_ID 직무,
    SUM(SALARY) 직무별_총급여,
    AVG(SALARY) 직무별_평균급여
FROM EMPLOYEES
WHERE EMPLOYEE_ID >= 10
GROUP BY JOB_ID
HAVING SUM(SALARY) > 30000
ORDER BY 직무별_총급여 DESC;