SQL 단일행 함수 - 숫자, 날짜 함수

1. 숫자함수

: 숫자를 인자로 사용하고 결과값도 숫자를 반환하는 함수

ROUND : N자리까지 반올림

>> ROUND(M, N)
  ROUND(123.4567, 3) => 123.457
  

TRUNC : N자리 미만을 절삭

>> TRUNK(M, N)
  TRUNC(123.4567, 3) => 123.456
  TRUNC(4567.678, -2) => 4500
   --인자로 음수를 주는 경우 확인

MOD : M을 N으로 나눈 나머지 계산

>> MOD(M,N)
  MOD(10, 4) => 2

POWER : M의 N승을 계산

>> POWER(M, N)
  POWER(2, 4) => 16
  

CEIL : M보다 큰(크거나 같은) 가장 작은 정수

>> CEIL(M)
  CEIL(2.34) => 3
  CEIL(2) => 2

FLOOR : M보다 작은 가장 큰 정수

>> FLOOR(M)
  FLOOR(2.34)=> 2
  

ABS : M의 절대값

>> ABS(M)
  ABS(-4) => 4

SQRT : M의 제곱근 계산

>> SQRT(M) = POWER(M, 1/2)
  SQRT(9) = POWER(9, 1/2) => 3
  

SIGN : M이 음수일 때 -1, 양수일 때 1, 0이면 0을 반환

>> SIGN (M)
  SIGN(03) => -1      

2. 날짜 함수와 날짜 연산의 이해

날짜 포맷 변경

( 날짜 FORMAT요소는 여기를 클릭해서 확인 )

SELECT SYSDATE FROM DUAL;

SELECT TO_CHAR(SYSDATE, 'YYYY:MM:DD:HH24:MI:SS') FROM DUAL;

SELECT SESSION SET NLS_DATE_FORMANT ='YYYY/MM/DD HH24:MI:SS');

SELECT SYSDATE FROM DUAL;

DUAL : 더미테이블

dual 이라는 의미 없는 속성 하나만 가진 테이블이다.
오라클에서는 select뒤에 반드시 from이 와야하는데,
sysdate 처럼 테이블이 필요 없는 경우에 dual 테이블을 사용한다.

날짜+숫자 : 날짜

SELECT SYSDATE + 5 FROM DUAL;

날짜-숫자 : 날짜

SELECT SYSDATE -5 FROM DUAL;

날짜+숫자/24 : 날짜 (숫자/24 : 시간)

SELECT SYSDATE -5/24 FROM DUAL;

날짜-날짜 : 숫자

SELECT SYSDATE - (SYSDATE - 5) FROM DUAL;

날짜함수

MONTHS_BETWEEN : 두 날짜간의 기간을 월 수로 계산

MONTHS_BETWEEN(날짜1, 날짜2)
    :날짜1 > 날짜 2 인 경우 결과는 양수

MONTHS_BETWEEN('2013/09/01', '2013/01/01') -> 0

ADD_MONTHS : 날짜에 N달을 더한 날짜

ADD_MONTHS(날짜, N)

  ADD_MONTHS('2011/07/01', 23) => '2013/06.01'
  
  ADD_MONTHS('2021/05/14', -2) => '2021/03/14'
'-' 붙이면 과거날짜도 구할 수 있음
  

NEXT_DAY : 날짜 이후 지정된 요일에 해당하는 날짜 계산

NEXT_DAY(날짜, 요일)

NEXT_DAY('2013/07/13', '일요일') -> '2019/07/21'

NEXT_DAY('2013/07/13', '1') -> '2019/07/21'
--요일을 숫자로 지정할 수도 있다.
일요일부터 차례대로 1~7

  

LAST_DAY : 날짜를 포함한 달의 마지막 날짜 계산

LAST_DAY(날짜)

LAST_DAY('2013/09/24') -> '2013/09/30'

TRUNC : 날짜를 년,월 단위로 버림

ROUND : 날짜를 년, 월 단위로 반올림

(SYSDATE : 2021/05/18:11:06:00 기준)

LAST_DAY('2013/09/24') -> '2013/09/30'
ROUND(SYSDATE,'YEAR') AS YYYY -> 2021/1/1
ROUND(SYSDATE, 'MONTH')AS MONTH -> 2021/6/1
ROUND(SYSDATE, 'DD')AS DD -> 2021/5/18	--DD는 하루 단위
ROUND(SYSDATE, 'DAY')AS DAY -> 2021/5/16 --DAY는 주 단위

---

EXERCISE 1. 다양한 숫자함수 이용한 결과 학인

SELECT ROUND(98.765), TRUNC(98.765), ROUND(98.765, 2), TRUNC(98.765, 2) FROM DUAL;

SELECT MOD(19,3), MOD(-19, 3) FROM DUAL;

SELECT CEIL(3.5), FLOOR(3.5) FROM DUAL;

EXERCISE 2. 10번 부서의 연봉을 계산. 단, 100단위 미만은 절삭.

SELECT ENO, ENAME, TRUNC(SAL*12+NVL(COMM, 0) , -2)  연봉 
FROM EMP WHERE DNO='10';

EXERCISE 3. 오늘날짜 입사일자 입사일로부터 오늘까지 기간

