Day 40. [Oracle SQL] 숫자 함수, 날짜 함수, 명시적 데이터 타입 변환, NVL 함수

데이터베이스

숫자 함수

ROUND : 지정한 소수점 자리로 값을 반올림

TRUNC : 지정한 소수점 자리까지 남기고 값을 버림

MOD : m 을 n 으로 나눈 나머지

-- ROUND(123.17, 1) -> 123.2

-- TRUNC(123.17, 1) -> 123.1

-- MOD(12, 10) -> 2

 

CEIL : 지정한 값보다 큰 수 중에서 가장 작은 정수 = 올림

FLOOR : 지정한 값보다 작은 수 중에서 가장 큰 정수 = 버림

-- CEIL(123.17) -> 124

-- FLOOR(123.17) -> 123

---

날짜 함수

SYSDATE : 시스템의 현재 날짜 (날짜)

MONTHS_BETWEEN : 날짜와 날짜 사이의 개월을 계산 (숫자)

ADD_MONTHS : 날짜에 개월을 더한 날짜 계산 (날짜)

-- MONTHS_BETWEEN (date1, date2)

-- ADD_MONTHS (date, 개월 수)

 

NEXT_DAY : 날짜후의 첫 요일의 날짜를 계산 (날짜)

LAST_DAY : 월의 마지막 날짜를 계산 (날짜)

-- NEXT_DAY (date, 'day') 'day'에는 숫자 들어가요! 1~7 일 ~ 토

-- LAST_DAY (date)

 

ROUND : 날짜를 반올림 (날짜)

TRUNC : 날짜를 절삭 (날짜)

-- ROUND (date [, 'format'])

-- TRUNC (date, [, 'format'])

---

명시적인 데이터 타입 변환

TO_CHAR : 숫자 / 날짜 타입을 문자로 변환

-- TO_CHAR('06/10', 'YYYY-MM') -> 2006-10

TO_NUMBER : 문자열을 숫자 타입으로 변환

-- TO_NUMBER(1000, '9.999') -> 1,000

TO_DATE : 문자열을 날짜 타입으로 변환

-- TO_DATE('06/10', 'YYYY-MM') -> 2006-10

---

NVL 변환 함수 : NULL을 0 또는 다른 값으로 변환하기 위한 함수

-- NVL(expression1, expression2)

-- expression1 : NULL을 포함하는 칼럼 또는 표현식

-- expression2 : NULL을 대체하는 값

-- 주의 : expression1과 expression2는 반드시 동일한 데이터 타입이어야한다.

-- 부서 테이블의 부서 이름 칼럼에서 "과"글자의 위치를 출력하여라

SELECT * FROM DEPARTMENT;

SELECT DNAME, INSTR(DNAME, '과') FROM DEPARTMENT;

-- 교수테이블에서 직급 칼럼의 왼쪽에 ?*? 묷자를 삽입하여 10바이
-- 트로 춗력하고 교수 아이다 칼럼은 오른쪽에 ?+?묷자를 삽입하여
-- 12바이트로 춗력하여라

SELECT * FROM professor;

SELECT POSITION, LPAD(POSITION, 10, '*') AS LP, USERID, RPAD(USERID, 12, '+') AS RP 
FROM PROFESSOR;

-- 학생 테이블에서 학생 ID의 뒷자리 두글자를 *로 변경,
-- 전화번호 뒷 세자리를 *로 변경
-- ID의 뒷자리 두글자를 *로 변경 >> 자리수 12개로 채우기
SELECT * FROM STUDENT;

SELECT USERID, SUBSTR(USERID, 1, LENGTH(USERID)-2) || '**' AS RESULT1,
RPAD(SUBSTR(USERID, 1, LENGTH(USERID)-2), 12, '*') AS RESULT3, TEL, CONCAT(SUBSTR(TEL, 1, LENGTH(TEL)-3), '***') AS RESULT2
FROM STUDENT;

