[ ORACLE ] SUB_QUERY, CREATE AND REPLACE 'VIEW'

SELECT T.연봉 * 2 "연봉두배"

 

FROM

{

        SELECT 연봉

        FROM TBL_EMP

}T;

 

 

--● 문제
-- TBL_SAWON 테이블을 활용하여 다음과 같은 항목들을 조회한다.
-- 사원번호, 사원명, 주민번호, 성별, 현재나이, 입사일,
-- 정년퇴직일, 근무일수, 남은일수 , 급여, 보너스

-- 단, 현재나이는 한국나이 계산법에 따라 연산을 수행한다.
-- 또한 , 정년퇴직일은 해당 직원의 나이가 한국나이로 60 세가 되는 해(년도)의
-- 그 직원의 입사 월, 일로 연산을 수행한다.
-- 그리고 보너스는 1000일 이상 2000일 미만 근무한 사원은
-- 그 사원의 원래 급여 기준 30% 지급
-- 2000일 이상 근무한 사원은 그 사원의 원래 급여 기준 50% 할 수 있도록 처리한다. 

 

 

-- TBL_SAWON 테이블에 존재하는 사원들의 입사일(HIREDATE) 컬럼에서
-- 월, 일만 조회하기
SELECT SANAME, HIREDATE, TO_CHAR(HIREDATE,'MM-DD')
FROM TBL_SAWON;

/*
김서현	2010-11-05	11-05
김아별	1999-08-16	08-16
이유림	2008-02-02	02-02
정주희	2009-07-15	07-15
한혜림	2009-07-15	07-15
이하이	2010-06-05	06-05
아이유	2012-07-13	07-13
정준이	2007-07-08	07-08
이이제	2008-12-10	12-10
선동열	1990-10-10	10-10
선우선	2002-10-10	10-10
선우용녀	1991-11-11	11-11
남주혁	2010-05-05	05-05
남궁선	2012-08-14	08-14
남이	    1990-08-14	08-14
김가영	2001-01-03	01-03*/




SELECT SANAME, HIREDATE, TO_CHAR(HIREDATE,'MM'), TO_CHAR(HIREDATE,'DD')
FROM TBL_SAWON;

/*
김서현	2010-11-05	11	05
김아별	1999-08-16	08	16
이유림	2008-02-02	02	02
정주희	2009-07-15	07	15
한혜림	2009-07-15	07	15
이하이	2010-06-05	06	05
아이유	2012-07-13	07	13
정준이	2007-07-08	07	08
이이제	2008-12-10	12	10
선동열	1990-10-10	10	10
선우선	2002-10-10	10	10
선우용녀	1991-11-11	11	11
남주혁	2010-05-05	05	05
남궁선	2012-08-14	08	14
남이	    1990-08-14	08	14
김가영	2001-01-03	01	03*/



SELECT SANAME, HIREDATE, TO_CHAR(HIREDATE,'MM') || '-' || TO_CHAR(HIREDATE,'DD')
FROM TBL_SAWON;
/*
김서현	    2010-11-05	11-05
김아별	    1999-08-16	08-16
이유림	    2008-02-02	02-02
정주희	    2009-07-15	07-15
한혜림	    2009-07-15	07-15
이하이	    2010-06-05	06-05
아이유	    2012-07-13	07-13
정준이 	    2007-07-08	07-08
이이제	    2008-12-10	12-10
선동열	    1990-10-10	10-10
선우선	    2002-10-10	10-10
선우용녀	    1991-11-11	11-11
남주혁	    2010-05-05	05-05
남궁선	    2012-08-14	08-14
남이	        1990-08-14	08-14
김가영	    2001-01-03	01-03*/


-- 사원번호, 사원명, 주민번호, 성별, 현재나이, 입사일,
-- 정년퇴직일, 근무일수, 남은일수 , 급여, 보너스

-- ⓐ 사원번호, 사원명, 주민번호, 성별, 현재나이, 입사일, 급여
SELECT SANO "사원번호"
    , SANAME "사원명"
    , JUBUN "주민번호"
    , CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','3') THEN '남성'
             WHEN SUBSTR(JUBUN,7,1) IN ('2','4') THEN '여성'
             ELSE '성별확인불가'
      END "성별"
    , CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN EXTRACT(YEAR FROM SYSDATE)-(TO_NUMBER(SUBSTR(JUBUN,1,2))+1899)
           WHEN SUBSTR(JUBUN,7,1) IN ('3','4') THEN EXTRACT(YEAR FROM SYSDATE)-(TO_NUMBER(SUBSTR(JUBUN,1,2))+1999)
           ELSE 0
      END "현재나이"
      , HIREDATE "입사일"
      , SAL "급여"
   
FROM TBL_SAWON;



--==========================★★★★★★★★★★★★★★★★★★★★================================================================================================
CREATE OR REPLACE VIEW VIEW_SAWON
AS

