문자함수
LOWER(), UPPER() : 대/소문자로 변환
SELECT 'DataBase', LOWER('DataBase')
FROM dual; --데이터베이스에서 제공하는 가상의 테이블SUBSTR() : 부분 문자열 추출
SELECT SUBSTR('abcde', 2, 4)
FROM dual; --bcde인덱스는 1부터 시작하며,
마지막 매개변수는 문자의 갯수이다.
SELECT SUBSTR(first_name, 4)
FROM employees; --인덱스 4부터 끝까지LENGTH : 문자열 길이
SELECT job, SUBSTR(job, -LENGTH(job))
FROM emp;-를 붙이면 뒤에서 N번째를 의미한다.
LPAD(), RPAD() : 데이터 빈 공간을 특정 문자로 채우기
SELECT 'Oracle',
LPAD('Oracle', 10, '#'),
RPAD('Oracle', 10, '*')
FROM dual;숫자 연산 함수
MOD : 나머지 연산
SELECT MOD(10, 3) FROM dual;ROUND : 소숫점 N번째에서 반올림
SELECT ROUND(3243.35543, 2) FROM dual;음수가 오면 정수 부분이 3200처럼 0으로 채워짐
날짜 함수
SYSDATE : 오늘로부터의 날짜 구하기
SELECT SYSDATE -1 --어제
FROM dual; --2021/02/17SELECT first_name, round((sysdate-hire_date)/365, 1) as "근속년"
FROM employees;ADD_MONTHS(날짜 데이터, 더할 개월 수)
NEXT_DAY(날짜 데이터, 요일 문자)
SELECT NEXT_DAY(SYSDATE, '월요일')
FROM dual;변환 함수
TO_CHAR() : 숫자, 날짜를 원하는 형식의 문자열로 변환
--오늘 날짜를 원하는 형식으로 변환
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;
--숫자를 원하는 형식으로 변환
SELECT TO_CHAR(5000000, '$999,999,999') FROM dual;SELECT * FROM employees
WHERE TO_CHAR(hire_date, 'YY') = '07';TO_DATE() : 문자열을 날짜 데이터로 변환
SELECT TO_DATE('2021-02-18', 'YYYY-MM-DD'),
TO_DATE('20210219', 'YYYY-MM-DD')
FROM dual;NVL(열, 변환하고 싶은 값) : NULL을 0 또는 다른 값으로 변환
SELECT employee_id, salary, NVL(commission_pct, 0)
FROM employees;NULL이 있으면 연산이 자유롭지 못하기 때문에 0과 같은 다른 값으로 변경
DECODE
SELECT job_id, DECODE(job_id, 'SA_MAN', 'Sales Dept',
'SH_CLERK', 'Sales Dept', 'Another')
FROM employees;job_id 중 'SA_MAN', 'SH_CLERK' 값이면 'Sales Dept'로 변경, 그 외는 'Another'로 변경한다.
CASE WHEN
SELECT job_id,
CASE job_id
WHEN 'SA_MAN' THEN 'Sales Dept'
WHEN 'SH_CLERK' THEN 'Sales Dept'
ELSE 'Another'
END "CASE"
FROM employees;위의 DECODE와 같은 기능을 CASE WHEN을 사용하면 위와 같이 표현할 수 있다.
예제
- 이름과 사원번호 뒷자리 * 처리하기
SELECT EMPNO,
RPAD(SUBSTR(EMPNO, 1, 2), 4, '*') AS MASKING_EMPNO,
ENAME,
RPAD(SUBSTR(ENAME, 1, 1), LENGTH(ENAME), '*') AS MASKING_ENAME
FROM EMP
WHERE LENGTH(ENAME) >= 5
AND LENGTH(ENAME) < 6; - 정직원이 되는 날짜 구하기(입사 3개월 후 첫 번째 월요일)
SELECT EMPNO, ENAME, TO_CHAR(hiredate, 'YYYY/MM/DD') AS HIREDATE,
TO_CHAR(NEXT_DAY(ADD_MONTHS(HIREDATE, 3), '월요일'), 'YYYY-MM-DD') AS R_JOB,
NVL(TO_CHAR(COMM), 'N/A') AS COMM
FROM EMP; 주의
- 날짜 데이터는 기본적으로 21/02/19와 같은 식으로 표기되는데 이 형식을 바꾸기 위해서는 TO_DATE가 아닌 TO_CHAR 사용
- NVL의 첫 번째 인자로 오는 열의 데이터는 CHAR형이어야 한다.
- 직속상관 번호(MGR)를 경우에 따라 바꿔서 표기하기
SELECT EMPNO, ENAME, MGR,
CASE
WHEN MGR IS NULL THEN '0000'
WHEN SUBSTR(MGR, 1, 2) = '78' THEN '8888'
WHEN SUBSTR(MGR, 1, 2) = '77' THEN '7777'
WHEN SUBSTR(MGR, 1, 2) = '76' THEN '6666'
WHEN SUBSTR(MGR, 1, 2) = '75' THEN '5555'
ELSE TO_CHAR(MGR)
END AS CHG_MGR
FROM EMP;주의
- CASE문 자체가 하나의 열을 표현하는 것이다.
- CASE문 사용시 결과로 나오는 데이터형이 모두 같도록 맞춰줘야 한다.
- 같은 직책에 종사하는 사람이 3명 이상인 직책과 인원 수 구하기
SELECT JOB,
COUNT(*)
FROM EMP
GROUP BY JOB
HAVING COUNT(*) >= 3; - 입사 연도를 기준으로 부서 별로 몇 명이 입사했는지 구하기
--GROUP BY만 이용
SELECT TO_CHAR(HIREDATE, 'YYYY') AS HIRE_YEAR,
DEPTNO,
COUNT(*) AS CNT
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY'), DEPTNO;
--ROLLUP 이용
SELECT TO_CHAR(hiredate, 'YYYY') AS HIRE_YEAR,
DEPTNO,
count(*) AS CNT
FROM EMP
GROUP BY ROLLUP(TO_CHAR(HIREDATE, 'YYYY'), DEPTNO);주의
위의 두 가지 쿼리는 거의 같지만, GROUP BY ROLLUP의 경우 첫번째 인자로 설정한 열을 기준으로 소계와 모든 그룹의 총계도 내주기 때문에, 필요없는 경우에는 그냥 GROUP BY를 사용한다.
- 추가 수당(COMM)을 받는 사원과 받지 않는 사원의 수
--NVL2 사용
SELECT NVL2(COMM, 'O', 'X') AS EXIST_COMM,
COUNT(*) AS CNT
FROM EMP
GROUP BY NVL2(COMM, 'O', 'X');
--CASE문 사용
SELECT
CASE
WHEN COMM IS NULL THEN 'X'
ELSE 'O'
END "EXIST_COMM"
, COUNT(*)
FROM EMP
GROUP BY
CASE
WHEN COMM IS NULL THEN 'X'
ELSE 'O'
END;주의
- case문을 사용했는데 group by 절에 해당 열을 사용해야할 경우, case문을 똑같이 다시 적어줘야한다.
- 각 부서의 입사 연도별 사원 수, 최고 급여, 급여 합, 평균 급여
그리고 각 부서별 소계와 모든 부서의 총계 구하기
SELECT DEPTNO,
TO_CHAR(HIREDATE, 'YYYY') AS HIRE_YEAR,
COUNT(*) AS CNT,
MAX(SAL) AS MAX_SAL,
SUM(SAL) AS SUM_SAL,
AVG(SAL) AS AVG_SAL
FROM EMP
GROUP BY ROLLUP(DEPTNO, TO_CHAR(HIREDATE, 'YYYY')); 소계와 총계가 필요하기 때문에 GROUP BY ROLLUP을 사용해주었다.
📝 dev wiki