--  교수 테이블에서 101학과 교수의 일급을 계산(월 근무일은 22일)
-- 하여 소수점 첪째 자리와 셋째 자리에서 젃삭 핚 값과 소숫점 왼
-- 쪽 첪째 자리에서 젃삭핚 값을 춗력하여라

SELECT
    SAL,
    SAL / 22,
    ROUND(SAL / 22),
    ROUND(SAL / 22 , 2),
    ROUND(SAL / 22 , -1)
FROM PROFESSOR
WHERE DEPTNO = 101;

-- 교수 테이블에서 101번 학과 교수의 급여를 보직 수당으로 나눈 나머지 계산하여 출력하라
SELECT
    NAME,
    SAL,
    COMM,
    MOD(SAL, COMM)
FROM PROFESSOR
WHERE DEPTNO = 101;

SELECT TO_DATE('2022', 'YYYY') + 2022 FROM DUAL;

-- 교수 번호가 9908인 교수의 입사일을 기준으로 입사 30일후의 날짜를 출력하여라
SELECT PROFNO, NAME, HIREDATE FROM PROFESSOR;

SELECT PROFNO, NAME, HIREDATE, HIREDATE + 30 AS AFTER30DAYS
FROM PROFESSOR
WHERE PROFNO = 9908;

SELECT SYSDATE() FROM DUAL;

-- 입사일 더하기 17년
-- 입사핚지 120개월 미맊인 교수의 교수번호, 입사일, 입사일로 부
-- 터 혂재일까지의 개월 수, 입사일에서 6개월 후의 날짜를 춗력하여라

SELECT PROFNO, NAME, HIREDATE, ADD_MONTHS(HIREDATE, 19 * 12),
FLOOR(MONTHS_BETWEEN(SYSDATE, ADD_MONTHS(HIREDATE, 19 * 12))) AS "현재까지 개월수" , ADD_MONTHS(ADD_MONTHS(HIREDATE, 19 * 12), 6) AS "입사6개월후"
FROM PROFESSOR
WHERE MONTHS_BETWEEN(SYSDATE, ADD_MONTHS(HIREDATE, 19 * 12)) < 120;

-- 오늘이 속핚 달의 마지막 날짜와 다가오는 일요일의 날짜를 춗력하여라
SELECT 
    SYSDATE,
    LAST_DAY(SYSDATE), 
    NEXT_DAY(SYSDATE, 1),
    NEXT_DAY(TRUNC(SYSDATE + 4) - 1/24, 1)
FROM DUAL;

-- 101번 학과 교수들의 입사일을 일, 월, 년을 기준으로 반올림하여 춗력하여라.

SELECT 
    PROFNO, NAME, HIREDATE,
    TO_CHAR(HIREDATE, 'YYYY/MM/DD HH24:MI:SS') AS HD,
    TO_CHAR(ROUND(HIREDATE, 'HH24'), 'YYYY/MM/DD HH24:MI:SS') AS HDHH,
    TO_CHAR(ROUND(HIREDATE, 'DD'), 'YYYY/MM/DD HH24:MI:SS') AS HDDD,
    TO_CHAR(ROUND(HIREDATE, 'MM'), 'YYYY/MM/DD HH24:MI:SS') AS HDMM,
    TO_CHAR(ROUND(HIREDATE, 'YY'), 'YYYY/MM/DD HH24:MI:SS') AS HDYY
FROM PROFESSOR
WHERE DEPTNO = 101;

SELECT '22-02-02' - 1 FROM DUAL;

INSERT INTO STUDENT(NAME, BIRTHDATE) VALUES ('신생아', SYSDATE);
INSERT INTO STUDENT(NAME, BIRTHDATE) VALUES ('신생아2', '22/02/02');

SELECT * FROM STUDENT;

SELECT SUBSTR(BIRTHDATE, 1, 2) FROM STUDENT;

