데이터베이스#4

-- 시스템의 현재 날짜를 출력하여라
select sysdate from dual;

--  입사한지 120개월 미만인 교수의 교수번호, 입사일, 입사일로 부터 현재일까지의 개월 수, 입사일에서 6개월 후의 날짜를 출력하여라
select profno
, hiredate
, trunc(months_between(Sysdate, hiredate)) as months
, add_months(hiredate, 6) 
from professor 
where months_between(sysdate, hiredate) <360;

-- 오늘이 속한 달의 마지막 날짜와 다가오는 일요일의 날짜를 출력하여라
select LAST_DAY(sysdate), NEXT_DAY(sysdate, 4) from dual;

--ROUND, TRUNC
select to_char(sysdate + 3/24, 'YY/MM/DD HH24:MI:SS')
, TO_CHAR(ROUND(SYSDATE +3 /24), 'YY/MM/DD HH24:MI:SS')--정오를 기준으로 시간에서 날짜로 반올림한다.
, TO_CHAR(TRUNC(SYSDATE +3 /24), 'YY/MM/DD HH24:MI:SS')--시간자체를 절삭(당일만 출력)
FROM DUAL;

SELECT ROUND(TO_DATE('231224', 'YYMMDD'),'MM') FROM DUAL;--월을 기준으로 반올림

-- 101번 학과 교수들의 입사일을 일, 월, 년을 기준으로 반올림하여 출력하여라
SELECT HIREDATE
, TO_CHAR(HIREDATE, 'YY/MM/DD HH24:MI:SS')
, TO_CHAR(ROUND(HIREDATE, 'DD'), 'YY/MM/DD HH24:MI:SS') AS 일
, TO_CHAR(ROUND(HIREDATE, 'MM'), 'YY/MM/DD HH24:MI:SS') AS 월 
, TO_CHAR(ROUND(HIREDATE, 'YY'), 'YY/MM/DD HH24:MI:SS') AS 년
FROM professor
WHERE DEPTNO = '101';

-- 학생 테이블에서 전인하 학생의 학번과 생년월일 중에서 년월만 출력하여라
SELECT NAME, TO_CHAR(birthdate, 'YY/MM')  FROM STUDENT WHERE NAME='전인하';

-- 학생 테이블에서 102번 학과 학생의 이름, 학년, 생년월일을 출력하여라
SELECT NAME, GRADE, TO_CHAR(BIRTHDATE, 'DAY MONTH DD, YYYY') FROM STUDENT WHERE DEPTNO=102;

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

-- 교수 테이블에서 101번 학과 교수들의 이름, 직급, 입사일을 출력하여라
SELECT NAME, POSITION, TO_CHAR(HIREDATE, 'MONTH "THE" DDTH "OF" YYYY') FROM PROFESSOR WHERE DEPTNO =101;

-- 보직수당을 받는 교수들의 이름, 급여, 보직수당, 그리고 급여와 보직수당을 더한 값에 12를 곱한 결과를 연봉으로 출력하여라.
SELECT NAME, SAL, COMM, TO_CHAR((SAL+COMM)*12, '9,999') FROM PROFESSOR WHERE COMM IS NOT NULL;

-- 주민등록번호에서 생년월일을 추출하여'YY/MM/DD' 형태로 출력하여라.
SELECT IDNUM, TO_CHAR(TO_DATE(SUBSTR(IDNUM,1,6),'YYMMDD'),'YY/MM/DD') FROM STUDENT;

-- 201번 학과 교수의 이름, 직급, 급여, 보직수당, 급여와 보직수당의 합계를 출력하여라. 단, 보직수당이 NULL인 경우에는 보직수당을 0으로 계산한다.
SELECT * FROM PROFESSOR;
SELECT NAME, POSITION, SAL, COMM,
SAL+NVL(COMM,0),
NVL(SAL+COMM, SAL),
NVL2(COMM,SAL+COMM,SAL)
FROM PROFESSOR WHERE DEPTNO=102;

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

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

