SELECT USER
FROM DUAL;
-- HR
-- EMPLOYEES 테이블의 직원들 SALARY를 10% 인상한다.
-- 단, 부서명이 'IT'인 경우로 한정한다.
-- (UPDATE 쿼리문 실행 및 이후 결과를 확인한 후 ROLLBACK 할 것)
SELECT *
FROM TAB;
SELECT *
FROM EMPLOYEES;
--==> 부서명 없음~!!!
-- 현재, EMPLOYEES 테이블은 부서명이 없다. >> 부서명IT는 DEPARTMENT 테이블에서 찾아라..
SELECT *
FROM DEPARTMENTS;
/*
EPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
:
*/
-- 60번으로 한정한다...
UPDATE EMPLOYEES
SET SALARY = SALARY * 1.1
WHERE DEPARTMENT_ID = 60; -- NAME 없고 ID만 있는 걸 눈으로 확인함.
-- 1) 부서명 IT 부서의 부서아이디
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'IT';
--==>> 60
UPDATE EMPLOYEES
SET SALARY = SALARY * 1.1
WHERE DEPARTMENT_ID = 부서명 IT 부서의 부서아이디;
UPDATE EMPLOYEES
SET SALARY = SALARY * 1.1
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'IT');
--==>> 5개 행 이(가) 업데이트되었습니다.
SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'IT');
/*
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
*/
ROLLBACK;
--○ EMPLOYEES 테이블에서 JOB_TITLE이 Sales Manager 인 사원의
-- SALARY 를 해당 직무(직종)의 최고 급여(MAX_SALARY)로 수정하는 쿼리문을 구성한다.
-- 단, 입사일이 2006년 이전(해당 년도 제외) 입사자에 한하여
-- 적용할 수 있도록 처리한다. -- MAX_SALARY 20080
-- (UPDATE 쿼리문 실행 및 이후 결과를 확인한 후 ROLLBACK 할 것)
SELECT *
FROM JOBS;
SELECT *
FROM EMPLOYEES;
SELECT *
FROM EMPLOYEES
WHERE JOB_ID = 'SA_MAN';
/*
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
*/
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 =(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 = 'SA_MAN';
/*
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
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
*/
ROLLBACK;
-- 롤백 완료
SELECT *
FROM DEPARTMENTS;
--Finance
--Executive
--Accounting
SELECT *
FROM EMPLOYEES;
--○ EMPLOYEES 테이블에서 SALARY를
-- 각 부서의 이름별로 다른 인상률을 적용하여 수정할 수 있도록 한다.
-- Finance → 10%
-- Executive → 15%
-- Accounting → 20%
-- 나머지 → 0%
-- (UPDATE 쿼리문 실행 및 이후 결과를 확인한 후 ROLLBACK 할 것)
-- 부서명 Finance , Executive, Accounting 조회
-- 상황에 따라 분기하는 구문
UPDATE EMPLOYEES
SET SALARY = CASE DEPARTMENT_ID WHEN ('Finance'의 부서아이디) 일 때 --DEPARTMENT NAME 이 없기 때문에
THEN SARARY * 1.1
WHEN('Executive'의 부서아이디)
THEN SARARY * 1.15
WHEN('Accounting'의 부서아이디)
THEN SARARY * 1.2
ELSE SARARY
END;
--('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 (
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;
--==>> 107개 행 이(가) 업데이트되었습니다. >> 이렇게 해도 기능상 문제는 없음
ROLLBACK;
--==>> 롤백 완료.
-- 메모리 덜 쓰게 하려면
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 (100,90,110);
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME IN ('Finance', 'Executive', 'Accounting')
);
-- (100,90,110)
WHERE DEPARTMENT_ID
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME IN ('Finance', 'Executive', 'Accounting');
/*
90
100
110
*/
-- 최종쿼리문
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'));
--==>> 11개 행 이(가) 업데이트되었습니다.
ROLLBACK;
-- 롤백완료
-- 『』 ◀ ★
--※
--○
/*
*/
-- →
댓글 영역