본문 바로가기

웹개발 수업/SQL

[Day+ 36 / SQL] 함수

210811 수

 

1. 숫자 처리 함수

1) ABS(숫자 | 숫자로 된 컬럼) 

: 절대값을 구하여 리턴하는 함수 

SELECT ABS(10) FROM DUAL;
SELECT ABS(-10) FROM DUAL;
SELECT ABS(10.9) FROM DUAL;
SELECT ABS(-10.9) FROM DUAL;
--답 : 10.9

 

2) MOD(숫자 | 숫자로 된 컬럼)
: 두 수를 나누어서 나머지를 구하는 함수(나누어지는 수, 나눌 수)

SELECT MOD(10, 3) FROM DUAL;
--답 : 1
SELECT MOD(10.9, 3) FROM DUAL;
--답 : 1.9

 

3) ROUND(숫자 | 숫자로 된 컬럼명, [위치]) 

: 반올림하여 리턴하는 함수

SELECT ROUND(123.456) FROM DUAL;
--답 : 123
SELECT ROUND(123.678) FROM DUAL;
--답 : 124
SELECT ROUND(123.456, 1) FROM DUAL;
--답 : 123.5
SELECT ROUND(123.456, -1) FROM DUAL;
--답 : 120

 

4) FLOOR(숫자 | 숫자로 된 컬럼명) 

: 내림 처리하는 함수

SELECT FLOOR(123.456) FROM DUAL;
--답 : 123
SELECT FLOOR(123.678) FROM DUAL;
--답 : 123

 

5) TRUNC(숫자 | 숫자로 된 컬럼명, [위치]) 

: 내림 처리 하는 함수

SELECT TRUNC(123.456) FROM DUAL;
--답 : 123
SELECT TRUNC(123.678, 1) FROM DUAL;
--답 : 123.6
SELECT TRUNC(123.456, -1) FROM DUAL;
--답 : 120

 

6) CEIL(숫자 | 숫자로 된 컬럼명) 

: 올림 처리 하는 함수

SELECT CEIL(123.456) FROM DUAL;
--답 : 124
SELECT CEIL(123.678) FROM DUAL;
--답 : 124

 

 

2. 날짜 처리 함수

1) SYSDATE

: 시스템에 저장 되어 있는 날짜를 반환

SELECT SYSDATE FROM DUAL;
--답 : 21/08/11

 

2) MONTHS_BETWEEN(날짜, 날짜)

 : 개월 수의 차이를 숫자로 리턴하는 함수

2-1) 사원명, 입사일, 근무 개월 수 조회

SELECT EMP_NAME
     , HIRE_DATE
     , CEIL(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) || '개월차'
  FROM EMPLOYEE;

3) ADD_MONTHS(날짜, 숫자) 

: 날짜에 숫자만큼 개월수를 더하여 날짜를 리턴

SELECT ADD_MONTHS(SYSDATE, 5) FROM DUAL;
--답 : 22/01/11

3-1) 사원명, 입사일, 입사 후 6개월이 된 날짜 조회

SELECT
EMP_NAME
, HIRE_DATE
, ADD_MONTHS(HIRE_DATE, 6)
FROM EMPLOYEE;

 

4) NEXT_DAT(기준 날짜, 요일(문자|숫자)) --> 1: 일 ~ 7 : 토
: 기준 날짜에서 구하려는 요일의 가장 가까운 날짜를 리턴하는 함수

SELECT SYSDATE, NEXT_DAY(SYSDATE, '월요일') FROM DUAL;
--답 : 21/08/11	21/08/16
SELECT SYSDATE, NEXT_DAY(SYSDATE, '화') FROM DUAL;
--답 : 21/08/11	21/08/17
SELECT SYSDATE, NEXT_DAY(SYSDATE, 3) FROM DUAL;
--답 : 21/08/11	21/08/17
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'THURSDAY') FROM DUAL;
--오류 뜸 --> 설정을 변경해야 함
ALTER SESSION SET NLS_LANGUAGE = AMERICAN;
--설정 변경 이후 답 : 21/08/11	21/08/12
ALTER SESSION SET NLS_LANGUAGE = KOREAN;

 

5) LAST_DAY(날짜) : 해당 월의 마지막 날짜를 구하여 리턴

SELECT SYSDATE, LAST_DAY(SYSDATE) FROM DUAL;
--답 : 21/08/11	21/08/31

 

6) EXTRACT

 : 년, 월, 일 정보를 추출하여 리턴
(1) EXTRACT(YEAR FROM 날짜) : 년도만 추출
(2) EXTRACT(MONTH FROM 날짜) : 월만 추출
(3) EXTRACT(DAY FROM 날짜) : 일만 추출

