6일차(2018.01.10)

날짜 연산

SELECT USER
FROM DUAL;
--==>>SCOTT

문제

-- 현재 시간과, 1일 2시간 3분 4초 후를 조회(출력)한다.

-----------------------------------------------------

-- 현재 시간 | 연산 후 시간

-----------------------------------------------------

-- 2018-01-09 16:50:52 | 2018-01-10 18:53:56

-----------------------------------------------------

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
--==>>Session이(가) 변경되었습니다.

-- 소희풀이

SELECT SYSDATE, SYSDATE+1+(2/24)+(3/(60*24))+(4/(60*60*24))
FROM DUAL;

-- 강사님 풀이

-- 방법 1. 각각을 연산하여 구성

SELECT SYSDATE "현재시간"
,SYSDATE + 1 + (2/24) + (3/(24*60)) + (4/(24*60*60)) "연산 후 시간"
FROM DUAL;
--==>>2018-01-10 09:09:21	2018-01-11 11:12:25

-- 방법 2. 초단위로 구성

SELECT SYSDATE "현재시간"
,SYSDATE + ((24*60*60) + (2*60*60) + (3*60) + 4) / (24*60*60) "연산 후 시간"
FROM DUAL;
--==>>2018-01-10 09:09:32	2018-01-11 11:12:36

--※ 날짜 - 날짜

-- 2018년 1월 10일 - 2018년 1월 9일 = 1일

SELECT TO_DATE('2018-01-10', 'YYYY-MM-DD') - TO_DATE('2018-01-09','YYYY-MM-DD')
FROM DUAL;
--==>> 1

-- 유효한 날짜 인지 내부적으로 확인

SELECT TO_DATE('2018-01-32','YYYY-MM-DD')
FROM DUAL;
--==>> ERROR

SELECT TO_DATE('2018-13-30','YYYY-MM-DD')
FROM DUAL;
--==>> ERROR

--※ TO_DATE() 함수를 통해 문자 타입을 날짜 타입으로 변환을 진행할 때 내부적으로 해당 날짜에 대한 유효성 검사가 이루어진다.

-- 유효하지 않은 날짜로 변환을 진행하게 되면 에러 발생한다.

날짜 함수

ADD_MONTHS()

--> 개월 수를 더해주는 함수

SELECT SYSDATE, ADD_MONTHS(SYSDATE, 2), ADD_MONTHS(SYSDATE, -2)
FROM DUAL;
--==>>2018-01-10 09:17:23
--    2018-03-10 09:17:23
--    2017-11-10 09:17:23

MONTHS_BETWEEN()

--> 첫 번째 인자값에서 두 번째 인자값을 뺀 개월 수

SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2002-05-31','YYYY-MM-DD'))
FROM DUAL;
--==>> 187.335170997610513739545997610513739546

--> 개월 수의 차이

--※ 결과값의 부호가 『-』 일 경우에는

-- 첫 번째 매개변수에 해당하는 날짜보다

-- 두 번째 매개변수에 해당하는 날짜가 『미래』라는 의미이다.

SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2018-01-19','YYYY-MM-DD'))
FROM DUAL;
--==>> -0.2776840651135005973715651135005973715651

-- 교육과정 종료 후 실무 투입 일자가 현재보다 더 미래다.

NEXT_DAY()

--> 첫 번째 매개변수에서(기준) 돌아오는 가장 빠른 요일 변환

SELECT NEXT_DAY(SYSDATE, '토')
FROM DUAL;
--==>> 2018-01-13 09:27:48

SELECT NEXT_DAY('1993-02-27', 'SAT')
FROM DUAL;
--==>> 1993-03-06 00:00:00

--※ 추가 세션 설정

ALTER SESSION SET NLS_DATE_LANGUAGE = 'ENGLISH';
--==>> Session이(가) 변경되었습니다.

ALTER SESSION SET NLS_DATE_LANGUAGE = 'KOREAN';
--==>> Session이(가) 변경되었습니다.

LAST_DAY()

매개변수의 해당 날짜가 포함되어 있는 그 달의 마지막 날을 반환한다.

SELECT LAST_DAY(SYSDATE), LAST_DAY(TO_DATE('2020-02-05', 'YYYY-MM-DD'))
FROM DUAL;
--==>> 2018-01-31 09:34:03	2020-02-29 00:00:00

--○ 퀴즈

오늘부로... 김선규 사원이... 군대에 또 끌려(?)간다...복무 기간은 22개월로 한다.

1. 전역 일자를 조회한다.

SELECT ADD_MONTHS(SYSDATE, 22)"전역일자"
FROM DUAL;
--==>>2018-01-10 09:37:19	2019-11-10 09:37:19

2. 하루 세 끼 식사를 한다고 가정하면

김선규 사원은... 도데체 몇 끼를 먹어야 집에 보내줄까...