입사일 이후 100일째 되는날 등 검사

SELECT SYSDATE 오늘, HDATE 입사일, TRUNC(SYSDATE) - TRUNC(HDATE)+1 근무일,
       HDATE + 99 "100일"FROM EMP;      

EXERCISE 4.

문시현이 오늘까지 일한 일수 검색

SELECT ENAME, TRUNC(SYSDATE)-TRUNC(HDATE)+1 DAY FROM EMP WHERE ENAME = '문시현';

EXERCISE 5.

숫자나 날짜를 반올림하거나 잘라내고 출력

SELECT SYSDATE, ROUND(SYSDATE, 'YY')년, 
                ROUND(SYSDATE, 'MM')월,
                ROUND(SYSDATE, 'DD')일
FROM DUAL;               

EXERCISE 6.       
SELECT SYSDATE, TRUNC(SYSDAET, 'YY') 년
                ,TRUNC(SYSDATE, 'MM') 월
                ,TRUNC(SYSDATE, 'DD') 일 FROM DUAL;

EXERCISE 7.

20번부서 직원들이 현재까지 근무한 개월수를 검색
SELECT ENO, ENAME, TRUNC(MONTHS_BETWEEN(SYSDATE, HDATE)) 근무개월 
FROM EMP 
WHERE DNO=20;

EXERCISE 8.

20번 부서원들이 입사 100일째 되는날과 10년째 되는날 검색

SELECT ENO, ENAME, HDATE 입사일, HDATE+99 "100일",
ADD_MONTHS(HDATE, 120) "10년" FROM EMP
WHERE DNO= '20';

EXERCISE 9.

20번 부서원들이 입사한 이후 첫번째 일요일을 검색

SELECT ENO, ENAME, HDATE, NEXT_DAY(HDATE, '일요일') SUNDAY FROM EMP
WHERE DNO = '20';

EXERCISE 10.

20번 부서원들의 입사한 달의 마지막 날짜와 입사한 달에 근무일수 검색

SELECT ENO, ENAME, HDATE, LAST_DAY(HDATE) 마지막날,
       LAST_DAY(TRUNC(HDATE)) - TRUNC(HDATE) + 1 "마지막날 근무일수"    --띄어쓰기때문에 ""
FROM EMP WHERE DNO = '20';

---

1. 교수들이 부임한 달에 근무한 일수는 며칠인지 검색

SELECT PNO, PNAME, HIREDATE, LAST_DAY(TRUNC(HIREDATE)) - TRUNC(HIREDATE)+1 근무일수
FROM PROFESSOR;

2. 교수들이 오늘까지 근무한 주가 몇 주인지 검색

SELECT PNO, PNAME, TRUNC((SYSDATE-HIREDATE+1)/7)  근무_주 
FROM PROFESSOR;

3. 1991년에서 1995년 사이에 부임한 교수 검색

SELECT PNO, PNAME, HIREDATE
FROM PROFESSOR
--WHERE TRUNC(HIREDATE) BETWEEN '1991/01/01' AND '1995/12/31';  --TRUNC(HIREDATE) : 시간정보 절삭하고 날짜까지만
WHERE TO_CHAR(HIREDATE, 'YYYY') BETWEEN 1991 AND 1995;

4. 학생들의 4.5 환산 평점을 검색 (소수 둘째자리까지)

SELECT SNO, SNAME, ROUND(AVR/4.0*4.5,2) "4.5환산 평점"
FROM STUDENT
ORDER BY ROUND(AVR/4.0*4.5,2);

5. 사원들이 일한 날짜에 대해서만 급여를 받는다면 급여가 현재와 동일하다는 조건에서 입사한 달의 급여는 얼마인가?

SELECT ENO
      ,ENAME
      ,LAST_DAY(TRUNC(HDATE)) - TRUNC(HDATE)+1 근무일수
      ,ROUND(SAL / TO_NUMBER(TO_CHAR(LAST_DAY(HDATE),'DD')),0) 일급여
      ,ROUND((LAST_DAY(TRUNC(HDATE)) - TRUNC(HDATE)+1) * (SAL / TO_NUMBER(TO_CHAR(LAST_DAY(HDATE),'DD'))),0) 지급액
FROM EMP;

6.사원들의 오늘까지 근무 기간이 몇년 몇개월 며칠인지 검색

SELECT ENO
      ,ENAME
      ,HDATE
      ,SYSDATE
      ,TRUNC(SYSDATE) -TRUNC(HDATE)+1 총_근무일
--      TRUNC(MONTHS_BETWEEN(SYSDATE, HDATE)) 총 개월수
      ,TRUNC(MONTHS_BETWEEN(SYSDATE, HDATE) / 12) 년
      ,MOD(TRUNC(MONTHS_BETWEEN(SYSDATE, HDATE)) ,12) 개월
      ,ROUND(SYSDATE - ADD_MONTHS(HDATE, TRUNC(MONTHS_BETWEEN(SYSDATE, HDATE)))) 일
      ,ROUND(ADD_MONTHS(SYSDATE, -MONTHS_BETWEEN(SYSDATE, HDATE)) - HDATE) 일2
FROM EMP;