SELECT T.사원번호, T.사원명, T.주민번호 , T.성별, T.현재나이, T.입사일
--정년퇴직일
--정년퇴직년도 = 해당 직원의 나이가 한국나이로 60세가 되는 해
--현재나이가 58세면 2년 후 정년퇴직. 현재 2021년이면 2023년이 될 것
-- ADD_MONTHS(SYSDATE, 남은년수*12)
-- 남은년수 = 60-현재나이
-- TO_CHAR(ADD_MONTHS(SYSDATE, (60-현재나이)*12), 'YYYY')
-- TO_CHAR(입사일, 'MM-DD')
--TO_CHAR(ADD_MONTHS(SYSDATE, (60-현재나이)*12), 'YYYY') || '-' || TO_CHAR(입사일, 'MM-DD') "정년퇴직일"
    ,  TO_CHAR(ADD_MONTHS(SYSDATE, (60-T.현재나이)*12), 'YYYY') || '-' || TO_CHAR(T.입사일, 'MM-DD') "정년퇴직일"



--근무일수(현재날짜 - 입사일)
--SYSDATE - T.입사일



    ,  TRUNC(SYSDATE - T.입사일) "근무일수"
    
-- 남은일수 = 정년퇴직일 - 현재일
-- TO_DATE(정년퇴직일문자열, 'YYYY-MM-DD')-SYSDATE  이후 TRUNC() 해줌
    , TRUNC(TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, (60-T.현재나이)*12), 'YYYY') || '-' || TO_CHAR(T.입사일, 'MM-DD') ,'YYYY-MM-DD') - SYSDATE) "남은일수"
    , T.급여
    
    -- 보너스(근무일수가 1000일 이상 2000일 미만이면 급여의 30% , 근무일수가 2000일 이상이면 급여의 50%, 나머지 경우에는 0)
    , CASE WHEN TRUNC(SYSDATE - T.입사일)>=2000 THEN T.급여 * 0.5
           WHEN TRUNC(SYSDATE - T.입사일)>=1000 THEN T.급여 * 0.3
           ELSE 0
      END "보너스"
FROM
(
    SELECT SANO "사원번호"
        , SANAME "사원명"
        , JUBUN "주민번호"
        , CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','3') THEN '남성'
                 WHEN SUBSTR(JUBUN,7,1) IN ('2','4') THEN '여성'
                 ELSE '성별확인불가'
          END "성별"
        , CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN EXTRACT(YEAR FROM SYSDATE)-(TO_NUMBER(SUBSTR(JUBUN,1,2))+1899)
               WHEN SUBSTR(JUBUN,7,1) IN ('3','4') THEN EXTRACT(YEAR FROM SYSDATE)-(TO_NUMBER(SUBSTR(JUBUN,1,2))+1999)
               ELSE 0
          END "현재나이"
          , HIREDATE "입사일"
          , SAL "급여"
       
    FROM TBL_SAWON
)T
ORDER BY T.사원번호;


--==================================★★★★★★★★★★★★★★★★★★★★★★===================================================================================


--===>>>View VIEW_SAWON이(가) 생성되었습니다.

-- 상기 내용에서 특정 근무일수의 사원을 확인해야 한다거나
-- 특정 보너스 금액을 받는 사원을 확인해야 할 경우가 생길 수 있다,
-- 이와 같은 경우... 해당 쿼리문을 다시 구성하는 번거로움을 줄일 수 있도록 
-- 뷰(VIEW) 를 만들어 저장해둘 수 있다.(CREATE OR REPLACE VIEW VIEW_SAWON  AS)
-- (CREATE 가 아니라 CREATE OR REPLACE 인 이유? 덮어쓰기가 지원됨. )

SELECT *
FROM VIEW_SAWON; --(만약 VIEW의 원본이 되는 TBL_SAWON 이 변경된다면 VIEW 에서도 확인 이 가능하다)
--==>> 1001	김가영	9402252234567	여성	28	2001-01-03	2053-01-03	7391	11601	3000	1500


SELECT *
FROM TBL_SAWON;
--==>> 1001	김가영	9402252234567	2001-01-03	3000

-- ●VIEW 생성 이후 데이터 변경
UPDATE TBL_SAWON
SET HIREDATE=SYSDATE, SAL=100
WHERE SANO = 1001;
--==>>1 행 이(가) 업데이트되었습니다.



SELECT *
FROM VIEW_SAWON; --(만약 VIEW의 원본이 되는 TBL_SAWON 이 변경된다면 VIEW 에서도 확인 이 가능하다)
--==>> 1001	김가영	9402252234567	여성	28	2021-03-30	2053-03-30	0	11687	100	0


SELECT *
FROM TBL_SAWON
ORDER BY SANO;
--==>> 1001	김가영	9402252234567	2021-03-30	100