-- 학생 테이블에서 젂인하 학생의 학번과 생년월일 중에서 년월맊 춗력하여라
SELECT STUDNO, BIRTHDATE, TO_CHAR(BIRTHDATE, 'YYYY/MM')
FROM STUDENT;

-- 학생 테이블에서 102번 학과 학생의 이름, 학년, 생년월일을 춗력하여라
SELECT NAME, GRADE, BIRTHDATE, TO_CHAR(BIRTHDATE, 'YYYY/MON/DAY A.M. MI/SS')
FROM STUDENT
WHERE DEPTNO = 102;

-- 교수 테이블에서 101번 학과 교수의 이름과 입사일을 춗력하여라
SELECT profno, NAME, TO_CHAR(HIREDATE, 'MONTH DDTH YYYY HH24:MI:SS PM DDSPTH') AS RESULT1
FROM PROFESSOR
WHERE DEPTNO = 101;

-- 보직수당을 받는 교수들의 이름, 급여, 보직수당, 그리고 급여와
-- 보직수당을 더핚 값에 12를 곱핚 결과를 연봉으로 춗력하여라

SELECT NAME, SAL, COMM, TO_CHAR((SAL + COMM) * 12, '9,999')
FROM PROFESSOR
WHERE COMM IS NOT NULL;

SELECT TO_CHAR(TO_NUMBER('1,234,567,890', '9,999,999,999') + 1, '9,999,999,999') FROM DUAL;

-- 주민등록번호에서 생년월일을 추춗하여 ?YY/MM/DD? 형태로 춗력하여라
SELECT 
    NAME, IDNUM, 
    TO_DATE(SUBSTR(IDNUM, 1, 6)) AS RESULT1, 
    TO_CHAR(TO_DATE(SUBSTR(IDNUM, 1, 6)), 'YY/MM/DD') AS RESULT2, -- 형태로 출력하여라 해서 이렇게가 정식적 문법
    TO_DATE(SUBSTR(IDNUM, 1, 6), 'YY/MM/DD') AS RESULT3
FROM STUDENT;

-- NVL(P1, P2) : P1 NULL의 가능성이 있는 값 P2: NULL 일 경우 대체 값
-- 201번 학과 교수의 이름, 직급, 급여, 보직수당, 급여와 보직수당
-- 의 합계를 춗력하여라. 단, 보직수당이 NULL인 경우에는 보직수당을 0으로 계산핚다.
SELECT
    DEPTNO,
    NAME, 
    POSITION, 
    SAL,
    COMM,
    NVL(COMM, 0),
    SAL+NVL(COMM, 0)
FROM PROFESSOR
-- WHERE DEPTNO = 201
;

SELECT
    COALESCE(NULL, 1, 2),
    NVL(NVL(NULL, 1), 2)
FROM DUAL;

-- 교수 테이블에서 교수의 소속 학과 번호를 학과 이름으로 변홖하여 춗력하여라. 
-- 학과 번호가 101이면 ?컴퓨터공학과?, 102이면 ?멀티미디어학과?, 201이면 ?젂자공학과?,
-- 나머지 학과 번호는 ?기계공학과?(default)로 변홖핚다.


SELECT
    PROFNO,
    NAME,
    DECODE(DEPTNO, 101, '컴퓨터공학과', 102, '멀티미디어학과',
    201, '전자공학과', '기계공학과') AS DEPTNAME
FROM professor;

-- 학생들의 태어난 달에 따라 계절을 출력 3~5 봄, 6~8 여름...
SELECT
    NAME,
    BIRTHDATE,
    DECODE(TRUNC(TO_CHAR(BIRTHDATE, 'MM') / 3), 1, '봄', 2, '여름', 3, '가을', 0, '겨울','에엥???') AS RESULT1,
    DECODE(TO_CHAR(ADD_MONTHS(BIRTHDATE, -2), 'Q'), 1, '봄', 2, '여름', 3, '가을', '겨울') AS RESULT2
FROM STUDENT;