10일차_UPDATE, DELETE, 뷰, 시퀀스

------------------------------------------------
UPDATE

1. 테이블에서 기존의 데이터를 변경한다.

2. UPDATE 테이블_명
 SET 컬럼_명= 변경할_값[, 컬럼_명= 변경할_값, ...]
 [WHERE 조건];

INSA 테이블에서 사번(NUM)이 1048인 직원(이기상)의 전화번호를 '010-111-2222'로 수정.
SELECT *
 FROM insa
 WHERE num=1048;

UPDATE insa
 SET tel='010-111-2222'
 WHERE num=1048;
COMMIT;
--> 실행후 COMMIT 또는 ROLLBACK를 반드시 선택할 것.

INSA 테이블에서 사번(NUM)이 1048인 직원(이기상)의 부서와 직위를 '영업부', '과장'로 수정.
UPDATE insa
 SET buseo='영업부', jikwi='과장'
 WHERE num=1048;
COMMIT;

 

문제) INSA 테이블에서 '과장' 직위(jikwi) 이상인(과장, 부장) 경우만 수당(sudang)을 10% 인상합니다.

SELECT name, jikwi, sudang, sudang*1.1 AS "10% 인상된 수당"
 FROM insa
 WHERE jikwi IN ('과장', '부장');

UPDATE insa
 SET sudang = sudang * 1.1
 WHERE jikwi IN ('과장', '부장');
COMMIT;
 

문제) INSA 테이블에서 전화번호가 011, 016, 017, 018, 019로 시작되는 전화번호인 경우 모두 010-XXXX-XXXX으로 변경합니다.

SELECT *
  FROM insa
  WHERE SUBSTR(tel, 1, 3) != '010';

SELECT SUBSTR(tel, 4, 10) AS tel
  FROM insa
  WHERE SUBSTR(tel, 1, 3) != '010';

UPDATE insa
  SET tel = ('010' || SUBSTR(tel, 4, 10))
  WHERE SUBSTR(tel, 1, 3) != '010';
COMMIT;

 

HR 계정에서 employees 테이블의 직원들의 salary를 10% 인상합니다. 단, 부서명(department_name)이 'IT'인 경우로 한정.

SELECT e.first_name, e.last_name, e.salary, d.department_name
 FROM employees e, departments d
 WHERE e.department_id = d.department_id
  AND d.department_name='IT';

SELECT first_name, last_name, salary
 FROM employees
 WHERE department_id IN (SELECT department_id
     FROM departments
     WHERE department_name='IT');

UPDATE employees
 SET salary = salary*1.1
 WHERE department_id IN (SELECT department_id
     FROM departments
     WHERE department_name='IT');

ROLLBACK;

 

 

 

문제) HR 계정 employees 테이블에서 job_title이 'Sales Manager'인 직원들의 salary를 해당 직무의 최고 급여(max_salary)로 수정. 단, 입사일(hire_date)가 2005년 이전(해당 년도 포함) 입사자에 한해서 적용함.

SELECT *
  FROM employees
   WHERE job_id = (SELECT job_id FROM jobs
    WHERE job_title='Sales Manager')
  AND TO_CHAR(hire_date, 'YYYY') <= 2005;

SELECT max_salary FROM jobs
   WHERE job_title='Sales Manager';

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_CHAR(hire_date, 'YYYY') <= 2005;
ROLLBACK;

문제) HR 계정 employees 테이블에서 모든 직원의 salary를 해당 직원의 직무(job_id) 최고 급여(max_salary)로 수정.
예를 들어, AD_PRES는 40000, AD_VP는 30000, IT_PROG는 10000으로 수정됨.
--> 상관 하위 쿼리 이용.

SELECT first_name, last_name, salary, job_id
  , (SELECT max_salary FROM jobs
   WHERE job_id = e.job_id) AS max_salary
  FROM employees e;

UPDATE employees e
 SET salary = (SELECT max_salary FROM jobs
   WHERE job_id = e.job_id);

SELECT first_name, last_name, salary, job_id
  , (SELECT max_salary FROM jobs
   WHERE job_id = e.job_id) AS max_salary
  FROM employees e;

ROLLBACK;

 