복무 기간 * 3

---------

(전역일 - 현재일)

(전역일 - 현재일) * 3

(ADD_MONTHS(SYSDATE, 22) - SYSDATE) * 3

SELECT (ADD_MONTHS(SYSDATE, 22) - SYSDATE) * 3"밥 수"
FROM DUAL;

--==>> 2007

SELECT SYSDATE
FROM DUAL;

--○ 퀴즈

현재 시간으로부터 올해 성탄절 오후 6시 (2018-12-25 18:00:00) 까지 남은 기간을 다음과 같이 조회할 수 있도록 한다.

/*
--------------------------------------------------------------------------------
현재시간     |        성탄절         |    일   |   시간   |  분  |   초
--------------------------------------------------------------------------------
2018-01-10 09:48:51 |  2018-12-25 18:00:00  |    320  |    8     |  12  |   7
--------------------------------------------------------------------------------
*/

-- 소희 풀이

SELECT SYSDATE"현재시간"
, TO_DATE('2018-12-25 18:00:00','YYYY-MM-DD HH24:MI:SS')"성탄절"
, ROUND(TO_DATE('2018-12-25','YYYY-MM-DD') -SYSDATE)"일"
, SYSDATE + 349
, (TO_DATE('2018-12-25','YYYY-MM-DD')-ROUND(TO_DATE('2018-12-25','YYYY-MM-DD')))"시간"
, SYSDATE"분"
, SYSDATE"초"
FROM DUAL;

-- 강사님 풀이

-- SELECT SYSDATE"현재시각", TO_DATE('2018-12-25 18:00:00')

--『93784초』를...『일, 시간, 분, 초』로 환산하면...

SELECT MOD(93784, 60)
FROM DUAL;
--==>> 4

SELECT MOD(TRUNC(93784/60), 60)
FROM DUAL;
--==>> 3

SELECT TRUNC(TRUNC(TRUNC(93784/60)/60)/24)  -- 일로 편입완료
,MOD(TRUNC(TRUNC(93784/60)/60), 24)   -- 일로 편입되지 못하는 시간~!!
,MOD(TRUNC(93784/60), 60)             -- 시간으로 편입되지 못하는 분~!!
,MOD(93784, 60)                       -- 분으로 편입되지 못하는 초~!!
FROM DUAL;
--==>> 1	2	3	4

-- 현재일로부터 성탄절까지 남은 일수 계산
-- 성탄일 - 현재일
-- 성탄일

SELECT TO_DATE('2018-12-25 18:00:00','YYYY-MM-DD HH24:MI:SS')
FROM DUAL;
--==>> 2018-12-25 18:00:00

-- 현재일

SELECT SYSDATE
FROM DUAL;
--==>> 2018-01-10 10:29:34

SELECT TO_DATE('2018-12-25 18:00:00','YYYY-MM-DD HH24:MI:SS') - SYSDATE
FROM DUAL;
--==>> 349.312256944444444444444444444444444444

-- 일수 기반의 남을 일자를... 초로 환산
SELECT 환산할일수 * (24*60*60)
FROM DUAL;

