SQL - 내장 함수

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;