문제) INSA 테이블에서 '영업부'인 경우는 기본급(basicpay)를 2100000으로 수정하고, '개발부'인 경우는 기본급을 2200000으로 수정, '총무부'인 경우는 기본급을 2000000으로 수정합니다. 단, 대리, 사원 직급(jikwi)만 해당.

UPDATE insa
 SET basicpay = (CASE
   WHEN buseo='영업부' THEN 2100000
   WHEN buseo='개발부' THEN 2200000
   WHEN buseo='총무부' THEN 2000000
      END)
   WHERE buseo IN ('총무부', '개발부', '영업부')
  AND jikwi IN ('사원', '대리');
COMMIT;

문제) HR 계정 employees 테이블에서 salary를 각 부서이름별(department_name)로 다른 인상율 적용합니다.
Finance -> 10%
Executive -> 15%
Accounting -> 20%

UPDATE employees
 SET salary = CASE
   WHEN department_id = (하위쿼리) THEN salary*1.1
   WHEN department_id = (하위쿼리) THEN salary*1.15
   WHEN department_id = (하위쿼리) THEN salary*1.2
   ELSE salary
       END;
ROLLBACK;

위에서 하위쿼리는
SELECT department_id FROM departments WHERE department_name='Finance'
SELECT department_id FROM departments WHERE department_name='Executive'
SELECT department_id FROM departments WHERE department_name='Accounting'
  

 

UPDATE employees
 SET salary = CASE
   WHEN department_id = (SELECT department_id FROM departments WHERE department_name='Finance') THEN salary*1.1
   WHEN department_id = (SELECT department_id FROM departments WHERE department_name='Executive') THEN salary*1.15
   WHEN department_id = (SELECT department_id FROM departments WHERE department_name='Accounting') THEN salary*1.2
   ELSE salary
       END;

 

문제) INSA 테이블에서 직위별로 기본급(basicpay) 인상율을 다르게 적용하는 쿼리 작성.
사원 ->  20%
대리 ->  15%
과장 ->  10%
부장 ->  5%

 

문제) INSA 테이블에서 직위별로 기본급(basicpay) 인상율을 다르게 적용하는 쿼리 작성.
단, 개발부 직원들(13명)만 적용할 것.
사원, 대리 ->  10%
과장, 부장 ->  5%

 

 

------------------------------------------------
DELETE

1. 테이블에서 지정한 행을 삭제하는데 사용한다.

2.
DELETE [FROM] 테이블_명 [WHERE 조건];

INSA 테이블에서 '서울' 지역 직원들만 삭제.
DELETE
 FROM insa
 WHERE city = '서울';
COMMIT;

INSA 테이블에서 사번(NUM)이 1048인 직원(이기상)의 정보를 삭제.
DELETE
 FROM insa
 WHERE num=1048;
COMMIT;

HR 계정에서 employees 테이블의 직원들의 정보를 삭제합니다.
단, 부서명(department_name)이 'IT'인 경우로 한정.
--> 실제로는 employees 테이블의 정보가 다른 테이블에 의해서 참조 당하는 경우 삭제가 안될 수 있습니다.

DELETE
 FROM employees
 WHERE department_id = (SELECT department_id FROM departments
    WHERE department_name = 'IT');
COMMIT;

DELETE FROM employees
  WHERE employee_id=103; --X. 자신이 다른 직원의 매니저로 연결된 상태
DELETE FROM employees
  WHERE employee_id=104; --O
ROLLBACK; 

 

***참조 당하는 경우 삭제가 안될 수 있습니다. FK 제약 지정시 삭제 옵션(on delete cascade) 추가된 상태인 경우 삭제 가능.

 

------------------------------------------------
뷰(View)

1. 뷰란 이미 특정한 데이터베이스 내에 존재하는 하나 이상의 테이블에서 사용자가 얻기 원하는 데이터들만을 정확하고 편하게 가져오기 위하여 사전에 원하는 컬럼들 만을 모아서 만들어 놓은 가상의 테이블로 편리성 및 보안에 목적이 있다. 가상의 테이블이란 뷰가 실제로 존재하는 테이블이 아니라 하나 이상의 테이블에서 파생된 또 다른 정보를 볼 수 있는 방법 이며 그 정보들을 추출해내는 SQL 문장이라고 볼 수 있다.

2. CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW 뷰이름
 [(alias[, alias]...]
 AS subquery
 [WITH CHECK OPTION]
 [WITH READ ONLY];

HR 계정 employees 테이블의 정보를 얻는 뷰 작성.
예를 들어,
employees, departments, locations, countries, regions 테이블.
직원 정보(first_name, last_name, job_title, department_name
, city, COUNTRY_NAME, REGION_NAME) 출력하는 쿼리인 경우

SELECT e.first_name, e.last_name, d.department_name, l.city
 , c.country_name, r.region_name
 FROM employees e, departments d, locations l, countries c, regions r
 WHERE e.department_id = d.department_id
  AND d.location_id = l.location_id
  AND l.country_id = c.country_id
  AND c.region_id = r.region_id;
와 같이 작성하면 복잡하고 어렵다.
이 쿼리에 대한 사용빈도가 높다면 이를 뷰로 만드는 것이 좋다.

CREATE OR REPLACE VIEW employees_detail_view
AS
SELECT e.first_name, e.last_name, d.department_name, l.city
 , c.country_name, r.region_name
 FROM employees e, departments d, locations l, countries c, regions r
 WHERE e.department_id = d.department_id
  AND d.location_id = l.location_id
  AND l.country_id = c.country_id
  AND c.region_id = r.region_id;

SELECT * FROM employees_detail_view;

--뷰의 구조 확인
DESC employees_detail_view;

--뷰의 소스 확인
SELECT view_name, text FROM user_views
 WHERE view_name='EMP_DETAILS_VIEW'; --뷰이름은 대문자로 작성

 

employees 테이블에서 급여(salary) 많은 직원 중에서 두 번째 직원 출력. 뷰 생성.
SELECT rn, employee_id, first_name, last_name, salary
FROM (SELECT ROWNUM AS rn, employee_id, first_name, last_name, salary 
FROM (SELECT employee_id, first_name, last_name, salary
 FROM employees
 ORDER BY salary DESC))
WHERE rn=2;

--뷰를 생성할 때 조건절 부분은 넣지 않는 것이 좋다.
CREATE OR REPLACE VIEW empView2
AS
SELECT rn, employee_id, first_name, last_name, salary
FROM (SELECT ROWNUM AS rn, employee_id, first_name, last_name, salary 
FROM (SELECT employee_id, first_name, last_name, salary
 FROM employees
 ORDER BY salary DESC));

SELECT *
 FROM empView2
 WHERE rn=1;
 
SELECT *
 FROM empView2
 WHERE rn=2;

 

 

문제) INSA 테이블에서 아래 결과를 얻는 뷰 작성.
이름, 부서, 출신도, 기본급, 수당, 기본급+수당(총급여), 세금, 실수령액 출력
       세금: 총급여가 250만원 이상이면 2%, 200만원 이상이면 1%, 나머지 0.
       실수령액: 총급여-세금
       CASE~END 문 사용.

원본 SELECT 쿼리 준비
SELECT 이름, 부서, 출신도, 기본급, 수당, 총급여, 세금, 총급여-세금 AS 실수령액
 FROM (SELECT name AS 이름, buseo AS 부서, city AS 출신도
 , basicpay AS 기본급
 , sudang AS 수당, basicpay+sudang AS 총급여
 , CASE
  WHEN basicpay+sudang>=2500000 
   THEN ROUND((basicpay+sudang) * 0.02)
  WHEN basicpay+sudang>=2000000 
   THEN ROUND((basicpay+sudang) * 0.01)
  ELSE 0
   END AS 세금
 FROM insa);

뷰 생성 구문 작성
CREATE OR REPLACE VIEW  insa_details_view
AS
SELECT 이름, 부서, 출신도, 기본급, 수당, 총급여, 세금
 , 총급여-세금 AS 실수령액
 FROM (SELECT name AS 이름, buseo AS 부서, city AS 출신도
 , basicpay AS 기본급
 , sudang AS 수당, basicpay+sudang AS 총급여
 , CASE
  WHEN basicpay+sudang>=2500000 
   THEN ROUND((basicpay+sudang) * 0.02)
  WHEN basicpay+sudang>=2000000 
   THEN ROUND((basicpay+sudang) * 0.01)
  ELSE 0
   END AS 세금
 FROM insa);

뷰 실행 테스트
SELECT * FROM insa_details_view;

 

