코드 및 쿼리문/SQL 공부
SQL - 내장 함수
황규진
2024. 7. 15. 15:09
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;