날짜함수
1) SYSDATE
- 시스템에서 제공하는 날짜 및 시간정보 반환
- '+', '-' 연산의 대상
2) ADD_MONTHS(d, n)
- 주어진 날짜자료 d에 정수 n만큼을 더한 날짜 반환
사용예) 사원테이블에서 입사일자가(HIRE_DATE) 수습시작일이라 가정하고 정식 발령일자(3개월 후)를 조회하여
이번달에 입사한 사원을 조회하시오.
Alias는 사원번호, 사원명, 수습일자, 발령일
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
HIRE_DATE AS 수습일자,
ADD_MONTHS(HIRE_DATE,3) AS 발령일
FROM EMP
WHERE EXTRACT(MONTH FROM ADD_MONTHS(HIRE_DATE,3))
= EXTRACT(MONTH FROM SYSDATE);
-- 오늘날짜에서 월을 뽑은값이 서로같은값인 9월달이면 출력
3) NEXT_DAY(d, C), LAST_DAY(d)
- NEXT_DAY: 주어진 날짜 d 이후의 날짜 중 가장 빠른 c요일('월','월요일','화','화요일',...)의 날짜를 반환
- LAST_DAY: 주어진 날짜 d에 포함된 월의 마지막일자를 나타내는 날짜 반환
사용예) 2005년 2월 제품별 매입합계를 조회하시오.
상품코드, 상품명 -- 매입테이블에 있는 상품코드와 상품 테이블에 있는 상품코드가 같은 상품명을 가져옴
, 매입수량합계, 매입금액합계 --집계함수, 조인이용
SELECT A.BUY_PROD AS 상품코드,
B.PROD_NAME AS 상품명,
SUM(A.BUY_QTY) AS 매입수량합계,
SUM(A.BUY_QTY*A.BUY_COST) AS 매입금액합계
-- SUM(A.BUY_QTY*B.PROD_COST)도 됨
FROM BUYPROD A, PROD B
WHERE B.PROD_ID=A.BUY_PROD -- 조인조건
AND A.BUY_DATE BETWEEN TO_DATE('20050201') AND
LAST_DAY(TO_DATE('20050201'))
--날짜 타입은 BETWEEN 연산자 쓸것. LIKE연산자 x
GROUP BY A.BUY_PROD, B.PROD_NAME
--상품코드가 같은 자료를 구별하고 또 다시 상품 이름같은것끼리 구별.
ORDER BY 1;
4) MONTHS_BEWEEN(d1,d2)
- 두 날짜자료 사이의 달수를 반환
사용예)회원테이블에서 회원들의 생년월일을 이용하여 경과된 개월 수를 조회하시오
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_BIR AS 생년월일,
ROUND(MONTHS_BETWEEN(SYSDATE,MEM_BIR)) AS 월수,
ROUND(MONTHS_BETWEEN(SYSDATE,MEM_BIR)/12) AS 나이
FROM MEMBER;
5)EXTRACT(FMT FROM d)
- 주어진 날짜 d에서 'fmt'로 정의된 요소 값을 반환
- fmt는 'YEAR', 'MONTH', 'DAY', 'HOUR', 'MINUTE', 'SECOND'
- 반환 값의 타입은 숫자형임
** MEMBER 테이블에서 다음 자료를 수정하시오.
회원번호: 'i001'
이름: '최지현'
MEM_REGNO1: '741220'=>'011220'
MEM_REGNO2: '2384719'=>'4384719'
MEM_BIR: '1974/12/20' => '2001/12/20'
UPDATE MEMBER
SET MEM_REGNO1='011220',
MEM_REGNO2='4384719',
MEM_BIR=TO_DATE('2001/12/20')
WHERE MEM_ID='i001';
SELECT MEM_ID, MEM_NAME, MEM_REGNO1, MEM_REGNO2, MEM_BIR
FROM MEMBER
WHERE MEM_ID='i001';
회원번호: 't001'
이름: '성원태'
MEM_REGNO1: '760506'=>'000506'
MEM_REGNO2: '1454731'=>'3454731'
MEM_BIR: '1976/05/06' => '2000/05/06'
UPDATE MEMBER
SET MEM_REGNO1='000506',
MEM_REGNO2='3454731',
MEM_BIR=TO_DATE('2000/05/06')
WHERE MEM_ID='t001';
SELECT MEM_ID, MEM_NAME, MEM_REGNO1, MEM_REGNO2, MEM_BIR
FROM MEMBER
WHERE MEM_ID='t001';
회원번호: 'e001'
이름: '이혜나'
MEM_REGNO1: '750501'=>'020501'
MEM_REGNO2: '2406017'=>'4406017'
MEM_BIR: '1975/05/01' => '2002/05/01'
UPDATE MEMBER
SET MEM_REGNO1='020501',
MEM_REGNO2='4406017',
MEM_BIR=TO_DATE('2002/05/01')
WHERE MEM_ID='e001';
SELECT MEM_ID, MEM_NAME, MEM_REGNO1, MEM_REGNO2, MEM_BIR
FROM MEMBER
WHERE MEM_ID='e001';
COMMIT;
사용예) 회원테이블에서 이번달 생일인 회원을 추출하시오
Alias는 회원번호, 회원명, 생년월일, 마일리지
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_BIR AS 생년월일,
MEM_MILEAGE AS 마일리지
FROM MEMBER
WHERE EXTRACT(MONTH FROM MEM_BIR)
=EXTRACT(MONTH FROM SYSDATE);
-- 다음달 생일 WHERE EXTRACT(MONTH FROM MEM_BIR)
=EXTRACT(MONTH FROM SYSDATE)+1;
사용예) 사원테이블에서 근속년수를 계산하여 근속년수가 20년 이상인 사원을 조회하시오.
Alias는 사원번호, 사원명, 입사일, 근속년수이다.
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
HIRE_DATE AS 입사일,
EXTRACT(YEAR FROM SYSDATE)
-EXTRACT(YEAR FROM HIRE_DATE) AS 근속년수
FROM EMP
WHERE EXTRACT(YEAR FROM SYSDATE)
-EXTRACT(YEAR FROM HIRE_DATE) >=20;