티스토리 뷰
반응형
강사 : 김호진 강사님
Oracle SQL Developer (4일차)
20180110_scott.sql
SELECT USER
FROM DUAL;
--==>> SCOTT
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
--==>> Session이(가) 변경되었습니다.
Test25
--○ 문제
-- 현재 시간과, 1일 2시간 3분 4초 후를 조회(출력)한다.
----------------------------------------------------------
-- 현재 시간 | 연산 후 시간
----------------------------------------------------------
-- 2018-01-09 16:50:36 | 2018-01-10 18:53:40
----------------------------------------------------------
-- 방법 1.
SELECT SYSDATE"현재 시간"
, SYSDATE + 1 + 2/24 + 3/24/60 + 4/24/60/60"연산 후 시간"
FROM DUAL;
--==>> 2018-01-10 09:03:49 2018-01-11 11:06:53
-- 방법 2.
SELECT SYSDATE "현재 시간"
, SYSDATE + ((24*60*60) + (2*60*60) + (3*60) + 4) / (24*60*60) "연산 후 시간"
FROM DUAL;
--==>> 2018-01-10 09:07:46 2018-01-11 11:10:50
날짜 연산
--※ 날짜 - 날짜 = 일수
-- 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;
SELECT TO_DATE('2018-13-30', 'YYYY-MM-DD')
FROM DUAL;
--※ TO_DATE() 함수를 통해 문자 타입을 날짜 타입으로 변환을 진행할 때
-- 내부적으로 해당 날짜에 대한 유효성 검사가 이루어진다.
-- 유효하지 않은 날짜로 변환을 진행하게 되면 에러가 발생한다.
날짜 함수
ADD_MONTHS()
--○ ADD_MONTHS()
--> 개월 수를 더해주는 함수
SELECT SYSDATE, ADD_MONTHS(SYSDATE, 2), ADD_MONTHS(SYSDATE, -2)
FROM DUAL;
--==>> 2018-01-10 09:17:12
-- 2018-03-10 09:17:12
-- 2017-11-10 09:17:12
MONTHS_BETWEEN()
--○ MONTHS_BETWEEN()
--> 첫 번째 인자값에서 두 번째 인자값을 뺀 개월 수
SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2002-05-31', 'YYYY-MM-DD'))
FROM DUAL;
--==>> 187.335151209677419354838709677419354839
--> 개월 수의 차이
--※ 결과값의 부호가 『-』일 경우에는
-- 첫 번째 매개변수에 해당하는 날짜보다
-- 두 번째 매개변수에 해당하는 날짜가 『미래』라는 의미이다.
SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2018-01-19', 'YYYY-MM-DD'))
FROM DUAL;
--==>> -0.2776698775388291517323775388291517323775
-- 교육과정 종료 후 실무 투입 일자가 현재보다 더 미래다.
NEXT_DAY()
--○ NEXT_DAY()
--> 첫 번째 매개변수에서(기준) 돌아오는 가장 빠른 요일 반환
SELECT NEXT_DAY(SYSDATE, '토')
FROM DUAL;
--==>> 2018-01-13 09:30:27
SELECT NEXT_DAY(TO_DATE('1993-10-09', 'YYYY-MM-DD'), '일')
FROM DUAL;
--==>> 1993-10-10 00:00:00
--※ 추가 세션 설정
ALTER SESSION SET NLS_DATE_LANGUAGE='ENGLISH';
--==>> Session이(가) 변경되었습니다.
--> 요일을 영어로 설정해야함 (요일이 한글로 설정된 경우 에러 발생)
SELECT NEXT_DAY(SYSDATE, 'SAT')
FROM DUAL;
--==>> 2018-01-13 09:29:26
--※ 추가 세션 설정
ALTER SESSION SET NLS_DATE_LANGUAGE='KOREAN';
--==>> Session이(가) 변경되었습니다.
--> 요일을 한글로 설정해야함 (요일이 영어로 설정된 경우 에러 발생)
LAST_DAY()
--○ LAST_DAY()
--> 매개변수의 해당 날짜가 포함되어 있는 그 달의 마지막 날을 반환한다.
SELECT LAST_DAY(SYSDATE), LAST_DAY(TO_DATE('2020-02-05', 'YYYY-MM-DD'))
FROM DUAL;
--==>> 2018-01-31 09:32:47 2020-02-29 00:00:00
Test26
--○ 퀴즈
-- 오늘부로... 김선규 사원이... 군대에 또 끌려(?)간다...
-- 복무 기간은 22개월로 한다.
-- 1. 전역 일자를 조회한다.
SELECT ADD_MONTHS(SYSDATE, 22) "전역일자"
FROM DUAL;
--==>> 2019-11-10 09:36:38
-- 2. 하루 세 끼 식사를 한다고 가정하면
-- 김선규 사원은... 몇 끼를 먹어야 집에 보내줄까...
-- 전역일자 - 입대일자 * 3
SELECT (ADD_MONTHS(SYSDATE, 22) - SYSDATE) * 3 "식사 횟수"
FROM DUAL;
--==>> 2007
Test27
--○ 현재 시간으로부터 올해 성탄절 오후 6시 (2018-12-25 18:00:00) 까지
-- 남은 기간을 다음과 같이 조회할 수 있도록 한다.
/*
--------------------------------------------------------------------------------
현재 시간 | 성탄절 | 일 | 시간 | 분 | 초
--------------------------------------------------------------------------------
2018-01-10 09:47:40 | 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')"성탄절"
, TRUNC(TO_DATE('2018-12-25 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE) "일"
, TRUNC((TO_DATE('2018-12-25 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE - TRUNC(TO_DATE('2018-12-25 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE))*24) "시간"
, TRUNC(((TO_DATE('2018-12-25 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE - TRUNC(TO_DATE('2018-12-25 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE))*24
- TRUNC((TO_DATE('2018-12-25 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE - TRUNC(TO_DATE('2018-12-25 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE))*24))*60) "분"
, ROUND((((TO_DATE('2018-12-25 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE - TRUNC(TO_DATE('2018-12-25 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE))*24
- TRUNC((TO_DATE('2018-12-25 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE - TRUNC(TO_DATE('2018-12-25 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE))*24))*60
- TRUNC(((TO_DATE('2018-12-25 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE - TRUNC(TO_DATE('2018-12-25 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE))*24
- TRUNC((TO_DATE('2018-12-25 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE - TRUNC(TO_DATE('2018-12-25 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE))*24))*60))*60) "초"
FROM DUAL;
--!! 풀이
-- 『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(TRUNC(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:05
-- 성탄일 - 현재일
SELECT TO_DATE('2018-12-25 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE
FROM DUAL;
--==>> 349.312673611111111111111111111111111111
-- 현재일로부터 성탄절까지 남은 일수 계산
-- 일수 기반의 남은 일자를... 초로 환산
-- 환산할 일수 * (24*60*60)
SELECT (TO_DATE('2018-12-25 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24*60*60)
FROM DUAL;
--==>> 30180358.99999999999999999999999999999999
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)"분" -- 시간으로 편입되지 못하는 분~!!!
, MOD(TRUNC((TO_DATE('2018-12-25 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24*60*60)), 60)"초" -- 분으로 편입되지 못하는 초~!!!
FROM DUAL;
--==>> 349 7 25 19
--※ 날짜에도 반올림, 절삭 등을 수행할 수 있다.
날짜 반올림
--○ 날짜 반올림
SELECT SYSDATE -- 2018-01-10 10:38:33
, 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:26
, 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()
-- 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월
, TO_CHAR(SYSDATE, 'MON') -- 1월
, TO_CHAR(SYSDATE, 'DD') -- 10
, TO_CHAR(SYSDATE, 'DAY') -- 수요일
, TO_CHAR(SYSDATE, 'HH24') -- 10
, TO_CHAR(SYSDATE, 'HH') -- 10
, TO_CHAR(SYSDATE, 'HH AM') -- 10 오전
, TO_CHAR(SYSDATE, 'HH PM') -- 10 오전
, TO_CHAR(SYSDATE, 'MI') -- 54
, TO_CHAR(SYSDATE, 'SS') -- 27
, TO_CHAR(SYSDATE, 'SSSSS') -- 39267 -- 금일 0시 0분 0초 부터 흘러온 전체 초
, TO_CHAR(SYSDATE, 'Q') -- 1 -- 분기
FROM DUAL;
EXTRACT()
--○ 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())
--○ 형식 맞춤 표기(TO_CHAR())
SELECT 60000
, TO_CHAR(60000, '99,999')
, TO_CHAR(60000, '$99,999')
, TO_CHAR(60000, 'L99,999') -- 『L』 : 세션에 설정된 통화 표시(결과값에 공백 표시)
, LTRIM(TO_CHAR(60000, 'L99,999')) -- 공백 제거
FROM DUAL;
--==>> 60000 60,000 $60,000 \60,000 \60,000
--※ 왼쪽 공백 없애기
-- LTRIM() 함수는 두 번째 인자값을 사용하지 않으면(생략)
-- 왼쪽 공백 제거 기능을 갖는 함수로 사용할 수 있다.
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
--==>> Session이(가) 변경되었습니다.
-- 현재 시간을 기준으로 1일 2시간 3분 4초 후를 조회한다.
SELECT SYSDATE"현재 시간"
, SYSDATE + 1 + (2/24) + (3/(24*60)) + (4/(24*60*60))"연산 시간"
FROM DUAL;
--==>> 2018-01-10 11:36:00 2018-01-11 13:39:04
-- 현재 시간을 기준으로 1년 2개월 3일 4시간 5분 6초 후를 조회한다.
-- TO_YMINTERVAL(), TO_DSINVERVAL()
SELECT SYSDATE"현재 시간"
, SYSDATE + TO_YMINTERVAL('01-02') + TO_DSINTERVAL('003 04:05:06')
FROM DUAL;
--==>> 2018-01-10 11:37:55 2019-03-13 15:43:01
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
DECODE()
--○ DECODE()
SELECT DECODE(5-2, 1, '일이야', 2, '이야', 3, '삼이야', '몰라몰라')
FROM DUAL;
--==>> 삼이야
Test28
--○ 문제
-- 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이(가) 변경되었습니다.
-- 서브 쿼리 사용 X
SELECT SANO"사원번호"
, SANAME"사원명"
, JUBUN"주민번호"
, CASE WHEN SUBSTR(JUBUN, 7, 1) IN ('1', '3') THEN '남자'
WHEN SUBSTR(JUBUN, 7, 1) IN ('2', '4') THEN '여자'
END"성별"
-- 현재나이 = 현재년도 - 태어난년도 + 1
, (EXTRACT(YEAR FROM SYSDATE) - TO_NUMBER(SUBSTR(JUBUN, 1, 2)) - 1900 + 1)"현재나이"
, HIREDATE"입사일"
-- 정년퇴직일 = 입사일 + 근무가능연수
-- 근무가능연수 = 60 - 입사일시의 나이
-- 입사일시의 나이 = 입사연도 - 주민번호 앞 2자리 - 1900 + 1 (한국식 나이 계산)
-- = EXTRACT(YEAR FROM HIREDATE) - TO_NUMBER(SUBSTR(JUBUN, 1, 2)) - 1900 + 1
, HIREDATE + TO_YMINTERVAL(60 - (EXTRACT(YEAR FROM HIREDATE) - TO_NUMBER(SUBSTR(JUBUN, 1, 2)) - 1900 + 1) || '-00')"정년퇴직일"
, TRUNC(SYSDATE - HIREDATE)"근무일수"
, TRUNC(HIREDATE + TO_YMINTERVAL(60 - (EXTRACT(YEAR FROM HIREDATE) - TO_NUMBER(SUBSTR(JUBUN, 1, 2)) - 1900 + 1) || '-00') - SYSDATE)"남은일수"
, SAL"급여"
, CASE WHEN (TRUNC(SYSDATE - HIREDATE) >= 1000 AND TRUNC(SYSDATE - HIREDATE) < 2000) THEN SAL*0.3
WHEN TRUNC(SYSDATE - HIREDATE) >= 2000 THEN SAL*0.5
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;
-- 주석 제거 정리 코드
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;
--==>>
/*
1000 한예슬 7502132234567 여자 44 1995-08-15 2034-08-15 8184 6060 5000000 2500000
1001 명소희 9302272234567 여자 26 2017-03-02 2052-03-02 314 12469 4000000 0
1002 임미영 9310092234567 여자 26 2017-03-02 2052-03-02 314 12469 4000000 0
1003 조태희 9010271234567 남자 29 2017-03-02 2049-03-02 314 11373 4000000 0
1004 김선규 9202231234567 남자 27 2017-11-13 2051-11-13 58 12359 4000000 0
1005 서운성 9307151234567 남자 26 2017-11-13 2052-11-13 58 12725 4000000 0
1006 박기범 9002271234567 남자 29 2017-11-13 2049-11-13 58 11629 4000000 0
1007 최진규 9105071234567 남자 28 2017-11-13 2050-11-13 58 11994 4000000 0
1008 김준협 9212101234567 남자 27 2017-11-13 2051-11-13 58 12359 4000000 0
1009 정승우 9304211234567 남자 26 2017-11-13 2052-11-13 58 12725 4000000 0
1010 오승우 9112271234567 남자 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
*/
-- 상기 내용에서 특정 근무일수의 사원을 확인한다거나
-- 특정 보너스 금액을 받는 사원을 확인해야 하는 등...
-- 추가적인 업무가 발생할 수 있다.
-- 이와 같은 경우는... 뷰(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)을
-- 부여해주어야 한다.
--○ SCOTT 계정에 VIEW를 생성할 수 있는 권한을 부여
GRANT CREATE VIEW TO SCOTT;
--==> Grant을(를) 성공했습니다.
--○ 권한을 부여받은 이후 다시 실행
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이(가) 생성되었습니다.
SELECT *
FROM VIEW_SAWON;
--==>>
/*
1000 한예슬 7502132234567 여자 44 1995-08-15 2034-08-15 8184 6060 5000000 2500000
1001 명소희 9302272234567 여자 26 2017-03-02 2052-03-02 314 12469 4000000 0
1002 임미영 9310092234567 여자 26 2017-03-02 2052-03-02 314 12469 4000000 0
1003 조태희 9010271234567 남자 29 2017-03-02 2049-03-02 314 11373 4000000 0
1004 김선규 9202231234567 남자 27 2017-11-13 2051-11-13 58 12359 4000000 0
1005 서운성 9307151234567 남자 26 2017-11-13 2052-11-13 58 12725 4000000 0
1006 박기범 9002271234567 남자 29 2017-11-13 2049-11-13 58 11629 4000000 0
1007 최진규 9105071234567 남자 28 2017-11-13 2050-11-13 58 11994 4000000 0
1008 김준협 9212101234567 남자 27 2017-11-13 2051-11-13 58 12359 4000000 0
1009 정승우 9304211234567 남자 26 2017-11-13 2052-11-13 58 12725 4000000 0
1010 오승우 9112271234567 남자 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
*/
참고 사항
ROLLUP CUBE HAVING 그룹함수, 분석함수, 중첩그룹함수, ...
인라인뷰(INLINEVIEW), 상관서브쿼리(서브상관쿼리)
각자 공부해보길...!
JOIN (조인)
SQL 1992 CODE
-- 1. SQL 1992 CODE
SELECT *
FROM EMP;
SELECT *
FROM DEPT;
SELECT *
FROM EMP, DEPT;
--> CROSS JOIN → 수학에서 말하는 데카르트 곱(Cartersian Product)
-- 두 테이블을 합친 모든 경우의 수
-- 사용하는 경우가 거의 없음...
SELECT COUNT(*)
FROM EMP;
--==>> 14
SELECT COUNT(*)
FROM DEPT;
--==>> 4
SELECT COUNT(*)
FROM EMP, DEPT;
--==>> 56
--> 56 EA 레코드 → 14 * 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 시 (+) 사용 방법
INSERT INTO EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM)
VALUES(7966, 'RAY', 'CLERK', 7839, SYSDATE, 1000, 1000);
--==>> 1 행 이(가) 삽입되었습니다.
COMMIT;
--==>> 커밋 완료.
SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
SELECT COUNT(*)
FROM EMP; --==>> 15
SELECT COUNT(*)
FROM DEPT; --==>> 4
SELECT COUNT(*)
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO; --==>> 14
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
-- 2. SQL 1999 CODE
SELECT *
FROM EMP CROSS JOIN DEPT;
--> 92 CODE의 CROSS JOIN과 동일
-- INNER JOIN
SELECT *
FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;
--> 92 CODE의 Equi Join과 동일
--> ON 사용
SELECT *
FROM EMP JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;
--> 『INNER』 생략 가능
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;
-- OUTER JOIN
--> 92 CODE의 Equi 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』 생략 가능 → LEFT, RIGHT, FULL이 붙어있는 경우에는 OUTER JOIN
SELECT *
FROM EMP E LEFT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;
SELECT *
FROM EMP E RIGHT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;
SELECT *
FROM EMP E FULL JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;
-- 위와 같이 조회한 결과에서
-- 직종이 MANAGER 와 CLERK 만 조회하고자 한다.
SELECT *
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
AND JOB IN ('MANAGER', 'CLERK');
--> 쿼리문을 이렇게 구성해도 조회하는 데는 문제가 없지만... 권장하지 않는다.
-- JOIN(결합) 조건인지 레코드의 선택 조건인지가 명확하지 않음
SELECT *
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO -- JOIN(결합) 조건
WHERE JOB IN ('MANAGER', 'CLERK'); -- 레코드의 선택 조건
Test29
--○ 퀴즈
-- EMP 테이블과 DEPT 테이블을 활용하여
-- 직종이 MANAGER 와 CLERK 인 사원들의
-- 부서번호, 부서명, 사원명, 직종명, 급여 항목으로 조회한다.
SELECT *
FROM EMP
WHERE JOB IN ('MANAGER', 'CLERK');
SELECT *
FROM DEPT;
SELECT D.DEPTNO"부서번호", D.DNAME"부서명", E.ENAME"사원명", E.JOB"직종명", E.SAL"급여"
FROM EMP E LEFT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE E.JOB IN ('MANAGER', 'CLERK');
-- 중복 컬럼 --- 소속 명시 ---- 부모 테이블
-- 중복 컬럼이 아닌 나머지 컬럼 ---- 소속 명시
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 (자기 조인)
--○ EMP 테이블의 정보를 다음과 같이 조회할 수 있도록 한다.
/*
사원번호 사원명 직종 관리자번호 관리자명 관리자직종명
7369 SMITH CLERK 7902 FORD ANALYST
:
*/
SELECT *
FROM EMP;
SELECT E1.EMPNO"사원번호", E1.ENAME"사원명", E1.JOB"직종", E1.MGR"관리자번호", E2.ENAME"관리자명", E2.JOB"관리자직종명"
FROM EMP E1 LEFT JOIN EMP E2
ON E1.MGR = E2.EMPNO;
반응형
'DB > Oracle' 카테고리의 다른 글
| [Oracle] 교육 5일차 (0) | 2018.01.11 |
|---|---|
| [Oracle] 교육 3일차 (0) | 2018.01.09 |
| [Oracle] 교육 2일차 (0) | 2018.01.05 |
| [Oracle] 교육 1일차 (0) | 2018.01.04 |
댓글
반응형
공지사항
최근에 올라온 글
최근에 달린 댓글
- Total
- Today
- Yesterday
링크
TAG
- ubuntu
- 하이퍼레저 인디
- Blockchain
- 블록 체인
- Private Data
- 직딩잇템
- 하이퍼레저 페브릭
- Hyperledger Fabric v1.1
- 문제풀이
- 암브로셔스
- 블록체인
- 코딩테스트
- 하이퍼레저 패브릭
- 코딜리티
- 코테
- 기초 of 기초 데이터 개념
- javascript
- 빅데이터 강의
- Hyperledger Fabric v1.2
- 빅데이터 교육
- 어서와 데이터는 처음이지
- 빅데이터
- ambrosus
- Hyperledger Indy
- docker
- DOCs
- codility
- 빅데이터 기초
- 알고리즘
- Hyperledger Fabric
| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | |||
| 5 | 6 | 7 | 8 | 9 | 10 | 11 |
| 12 | 13 | 14 | 15 | 16 | 17 | 18 |
| 19 | 20 | 21 | 22 | 23 | 24 | 25 |
| 26 | 27 | 28 | 29 | 30 | 31 |
글 보관함