-- 전체 총 초
SELECT (TO_DATE('2018-12-25 18:00:00','YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24*60*60)
FROM DUAL;
--==>> 30180463.00000000000000000000000000000001

SELECT SYSDATE"현재시간"
,TO_DATE('2018-12-25 18:00:00','YYYY-MM-DD HH24:MI:SS')"성탄절"
,TRUNC(TRUNC(TRUNC((TO_DATE('2018-12-25 18:00:00','YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24*60*60)/60)/60)/24)"일"  -- 일로 편입완료
,MOD(TRUNC(TRUNC((TO_DATE('2018-12-25 18:00:00','YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24*60*60)/60)/60), 24)"시간" -- 일로 편입되지 못하는 시간~!!
,MOD(TRUNC((TO_DATE('2018-12-25 18:00:00','YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24*60*60)/60), 60)"분"             -- 시간으로 편입되지 못하는 분~!!
,TRUNC(MOD((TO_DATE('2018-12-25 18:00:00','YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24*60*60), 60))"초"                -- 분으로 편입되지 못하는 초~!!
FROM DUAL;
--==>> 2018-01-10 10:35:22  |	2018-12-25 18:00:00	| 349 |	7 |	24 | 37

--※ 날짜에도 반올림, 절삭 등을 수행할 수 있다.

날짜 반올림

SELECT SYSDATE                  -- 2018-01-10 10:39:13
,ROUND(SYSDATE,'YEAR')    -- 2018-01-01 00:00:00 -- 년도까지 유효한 데이터. 상반기/하반기냐 기준
,ROUND(SYSDATE,'MONTH')   -- 2018-01-01 00:00:00 -- 월까지 유효한 데이터. 15일 기준
,ROUND(SYSDATE,'DD')      -- 2018-01-10 00:00:00 -- 날짜까지 유효한 데이터. 정오 기준(12시)
,ROUND(SYSDATE,'DAY')     -- 2018-01-07 00:00:00 -- 주(7일) 기준 (수요일 정오). 일요일로
FROM DUAL;

SELECT SYSDATE                  -- 2018-01-10 10:46:34
,TRUNC(SYSDATE,'YEAR')    -- 2018-01-01 00:00:00 -- 년도까지 유효한 데이터. 상반기/하반기냐 기준
,TRUNC(SYSDATE,'MONTH')   -- 2018-01-01 00:00:00 -- 월까지 유효한 데이터. 15일 기준
,TRUNC(SYSDATE,'DD')      -- 2018-01-10 00:00:00 -- 날짜까지 유효한 데이터. 정오 기준(12시)
,TRUNC(SYSDATE,'DAY')     -- 2018-01-07 00:00:00 -- 주(7일) 기준 (수요일 정오). 일요일로
FROM DUAL;

변환 함수

TO_CHAR() : 숫자나 날짜를 문자로 변환시켜주는 함수

TO_DATE() : 문자(날짜 형식에 맞게 구성된...)를 날짜로 변환시켜주는 함수

TO_NUMBER(): 문자(숫자 형식에 맞게 구성된...)를 숫자로 변환시켜주는 함수

--※ 테스트를 진행하기 전 세션 설정 통일

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';
--==>> Session이(가) 변경되었습니다.

ALTER SESSION SET NLS_DATE_LANGUAGE='KOREAN';
--==>> Session이(가) 변경되었습니다.

ALTER SESSION SET NLS_LANGUAGE='KOREAN';
--==>> Session이(가) 변경되었습니다.

ALTER SESSION SET NLS_CURRENCY='\';
--==>> Session이(가) 변경되었습니다.

SELECT TO_CHAR(SYSDATE)             -- 2018-01-10
,TO_CHAR(SYSDATE, 'YYYY')     -- 2018
,TO_CHAR(SYSDATE, 'YEAR')     -- TWENTY EIGHTEEN
,TO_CHAR(SYSDATE, 'MM')       -- 01
,TO_CHAR(SYSDATE, 'MONTH')    -- 1월     (=MON)
,TO_CHAR(SYSDATE, 'MON')      -- 1월
,TO_CHAR(SYSDATE, 'DD')       -- 10
,TO_CHAR(SYSDATE, 'DAY')      -- 수요일
,TO_CHAR(SYSDATE, 'HH24')     -- 10      (=24시간제)
,TO_CHAR(SYSDATE, 'HH')       -- 10      (=12시간제)
,TO_CHAR(SYSDATE, 'HH AM')    -- 10 오전 (= HH PM)
,TO_CHAR(SYSDATE, 'HH PM')    -- 10 오전
,TO_CHAR(SYSDATE, 'MI')       -- 54
,TO_CHAR(SYSDATE, 'SS')       -- 44
,TO_CHAR(SYSDATE, 'SSSSS')    -- 39284   (= 금일 0시 0분 0초부터 흘러온 전체 초)
,TO_CHAR(SYSDATE, 'Q')        -- 1       (= 분기)
FROM DUAL;

EXTRACT()

-- 날짜에서 특정 항목을 숫자 기반으로 추출하는 함수

SELECT TO_CHAR(TO_DATE('2020-05-05', 'YYYY-MM-DD'), 'YYYY') - TO_CHAR(SYSDATE, 'YYYY')
FROM DUAL;

SELECT TO_CHAR(SYSDATE, 'YYYY')     -- 2018 문자형(연도를 추출하여 문자로)
,TO_CHAR(SYSDATE, 'MM')       -- 01   문자형(월를 추출하여 문자로)
,TO_CHAR(SYSDATE, 'DD')       -- 10   문자형(일를 추출하여 문자로)
,EXTRACT(YEAR FROM SYSDATE)   -- 2018 숫자형(연도를 추출하여 숫자로)
,EXTRACT(MONTH FROM SYSDATE)  -- 1    숫자형(월를 추출하여 숫자로)
,EXTRACT(DAY FROM SYSDATE)    -- 10   숫자형(일를 추출하여 숫자로)
FROM DUAL;

--※ 년, 월, 일 이하 다른 것은 불가~!!

형식 맞춤 표기(TO_CHAR())

SELECT 60000                      -- 60000
, TO_CHAR(60000,'99,999')    -- 60,000
, TO_CHAR(60000,'$99,999')   -- $60,000
, TO_CHAR(60000,'L99,999')   -- \60,000 통화기호를 위해 넓게 확보
, LTRIM(TO_CHAR(60000,'L99,999'))
FROM DUAL;

--※ 왼쪽 공백 없애기

-- LTRIM() 함수는 두 번째 인자값을 사용하지 않으면(생략) 왼쪽 공백 제거 기능을 갖는 함수로 사용할 수 있다.

-- 현재 시간을 기준으로 1일 2시간 3분 4초 후를 조회한다.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

SELECT SYSDATE"현재시간"
,SYSDATE + 1 +(2/24) + (3/(24*60)) + (4/(24*60*60))"연산시간"
FROM DUAL;
--==>> 2018-01-10 11:36:41	2018-01-11 13:39:45

-- 현재 시간을 기준으로 1년 2개월 3일 4시간 5분 6초 후를 조회한다.

-- TO_YMINTERVAL(), TO_DSINTERVAL()
SELECT SYSDATE"현재시간"
,SYSDATE + TO_YMINTERVAL('01-02') + TO_DSINTERVAL('003 04:05:06')
FROM DUAL;
--==>> 2018-01-10 11:38:49	2019-03-13 15:43:55

CASE 문 (조건문, 반복문)

CASE 상황

WHEN 상황이 맞다면

THEN 이렇게 처리해

ELSE 맞는 상황이 없으면 이렇게 처리해

END

SELECT CASE 5-2 WHEN 1 THEN '5-2=1' ELSE '5-2는 몰라요' END
FROM DUAL;
--==>> 5-2는 몰라요

--> 전제상황이 같을 떄

SELECT CASE WHEN 5-2=1 THEN '5-2=1'
WHEN 5-2=2 THEN '5-2=2'
WHEN 5-2=3 THEN '5-2=3'
ELSE '난 산수를 몰라요'
END
FROM DUAL;
--==>> 5-2=3

--> 전제상황이 다를 때, TRUE가 나오면 다음 조건은 보지않음

DECODE(조건, 값, 처리, 값, 처리, 값, 처리, 처리)

SELECT DECODE(5-2, 1, '일이야', 2, '이야', 3, '삼이야','몰라몰라')
FROM DUAL;
--==>> 삼이야

SELECT *
FROM TBL_SAWON;

문제

-- TBL_SAWON 테이블을 대상으로 다음과 같이 조회할 수 있도록 한다.

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

-- 단, 현재나이는 한국나이 계산법에 따라 연산을 수행한다.

-- 또한, 정년퇴직일은 해당 직원의 나이가 한국나이로 60세가 되는 해의 그 직원의 입사 월, 일로 한다.

-- 그리고 보너스는 1000일이상 2000일미만 근무한 사원은 그 사원이 받는 급여의 30%로 하고

-- 2000일 이상 근무한 사원은 그 사원이 받는 급여의 50% 한다.

INSERT INTO TBL_SAWON VALUES(1000, '한예슬', '7502132234567', TO_DATE('1995-08-15','YYYY-MM-DD'), 5000000);
--==>> 1 행 이(가) 삽입되었습니다.

COMMIT;
--==>>커밋 완료.

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';
--==>> Session이(가) 변경되었습니다.

소희 풀이

SELECT SANO"사원번호", SANAME"사원명", JUBUN"주민번호"
     , DECODE(SUBSTR(JUBUN, 7, 1),'1','남','3','남','2','여','4','여')"성별"
     , EXTRACT(YEAR FROM SYSDATE)-DECODE(SUBSTR(JUBUN, 7, 1),'3',CONCAT('20',SUBSTR(JUBUN, 1,2)),'4',CONCAT('20',SUBSTR(JUBUN, 1,2)),'1',CONCAT('19',SUBSTR(JUBUN, 1,2)),'2',CONCAT('19',SUBSTR(JUBUN, 1,2)))+1"현재나이"
     , HIREDATE"입사일"
     , EXTRACT(YEAR FROM (SYSDATE+TO_YMINTERVAL(TO_CHAR(60-(EXTRACT(YEAR FROM SYSDATE)-DECODE(SUBSTR(JUBUN, 7, 1),'3',CONCAT('20',SUBSTR(JUBUN, 1,2)),'4',CONCAT('20',SUBSTR(JUBUN, 1,2)),CONCAT('19',SUBSTR(JUBUN, 1,2)))+1))||'-00')))||'-'||TO_CHAR(HIREDATE, 'MM-DD')"정년퇴직일"
     , TRUNC(SYSDATE-HIREDATE)"근무일수"
     , TRUNC(TO_DATE(EXTRACT(YEAR FROM (SYSDATE+TO_YMINTERVAL(TO_CHAR(60-(EXTRACT(YEAR FROM SYSDATE)-DECODE(SUBSTR(JUBUN, 7, 1),'3',CONCAT('20',SUBSTR(JUBUN, 1,2)),'4',CONCAT('20',SUBSTR(JUBUN, 1,2)),CONCAT('19',SUBSTR(JUBUN, 1,2)))+1))||'-00')))||'-'||TO_CHAR(HIREDATE, 'MM-DD'))-SYSDATE)"남은일수"
     , SAL"급여"
     , CASE WHEN TRUNC(SYSDATE - HIREDATE) >= 2000 THEN SAL*0.5
            WHEN TRUNC(SYSDATE - HIREDATE) >= 1000 THEN SAL*0.3
            ELSE 0  
       END"보너스"
FROM TBL_SAWON;

강사님 풀이

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

SELECT T.사원번호, T.사원명, T.주민번호, T.성별, T.현재나이, T.입사일

-- 정년퇴직일 → 해당 직원의 나이가 한국나이로 60세가 되는 해 || 입사월일
-- 현재나이가 58세 ...  2년 후        2018   2020
-- 현재나이가 35세 ... 25년 후        2018   2043

-- ADD_MONTHS(SYSDATE, (남은년수)*12)
--                     --------
--                     (60 - 현재나이)
-- ADD_MONTHS(SYSDATE, (60 - 현재나이)*12)

-- TO_CHAR(ADD_MONTHS(SYSDATE, (60 - 현재나이)*12), 'YYYY') || '-' || TO_CHAR(HIREDATE, 'MM-DD')
, TO_CHAR(ADD_MONTHS(SYSDATE, (60-T.현재나이)*12), 'YYYY') || '-' || TO_CHAR(T.입사일, 'MM-DD')"정년퇴직일"

-- 근무일수 → 현재일 - 입사일
, TRUNC(SYSDATE - T.입사일)"근무일수"

-- 남은일수 → 정년퇴직일 - 현재일
, TRUNC(TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, (60-T.현재나이)*12), 'YYYY') || '-' || TO_CHAR(T.입사일, 'MM-DD')) - SYSDATE)"남은일수"
, T.급여"급여"

-- 보너스

-- 근무일수가 1000일 이상 2000일 미만    급여*0.3

-- 근무일수가 2000일 이상                급여*0.5
, 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"주민번호"
-- 성별 → 주민번호 7번째 자리 확인을 통한 분기
, CASE WHEN SUBSTR(JUBUN, 7, 1) IN ('1', '3') THEN '남자'
WHEN SUBSTR(JUBUN, 7, 1) IN ('2', '4') THEN '여자'
ELSE '성별확인불가'
END"성별"

-- 현재나이 → 현재년도 - 태어난년도 + 1
--                        (1900년대생 / 2000년대생)
--                        주민번호 7번째 자리 확인을 통한 분기

, 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;
--==>>
/*
1001	명소희	  9507262234567	여자	24	2017-03-02	2052-03-02	314	12469	4000000	      0
1002	임미영	  9311092234567	여자	26	2017-03-02	2052-03-02	314	12469	4000000	      0
1003	조태희	  9110271234567	남자	29	2017-03-02	2049-03-02	314	11373	4000000	      0
1004	김선규	  9212231234567	남자	27	2017-11-13	2051-11-13	  58	12359	4000000	      0
1005	서운성	  9307251234567	남자	26	2017-11-13	2052-11-13	  58	12725	4000000	      0
1006	박기범	  8002271234567	남자	29	2017-11-13	2049-11-13	  58	11629	4000000	      0
1007	최진규	  9405071234567	남자	28	2017-11-13	2050-11-13	  58	11994	4000000	      0
1008	김준협	  9211101234567	남자	27	2017-11-13	2051-11-13	  58	12359	4000000	      0
1009	정승우	  9604211234567	남자	26	2017-11-13	2052-11-13	  58	12725	4000000	      0
1010	오승우	  9812271234567	남자	28	2017-11-13	2050-11-13	  58	11994	4000000	      0
1011	선동렬	  0212273234567	남자	17	2018-01-02	2061-01-02	   8	15697	2000000	      0
1012	선우용녀  0301024234567	여자	16	2018-01-02	2062-01-02	   8	16062	2000000	      0
1013	선우선	  0405064234567	여자	15	2018-01-02	2063-01-02	   8	16427	2000000	      0
1014	스윙스	  0505053234567	남자	14	2018-01-02	2064-01-02	   8	16792	2000000	      0
1000	한예슬	  7502132234567	여자	44	1995-08-15	2034-08-15	8184	6060	5000000	2500000
*/

SELECT A.사원번호, A.사원명, A.주민번호, A.성별, A.현재나이, A.입사일
, A.정년퇴직일, A.근무일수
, TRUNC(TO_DATE(A.정년퇴직일, 'YYYY-MM-DD') - SYSDATE)"남은일수"
, A.급여
, CASE WHEN A.근무일수 >= 2000 THEN A.급여*0.5
WHEN A.근무일수 >= 1000 THEN A.급여*0.3
ELSE 0
END"보너스"
FROM
(
SELECT T.사원번호, T.사원명, T.주민번호, T.성별, T.현재나이, T.입사일
, TO_CHAR(ADD_MONTHS(SYSDATE, (60-T.현재나이)*12), 'YYYY')
|| '-' || TO_CHAR(T.입사일, 'MM-DD')"정년퇴직일"
, TRUNC(SYSDATE-T.입사일)"근무일수"
, T.급여
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
) A;

뷰(View)

-- 상기 내용에서 특정 근무일수의 사원을 확인한다거나

-- 특정 보너스 금액을 받는 사원을 확인해야 하는 등...

-- 추가적인 업무가 발생할 수 있다.

-- 이와 같은 경우는... 뷰(view)를 만들어 저장해둘 수 있다.

CREATE OR REPLACE VIEW VIEW_SAWON
AS
SELECT A.사원번호, A.사원명, A.주민번호, A.성별, A.현재나이, A.입사일
, A.정년퇴직일, A.근무일수
, TRUNC(TO_DATE(A.정년퇴직일, 'YYYY-MM-DD') - SYSDATE)"남은일수"
, A.급여
, CASE WHEN A.근무일수 >= 2000 THEN A.급여*0.5
WHEN A.근무일수 >= 1000 THEN A.급여*0.3
ELSE 0
END"보너스"
FROM
(
SELECT T.사원번호, T.사원명, T.주민번호, T.성별, T.현재나이, T.입사일
, TO_CHAR(ADD_MONTHS(SYSDATE, (60-T.현재나이)*12), 'YYYY') || '-' || TO_CHAR(T.입사일, 'MM-DD')"정년퇴직일"
, TRUNC(SYSDATE-T.입사일)"근무일수"
, T.급여
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
) A;

--==> 에러발생( SQL 오류: ORA-01031: insufficient privileges )

권한(뷰를 생성할 수 이쓴 권한)이 불충분하여 뷰(VIEW)를 생성할 수 없음.

--※ 현재 SCOTT은 VIEW의 생성 권한을 갖지 못한 상태이므로 SYS로 접속하여 SCOTT이 VIEW를 생성할 수 있는 권한(CREATE VIEW)을 부여해주어야 한다.

권한을 부여받은 이후 다시 실행

CREATE OR REPLACE VIEW VIEW_SAWON
AS
SELECT A.사원번호, A.사원명, A.주민번호, A.성별, A.현재나이, A.입사일
, A.정년퇴직일, A.근무일수
, TRUNC(TO_DATE(A.정년퇴직일, 'YYYY-MM-DD') - SYSDATE)"남은일수"
, A.급여
, CASE WHEN A.근무일수 >= 2000 THEN A.급여*0.5
WHEN A.근무일수 >= 1000 THEN A.급여*0.3
ELSE 0
END"보너스"
FROM
(
SELECT T.사원번호, T.사원명, T.주민번호, T.성별, T.현재나이, T.입사일
, TO_CHAR(ADD_MONTHS(SYSDATE, (60-T.현재나이)*12), 'YYYY') || '-' || TO_CHAR(T.입사일, 'MM-DD')"정년퇴직일"
, TRUNC(SYSDATE-T.입사일)"근무일수"
, T.급여
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
) A;
--==>> View VIEW_SAWON이(가) 생성되었습니다.

JOIN(조인)

SQL 1992 CODE

CROSS JOIN

→ 수학에서 말하는 데카르트 곱(Cartersian Product)

SELECT *
FROM EMP;

SELECT *
FROM DEPT;

SELECT *
FROM EMP, DEPT;

두 테이블을 합친 모든 경우의 수(56 EA 레코드 → 14 * 4)

SELECT COUNT(*)
FROM EMP;
--==>> 14

SELECT COUNT(*)
FROM DEPT;
--==> 4

Equi Join

→ 서로 정확히 같은 것끼리 결합

SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

Non Equi Join

→ 범위 안에 적합한 것들 끼리 결합하는 방법

SELECT *
FROM SALGRADE;

SELECT *
FROM EMP;

SELECT *
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

Equi Join 시 (+) 사용방법

SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

SELECT COUNT(*)
FROM EMP;

SELECT COUNT(*)
FROM DEPT;

INSERT INTO EMP(ENAME,EMPNO, JOB, MGR, HIREDATE, SAL, COMM) VALUES('RAY',7966,'CLERK', 7839, SYSDATE, 1000, 1000);
--==>> 1 행 이(가) 삽입되었습니다.

COMMIT;
--==>> 커밋 완료.

SELECT *
FROM DEPT;

SELECT *
FROM EMP;

SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO(+);

SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO(+)= D.DEPTNO;

-- ※ (+)가 없는 쪽 테이블의 데이터를 모두 먼저 메모리에 퍼올린 후
--    (+)가 있는 쪽 테이블의 데이터와 짝을 구성하는 과정을 통해
--    조회 결과를 종합.

SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO(+);
--> 이와 같은 구문은 존재하지 않는다.
--==>> 에러 발생

SQL 1999 CODE

CROSS JOIN

SELECT *
FROM EMP CROSS JOIN DEPT;

INNER JOIN

SELECT *
FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;

SELECT *
FROM EMP JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;

-- 결합 조건은 WHERE가 아니라 ON을 사용

SELECT *
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;

SELECT *
FROM EMP E JOIN SALGRADE S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL;

OUTTER JOIN == (+)

SELECT *
FROM EMP E LEFT OUTER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;

SELECT *
FROM EMP E RIGHT OUTER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;

SELECT *
FROM EMP E FULL OUTER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;

-- OUTER 생략가능
-- 위와 같이 조회한 결과에서
-- 직종이 MANAGER 와 CLERK 만 조회하고자 한다.

SELECT *
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
AND JOB IN ('MANAGER', 'CLERK');

-- 쿼리문을 이렇게 구성해도 조회하는 데는 문제가 없지만... 권장하지 않는다.

SELECT *
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO              --JOIN(결합) 조건
WHERE JOB IN ('MANAGER', 'CLERK');  --레코드의 선택 조건

퀴즈

-- EMP 테이블과 DEPT 테이블을 활용하여 직종이 MANAGER 와 CLERK인 사원들의 부서번호, 부서명, 사원명, 직종명, 급여 항목으로 조회한다.

SELECT *
FROM EMP;

SELECT *
FROM DEPT;

SELECT E.DEPTNO"부서번호", DNAME"부서명", ENAME"사원명", JOB"직종명", SAL"급여"
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE JOB IN ('MANAGER', 'CLERK');

SELECT D.DEPTNO"부서번호", D.DNAME"부서명", E.ENAME"사원명", E.JOB"직종명", E.SAL"급여"
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE JOB IN ('MANAGER', 'CLERK');

-- 중복 칼럼 --- 소속 명시 -- 부모 테이블(DEPT)
-- 중복 칼럼이 아닌 나머지 칼럼 ---- 소속 명시
-- 소속을 명시하지 않으면 JOIN한 테이블 모두에서 칼럼을 찾는다.
-- DEPTNO가 DEPT에서는 PRIMANY 이기 때문에 DEPT가 부모이다.

SELECT E.DEPTNO"부서번호", D.DNAME"부서명", E.ENAME"사원명", E.JOB"직종명", E.SAL"급여"
FROM EMP E RIGHT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;
-- 중복 칼럼 확인

SELF JOIN (자기 조인)

--■■■ SELF JOIN (자기 조인) ■■■--

--○ EMP 테이블의 정보를 다음과 같이 조회할 수 있도록 한다.

/*
사원번호    사원명     직종      관리자번호     관리자명      관리자직종명
7369     SMITH      CLERK       7902         FORD          ANALYST
:
:
*/

SELECT *
FROM EMP;

SELECT *
FROM DEPT;

SELECT F.EMPNO"사원번호", F.ENAME"사원명", F.JOB"직종", F.MGR"관리자번호", E.ENAME"관리자명", E.JOB"관리자직종명"
FROM EMP E JOIN EMP F
ON E.EMPNO = F.MGR;

'교육 및 세미나 > 신입사원 교육' 카테고리의 다른 글

8일차(2018.01.12) (0)	2018.01.12
7일차(2018.01.11) (0)	2018.01.12
5일차(2018.01.09) (0)	2018.01.12
4일차(2018.01.05) (0)	2018.01.12
3일차(2018.01.04) (0)	2018.01.09

27

6일차(2018.01.10)

날짜 연산

날짜 함수

ADD_MONTHS()

--> 개월 수를 더해주는 함수
SELECT SYSDATE, ADD_MONTHS(SYSDATE, 2), ADD_MONTHS(SYSDATE, -2) FROM DUAL; --==>>2018-01-10 09:17:23 -- 2018-03-10 09:17:23 -- 2017-11-10 09:17:23

MONTHS_BETWEEN()

NEXT_DAY()

LAST_DAY()

매개변수의 해당 날짜가 포함되어 있는 그 달의 마지막 날을 반환한다.
SELECT LAST_DAY(SYSDATE), LAST_DAY(TO_DATE('2020-02-05', 'YYYY-MM-DD')) FROM DUAL; --==>> 2018-01-31 09:34:03 2020-02-29 00:00:00

--○ 퀴즈

--○ 퀴즈

날짜 반올림

변환 함수

EXTRACT()

형식 맞춤 표기(TO_CHAR())

CASE 문 (조건문, 반복문)

DECODE(조건, 값, 처리, 값, 처리, 값, 처리, 처리)

뷰(View)

JOIN(조인)

SQL 1992 CODE

CROSS JOIN

→ 수학에서 말하는 데카르트 곱(Cartersian Product)
SELECT * FROM EMP; SELECT * FROM DEPT; SELECT * FROM EMP, DEPT;

두 테이블을 합친 모든 경우의 수(56 EA 레코드 → 14 * 4)
SELECT COUNT() FROM EMP; --==>> 14 SELECT COUNT() FROM DEPT; --==> 4

Equi Join

→ 서로 정확히 같은 것끼리 결합
SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; SELECT * FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;

Non Equi Join

→ 범위 안에 적합한 것들 끼리 결합하는 방법
SELECT * FROM SALGRADE; SELECT * FROM EMP; SELECT * FROM EMP E, SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

Equi Join 시 (+) 사용방법

SQL 1999 CODE

CROSS JOIN

SELECT * FROM EMP CROSS JOIN DEPT;

INNER JOIN

OUTTER JOIN == (+)

SELF JOIN (자기 조인)

'교육 및 세미나 > 신입사원 교육' 카테고리의 다른 글

티스토리툴바

6일차(2018.01.10)

날짜 연산

날짜 함수

ADD_MONTHS()

--> 개월 수를 더해주는 함수SELECT SYSDATE, ADD_MONTHS(SYSDATE, 2), ADD_MONTHS(SYSDATE, -2) FROM DUAL; --==>>2018-01-10 09:17:23 -- 2018-03-10 09:17:23 -- 2017-11-10 09:17:23

MONTHS_BETWEEN()

NEXT_DAY()

LAST_DAY()

매개변수의 해당 날짜가 포함되어 있는 그 달의 마지막 날을 반환한다.SELECT LAST_DAY(SYSDATE), LAST_DAY(TO_DATE('2020-02-05', 'YYYY-MM-DD')) FROM DUAL; --==>> 2018-01-31 09:34:03 2020-02-29 00:00:00

--○ 퀴즈

--○ 퀴즈

날짜 반올림

변환 함수

EXTRACT()

형식 맞춤 표기(TO_CHAR())

CASE 문 (조건문, 반복문)

DECODE(조건, 값, 처리, 값, 처리, 값, 처리, 처리)

뷰(View)

JOIN(조인)

SQL 1992 CODE

CROSS JOIN

→ 수학에서 말하는 데카르트 곱(Cartersian Product)SELECT * FROM EMP; SELECT * FROM DEPT; SELECT * FROM EMP, DEPT; 두 테이블을 합친 모든 경우의 수(56 EA 레코드 → 14 * 4)SELECT COUNT(*) FROM EMP; --==>> 14 SELECT COUNT(*) FROM DEPT; --==> 4

Equi Join

→ 서로 정확히 같은 것끼리 결합SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; SELECT * FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;

Non Equi Join

→ 범위 안에 적합한 것들 끼리 결합하는 방법SELECT * FROM SALGRADE; SELECT * FROM EMP; SELECT * FROM EMP E, SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

Equi Join 시 (+) 사용방법

SQL 1999 CODE

CROSS JOIN

SELECT * FROM EMP CROSS JOIN DEPT;

INNER JOIN

OUTTER JOIN == (+)

SELF JOIN (자기 조인)

'교육 및 세미나 > 신입사원 교육' 카테고리의 다른 글

'교육 및 세미나/신입사원 교육' Related Articles

티스토리툴바

--> 개월 수를 더해주는 함수
SELECT SYSDATE, ADD_MONTHS(SYSDATE, 2), ADD_MONTHS(SYSDATE, -2) FROM DUAL; --==>>2018-01-10 09:17:23 -- 2018-03-10 09:17:23 -- 2017-11-10 09:17:23

매개변수의 해당 날짜가 포함되어 있는 그 달의 마지막 날을 반환한다.
SELECT LAST_DAY(SYSDATE), LAST_DAY(TO_DATE('2020-02-05', 'YYYY-MM-DD')) FROM DUAL; --==>> 2018-01-31 09:34:03 2020-02-29 00:00:00

→ 수학에서 말하는 데카르트 곱(Cartersian Product)
SELECT * FROM EMP; SELECT * FROM DEPT; SELECT * FROM EMP, DEPT;

두 테이블을 합친 모든 경우의 수(56 EA 레코드 → 14 * 4)
SELECT COUNT() FROM EMP; --==>> 14 SELECT COUNT() FROM DEPT; --==> 4

→ 서로 정확히 같은 것끼리 결합
SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; SELECT * FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;

→ 범위 안에 적합한 것들 끼리 결합하는 방법
SELECT * FROM SALGRADE; SELECT * FROM EMP; SELECT * FROM EMP E, SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;