문제) HR 계정 employees 테이블에서 직원 정보 출력시 부서명(department_name), 직위명(job_title)을 같이 출력하는 뷰 작성. 부서명이 없어도 직원 정보는 출력할 것. 뷰이름은 employees_all_view로 지정.

SELECT EMPLOYEE_ID
      ,FIRST_NAME
      ,LAST_NAME
      ,EMAIL
      ,PHONE_NUMBER
      ,HIRE_DATE
      ,JOB_ID        --> JOB_TITLE
      ,SALARY
      ,COMMISSION_PCT
      ,MANAGER_ID
      ,DEPARTMENT_ID --> DEPARTMENT_NAME
      FROM employees;

CREATE OR REPLACE VIEW employees_all_view
AS
SELECT e.employee_id
      ,e.first_name
      ,e.last_name
      ,e.email
      ,e.phone_number
      ,e.hire_date
      ,j.job_title AS job_title
      ,e.salary
      ,e.commission_pct
      ,e.manager_id
      ,d.department_name AS department_name
      FROM employees e, departments d, jobs j
 WHERE e.department_id = d.department_id(+)
  AND e.job_id = j.job_id;

SELECT * FROM employees_all_view;

문제) HR 계정 employees 테이블에서 부서명(department_name)별 직원의 수 출력. employees_all_view 사용할 것.

SELECT department_name, COUNT(*) AS 부서별직원수
 FROM employees_all_view
 GROUP BY department_name;
 

 

3. 인라인 뷰
인라인 뷰는 FROM 절에서 서브 쿼리를 사용하여 생성한 임시 뷰이다. 인라인 뷰는 SQL 문이 실행되는 동안만 임시적으로 정의된다. 즉, 객체로서 저장되지 않는다.

employees 테이블에서 입사일(hire_date)이 빠른 순으로 5순위까지만 출력.
SELECT *
 FROM (SELECT first_name, last_name, hire_date
 , RANK() OVER(ORDER BY hire_date ASC) AS rank
 FROM employees)
 WHERE rank <= 5;

--인라인 뷰 기반 DELETE 쿼리 실행
employees 테이블에서 job_title이 'Shipping Clerk'인 직원 삭제.

하위 쿼리를 이용하는 경우
DELETE
 FROM employees
 WHERE job_id IN (SELECT job_id FROM jobs
    WHERE job_title='Shipping Clerk');
--> 실제로는 employees 테이블의 정보가 다른 테이블에 의해서 참조 당하는 경우 삭제가 안될 수 있습니다.
ROLLBACK;

인라인 뷰를 이용하는 경우
DELETE
 FROM (SELECT * FROM employees e, jobs j
   WHERE e.job_id=j.job_id) ej  --AS 구문 뺄 것.
 WHERE ej.job_title = 'Shipping Clerk';
ROLLBACK;

-- 인라인 뷰 기반 UPDATE 쿼리 실행
employees 테이블에서 job_title이 'Shipping Clerk'인 직원의 salary를 10% 인상.

하위 쿼리를 이용하는 경우
UPDATE employees
 SET salary = salary * 1.1
 WHERE job_id IN (SELECT job_id FROM jobs
    WHERE job_title='Shipping Clerk');

인라인 뷰를 이용하는 경우
UPDATE (SELECT * FROM employees e, jobs j
   WHERE e.job_id=j.job_id) ej
 SET ej.salary = ej.salary * 1.1
 WHERE ej.job_title = 'Shipping Clerk';
ROLLBACK;

문제) HR 계정에서 (departments 테이블) department_name은 'Sales'이고, (jobs 테이블) job_title은 'Sales Manager'인 경우만 (employees 테이블) salary를 10% 인상. 인라인 뷰 이용할 것.

UPDATE ( departments, jobs, employees 테이블에 대한 JOIN 쿼리 ) ej
 SET ej.salary = ej.salary * 1.1
 WHERE ej.department_name='Sales' AND ej.job_title='Sales Manager';
ROLLBACK;

 

문제) HR 계정 employees 테이블에서 salary를 각 부서이름별(department_name)로 다른 인상율 적용합니다. 인라인 뷰 이용할 것.
Finance -> 10%
Executive -> 15%
Accounting -> 20%