SELECT 
      EMP_NAME 사원명
    , EXTRACT(YEAR FROM HIRE_DATE) 입사년도
    , EXTRACT(MONTH FROM HIRE_DATE) 입사월
    , EXTRACT(DAY FROM HIRE_DATE) 입사일
FROM EMPLOYEE
--만약 정렬을 하고 싶다면 
--ORDER BY EMP_NAME DESC;
--ORDER BY 2;(입사년도)
ORDER BY 입사년도, 사원명;

 

6-1) 사원명, 입사일, 근무년수 조회

--1) 현재년도 - 입사년도(년도 기준이므로 일수 고려가 안됨)
SELECT 
EMP_NAME
, HIRE_DATE
, EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) 근무년수
FROM EMPLOYEE;
--2) MONTHS_BETWEEN (만으로 채운 근무년수)
SELECT
EMP_NAME, HIRE_DATE, FLOOR(MONTHS_BETWEEN(SYSDATE, HIRE_DATE) / 12) || '년' "만 근무년수"
FROM EMPLOYEE;

 

*실습문제1

--1. 사원명, 입사일 - 오늘, 오늘 - 입사일 조회
--단, 별칭은 근무일수1, 근무일수2로 하고 모두 정수/양수 처리 된 결과로 조회
SELECT 
EMP_NAME
, FLOOR(ABS(HIRE_DATE - SYSDATE)) 근무일수1
, FLOOR(ABS(SYSDATE - HIRE_DATE)) 근무일수2
FROM EMPLOYEE;


--2. 사번(EMP_ID)이 홀수인 직원들의 정보 모두 조회
SELECT
*
FROM EMPLOYEE
WHERE MOD(EMP_ID, 2) = 1;


--3. 만 근수년수가 20년 이상인 직원 정보 모두 조회
SELECT 
*
FROM
EMPLOYEE
WHERE ADD_MONTHS(HIRE_DATE, 240) <= SYSDATE;

--4. 사원명, 입사일, 입사한 월의 근무일수(주말 포함)를 조회
SELECT
EMP_NAME
, HIRE_DATE
,LAST_DAY(HIRE_DATE) - HIRE_DATE + 1 "입사월의 근무일수"
FROM EMPLOYEE;

 

 

3. 형변환 함수

1) TO_CHAR(날짜 | 숫자, [포맷]) 

: 날짜/숫자형 데이털르 문자형 데이터로 변경

1-1) 숫자 -> 문자

SELECT TO_CHAR(1234, '99999') FROM DUAL; -- 5칸, 오른쪽 정렬, 빈칸 공백
--답 : 1234
SELECT TO_CHAR(1234, '00000') FROM DUAL; -- 5칸, 오른쪽 정렬, 빈칸 0
--답 : 01234
SELECT TO_CHAR(1234, '99,999') FROM DUAL;
--답 :  1,234
SELECT TO_CHAR(1234, '00,000') FROM DUAL;
--답 :  01,234
SELECT TO_CHAR(1234, '999') FROM DUAL; -- 실제 값보다 자리수를 작게 잡으면 표현할 수 없음
--답 : ####
SELECT TO_CHAR(1234, 'L99999') FROM DUAL; --설정된 나라의 화폐 단위
--답 : ₩1234
SELECT TO_CHAR(1234, '$99999') FROM DUAL; --달러
--답 : $1234

1-2) 사원명, 월급(포맷) 조회

SELECT
EMP_NAME
, TO_CHAR(SALARY, 'L999,999,999')
FROM EMPLOYEE;

 

2) 날짜 -> 문자

2-1) 기본

SELECT TO_CHAR(SYSDATE, 'PM HH24:MI:SS') FROM DUAL;
--답 : 오후 17:01:35
SELECT TO_CHAR(SYSDATE, 'AM HH:MI:SS') FROM DUAL;
--답 : 오후 05:01:59
SELECT TO_CHAR(SYSDATE, 'MON, DY, YYYY') FROM DUAL;
--답 : 8월 , 수, 2021
SELECT TO_CHAR(SYSDATE, 'YYYY-FMMM-DD DAY') FROM DUAL;
--답 : 2021-8-11 수요일
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY') FROM DUAL;
--답 : 2021-08-11 수요일
SELECT TO_CHAR(SYSDATE, 'YEAR, Q') || '분기' FROM DUAL;
--답 : TWENTY TWENTY-ONE, 3분기

2-2) 응용 

SELECT
EMP_NAME
, TO_CHAR(HIRE_DATE, 'YYYY"년" MM"월" DD"일"')
FROM employee;

