Everything has an expiration date
[Oracle] 20231101 [프로그램 소스] 본문
20231101_01_scott.sql
SELECT USER
FROM DUAL;
--==>> SCOTT
--■■■ UPDATE ■■■--
-- 1. 테이블에서 기존 데이터를 수정(변경)하는 구문
-- 2. 형식 및 구조
-- UPDATE 테이블명
-- SET 컬럼명 = 변경할 값[, 컬럼명-변경할값, ...]
-- [WHERE 조건절]
SELECT *
FROM TBL_SAWON;
--○ TBL_SAWON 테이블에서 사원번호 1005번 사원의
-- 주민번호를 『7609172234567』로 수정한다.
UPDATE TBL_SAWON
SET JUBUN = '7609172234567'
WHERE SANO = 1005;
-- 특정 테이블 전체를 변경할 때는 WHERE 절 X
UPDATE TBL_SAWON
SET 주민번호를 '7609172234567'
WHERE 사원번호가 1005
UPDATE TBL_SAWON
SET JUBUN = '7609172234567'
WHERE SANO = 1005;
--==>> 1 행 이(가) 업데이트되었습니다.
-- 확인
SELECT *
FROM TBL_SAWON;
--==>>
/*
:
1005 아이유 7609172234567 2015-10-19 1000
:
*/
-- 실행 후 COMMIT 또는 ROLLBACK 을 반드시 선택적으로 실행
COMMIT;
--==>> 커밋완료.
--○ TBL_SAWON 테이블에서 1005번 사원의 입사일과 급여를
-- 각각 2020-04-01, 1200 으로 변경한다.
UPDATE TBL_SAWON
SET HIREDATE = TO_DATE('2020-04-01', 'YYYY-MM-DD')
, SAL = 1200
-- SET 에서는 쉼표를 사용하도록 하자. AND 아님!
WHERE SANO = 1005;
SELECT *
FROM TBL_SAWON;
-- 조건이 아니므로 AND 쓰지 않는다! 변경할 값들을 ,(콤마)로 열거하면 된다.
UPDATE TBL_SAWON
SET HIREDATE = TO_DATE('2020-04-01', 'YYYY-MM-DD'), SAL=1200
WHERE SANO = 1005;
--==>> 1 행 이(가) 업데이트되었습니다.
-- 확인
SELECT *
FROM TBL_SAWON;
--==>>
/*
:
1005 아이유 7609172234567 2020-04-01 1200
:
*/
-- 커밋
COMMIT;
--==>> 커밋 완료.
--○ TBL_INSA 테이블 복사(구조와 데이터만...)
SELECT *
FROM TBL_INSA;
CREATE TABLE TBL_INSABACKUP
AS
SELECT *
FROM TBL_INSA;
--==>> Table TBL_INSABACKUP이(가) 생성되었습니다.
-- 확인
SELECT *
FROM TBL_INSABACKUP;
--==>>
/*
NUM NAME SSN IBSADATE CITY TEL BUSEO JIKWI BASICPAY SUDANG
1001 홍길동 771212-1022432 1998-10-11 서울 011-2356-4528 기획부 부장 2610000 200000
1002 이순신 801007-1544236 2000-11-29 경기 010-4758-6532 총무부 사원 1320000 200000
1003 이순애 770922-2312547 1999-02-25 인천 010-4231-1236 개발부 부장 2550000 160000
1004 김정훈 790304-1788896 2000-10-01 전북 019-5236-4221 영업부 대리 1954200 170000
1005 한석봉 811112-1566789 2004-08-13 서울 018-5211-3542 총무부 사원 1420000 160000
1006 이기자 780505-2978541 2002-02-11 인천 010-3214-5357 개발부 과장 2265000 150000
1007 장인철 780506-1625148 1998-03-16 제주 011-2345-2525 개발부 대리 1250000 150000
1008 김영년 821011-2362514 2002-04-30 서울 016-2222-4444 홍보부 사원 950000 145000
1009 나윤균 810810-1552147 2003-10-10 경기 019-1111-2222 인사부 사원 840000 220400
1010 김종서 751010-1122233 1997-08-08 부산 011-3214-5555 영업부 부장 2540000 130000
1011 유관순 801010-2987897 2000-07-07 서울 010-8888-4422 영업부 사원 1020000 140000
1012 정한국 760909-1333333 1999-10-16 강원 018-2222-4242 홍보부 사원 880000 114000
1013 조미숙 790102-2777777 1998-06-07 경기 019-6666-4444 홍보부 대리 1601000 103000
1014 황진이 810707-2574812 2002-02-15 인천 010-3214-5467 개발부 사원 1100000 130000
1015 이현숙 800606-2954687 1999-07-26 경기 016-2548-3365 총무부 사원 1050000 104000
1016 이상헌 781010-1666678 2001-11-29 경기 010-4526-1234 개발부 과장 2350000 150000
1017 엄용수 820507-1452365 2000-08-28 인천 010-3254-2542 개발부 사원 950000 210000
1018 이성길 801028-1849534 2004-08-08 전북 018-1333-3333 개발부 사원 880000 123000
1019 박문수 780710-1985632 1999-12-10 서울 017-4747-4848 인사부 과장 2300000 165000
1020 유영희 800304-2741258 2003-10-10 전남 011-9595-8585 자재부 사원 880000 140000
1021 홍길남 801010-1111111 2001-09-07 경기 011-9999-7575 개발부 사원 875000 120000
1022 이영숙 800501-2312456 2003-02-25 전남 017-5214-5282 기획부 대리 1960000 180000
1023 김인수 731211-1214576 1995-02-23 서울 영업부 부장 2500000 170000
1024 김말자 830225-2633334 1999-08-28 서울 011-5248-7789 기획부 대리 1900000 170000
1025 우재옥 801103-1654442 2000-10-01 서울 010-4563-2587 영업부 사원 1100000 160000
1026 김숙남 810907-2015457 2002-08-28 경기 010-2112-5225 영업부 사원 1050000 150000
1027 김영길 801216-1898752 2000-10-18 서울 019-8523-1478 총무부 과장 2340000 170000
1028 이남신 810101-1010101 2001-09-07 제주 016-1818-4848 인사부 사원 892000 110000
1029 김말숙 800301-2020202 2000-09-08 서울 016-3535-3636 총무부 사원 920000 124000
1030 정정해 790210-2101010 1999-10-17 부산 019-6564-6752 총무부 과장 2304000 124000
1031 지재환 771115-1687988 2001-01-21 서울 019-5552-7511 기획부 부장 2450000 160000
1032 심심해 810206-2222222 2000-05-05 전북 016-8888-7474 자재부 사원 880000 108000
1033 김미나 780505-2999999 1998-06-07 서울 011-2444-4444 영업부 사원 1020000 104000
1034 이정석 820505-1325468 2005-09-26 경기 011-3697-7412 기획부 사원 1100000 160000
1035 정영희 831010-2153252 2002-05-16 인천 개발부 사원 1050000 140000
1036 이재영 701126-2852147 2003-08-10 서울 011-9999-9999 자재부 사원 960400 190000
1037 최석규 770129-1456987 1998-10-15 인천 011-7777-7777 홍보부 과장 2350000 187000
1038 손인수 791009-2321456 1999-11-15 부산 010-6542-7412 영업부 대리 2000000 150000
1039 고순정 800504-2000032 2003-12-28 경기 010-2587-7895 영업부 대리 2010000 160000
1040 박세열 790509-1635214 2000-09-10 경북 016-4444-7777 인사부 대리 2100000 130000
1041 문길수 721217-1951357 2001-12-10 충남 016-4444-5555 자재부 과장 2300000 150000
1042 채정희 810709-2000054 2003-10-17 경기 011-5125-5511 개발부 사원 1020000 200000
1043 양미옥 830504-2471523 2003-09-24 서울 016-8548-6547 영업부 사원 1100000 210000
1044 지수환 820305-1475286 2004-01-21 서울 011-5555-7548 영업부 사원 1060000 220000
1045 홍원신 690906-1985214 2003-03-16 전북 011-7777-7777 영업부 사원 960000 152000
1046 허경운 760105-1458752 1999-05-04 경남 017-3333-3333 총무부 부장 2650000 150000
1047 산마루 780505-1234567 2001-07-15 서울 018-0505-0505 영업부 대리 2100000 112000
1048 이기상 790604-1415141 2001-06-07 전남 개발부 대리 2050000 106000
1049 이미성 830908-2456548 2000-04-07 인천 010-6654-8854 개발부 사원 1300000 130000
1050 이미인 810403-2828287 2003-06-07 경기 011-8585-5252 홍보부 대리 1950000 103000
1051 권영미 790303-2155554 2000-06-04 서울 011-5555-7548 영업부 과장 2260000 104000
1052 권옥경 820406-2000456 2000-10-10 경기 010-3644-5577 기획부 사원 1020000 105000
1053 김싱식 800715-1313131 1999-12-12 전북 011-7585-7474 자재부 사원 960000 108000
1054 정상호 810705-1212141 1999-10-16 강원 016-1919-4242 홍보부 사원 980000 114000
1055 정한나 820506-2425153 2004-06-07 서울 016-2424-4242 영업부 사원 1000000 104000
1056 전용재 800605-1456987 2004-08-13 인천 010-7549-8654 영업부 대리 1950000 200000
1057 이미경 780406-2003214 1998-02-11 경기 016-6542-7546 자재부 부장 2520000 160000
1058 김신제 800709-1321456 2003-08-08 인천 010-2415-5444 기획부 대리 1950000 180000
1059 임수봉 810809-2121244 2001-10-10 서울 011-4151-4154 개발부 사원 890000 102000
1060 김신애 810809-2111111 2001-10-10 서울 011-4151-4444 개발부 사원 900000 102000
*/
--○ TBL_INSABACKUP 테이블에서
-- 과장과 부장만 수당 10% 인상하는 쿼리문을 작성한다.
SELECT SUDANG
FROM TBL_INSABACKUP
WHERE JIKWI IN ('과장', '부장');
/*
[변경전]
200000
160000
150000
130000
150000
165000
170000
170000
124000
160000
187000
150000
150000
104000
160000
*/
UPDATE TBL_INSABACKUP
SET SUDANG = SUDANG + SUDANG * 0.1
WHERE JIKWI IN ('과장', '부장');
--==>> 15개 행 이(가) 업데이트되었습니다.
SELECT SUDANG
FROM TBL_INSABACKUP
WHERE JIKWI IN ('과장', '부장');
/*
[변경후]
220000
176000
165000
143000
165000
181500
187000
187000
136400
176000
205700
165000
165000
114400
176000
*/
SELECT NAME "사원명", JIKWI "직위", SUDANG "수당"
, SUDANG + (SUDANG * 0.1) "10%인상된수당"
, SUDANG * 1.1 "10%인상된수당"
FROM TBL_INSABACKUP
WHERE JIKWI IN ('과장', '부장');
--==>>
/*
1001 홍길동 771212-1022432 1998-10-11 서울 011-2356-4528 기획부 부장 2610000 220000
1003 이순애 770922-2312547 1999-02-25 인천 010-4231-1236 개발부 부장 2550000 176000
1006 이기자 780505-2978541 2002-02-11 인천 010-3214-5357 개발부 과장 2265000 165000
1010 김종서 751010-1122233 1997-08-08 부산 011-3214-5555 영업부 부장 2540000 143000
1016 이상헌 781010-1666678 2001-11-29 경기 010-4526-1234 개발부 과장 2350000 165000
1019 박문수 780710-1985632 1999-12-10 서울 017-4747-4848 인사부 과장 2300000 181500
1023 김인수 731211-1214576 1995-02-23 서울 영업부 부장 2500000 187000
1027 김영길 801216-1898752 2000-10-18 서울 019-8523-1478 총무부 과장 2340000 187000
1030 정정해 790210-2101010 1999-10-17 부산 019-6564-6752 총무부 과장 2304000 136400
1031 지재환 771115-1687988 2001-01-21 서울 019-5552-7511 기획부 부장 2450000 176000
1037 최석규 770129-1456987 1998-10-15 인천 011-7777-7777 홍보부 과장 2350000 205700
1041 문길수 721217-1951357 2001-12-10 충남 016-4444-5555 자재부 과장 2300000 165000
1046 허경운 760105-1458752 1999-05-04 경남 017-3333-3333 총무부 부장 2650000 165000
1051 권영미 790303-2155554 2000-06-04 서울 011-5555-7548 영업부 과장 2260000 114400
1057 이미경 780406-2003214 1998-02-11 경기 016-6542-7546 자재부 부장 2520000 176000
*/
UPDATE TBL_INSABACKUP
SET SUDANG = SUDANG * 1.1
WHERE JIKWI IN ('과장', '부장');
--==>> 15개 행 이(가) 업데이트되었습니다.
-- 확인
SELECT *
FROM TBL_INSABACKUP;
--==>>
/*
NUM NAME SSN IBSADATE CITY TEL BUSEO JIKWI BASICPAY SUDANG
1001 홍길동 771212-1022432 1998-10-11 서울 011-2356-4528 기획부 부장 2610000 220000
1002 이순신 801007-1544236 2000-11-29 경기 010-4758-6532 총무부 사원 1320000 200000
1003 이순애 770922-2312547 1999-02-25 인천 010-4231-1236 개발부 부장 2550000 176000
1004 김정훈 790304-1788896 2000-10-01 전북 019-5236-4221 영업부 대리 1954200 170000
1005 한석봉 811112-1566789 2004-08-13 서울 018-5211-3542 총무부 사원 1420000 160000
1006 이기자 780505-2978541 2002-02-11 인천 010-3214-5357 개발부 과장 2265000 165000
1007 장인철 780506-1625148 1998-03-16 제주 011-2345-2525 개발부 대리 1250000 150000
1008 김영년 821011-2362514 2002-04-30 서울 016-2222-4444 홍보부 사원 950000 145000
1009 나윤균 810810-1552147 2003-10-10 경기 019-1111-2222 인사부 사원 840000 220400
1010 김종서 751010-1122233 1997-08-08 부산 011-3214-5555 영업부 부장 2540000 143000
1011 유관순 801010-2987897 2000-07-07 서울 010-8888-4422 영업부 사원 1020000 140000
1012 정한국 760909-1333333 1999-10-16 강원 018-2222-4242 홍보부 사원 880000 114000
1013 조미숙 790102-2777777 1998-06-07 경기 019-6666-4444 홍보부 대리 1601000 103000
1014 황진이 810707-2574812 2002-02-15 인천 010-3214-5467 개발부 사원 1100000 130000
1015 이현숙 800606-2954687 1999-07-26 경기 016-2548-3365 총무부 사원 1050000 104000
1016 이상헌 781010-1666678 2001-11-29 경기 010-4526-1234 개발부 과장 2350000 165000
1017 엄용수 820507-1452365 2000-08-28 인천 010-3254-2542 개발부 사원 950000 210000
1018 이성길 801028-1849534 2004-08-08 전북 018-1333-3333 개발부 사원 880000 123000
1019 박문수 780710-1985632 1999-12-10 서울 017-4747-4848 인사부 과장 2300000 181500
1020 유영희 800304-2741258 2003-10-10 전남 011-9595-8585 자재부 사원 880000 140000
1021 홍길남 801010-1111111 2001-09-07 경기 011-9999-7575 개발부 사원 875000 120000
1022 이영숙 800501-2312456 2003-02-25 전남 017-5214-5282 기획부 대리 1960000 180000
1023 김인수 731211-1214576 1995-02-23 서울 영업부 부장 2500000 187000
1024 김말자 830225-2633334 1999-08-28 서울 011-5248-7789 기획부 대리 1900000 170000
1025 우재옥 801103-1654442 2000-10-01 서울 010-4563-2587 영업부 사원 1100000 160000
1026 김숙남 810907-2015457 2002-08-28 경기 010-2112-5225 영업부 사원 1050000 150000
1027 김영길 801216-1898752 2000-10-18 서울 019-8523-1478 총무부 과장 2340000 187000
1028 이남신 810101-1010101 2001-09-07 제주 016-1818-4848 인사부 사원 892000 110000
1029 김말숙 800301-2020202 2000-09-08 서울 016-3535-3636 총무부 사원 920000 124000
1030 정정해 790210-2101010 1999-10-17 부산 019-6564-6752 총무부 과장 2304000 136400
1031 지재환 771115-1687988 2001-01-21 서울 019-5552-7511 기획부 부장 2450000 176000
1032 심심해 810206-2222222 2000-05-05 전북 016-8888-7474 자재부 사원 880000 108000
1033 김미나 780505-2999999 1998-06-07 서울 011-2444-4444 영업부 사원 1020000 104000
1034 이정석 820505-1325468 2005-09-26 경기 011-3697-7412 기획부 사원 1100000 160000
1035 정영희 831010-2153252 2002-05-16 인천 개발부 사원 1050000 140000
1036 이재영 701126-2852147 2003-08-10 서울 011-9999-9999 자재부 사원 960400 190000
1037 최석규 770129-1456987 1998-10-15 인천 011-7777-7777 홍보부 과장 2350000 205700
1038 손인수 791009-2321456 1999-11-15 부산 010-6542-7412 영업부 대리 2000000 150000
1039 고순정 800504-2000032 2003-12-28 경기 010-2587-7895 영업부 대리 2010000 160000
1040 박세열 790509-1635214 2000-09-10 경북 016-4444-7777 인사부 대리 2100000 130000
1041 문길수 721217-1951357 2001-12-10 충남 016-4444-5555 자재부 과장 2300000 165000
1042 채정희 810709-2000054 2003-10-17 경기 011-5125-5511 개발부 사원 1020000 200000
1043 양미옥 830504-2471523 2003-09-24 서울 016-8548-6547 영업부 사원 1100000 210000
1044 지수환 820305-1475286 2004-01-21 서울 011-5555-7548 영업부 사원 1060000 220000
1045 홍원신 690906-1985214 2003-03-16 전북 011-7777-7777 영업부 사원 960000 152000
1046 허경운 760105-1458752 1999-05-04 경남 017-3333-3333 총무부 부장 2650000 165000
1047 산마루 780505-1234567 2001-07-15 서울 018-0505-0505 영업부 대리 2100000 112000
1048 이기상 790604-1415141 2001-06-07 전남 개발부 대리 2050000 106000
1049 이미성 830908-2456548 2000-04-07 인천 010-6654-8854 개발부 사원 1300000 130000
1050 이미인 810403-2828287 2003-06-07 경기 011-8585-5252 홍보부 대리 1950000 103000
1051 권영미 790303-2155554 2000-06-04 서울 011-5555-7548 영업부 과장 2260000 114400
1052 권옥경 820406-2000456 2000-10-10 경기 010-3644-5577 기획부 사원 1020000 105000
1053 김싱식 800715-1313131 1999-12-12 전북 011-7585-7474 자재부 사원 960000 108000
1054 정상호 810705-1212141 1999-10-16 강원 016-1919-4242 홍보부 사원 980000 114000
1055 정한나 820506-2425153 2004-06-07 서울 016-2424-4242 영업부 사원 1000000 104000
1056 전용재 800605-1456987 2004-08-13 인천 010-7549-8654 영업부 대리 1950000 200000
1057 이미경 780406-2003214 1998-02-11 경기 016-6542-7546 자재부 부장 2520000 176000
1058 김신제 800709-1321456 2003-08-08 인천 010-2415-5444 기획부 대리 1950000 180000
1059 임수봉 810809-2121244 2001-10-10 서울 011-4151-4154 개발부 사원 890000 102000
1060 김신애 810809-2111111 2001-10-10 서울 011-4151-4444 개발부 사원 900000 102000
*/
COMMIT;
--==>> 커밋 완료.
--○ TBL_INSABACKUP 테이블에서
-- 전화번호가 016, 017, 018, 019 로 시작하는 전화번호인 경우
-- 이를 모두 010 으로 변경하는 쿼리문을 구성한다.
SELECT *
FROM TBL_INSABACKUP;
SELECT '010' || '-' || SUBSTR(TEL, 5, 4) || '-' || SUBSTR(TEL, 10, 4)
, '010-' || SUBSTR(TEL, 5, 9)
, '010' || SUBSTR(TEL, 4)
FROM TBL_INSABACKUP;
UPDATE TBL_INSABACKUP
SET TEL = '010' || '-' || SUBSTR(TEL, 5, 4) || '-' || SUBSTR(TEL, 10, 4)
-- SET TEL = '010-' || SUBSTR(TEL, 5, 9)
-- '010' || '-' || SUBSTR(TEL, 5, 4) || '-' || SUBSTR(TEL, 10, 4)
-- '010-' || SUBSTR(TEL, 5, 9)
-- '010' || SUBSTR(TEL, 4)
WHERE TEL LIKE '016%'
OR
TEL LIKE '017%'
OR
TEL LIKE '018%'
OR
TEL LIKE '019%';
-- 혜성오빠 풀이!
/*
WHERE SUBSTR(TEL, 1, 3) IN ('016', '017', '018', '019')
*/
SELECT *
FROM TBL_INSABACKUP;
--[전화번호가 오로지 016이여야만 조건에 맞게되는 것.]
SELECT *
FROM TBL_INSABACKUP
WHERE TEL IN ('016', '017', '018', '019');
--==>> 조회 결과 없음.
--[전화번호 맨 앞 3자리만 추출하여 비교하자.]
SELECT *
FROM TBL_INSABACKUP
WHERE SUBSTR(TEL, 1, 3) IN ('016', '017', '018', '019');
-- '010' 이 아니라 숫자타입 010 으로 작성하지 않도록 한다!
SELECT TEL "기존전화번호", '010' || TEL "변경될전화번호"
FROM TBL_INSABACKUP
WHERE SUBSTR(TEL, 1, 3) IN ('016', '017', '018', '019');
-- SUBSTR(대상, 시작값) 끝 값을 정해주지 않으면, 맨 끝까지 추출.
SELECT TEL "기존전화번호", '010' || SUBSTR(TEL, 4) "변경될전화번호"
FROM TBL_INSABACKUP
WHERE SUBSTR(TEL, 1, 3) IN ('016', '017', '018', '019');
UPDATE TBL_INSABACKUP
SET TEL =
WHERE SUBSTR(TEL, 1, 3) IN ('016', '017', '018', '019');
--==>> 24개 행 이(가) 업데이트되었습니다.
SELECT *
FROM TBL_INSABACKUP;
--==>>
/*
NUM NAME SSN IBSADATE CITY TEL BUSEO JIKWI BASICPAY SUDANG
1001 홍길동 771212-1022432 1998-10-11 서울 011-2356-4528 기획부 부장 2610000 220000
1002 이순신 801007-1544236 2000-11-29 경기 010-4758-6532 총무부 사원 1320000 200000
1003 이순애 770922-2312547 1999-02-25 인천 010-4231-1236 개발부 부장 2550000 176000
1004 김정훈 790304-1788896 2000-10-01 전북 010-5236-4221 영업부 대리 1954200 170000
1005 한석봉 811112-1566789 2004-08-13 서울 010-5211-3542 총무부 사원 1420000 160000
1006 이기자 780505-2978541 2002-02-11 인천 010-3214-5357 개발부 과장 2265000 165000
1007 장인철 780506-1625148 1998-03-16 제주 011-2345-2525 개발부 대리 1250000 150000
1008 김영년 821011-2362514 2002-04-30 서울 010-2222-4444 홍보부 사원 950000 145000
1009 나윤균 810810-1552147 2003-10-10 경기 010-1111-2222 인사부 사원 840000 220400
1010 김종서 751010-1122233 1997-08-08 부산 011-3214-5555 영업부 부장 2540000 143000
1011 유관순 801010-2987897 2000-07-07 서울 010-8888-4422 영업부 사원 1020000 140000
1012 정한국 760909-1333333 1999-10-16 강원 010-2222-4242 홍보부 사원 880000 114000
1013 조미숙 790102-2777777 1998-06-07 경기 010-6666-4444 홍보부 대리 1601000 103000
1014 황진이 810707-2574812 2002-02-15 인천 010-3214-5467 개발부 사원 1100000 130000
1015 이현숙 800606-2954687 1999-07-26 경기 010-2548-3365 총무부 사원 1050000 104000
1016 이상헌 781010-1666678 2001-11-29 경기 010-4526-1234 개발부 과장 2350000 165000
1017 엄용수 820507-1452365 2000-08-28 인천 010-3254-2542 개발부 사원 950000 210000
1018 이성길 801028-1849534 2004-08-08 전북 010-1333-3333 개발부 사원 880000 123000
1019 박문수 780710-1985632 1999-12-10 서울 010-4747-4848 인사부 과장 2300000 181500
1020 유영희 800304-2741258 2003-10-10 전남 011-9595-8585 자재부 사원 880000 140000
1021 홍길남 801010-1111111 2001-09-07 경기 011-9999-7575 개발부 사원 875000 120000
1022 이영숙 800501-2312456 2003-02-25 전남 010-5214-5282 기획부 대리 1960000 180000
1023 김인수 731211-1214576 1995-02-23 서울 영업부 부장 2500000 187000
1024 김말자 830225-2633334 1999-08-28 서울 011-5248-7789 기획부 대리 1900000 170000
1025 우재옥 801103-1654442 2000-10-01 서울 010-4563-2587 영업부 사원 1100000 160000
1026 김숙남 810907-2015457 2002-08-28 경기 010-2112-5225 영업부 사원 1050000 150000
1027 김영길 801216-1898752 2000-10-18 서울 010-8523-1478 총무부 과장 2340000 187000
1028 이남신 810101-1010101 2001-09-07 제주 010-1818-4848 인사부 사원 892000 110000
1029 김말숙 800301-2020202 2000-09-08 서울 010-3535-3636 총무부 사원 920000 124000
1030 정정해 790210-2101010 1999-10-17 부산 010-6564-6752 총무부 과장 2304000 136400
1031 지재환 771115-1687988 2001-01-21 서울 010-5552-7511 기획부 부장 2450000 176000
1032 심심해 810206-2222222 2000-05-05 전북 010-8888-7474 자재부 사원 880000 108000
1033 김미나 780505-2999999 1998-06-07 서울 011-2444-4444 영업부 사원 1020000 104000
1034 이정석 820505-1325468 2005-09-26 경기 011-3697-7412 기획부 사원 1100000 160000
1035 정영희 831010-2153252 2002-05-16 인천 개발부 사원 1050000 140000
1036 이재영 701126-2852147 2003-08-10 서울 011-9999-9999 자재부 사원 960400 190000
1037 최석규 770129-1456987 1998-10-15 인천 011-7777-7777 홍보부 과장 2350000 205700
1038 손인수 791009-2321456 1999-11-15 부산 010-6542-7412 영업부 대리 2000000 150000
1039 고순정 800504-2000032 2003-12-28 경기 010-2587-7895 영업부 대리 2010000 160000
1040 박세열 790509-1635214 2000-09-10 경북 010-4444-7777 인사부 대리 2100000 130000
1041 문길수 721217-1951357 2001-12-10 충남 010-4444-5555 자재부 과장 2300000 165000
1042 채정희 810709-2000054 2003-10-17 경기 011-5125-5511 개발부 사원 1020000 200000
1043 양미옥 830504-2471523 2003-09-24 서울 010-8548-6547 영업부 사원 1100000 210000
1044 지수환 820305-1475286 2004-01-21 서울 011-5555-7548 영업부 사원 1060000 220000
1045 홍원신 690906-1985214 2003-03-16 전북 011-7777-7777 영업부 사원 960000 152000
1046 허경운 760105-1458752 1999-05-04 경남 010-3333-3333 총무부 부장 2650000 165000
1047 산마루 780505-1234567 2001-07-15 서울 010-0505-0505 영업부 대리 2100000 112000
1048 이기상 790604-1415141 2001-06-07 전남 개발부 대리 2050000 106000
1049 이미성 830908-2456548 2000-04-07 인천 010-6654-8854 개발부 사원 1300000 130000
1050 이미인 810403-2828287 2003-06-07 경기 011-8585-5252 홍보부 대리 1950000 103000
1051 권영미 790303-2155554 2000-06-04 서울 011-5555-7548 영업부 과장 2260000 114400
1052 권옥경 820406-2000456 2000-10-10 경기 010-3644-5577 기획부 사원 1020000 105000
1053 김싱식 800715-1313131 1999-12-12 전북 011-7585-7474 자재부 사원 960000 108000
1054 정상호 810705-1212141 1999-10-16 강원 010-1919-4242 홍보부 사원 980000 114000
1055 정한나 820506-2425153 2004-06-07 서울 010-2424-4242 영업부 사원 1000000 104000
1056 전용재 800605-1456987 2004-08-13 인천 010-7549-8654 영업부 대리 1950000 200000
1057 이미경 780406-2003214 1998-02-11 경기 010-6542-7546 자재부 부장 2520000 176000
1058 김신제 800709-1321456 2003-08-08 인천 010-2415-5444 기획부 대리 1950000 180000
1059 임수봉 810809-2121244 2001-10-10 서울 011-4151-4154 개발부 사원 890000 102000
1060 김신애 810809-2111111 2001-10-10 서울 011-4151-4444 개발부 사원 900000 102000
*/
COMMIT;
--==>> 커밋 완료.
20231101_02_hr.sql
SELECT USER
FROM DUAL;
--==>> HR
--○ EMPLOYEES 테이블의 직원들 SALARY 를 10% 인상한다.
-- 단, 부서명이 'IT'인 직원들만 한정한다.
-- (또한, 쿼리문에 대한 변경 결과 확인 후 ROLLBACK 을 수행한다~!!!)
-- #1 부서명이 IT인 부서번호 찾기
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'IT';
SELECT SALARY "이전급여"
, SALARY * 1.1 "10%인상급"
FROM EMPLOYEES
WHERE DEPARTMENT_ID
IN
(
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'IT'
);
--==>>
/*
이전급여 10%인상급
9000 9900
6000 6600
4800 5280
4800 5280
4200 4620
*/
UPDATE EMPLOYEES
SET SALARY = SALARY * 1.1
WHERE DEPARTMENT_ID
IN
(
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'IT'
);
SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID
IN
(
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'IT'
);
ROLLBACK;
SELECT *
FROM EMPLOYEES;
SELECT *
FROM DEPARTMENTS;
--[INNER JOIN 사용한 풀이]===================================
SELECT *
FROM EMPLOYEES E
INNER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE D.DEPARTMENT_NAME = 'IT';
--==========================================================
-- IT 부서 직원들의 FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID 조회
SELECT FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES;
SELECT FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE 부서명 = 'IT';
SELECT FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE 부서아이디 = 부서명이 'IT'인 부서의 부서아이디;
SELECT FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;
SELECT FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (부서명이 'IT'인 부서의 부서아이디);
-- IT 부서의 부서 ID를 구하는 쿼리문
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'IT';
SELECT FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'IT'
); --> 내부적으로 60으로 변한다.
--==>>
/*
FIRST_NAME LAST_NAME SALARY DEPARTMENT_ID
Alexander Hunold 9000 60
Bruce Ernst 6000 60
David Austin 4800 60
Valli Pataballa 4800 60
Diana Lorentz 4200 60
*/
SELECT FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID
, SALARY * 1.1 "10%인상된급여"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'IT'
);
--==>>
/*
FIRST_NAME LAST_NAME SALARY DEPARTMENT_ID 10%인상된급여
Alexander Hunold 9000 60 9900
Bruce Ernst 6000 60 6600
David Austin 4800 60 5280
Valli Pataballa 4800 60 5280
Diana Lorentz 4200 60 4620
*/
UPDATE EMPLOYEES
SET SALARY = SALARY * 1.1
WHERE DEPARTMENT_ID = (
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'IT'
);
--==>> 5개 행 이(가) 업데이트되었습니다.
-- 확인
SELECT *
FROM EMPLOYEES;
--==>>
/*
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
100 Steven King SKING 515.123.4567 2003-06-17 AD_PRES 24000 90
101 Neena Kochhar NKOCHHAR 515.123.4568 2005-09-21 AD_VP 17000 100 90
102 Lex De Haan LDEHAAN 515.123.4569 2001-01-13 AD_VP 17000 100 90
103 Alexander Hunold AHUNOLD 590.423.4567 2006-01-03 IT_PROG 9900 102 60
104 Bruce Ernst BERNST 590.423.4568 2007-05-21 IT_PROG 6600 103 60
105 David Austin DAUSTIN 590.423.4569 2005-06-25 IT_PROG 5280 103 60
106 Valli Pataballa VPATABAL 590.423.4560 2006-02-05 IT_PROG 5280 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 2007-02-07 IT_PROG 4620 103 60
108 Nancy Greenberg NGREENBE 515.124.4569 2002-08-17 FI_MGR 12008 101 100
109 Daniel Faviet DFAVIET 515.124.4169 2002-08-16 FI_ACCOUNT 9000 108 100
110 John Chen JCHEN 515.124.4269 2005-09-28 FI_ACCOUNT 8200 108 100
111 Ismael Sciarra ISCIARRA 515.124.4369 2005-09-30 FI_ACCOUNT 7700 108 100
112 Jose Manuel Urman JMURMAN 515.124.4469 2006-03-07 FI_ACCOUNT 7800 108 100
113 Luis Popp LPOPP 515.124.4567 2007-12-07 FI_ACCOUNT 6900 108 100
114 Den Raphaely DRAPHEAL 515.127.4561 2002-12-07 PU_MAN 11000 100 30
115 Alexander Khoo AKHOO 515.127.4562 2003-05-18 PU_CLERK 3100 114 30
116 Shelli Baida SBAIDA 515.127.4563 2005-12-24 PU_CLERK 2900 114 30
117 Sigal Tobias STOBIAS 515.127.4564 2005-07-24 PU_CLERK 2800 114 30
118 Guy Himuro GHIMURO 515.127.4565 2006-11-15 PU_CLERK 2600 114 30
119 Karen Colmenares KCOLMENA 515.127.4566 2007-08-10 PU_CLERK 2500 114 30
120 Matthew Weiss MWEISS 650.123.1234 2004-07-18 ST_MAN 8000 100 50
121 Adam Fripp AFRIPP 650.123.2234 2005-04-10 ST_MAN 8200 100 50
122 Payam Kaufling PKAUFLIN 650.123.3234 2003-05-01 ST_MAN 7900 100 50
123 Shanta Vollman SVOLLMAN 650.123.4234 2005-10-10 ST_MAN 6500 100 50
124 Kevin Mourgos KMOURGOS 650.123.5234 2007-11-16 ST_MAN 5800 100 50
125 Julia Nayer JNAYER 650.124.1214 2005-07-16 ST_CLERK 3200 120 50
126 Irene Mikkilineni IMIKKILI 650.124.1224 2006-09-28 ST_CLERK 2700 120 50
127 James Landry JLANDRY 650.124.1334 2007-01-14 ST_CLERK 2400 120 50
128 Steven Markle SMARKLE 650.124.1434 2008-03-08 ST_CLERK 2200 120 50
129 Laura Bissot LBISSOT 650.124.5234 2005-08-20 ST_CLERK 3300 121 50
130 Mozhe Atkinson MATKINSO 650.124.6234 2005-10-30 ST_CLERK 2800 121 50
131 James Marlow JAMRLOW 650.124.7234 2005-02-16 ST_CLERK 2500 121 50
132 TJ Olson TJOLSON 650.124.8234 2007-04-10 ST_CLERK 2100 121 50
133 Jason Mallin JMALLIN 650.127.1934 2004-06-14 ST_CLERK 3300 122 50
134 Michael Rogers MROGERS 650.127.1834 2006-08-26 ST_CLERK 2900 122 50
135 Ki Gee KGEE 650.127.1734 2007-12-12 ST_CLERK 2400 122 50
136 Hazel Philtanker HPHILTAN 650.127.1634 2008-02-06 ST_CLERK 2200 122 50
137 Renske Ladwig RLADWIG 650.121.1234 2003-07-14 ST_CLERK 3600 123 50
138 Stephen Stiles SSTILES 650.121.2034 2005-10-26 ST_CLERK 3200 123 50
139 John Seo JSEO 650.121.2019 2006-02-12 ST_CLERK 2700 123 50
140 Joshua Patel JPATEL 650.121.1834 2006-04-06 ST_CLERK 2500 123 50
141 Trenna Rajs TRAJS 650.121.8009 2003-10-17 ST_CLERK 3500 124 50
142 Curtis Davies CDAVIES 650.121.2994 2005-01-29 ST_CLERK 3100 124 50
143 Randall Matos RMATOS 650.121.2874 2006-03-15 ST_CLERK 2600 124 50
144 Peter Vargas PVARGAS 650.121.2004 2006-07-09 ST_CLERK 2500 124 50
145 John Russell JRUSSEL 011.44.1344.429268 2004-10-01 SA_MAN 14000 0.4 100 80
146 Karen Partners KPARTNER 011.44.1344.467268 2005-01-05 SA_MAN 13500 0.3 100 80
147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 2005-03-10 SA_MAN 12000 0.3 100 80
148 Gerald Cambrault GCAMBRAU 011.44.1344.619268 2007-10-15 SA_MAN 11000 0.3 100 80
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 2008-01-29 SA_MAN 10500 0.2 100 80
150 Peter Tucker PTUCKER 011.44.1344.129268 2005-01-30 SA_REP 10000 0.3 145 80
151 David Bernstein DBERNSTE 011.44.1344.345268 2005-03-24 SA_REP 9500 0.25 145 80
152 Peter Hall PHALL 011.44.1344.478968 2005-08-20 SA_REP 9000 0.25 145 80
153 Christopher Olsen COLSEN 011.44.1344.498718 2006-03-30 SA_REP 8000 0.2 145 80
154 Nanette Cambrault NCAMBRAU 011.44.1344.987668 2006-12-09 SA_REP 7500 0.2 145 80
155 Oliver Tuvault OTUVAULT 011.44.1344.486508 2007-11-23 SA_REP 7000 0.15 145 80
156 Janette King JKING 011.44.1345.429268 2004-01-30 SA_REP 10000 0.35 146 80
157 Patrick Sully PSULLY 011.44.1345.929268 2004-03-04 SA_REP 9500 0.35 146 80
158 Allan McEwen AMCEWEN 011.44.1345.829268 2004-08-01 SA_REP 9000 0.35 146 80
159 Lindsey Smith LSMITH 011.44.1345.729268 2005-03-10 SA_REP 8000 0.3 146 80
160 Louise Doran LDORAN 011.44.1345.629268 2005-12-15 SA_REP 7500 0.3 146 80
161 Sarath Sewall SSEWALL 011.44.1345.529268 2006-11-03 SA_REP 7000 0.25 146 80
162 Clara Vishney CVISHNEY 011.44.1346.129268 2005-11-11 SA_REP 10500 0.25 147 80
163 Danielle Greene DGREENE 011.44.1346.229268 2007-03-19 SA_REP 9500 0.15 147 80
164 Mattea Marvins MMARVINS 011.44.1346.329268 2008-01-24 SA_REP 7200 0.1 147 80
165 David Lee DLEE 011.44.1346.529268 2008-02-23 SA_REP 6800 0.1 147 80
166 Sundar Ande SANDE 011.44.1346.629268 2008-03-24 SA_REP 6400 0.1 147 80
167 Amit Banda ABANDA 011.44.1346.729268 2008-04-21 SA_REP 6200 0.1 147 80
168 Lisa Ozer LOZER 011.44.1343.929268 2005-03-11 SA_REP 11500 0.25 148 80
169 Harrison Bloom HBLOOM 011.44.1343.829268 2006-03-23 SA_REP 10000 0.2 148 80
170 Tayler Fox TFOX 011.44.1343.729268 2006-01-24 SA_REP 9600 0.2 148 80
171 William Smith WSMITH 011.44.1343.629268 2007-02-23 SA_REP 7400 0.15 148 80
172 Elizabeth Bates EBATES 011.44.1343.529268 2007-03-24 SA_REP 7300 0.15 148 80
173 Sundita Kumar SKUMAR 011.44.1343.329268 2008-04-21 SA_REP 6100 0.1 148 80
174 Ellen Abel EABEL 011.44.1644.429267 2004-05-11 SA_REP 11000 0.3 149 80
175 Alyssa Hutton AHUTTON 011.44.1644.429266 2005-03-19 SA_REP 8800 0.25 149 80
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 2006-03-24 SA_REP 8600 0.2 149 80
177 Jack Livingston JLIVINGS 011.44.1644.429264 2006-04-23 SA_REP 8400 0.2 149 80
178 Kimberely Grant KGRANT 011.44.1644.429263 2007-05-24 SA_REP 7000 0.15 149
179 Charles Johnson CJOHNSON 011.44.1644.429262 2008-01-04 SA_REP 6200 0.1 149 80
180 Winston Taylor WTAYLOR 650.507.9876 2006-01-24 SH_CLERK 3200 120 50
181 Jean Fleaur JFLEAUR 650.507.9877 2006-02-23 SH_CLERK 3100 120 50
182 Martha Sullivan MSULLIVA 650.507.9878 2007-06-21 SH_CLERK 2500 120 50
183 Girard Geoni GGEONI 650.507.9879 2008-02-03 SH_CLERK 2800 120 50
184 Nandita Sarchand NSARCHAN 650.509.1876 2004-01-27 SH_CLERK 4200 121 50
185 Alexis Bull ABULL 650.509.2876 2005-02-20 SH_CLERK 4100 121 50
186 Julia Dellinger JDELLING 650.509.3876 2006-06-24 SH_CLERK 3400 121 50
187 Anthony Cabrio ACABRIO 650.509.4876 2007-02-07 SH_CLERK 3000 121 50
188 Kelly Chung KCHUNG 650.505.1876 2005-06-14 SH_CLERK 3800 122 50
189 Jennifer Dilly JDILLY 650.505.2876 2005-08-13 SH_CLERK 3600 122 50
190 Timothy Gates TGATES 650.505.3876 2006-07-11 SH_CLERK 2900 122 50
191 Randall Perkins RPERKINS 650.505.4876 2007-12-19 SH_CLERK 2500 122 50
192 Sarah Bell SBELL 650.501.1876 2004-02-04 SH_CLERK 4000 123 50
193 Britney Everett BEVERETT 650.501.2876 2005-03-03 SH_CLERK 3900 123 50
194 Samuel McCain SMCCAIN 650.501.3876 2006-07-01 SH_CLERK 3200 123 50
195 Vance Jones VJONES 650.501.4876 2007-03-17 SH_CLERK 2800 123 50
196 Alana Walsh AWALSH 650.507.9811 2006-04-24 SH_CLERK 3100 124 50
197 Kevin Feeney KFEENEY 650.507.9822 2006-05-23 SH_CLERK 3000 124 50
198 Donald OConnell DOCONNEL 650.507.9833 2007-06-21 SH_CLERK 2600 124 50
199 Douglas Grant DGRANT 650.507.9844 2008-01-13 SH_CLERK 2600 124 50
200 Jennifer Whalen JWHALEN 515.123.4444 2003-09-17 AD_ASST 4400 101 10
201 Michael Hartstein MHARTSTE 515.123.5555 2004-02-17 MK_MAN 13000 100 20
202 Pat Fay PFAY 603.123.6666 2005-08-17 MK_REP 6000 201 20
203 Susan Mavris SMAVRIS 515.123.7777 2002-06-07 HR_REP 6500 101 40
204 Hermann Baer HBAER 515.123.8888 2002-06-07 PR_REP 10000 101 70
205 Shelley Higgins SHIGGINS 515.123.8080 2002-06-07 AC_MGR 12008 101 110
206 William Gietz WGIETZ 515.123.8181 2002-06-07 AC_ACCOUNT 8300 205 110
*/
SELECT FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID
, SALARY * 1.1 "10%인상된급여"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'IT'
);
ROLLBACK;
--==>> 롤백 완료.
--○ EMPLOYEES 테이블에서 JOB_TITLE 이 'Sales Manager' 인 사원들의
-- SALARY 를 해당 직무(직종)의 최고급여 (MAX_SALARY)로 수정한다.
-- 단, 입사일이 2006년 이전 (해당 년도 제외) 입사자에 한해 적용할 수 있도록 처리한다.
-- (또한, 변경에 대한 겨로가 확인 후 ROLLBACK 수행한다~!!!)
SELECT *
FROM EMPLOYEES;
SELECT *
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMPLOYEES';
SELECT *
FROM JOBS;
-- #1 입사일이 2006년 이전 (HIRE_DATE) (2006년 포함 X)
SELECT *
FROM EMPLOYEES
WHERE EXTRACT(YEAR FROM HIRE_DATE) < 2006;
-- #2 JOB_TITLE 이 'Sales Manager' 인 사원
SELECT *
FROM EMPLOYEES;
SELECT *
FROM EMPLOYEES E
INNER JOIN JOBS J
ON E.JOB_ID = J.JOB_ID
WHERE EXTRACT(YEAR FROM HIRE_DATE) < 2006 -- ① 입사일이 2006년 이전이면서
AND
J.JOB_TITLE = 'Sales Manager'; -- ② 직업명이 Sales Manager
-- #3 SALARY 를 해당 직무(직종)의 최고급여 (MAX_SALARY)로 수정
SELECT E.*, J.MAX_SALARY
FROM EMPLOYEES E
INNER JOIN JOBS J
ON E.JOB_ID = J.JOB_ID
WHERE EXTRACT(YEAR FROM HIRE_DATE) < 2006
AND
J.JOB_TITLE = 'Sales Manager';
SELECT E.*
FROM EMPLOYEES E
WHERE EXTRACT(YEAR FROM HIRE_DATE) < 2006
AND
E.JOB_ID
IN
(
SELECT JOB_ID
FROM JOBS
WHERE JOB_TITLE = 'Sales Manager'
);
SELECT JOB_ID, JOB_TITLE
FROM JOBS
WHERE JOB_TITLE = 'Sales Manager';
SELECT MAX_SALARY
FROM JOBS
WHERE JOB_TITLE = 'Sales Manager';
-- #4 실제로 데이터 변경.
UPDATE EMPLOYEES E
SET E.SALARY
=
(
SELECT MAX_SALARY
FROM JOBS
WHERE JOB_TITLE = 'Sales Manager'
)
WHERE EXTRACT(YEAR FROM HIRE_DATE) < 2006
AND
E.JOB_ID
IN
(
SELECT JOB_ID
FROM JOBS
WHERE JOB_TITLE = 'Sales Manager'
);
SELECT *
FROM EMPLOYEES;
SELECT *
FROM EMPLOYEES
WHERE EXTRACT(YEAR FROM HIRE_DATE) < 2006
AND
JOB_ID
IN
(
SELECT JOB_ID
FROM JOBS
WHERE JOB_TITLE = 'Sales Manager'
);
--==>>
/*
145 John Russell JRUSSEL 011.44.1344.429268 2004-10-01 SA_MAN 20080 0.4 100 80
146 Karen Partners KPARTNER 011.44.1344.467268 2005-01-05 SA_MAN 20080 0.3 100 80
147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 2005-03-10 SA_MAN 20080 0.3 100 80
*/
ROLLBACK;
--★★★★★[INNER JOIN을 통해 UPDATE를 한 방식!!!!!!!!!!!!!!!!!!!]===============
UPDATE
(
SELECT E.*, J.MAX_SALARY
FROM EMPLOYEES E
INNER JOIN JOBS J
ON E.JOB_ID = J.JOB_ID
WHERE EXTRACT(YEAR FROM HIRE_DATE) < 2006
AND
J.JOB_TITLE = 'Sales Manager'
)
SET SALARY = MAX_SALARY;
-->
--==============================================================================
SELECT *
FROM JOBS;
--==>>
/*
:
SA_MAN Sales Manager 10000 20080
:
*/
UPDATE EMPLOYEES
SET SALARY = ('Sales Manager'의 MAX_SALARY)
WHERE JOB_TITLE = 'Sales Manager'
AND 입사일이 2006년 이전;
UPDATE EMPLOYEES
SET SALARY = ('Sales Manager'의 MAX_SALARY)
WHERE JOB_ID = ('Sales Manager'의 JOB_ID)
AND 입사일이 2006년 이전;
UPDATE EMPLOYEES
SET SALARY = ('Sales Manager'의 MAX_SALARY)
WHERE JOB_ID = ('Sales Manager'의 JOB_ID)
AND HIRE_DATE이 2006년 이전;
UPDATE EMPLOYEES
SET SALARY = ('Sales Manager'의 MAX_SALARY)
WHERE JOB_ID = ('Sales Manager'의 JOB_ID)
AND TO_CHAR(HIRE_DATE, 'YYYY') < 2006;
UPDATE EMPLOYEES
SET SALARY = ('Sales Manager'의 MAX_SALARY)
WHERE JOB_ID = ('Sales Manager'의 JOB_ID)
AND TO_NUMBER(TO_CHAR(HIRE_DATE, 'YYYY')) < 2006;
UPDATE EMPLOYEES
SET SALARY = ('Sales Manager'의 MAX_SALARY)
WHERE JOB_ID = ('Sales Manager'의 JOB_ID)
AND TO_NUMBER(TO_CHAR(HIRE_DATE, 'YYYY')) < 2006;
-- 'Sales Manager'의 MAX_SALARY
SELECT MAX_SALARY
FROM JOBS
WHERE JOB_TITLE = 'Sales Manager';
--==>> 20080
-- 'Sales Manager'의 JOB_ID
SELECT JOB_ID
FROM JOBS
WHERE JOB_TITLE = 'Sales Manager';
--==>> SA_MAN
UPDATE EMPLOYEES
SET SALARY = (20080)
WHERE JOB_ID = ('SA_MAN')
AND TO_NUMBER(TO_CHAR(HIRE_DATE, 'YYYY')) < 2006;
-- ↓ 고정 값을 서브 쿼리로 구성.
UPDATE EMPLOYEES
SET SALARY = (
SELECT MAX_SALARY
FROM JOBS
WHERE JOB_TITLE = 'Sales Manager'
)
WHERE JOB_ID = (
SELECT JOB_ID
FROM JOBS
WHERE JOB_TITLE = 'Sales Manager'
)
AND TO_NUMBER(TO_CHAR(HIRE_DATE, 'YYYY')) < 2006;
--==>> 3개 행 이(가) 업데이트되었습니다.
SELECT *
FROM EMPLOYEES
WHERE JOB_ID = (
SELECT JOB_ID
FROM JOBS
WHERE JOB_TITLE = 'Sales Manager'
)
AND TO_NUMBER(TO_CHAR(HIRE_DATE, 'YYYY')) < 2006;
--=>>
/*
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
145 John Russell JRUSSEL 011.44.1344.429268 2004-10-01 SA_MAN 20080 0.4 100 80
146 Karen Partners KPARTNER 011.44.1344.467268 2005-01-05 SA_MAN 20080 0.3 100 80
147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 2005-03-10 SA_MAN 20080 0.3 100 80
*/
ROLLBACK;
--○ EMPLOYEES 테이블에서 SALARY 를
-- 각 부서의 이름별로 다른 인상률을 적용하여 수정할 수 있도록 한다.
-- Finance → 10% 인상
-- Executive → 15% 인상
-- Accounting → 20% 인상
-- (쿼리문에 의한 변경 결과 확인 후 ROLLBACK~!!!)
-- #1 부서명이 'Finance', 'Executive', 'Accounting' 인 부서테이블 조회
SELECT *
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME IN ('Finance', 'Executive', 'Accounting');
SELECT *
FROM EMPLOYEES;
-- #2 INNER JOIN을 통해 테이블 조인 후, 인상률 계산
SELECT DECODE(D.DEPARTMENT_NAME, 'Finance', 1.1, 'Executive', 1.15, 'Accounting', 1.2) "인상률"
FROM DEPARTMENTS D
WHERE DEPARTMENT_NAME IN ('Finance', 'Executive', 'Accounting');
SELECT E.*, DT.인상률, E.SALARY * DT.인상률 "인상된급여"
FROM
EMPLOYEES E
INNER JOIN
(
SELECT DECODE(D.DEPARTMENT_NAME, 'Finance', 1.1, 'Executive', 1.15, 'Accounting', 1.2) "인상률"
, DEPARTMENT_ID "부서ID"
FROM DEPARTMENTS D
WHERE DEPARTMENT_NAME IN ('Finance', 'Executive', 'Accounting')
)DT
ON E.DEPARTMENT_ID = DT.부서ID;
--==가영님꺼!!!!=======================
SELECT DECODE(D.DEPARTMENT_NAME,
FROM EMPLOYEES E
INNER JOIN
FROM DEPARTMENT D
--=====================================
-- #3 이걸 다시 UPDATE 로 구성...
--① [INNER JOIN 과 UPDATE]■■■■■■■■■■■■■■■■■■■■■■■■■■■■
UPDATE
(
SELECT E.*, DT.인상률, E.SALARY * DT.인상률 "인상된급여"
FROM
EMPLOYEES E
INNER JOIN
(
SELECT DECODE(D.DEPARTMENT_NAME, 'Finance', 1.1, 'Executive', 1.15, 'Accounting', 1.2) "인상률"
, DEPARTMENT_ID "부서ID"
FROM DEPARTMENTS D
WHERE DEPARTMENT_NAME IN ('Finance', 'Executive', 'Accounting')
)DT
ON E.DEPARTMENT_ID = DT.부서ID
)
SET SALARY = "인상된급여";
--■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
--------------------------------------------------------------------------------
SELECT E.*, DECODE(D.DEPARTMENT_NAME, 'Finance', 1.1, 'Executive', 1.15, 'Accounting', 1.2) "인상률"
, D.DEPARTMENT_NAME
FROM EMPLOYEES E
INNER JOIN
DEPARTMENTS D
ON
E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE DEPARTMENT_NAME IN ('Finance', 'Executive', 'Accounting');
/*
24000 Executive 1.15
17000 Executive 1.15
17000 Executive 1.15
12008 Finance 1.1
9000 Finance 1.1
8200 Finance 1.1
7700 Finance 1.1
7800 Finance 1.1
6900 Finance 1.1
12008 Accounting 1.2
8300 Accounting 1.2
*/
/*
27600 Executive 1.15
19550 Executive 1.15
19550 Executive 1.15
13208.8 Finance 1.1
9900 Finance 1.1
9020 Finance 1.1
8470 Finance 1.1
8580 Finance 1.1
7590 Finance 1.1
14409.6 Accounting 1.2
9960 Accounting 1.2
*/
ROLLBACK;
--○ EMPLOYEES 테이블에서 SALARY 를
-- 각 부서의 이름별로 다른 인상률을 적용하여 수정할 수 있도록 한다.
-- Finance → 10% 인상
-- Executive → 15% 인상
-- Accounting → 20% 인상
-- (쿼리문에 의한 변경 결과 확인 후 ROLLBACK~!!!)
SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID
IN
(
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME IN ('Finance', 'Executive', 'Accounting')
);
-- [SET 의 CASE WHEN과 UPDATE]■■■■■■■■■■■■■■■■■■■■■■■■■■■
UPDATE EMPLOYEES
SET SALARY
= CASE WHEN DEPARTMENT_ID
IN
(
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME IN ('Finance')
)
THEN SALARY * 1.1
WHEN DEPARTMENT_ID
IN
(
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME IN ('Executive')
)
THEN SALARY * 1.15
WHEN DEPARTMENT_ID
IN
(
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME IN ('Accounting')
)
THEN SALARY * 1.2
END
WHERE DEPARTMENT_ID
IN
(
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME IN ('Finance', 'Executive', 'Accounting')
) ;
--■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
--[선생님 푸링]
--○ EMPLOYEES 테이블에서 SALARY 를
-- 각 부서의 이름별로 다른 인상률을 적용하여 수정할 수 있도록 한다.
-- Finance → 10% 인상
-- Executive → 15% 인상
-- Accounting → 20% 인상
-- (쿼리문에 의한 변경 결과 확인 후 ROLLBACK~!!!)
SELECT *
FROM EMPLOYEES;
SELECT *
FROM DEPARTMENTS;
SELECT *
FROM DEPARTMENTS
WHERE 부서명 IN ('Finance', 'Executive', 'Accounting');
SELECT *
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME IN ('Finance', 'Executive', 'Accounting');
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME IN ('Finance', 'Executive', 'Accounting');
--==>>
/*
90
100
110
*/
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME IN (90, 100, 110);
SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID
IN (
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME IN ('Finance', 'Executive', 'Accounting')
);
-- SET에서는 ALIAS를 부여하면 안 된다. 값을 가져오는 것이므로.
-- ★ 인상이 없다면 자신의 급여 그대로 받으면 되므로
-- CASE WHEN 절의 ELSE일 경우 SALARY로 구성!!!
UPDATE EMPLOYEES
SET SALARY = CASE DEPARTMENT_ID WHEN ('Finance'의 부서아이디)
THEN SALARY * 1.1
WHEN ('Executive'의 부서아이디)
THEN SALARY * 1.15
WHEN ('Accounting'의 부서아이디)
THEN SALARY * 1.2
ELSE SALARY
END
WHERE DEPARTMENT_ID
IN (
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME IN ('Finance', 'Executive', 'Accounting')
);
-- ('Finance'의 부서아이디)
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'Finance';
--==>> 100
-- ('Executive'의 부서아이디)
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'Executive';
--==>> 90
-- ('Accounting'의 부서아이디)
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'Accounting';
--==>> 110
UPDATE EMPLOYEES
SET SALARY = CASE DEPARTMENT_ID WHEN (100)
THEN SALARY * 1.1
WHEN (90)
THEN SALARY * 1.15
WHEN (110)
THEN SALARY * 1.2
ELSE SALARY
END
WHERE DEPARTMENT_ID
IN (
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME IN ('Finance', 'Executive', 'Accounting')
);
UPDATE EMPLOYEES
SET SALARY = CASE DEPARTMENT_ID WHEN (
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'Finance'
)
THEN SALARY * 1.1
WHEN (
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'Executive'
)
THEN SALARY * 1.15
WHEN (
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'Accounting'
)
THEN SALARY * 1.2
ELSE SALARY
END
WHERE DEPARTMENT_ID
IN (
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME IN ('Finance', 'Executive', 'Accounting')
);
-- 이 업데이트 쿼리문은 WHERE 절이 없더라도 제대로 동작한다!
-- SET SALARY = CASE WHEN 문에서 조건에 맞을 때만 변경되고
-- ELSE 일 경우 SALARY로 그대로 적용되기 때문에!
-- WHERE 절이 없더라도 전체 레코드를 돌아도 문제 없다!
-- 하지만, 반드시 WHERE 조건절을 작성해 주도록 한다!
--★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
-- WHERE 절을 쓰지 않고 업데이트 할 경우 --> 107개 행 이(가) 업데이트되었습니다.
-- WHERE 절을 쓰고 업데이트 할 경우 --> 11개 행 이(가) 업데이트되었습니다.
--★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
--==>> 즉, 같은 결과를 내는 데도 더 많은 리소스 소모가 일어난다는 것이다.
-- 메모리에 107개의 행을 퍼올린 것과
-- 메모리에 11개의 행을 퍼올린 것 중 뭐가 더 바람직한가? ==> 11개!!!
--------------------------------------------------------------------------------
--■■■ DELETE ■■■--
-- 1. 테이블에서 지정된 행(레코드)ㅡㅇㄹ 삭제하는데 사용하는 구문
-- 2. 형식 및 구조
-- DELETE [FROM] 테이블명
-- [WHERE 조건절];
-- [ DELETE 강조 2개!!]★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
-- ① DELETE 구문 같은 경우에, FROM 꼭 붙여서 작성하자. 실수를 줄일 수 있다.
-- ② 『SELECT *』 부터 작성하여 먼저 작성할 항목을 조회한 후에
-- 『SELECT *』을 『DELETE』로 바꾸어서 삭제하자!
-- ★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
-- 참조가 어떻게 구성되어 있느냐에 따라서 삭제가 안 될 수 있다.
-- 자기참조가 있는 테이블 EMPLOYEES MANAGER_ID 이 EMP_NO를 참조하게끔 되어있다.
-- 해당 사원번호가 등록되어 있지 않으면 MANAGER_ID 로 등록 불가
-- 내가 관리하는 사원들이 있을 경우(MANAGER_ID) 내 사원번호 삭제 불가.
SELECT *
FROM EMPLOYEES
WHERE EMPLOYEES_ID = 198;
--○ EMPLOYEES 테이블에서 직원들의 데이터를 삭제한다.
-- 단, 부서명이 'IT'인 경우로 한정한다.
SELECT *
FROM EMPLOYEES;
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'IT';
SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID
IN
(
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'IT'
);
/*
103 Alexander Hunold AHUNOLD 590.423.4567 2006-01-03 IT_PROG 9000 102 60
104 Bruce Ernst BERNST 590.423.4568 2007-05-21 IT_PROG 6000 103 60
105 David Austin DAUSTIN 590.423.4569 2005-06-25 IT_PROG 4800 103 60
106 Valli Pataballa VPATABAL 590.423.4560 2006-02-05 IT_PROG 4800 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 2007-02-07 IT_PROG 4200 103 60
*/
SELECT D.*, D.MANAGER_ID
FROM DEPARTMENTS D
WHERE DEPARTMENT_NAME = 'IT';
SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID
IN
(
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'IT'
);
SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID
IN
(
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'IT'
);
-- 'IT' 부서 사원 지우기 과정
--[ ① EMPLOYEE_ID를 FOREIGN KEY로 갖는 테이블의 컬럼 찾기 ]======================
SELECT *
FROM USER_CONSTRAINTS
WHERE R_CONSTRAINT_NAME = 'EMP_EMP_ID_PK';
/*
OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME SEARCH_CONDITION R_OWNER R_CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHANGE INDEX_OWNER INDEX_NAME INVALID VIEW_RELATED
HR JHIST_EMP_ID_FK R JOB_HISTORY HR EMP_EMP_ID_PK NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2023-11-01
HR DEPT_MGR_FK R DEPARTMENTS HR EMP_EMP_ID_PK NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2023-11-01
HR EMP_MANAGER_FK R EMPLOYEES HR EMP_EMP_ID_PK NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2023-11-01
*/
--[ ② ①번에서의 제약 조건을 모두 삭제 ]=========================================
--ⓐ [JHIST_EMP_ID_FK : JOB_HISTORY에서의 EMPLOYEE_ID FOREIGN KEY 제거.]
--ALTER TABLE JOB_HISTORY001
--DROP CONSTRAINT JHIST_EMP_ID_FK;
--ⓑ [DEPT_MGR_FK : DEPARTMENTS에서의 EMPLOYEE_ID FOREIGN KEY 제거.]
--ALTER TABLE DEPARTMENTS001
--DROP CONSTRAINT DEPT_MGR_FK;
--ⓒ [EMP_MANAGER_FK : EMPLOYEES에서의 EMPLOYEE_ID FOREIGN KEY 제거.]
--ALTER TABLE EMPLOYEES001
--DROP CONSTRAINT EMP_MANAGER_FK;
--==============================================================================
--[ ③ EMPLOYEE_ID를 FOREIGN KEY로 갖는 제약조건을 모두 삭제했으므로 ]=============
--[ EMP_EMP_ID_PK 제약조건 제거 (PRIMARY KEY) ]
--ALTER TABLE EMPLOYEES001
--DROP CONSTRAINT EMP_EMP_ID_PK;
--==============================================================================
--[ ④ EMPLOYEES의 EMPLOYEE_ID는 더이상 PRIMARY KEY가 아니고, ]=====
--[ 이를 참조하는 FOREIGN KEY도 더이상 존재하지 않으므로 데이터삭제 가능. ]
/*
DELETE
FROM EMPLOYEES001
WHERE DEPARTMENT_ID
IN
(
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'IT'
);
*/
--==============================================================================
--※ 실제로는 EMPLOYEES 테이블의 데이터가(→ 삭제하고자 하는 대상 데이터)
-- 다른 레코드에 의해 참조당하고 있는 경우
-- 삭제되지 않을 수 있다는 사실을 인지할 수 있어야 하며
-- 그에 대한 이유도 알아야 한다.'[Oracle] > SQL (Program source)' 카테고리의 다른 글
| [Oracle] 20231031 [팀 과제 - 샘플 스키마 재구성] - 20231031_03_HR (0) | 2023.11.01 |
|---|---|
| [Oracle] 20231031 [프로그램소스] - 20231031_01_HR (0) | 2023.11.01 |
| [Oracle] 20231030 [프로그램소스] - 20231030_01_HR, TBL_INSA(중간 수행평가) (0) | 2023.10.30 |
| [Oracle] 20231027 [프로그램소스] - 20231027_01_SCOTT, TBL_INSA(중간 수행평가) (0) | 2023.10.27 |
| [Oracle] 20231026 [프로그램소스] - 231026_01_SCOTT, 231026_02_HR, 231026_03_SCOTT (0) | 2023.10.26 |