0206 오라클 DB 문제풀이 - 데이터 조작어 (INSERT, UPDATE), 트랜잭션

1. 간단 문제 (279P / 309P)

1) 279P - EMP_TEMP 테이블의 사원들 중에서 급여 2500 이하인 사원만

추가 수당을 50으로 수정하는 다음 코드들 채워 보세요.

--279P Q1.
UPDATE EMP_TEMP
SET COMM = 50
WHERE SAL <= 2500;
SELECT * FROM EMP_TEMP
ROLLBACK;

2) 세션 A,B의 결과값을 입력하시오

--309P
--Q1.
CREATE TABLE DEPT_HW AS SELECT * FROM DEPT;
SELECT * FROM DEPT_HW;

--1
UPDATE DEPT_HW
SET DNAME='DATABASE', LOC='SEOUL'
WHERE DEPTNO = 30;
SELECT * FROM DEPT_HW;
--세션A
ROLLBACK;
--세션 A, B 순서대로 각자
COMMIT;

 

 

2. 본 문제 (287P)

1)

--287P
--Q1.
--생략 안하는 방식
INSERT INTO CHAP10HW_DEPT (DEPTNO,DNAME,LOC)
VALUES (50,'ORACLE','BUSAN');
INSERT INTO CHAP10HW_DEPT (DEPTNO,DNAME,LOC)
VALUES (60,'SQL','ILSAN');
INSERT INTO CHAP10HW_DEPT (DEPTNO,DNAME,LOC)
VALUES (70,'SELECT','INCHEON');
INSERT INTO CHAP10HW_DEPT (DEPTNO,DNAME,LOC)
VALUES (80,'DML','BUNDANG');
INSERT INTO CHAP10HW_DEPT (DEPTNO,DNAME,LOC)
VALUES (50,'ORACLE','BUSAN');

--생략 하는 방식
INSERT INTO CHAP10HW_DEPT VALUES (50,'ORACLE','BUSAN');
INSERT INTO CHAP10HW_DEPT VALUES (60,'SQL','ILSAN');
INSERT INTO CHAP10HW_DEPT VALUES (70,'SELECT','INCHEON');
INSERT INTO CHAP10HW_DEPT VALUES (80,'DML','BUNDANG');

--다중 INSERT 하는 방식
INSERT ALL 
INTO CHAP10HW_DEPT VALUES (50,'ORACLE','BUSAN')
INTO CHAP10HW_DEPT VALUES (60,'SQL','ILSAN')
INTO CHAP10HW_DEPT VALUES (70,'SELECT','INCHEON')
INTO CHAP10HW_DEPT VALUES (80,'DML','BUNDANG')
SELECT * FROM DUAL;
SELECT * FROM CHAP10HW_DEPT;
ROLLBACK ;

결과값

2)

--Q2
--생략 안하는 방식
INSERT INTO CHAP10HW_EMP  (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (7201,'TEST_USER1','MANAGER',7788,'2016-01-02',4500,NULL,50);
INSERT INTO CHAP10HW_EMP  (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (7202,'TEST_USER2','CLERK',7201,'2016-02-11',1800,NULL,50);
INSERT INTO CHAP10HW_EMP  (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (7203,'TEST_USER3','ANALYST',7201,'2016-04-11',3400,NULL,60);
INSERT INTO CHAP10HW_EMP  (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (7204,'TEST_USER4','SALESMAN',7201,'2016-05-31',2700,300,60);
INSERT INTO CHAP10HW_EMP  (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (7205,'TEST_USER5','CLERK',7201,'2016-07-20',2600,NULL,70);
INSERT INTO CHAP10HW_EMP  (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (7206,'TEST_USER6','CLERK',7201,'2016-09-08',2600,NULL,70);
INSERT INTO CHAP10HW_EMP  (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (7207,'TEST_USER7','LECTURER',7201,'2016-10-28',2300,NULL,80);
INSERT INTO CHAP10HW_EMP  (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (7207,'TEST_USER8','STUDENT',7201,'2018-03-09',1200,NULL,80);

--생략 하는 방식
INSERT INTO CHAP10HW_EMP VALUES (7201,'TEST_USER1','MANAGER',7788,'2016-01-02',4500,NULL,50);
INSERT INTO CHAP10HW_EMP VALUES (7202,'TEST_USER2','CLERK',7201,'2016-02-11',1800,NULL,50);
INSERT INTO CHAP10HW_EMP VALUES (7203,'TEST_USER3','ANALYST',7201,'2016-04-11',3400,NULL,60);
INSERT INTO CHAP10HW_EMP VALUES (7204,'TEST_USER4','SALESMAN',7201,'2016-05-31',2700,NULL,60);
INSERT INTO CHAP10HW_EMP VALUES (7205,'TEST_USER5','CLERK',7201,'2016-07-20',2600,NULL,70);
INSERT INTO CHAP10HW_EMP VALUES (7206,'TEST_USER6','CLERK',7201,'2016-09-08',2600,NULL,70);
INSERT INTO CHAP10HW_EMP VALUES (7207,'TEST_USER7','LECTURER',7201,'2016-10-28',2300,NULL,80);
INSERT INTO CHAP10HW_EMP VALUES (7207,'TEST_USER8','STUDENT',7201,'2018-03-09',1200,NULL,80);
ROLLBACK;

--다중 INSERT 
--WHY 안됨 -> 값 제대로 넣기
SELECT * FROM CHAP10HW_EMP;  
INSERT ALL
INTO CHAP10HW_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7201,'TEST_USER1','MANAGER',7788,'2016-01-02',4500,NULL,50)
INTO CHAP10HW_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7202,'TEST_USER2','CLERK',7201,'2016-02-11',1800,NULL,50)
INTO CHAP10HW_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7203,'TEST_USER3','ANALYST',7201,'2016-04-11',3400,NULL,60)
INTO CHAP10HW_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7204,'TEST_USER4','SALESMAN',7201,'2016-05-31',2700,300,60)
INTO CHAP10HW_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7205,'TEST_USER5','CLERK',7201,'2016-07-20',2600,NULL,70)
INTO CHAP10HW_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7206,'TEST_USER6','CLERK',7201,'2016-09-08',2600,NULL,70)
INTO CHAP10HW_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7207,'TEST_USER7','LECTURER',7201,'2016-10-28',2300,NULL,80)
INTO CHAP10HW_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7207,'TEST_USER8','STUDENT',7201,'2018-03-09',1200,NULL,80)
SELECT * FROM DUAL;
SELECT * FROM CHAP10HW_EMP;