2-3) 오늘 날짜에 대해 4자리 년도, 2자리 년도로 출력

*RR과 YY의 차이
1) RR : 2자리 년도를 4자리로 변경할 때 50미만이면 2000년, 50 이상이면 1900년
2) YY : 는 무조건 금세기를 적용(2000년)
SELECT TO_CHAR(SYSDATE, 'YYYY') YYYY, TO_CHAR(SYSDATE, 'YY') YY
, TO_CHAR(SYSDATE, 'RRRR') RRRR, TO_CHAR(SYSDATE, 'RR') RR
FROM DUAL;

2-4) 월 출력(MM/MONTH/MON/RM)

SELECT TO_CHAR(SYSDATE, 'MM') MM, TO_CHAR(SYSDATE, 'MONTH') MONTH,
       TO_CHAR(SYSDATE, 'MON') MON , TO_CHAR(SYSDATE, 'RM') RM
FROM DUAL;

 

2-5) 일 출력(DDD/DD/D)

SELECT TO_CHAR(SYSDATE, '"1년 기준" DDD "일째"'),
TO_CHAR(SYSDATE, '"달 기준" DD "일째"'),
TO_CHAR(SYSDATE, '"주 기준" D "일째"')
FROM DUAL;

 

2-6) 분기, 요일 출력

SELECT TO_CHAR(SYSDATE, 'Q"분기"'), TO_CHAR(SYSDATE, 'DAY'), TO_CHAR(SYSDATE, 'DY')
FROM DUAL;

 

2-7) 사원명, 입사일 조회
입사일은 포맷 적용 '2017년 12월 6일 (수)' 형식으로 출력

SELECT 
EMP_NAME
, TO_CHAR(HIRE_DATE, 'YYYY"년" MM"월" DD"일" "("DY")"')
FROM employee;

2-8) TO_DATE(문자|숫자, [포맷] : 문자|숫자형 데이터를 날짜로 변경

SELECT TO_DATE('20100101', 'YYYYMMDD') FROM DUAL;
--답 : 10/01/01
SELECT TO_DATE(20100101, 'YYYYMMDD') FROM DUAL;
--답 : 10/01/01

SELECT TO_DATE('041030 143000', 'YYMMDD HH24MISS') FROM DUAL;
--답 : 04/10/30
SELECT TO_CHAR(TO_DATE('041030 143000', 'YYMMDD HH24MISS'), 'DD-MM-YY HH:MI:SS PM') FROM DUAL;
--답 : 30-10-04 02:30:00 오후

 

2-9) 2자리로 표기된 년도를 DATE화 할 때(TO_DATE)
--RR은 50미만 -> 2000년대, 50이상 -> 1900년대 적용
--YY는 무조건 2000년대 적용

SELECT TO_CHAR(TO_DATE('980630', 'YYMMDD'), 'YYYYMMDD') FROM DUAL;
--답 : 20980630
SELECT TO_CHAR(TO_DATE('201130', 'YYMMDD'), 'YYYYMMDD') FROM DUAL;
--답 : 20201130
SELECT TO_CHAR(TO_DATE('980630', 'RRMMDD'), 'YYYYMMDD') FROM DUAL;
--답 : 19980630
SELECT TO_CHAR(TO_DATE('201130', 'RRMMDD'), 'YYYYMMDD') FROM DUAL;
--답 : 20201130

 

2-10) TO_NUMBER(문자, [포맷]) : 문자형 데이터를 숫자 데이터로

SELECT '123' + '456' FROM DUAL;
--답 : 579
SELECT '123' || '456' FROM DUAL;
--답 : 123456
SELECT '1,000,000' + '550,000' FROM DUAL;
--답 안나옴
SELECT TO_NUMBER('1,000,000', '9,999,999') + TO_NUMBER('550,000', '999,999') FROM DUAL;
--답 : 1550000

 

3. NULL 처리 함수

1) NVL(컬럼명, 컬럼값이 NULL일 때 바꿀 값)

SELECT
EMP_NAME
, BONUS
, NVL(BONUS, 0)
FROM EMPLOYEE;

 

2) NVL2(컬럼값, 바꿀값1, 바꿀값2)
: 해당 컬럼의 값이 있으면 바꿀값1로 변경, 해당 컬럼이 NULL이면 바꿀값2로 변경

2-1) 보너스가 NULL인 직원은 0.5로 보너스가 있는 직원은 0.7로 변경

SELECT 
EMP_NAME
, BONUS "현재 보너스 값"
, NVL2(BONUS, 0.7, 0.5) "변경될 보너스 값"
FROM EMPLOYEE;

 

 

