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
'웹개발 수업 > SQL' 카테고리의 다른 글
[Day +37 / SQL]Group By & Having (0) | 2021.08.12 |
---|---|
[Day +36 / SQL 과제]함수 기본 6문제, SELECT(Funtion) 10~15번 (0) | 2021.08.12 |
[Day +35 / SQL 실습]함수 SELECT(Basic) 실습 10문제 (0) | 2021.08.10 |
[Day +35 / SQL]연산, 함수 (0) | 2021.08.10 |
[Day +34 / SQL]Database, Oracle 설치, SQL, DML & 미니 프로젝트 발표 (0) | 2021.08.10 |