UPDATE (SELECT * FROM employees e, departments d
  WHERE e.department_id=d.department_id) ed
 SET salary = CASE
   WHEN department_name='Finance' THEN salary*1.1
   WHEN department_name='Executive' THEN salary*1.15
   WHEN department_name='Accounting' THEN salary*1.2
   ELSE salary
       END;
ROLLBACK;

4. WITH CHECK OPTION 지정 뷰
WITH CHECK OPTION 절을 사용하면 뷰를 통해 참조 무결성(reference integrity)을 검사할 수 있고 DB 레벨에서의 constraint 적용이 가능하다.

INSA 테이블에서 buseo가 '영업부'인 직원만 출력하는 뷰 생성.

CREATE OR REPLACE VIEW insa_op_view
AS
SELECT *
 FROM insa
 WHERE buseo='영업부';

CREATE OR REPLACE VIEW insa_dp_view
AS
SELECT *
 FROM insa
 WHERE buseo='영업부' -->이 조건식이 제약으로 지정될 예정임.
WITH CHECK OPTION CONSTRAINT insa_check_view;

INSERT INTO insa_op_view (num, name, ssn, ibsaDate
    , city, tel, buseo, jikwi, basicPay, sudang)
  VALUES (1102, '박길동', '801212-1022432', '1998-10-11'
  , '서울', '011-2356-5555', '기획부', '부장', 2610000, 200000);
COMMIT;
--> 뷰를 통해서도 INSERT가 가능합니다.

INSERT INTO insa_dp_view (num, name, ssn, ibsaDate
    , city, tel, buseo, jikwi, basicPay, sudang)
  VALUES (1103, '박길동', '801212-1022432', '1998-10-11'
  , '서울', '011-2356-5555', '기획부', '부장', 2610000, 200000);
--> ORA-01402
--> buseo가 '기획부'인 경우는 WITH CHECK OPTION에 위배됨.
--> buseo는 '영업부'만 가능함.

INSERT INTO insa_dp_view (num, name, ssn, ibsaDate
    , city, tel, buseo, jikwi, basicPay, sudang)
  VALUES (1103, '박길동', '801212-1022432', '1998-10-11'
  , '서울', '011-2356-5555', '영업부', '부장', 2610000, 200000);
COMMIT;

SELECT * FROM insa_dp_view;

 

---------------------------------------------------
시퀀스 (SEQUENCE)

1. 데이터베이스와 관련된 응용프로그램을 개발하다 보면, 연속적으로 숫자 값을 자동으로 증가시켜야 하는 경우가 있다. 이와 같이 특정 컬럼에 대해 유일한 값을 연속적으로 생성시키기 위해 사용하는 방법으로 오라클에서 제공하는 것이 sequence라는 객체이다.

2. CREATE SEQUENCE seq_name
 [INCREMENT BY n] /* n= 증가치 */
 [START WITH n] /* n= 시작값 */
 [[NO]MAXVALUE n] /* n= 최고값 */
 [[NO]MINVALUE n] /* n= 최소값 */
 [[NO]CYCLE] /* MAXVALUE에 도달하면 초기값 부터 다시 시작 여부 */
 [[NO]CACHE n] /* 미리 캐시 해 두는 개수 */

CREATE TABLE bbs (
 sid NUMBER PRIMARY KEY  --글번호(자동 번호 증가, 자동 입력)
 ,name VARCHAR2(10)      --글쓴이 이름
 ,content VARCHAR2(100)  --글내용
 ,writeday DATE  DEFAULT SYSDATE  --글쓴 날짜(현재 날짜 자동 입력)
);
--> sid 운영하려면 시퀀스 객체 필요함.
--> 자동 입력되는 컬럼은 입력 항목에서 제외해야 됩니다.

CREATE SEQUENCE bbs_seq;  -->시퀀스 객체 생성

INSERT INTO bbs (sid, name, content)
 VALUES (bbs_seq.nextval, 'hong', 'TEST');
INSERT INTO bbs (sid, name, content)
 VALUES (bbs_seq.nextval, 'kim', 'KOREA');
COMMIT;
SELECT * FROM bbs;
--> sid 컬럼의 값은 자동 증가, 자동 입력되어 있다.

--현재 증가된 시퀀스 번호 확인
SELECT bbs_seq.currval FROM dual;

--시퀀스 삭제
DROP SEQUENCE bbs_seq;

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