4. 선택 함수
1) DECODE(계산식 | 컬럼명, 조건값1, 선택값1, 조건값2, 선택값2.....)
: 비교하고자 하는 값 또는 컬럼이 조건값과 같으면 선택값 반환

1-1) 기본

SELECT
EMP_NAME
, EMP_NO
, DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남', '2', '여')
FROM EMPLOYEE;

일치하는 값이 없으면 NULL 반환
--또는 마지막 인자로 조건값 없이 선택값을 작성하면
--일치 값이 없을 때 마지막에 작성한 선택값을 무조건 선택


여기 다시 정리

1-2) 직원 급여 인상
J7 10%, J6 15%, J5 20%,  그 외 5%

SELECT
EMP_NAME
, JOB_CODE
, SALARY
, DECODE(JOB_CODE, 'J7', SALARY * 1.1, 'J6', 
SALARY * 1.15, 'J5', SALARY * 1.2, SALARY * 1.05) 인상급여
FROM EMPLOYEE;

1-3) CASE절

CASE WHEN 조건식 THEN 결과값
     WHEN 조건식 THEN 결과값
     ELSE 결과값
END
SELECT 
EMP_NAME
, EMP_NO
, CASE WHEN SUBSTR(EMP_NO, 8, 1) = '1' THEN '남'
       ELSE '여'
  END 성별
FROM EMPLOYEE;

1-4) SALARY가 500만원 이상이며 1등급
-350원 이상이면 2등급, 200만원 이상이면 3등급, 나머지는 4등급
-나머지는 4등급으로 처리하여 사원명, 월급, 등급을 조회

SELECT
EMP_NAME
, SALARY
, CASE WHEN SALARY >= 5000000 THEN '1등급'
       WHEN SALARY >= 3500000 THEN '2등급'
       WHEN SALARY >= 2000000 THEN '3등급'
       ELSE '4등급'
    END 등급
FROM EMPLOYEE
ORDER BY 등급, 1;

 

5. 그룹 함수 

: 하나 이상의 행을 그룹으로 묶어 연산하며 총합, 평균 등의 하나의 결과를 반환하는 함수

 

1) SUM(숫자칼럼) : 합계를 구하여 리턴

1-1) 전 사원의 급여 총합 조회

SELECT 
SUM(SALARY)
FROM EMPLOYEE;
--답 : 70096240

1-2) 여자 사원의 급여 총합 조회

SELECT
SUM(SALARY)
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = 2;
--답 : 20336240

1-3) D5부서의 직원의 보너스 포함한 연봉의 합 조회

SELECT
      SUM((SALARY + (SALARY * NVL(BONUS, 0))) * 12)
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5';
--답 : 198060000

 

2) AVG(숫자 컬럼) : 평균을 구하여 리턴
2-1) 전 사원의 급여 평균 조회

SELECT
ROUND(AVG(SALARY))
FROM EMPLOYEE;
--답 : 3047663

 

2-2) 전 사원의 보너스 평균을 소수 셋째 자리에서 반올림해서 조회
-BONUS가 NULL인 사원은 0으로 처리

SELECT
ROUND(AVG(NVL(BONUS, 0)), 2)
FROM EMPLOYEE;
--답 : 0.08
--NVL처리를 하지 않을 시 NULL값을 가진 행은 평균 계산에서 제외됨

 

3) MIN(컬럼명):컬럼에서 가장 작은 값 리턴
: 취급하는 자료형은 ANY TYPE

SELECT 
MIN(EMAIL)
, MIN(HIRE_DATE)
,MIN(SALARY)
FROM EMPLOYEE;
--답 : bang_ms@kh.or.kr 90/02/06 1380000

 

4) MAX(컬럼명) : 컬럼에서 가장 큰 값 리턴
: 취급하는 자료형은 ANY TYPE

SELECT
MAX(EMAIL)
, MAX(HIRE_DATE)
, MAX(SALARY)
FROM EMPLOYEE;
--답 : youn_eh@kh.or.kr 17/06/19 8000000

 

5) COUNT(* | 컬럼명): 행의 개수를 헤아려서 리턴
(1) COUNT(DISTINCT 컬럼명) : 중복을 제거한 행 개수 리턴
(2) COUNT(*) : NULL을 포함한 전체 행 개수 리턴
(3) COUNT(컬럼명) : NULL을 제외한 실제 값이 기록된 행 개수 리턴

SELECT
COUNT(*)
, COUNT(DEPT_CODE)
, COUNT(DISTINCT DEPT_CODE)
FROM EMPLOYEE;
--답 : 23 21 6