--학생테이블에서 생년월일 기준으로 탄생월 추출하여 이름, 탄생월, 탄생 계절 조회하시오
SELECT * FROM STUDENT;
SELECT NAME
, TO_CHAR(BIRTHDATE, 'MM') AS "탄생월"
, ADD_MONTHS(BIRTHDATE, -2) AS "ADD_MONTH"
, DECODE(TO_CHAR(ADD_MONTHS(BIRTHDATE, -2),'Q'), 1,'봄', 2, '여름', 3, '가을', 4, '겨울') AS "ADD_MONTH"
, DECODE(TO_CHAR(TO_DATE(SUBSTR(IDNUM,3,2),'MM'),'Q'), 1,'봄', 2, '여름', 3, '가을', 4, '겨울') AS "탄생계절"
, TRUNC(TO_CHAR(BIRTHDATE, 'MM')/3)
, DECODE(TRUNC(TO_CHAR(BIRTHDATE, 'MM')/3),1,'봄', 2, '여름', 3, '가을', '겨울')
FROM STUDENT;

-- 교수 테이블에서 소속 학과에 따라 보너스를 다르게 계산하여 출력하여라. 학과 번호별로 보너스는 다음과 같이 계산한다. 
-- 학과 번호가 101이면 보너스는 급여의 10%, 102이면 20%, 201이 면 30%, 나머지 학과는 0%이다.
SELECT PROFNO, NAME, DEPTNO, SAL
, DECODE(DEPTNO, 101, SAL*1.1, 102, SAL*1.2, 201, SAL*1.3, SAL) AS DECODE

, CASE DEPTNO
    WHEN 101 THEN SAL*.1
    WHEN 102 THEN SAL*.2
    WHEN 201 THEN SAL*.3
    ELSE 0
END AS CASE문
, CASE 
    WHEN DEPTNO = 101 THEN SAL*1.1
    WHEN DEPTNO = 102 THEN SAL*1.2
    WHEN DEPTNO = 201 THEN SAL*1.3
    ELSE SAL
END AS CASE문VER2
FROM PROFESSOR;

SELECT * FROM STUDENT;
SELECT NAME, BIRTHDATE
, TO_CHAR(BIRTHDATE, 'MM') AS TOCHAR
, TO_NUMBER(TO_CHAR(BIRTHDATE, 'MM')) AS  TONUMBER
, CASE 
    WHEN TO_NUMBER(TO_CHAR(BIRTHDATE, 'MM'))>=3 AND TO_NUMBER(TO_CHAR(BIRTHDATE, 'MM'))<=5 THEN '봄'
    WHEN TO_NUMBER(TO_CHAR(BIRTHDATE, 'MM'))>=6 AND TO_NUMBER(TO_CHAR(BIRTHDATE, 'MM'))<=8 THEN '여름'
    WHEN TO_NUMBER(TO_CHAR(BIRTHDATE, 'MM'))>=9 AND TO_NUMBER(TO_CHAR(BIRTHDATE, 'MM'))<=11 THEN '가을'
    ELSE '겨울'
END AS A

, CASE TO_NUMBER(TO_CHAR(ADD_MONTHS(BIRTHDATE, -2),'Q'))
    WHEN 1 THEN '봄'
    WHEN 2 THEN '여름'
    WHEN 3 THEN '가을'
    ELSE '겨울'
END AS B
, CASE
    WHEN TO_CHAR(BIRTHDATE, 'MM') BETWEEN 3 AND 5 THEN '봄'
    WHEN TO_CHAR(BIRTHDATE, 'MM') IN(6,7,8)THEN '여름'
    WHEN TO_NUMBER(TO_CHAR(BIRTHDATE, 'MM'))>=9 AND TO_NUMBER(TO_CHAR(BIRTHDATE, 'MM'))<=11 THEN '가을'
    ELSE '겨울'
END AS C
FROM STUDENT;
SELECT 
    CASE
        WHEN TO_CHAR(BIRTHDATE, 'MM') BETWEEN 3 AND 5 THEN '봄'
        WHEN TO_CHAR(BIRTHDATE, 'MM') IN(6,7,8)THEN '여름'
        WHEN TO_NUMBER(TO_CHAR(BIRTHDATE, 'MM'))>=9 AND TO_NUMBER(TO_CHAR(BIRTHDATE, 'MM'))<=11 THEN '가을'
        ELSE '겨울'
    END AS MONTH
FROM(
    SELECT STUDENT.*, TO_NUMBER(TO_CHAR(BIRTHDATE, 'MM')) AS MON FROM STUDENT
);