3)

--Q3
--서브 쿼리 : 
SELECT AVG(SAL) FROM CHAP10HW_EMP WHERE DEPTNO = 50;
--확인
SELECT * FROM CHAP10HW_EMP WHERE SAL > (SELECT AVG(SAL) FROM CHAP10HW_EMP WHERE DEPTNO = 50);
--평균 값 구하는 법?
UPDATE CHAP10HW_EMP 
SET DEPTNO = 70
WHERE SAL > (SELECT AVG(SAL) FROM CHAP10HW_EMP WHERE DEPTNO = 50);
--결과값 확인
SELECT * FROM CHAP10HW_EMP WHERE DEPTNO = 70 ;

서브쿼리

서브쿼리에 해당 하는 사원 정보

결과값

4) 

--Q4
--서브 쿼리 :
SELECT MIN(HIREDATE) FROM CHAP10HW_EMP WHERE DEPTNO = 60;
--확인
SELECT * FROM CHAP10HW_EMP WHERE HIREDATE > (SELECT MIN(HIREDATE) FROM CHAP10HW_EMP WHERE DEPTNO = 60);
--문제 처리
UPDATE CHAP10HW_EMP 
SET DEPTNO = 80,
SAL = SAL*1.1
WHERE HIREDATE > (SELECT MIN(HIREDATE) FROM CHAP10HW_EMP WHERE DEPTNO = 60);
SELECT * FROM CHAP10HW_EMP ;
SELECT * FROM CHAP10HW_EMP WHERE DEPTNO = 80;

서브쿼리

서브쿼리에 해당 하는 사원 정보

결과값

5)

--Q5
--서브쿼리
SELECT E.EMPNO FROM CHAP10HW_EMP E, CHAP10HW_SALGRADE
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL AND GRADE = 5;
--확인 1
SELECT * FROM CHAP10HW_DEPT;  
SELECT * FROM CHAP10HW_EMP;  
SELECT * FROM CHAP10HW_SALGRADE;  
--확인 2
SELECT E.EMPNO
FROM CHAP10HW_EMP E, CHAP10HW_SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL AND GRADE = 5;
--확인 3
SELECT * FROM CHAP10HW_EMP WHERE EMPNO IN (7839,7201,7203,7205,7206);
--확인 3의 다른 방법
SELECT * FROM CHAP10HW_EMP WHERE EMPNO IN (SELECT E.EMPNO
FROM CHAP10HW_EMP E, CHAP10HW_SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL AND GRADE = 5);
--삭제 방법
DELETE FROM CHAP10HW_EMP 
WHERE EMPNO IN (SELECT E.EMPNO FROM CHAP10HW_EMP E, CHAP10HW_SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL AND GRADE = 5);

SELECT * FROM CHAP10HW_EMP ;

ROLLBACK;

서브쿼리에 해당 하는 사원 정보

서브쿼리 전체값

GRADE = 5의 사원이 삭제된 결과 값