Oracle 시퀀스, DDL 및 서브쿼리 활용문제

학습 목표

– 시퀀스 활용과 DDL 활용방법을 숙지한다

단 서브쿼리의 숙지를 위해 서브쿼리만 사용하도록하자

그리고 DCL의 숙지를 위해 테이블을 추가 수정 삭제 시 오류 문으로 인해 잘못된 정보를 저장하지

않도록 테이블 추가 삭제 수정시 SELECT문을 활용하여 사전 데이터를 확인하여 작업하자

그리고 ROLLBACK과 COMMIT을 활용하여 잘못된 정보 저장을 방지하자

 

 

DDL 문제 -

테이블을 구조와 정보를 복사하는 방법

 

1. EMP 테이블과 같은 EMP_TEMP 테이블 생성하시오

CREATE TABLE EMP_TEMP AS SELECT * FROM EMP;

 

2. DEPT 테이블과 같은 DEPT_TEMP 테이블을 생성하시오

CREATE TABLE DEPT_TEMP AS SELECT * FROM DEPT;

 

3. SALGRADE 테이블과 같은 SALGRADE_TEMP 테이블을 생성하시오

CREATE TABLE SALGRADE_TEMP AS SELECT * FROM SALGRADE;

 

4. 교육생의 TRAINEES 테이블을 만드시오

IDX(숫자 4자리이상), NAME(문자형 5자리이상), TEL(문자형 13자리이상),

CLASS (문자형 10자리이상) ROOM(숫자 3자리이상), INDATE(날짜형),

GENDER(남자 또는 여자 둘 중 하나 지정)

 

아이디 넘버를 PRIMARY KEY로 설정하고 이름은 필수입력값으로 지정한다

제약조건은 필히 ALTER 사용

 

ALTER TABLE TRAINEES ADD CONSTRAINT TRAINEES_CK_GENDER CHECK (GENDER IN('남','여'));

 

5. 교육생을 추가하시오 ( 교육생 정보는 아래와 같다 )

일반방법으로 추가 한다  1, '이순신', '02-3333-5555', '자바개발자반', 401 2019-06-05, 남

 

INSERT INTO COUNTRIES(IDX, NAME, TEL, CLASS, ROOM, INDATE)

VALUES(1,'이순신', '02-3333-5555', '자바개발자반', 401 2019-06-05, 남);

 

두 번째는 ( 아이디 번호를 작성할 때 MAX 활용법과 시퀀스 두 가지 모두 사용하자 )(dual 활용방법도 생각할 수 있다)

2, '유관순', '010-9999-0231', '빅데이터반', 402, SYSDATE, 여

 

INSERT INTO TRAINEES(IDX, NAME, TEL, CLASS, ROOM, INDATE)

VALUES( (SELECT NVL(MAX(REGION_ID)+1,0)

              FROM TRAINEES), 

              '유관순', '010-9999-0231', '빅데이터반', 402, SYSDATE, 여);

 

CREATE SEQUENCE seq_trainees_idx

START WITH 2

INCREMENT BY 1

 

INSERT INTO TRAINEES VALUES(SEQ_TRAINEES_IDX.NEXTVAL,

                                                                    '유관순', '010-9999-0231', '빅데이터반', 402, SYSDATE, '여자')

 

 

6. 사내 확장으로 부서가 증가했다. ORACL, SQL, JAVA, JSP 각 순서대로 부서번호를 50, 60, 70, 80번을 부여하고 지역 또 한, BUSAN, ILSAN, INCHON, BUNDANG 추가하시오

 

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');

 

 

테이블 구조만 복사하는 방법

7. EMP 테이블의 구조만 같은 RETIRE 테이블을 새로 생성하시오

CREATE TABLE RETIRE AS SELECT * FROM EMP WHERE 1 <> 1;

 

 

8. EMP_TEMP 부서별 최대 급여를 받는 사원의 모든 정보를 RETIRE 테이블로 이동하시오

(EMP_TEMP 테이블을 호출하여 칼럼을 클릭하면 해당 칼럼 이름을 출력할 수 있다 활용할 것)

 

INSERT INTO RETIRE (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

SELECT * FROM EMP_TEMP WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL)

                                                                        FROM EMP_TEMP

                                                                        GROUP BY DEPTNO)

 

 

9. 30번 부서가 매출 부진으로 인해 부서이동을 실시한다 30번 내에서 급여등급 2인 사원만 20번 부서로 이동하라

UPDATE EMP_TEMP SET DEPTNO = 20

WHERE ENAME IN ( SELECT ENAME

                           FROM EMP_TEMP E, SALGRADE S

                           WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL

                           AND DEPTNO = 30

                           AND S.GRADE = 2)

 

 

10. EMP_TEMP테이블에 8명의 신입사원을 추가하여라 (아래는 신입사원의 정보이다)

7201, 'TEST_USER1', 'MANAGER', 7788, 2016-01-02, 4500, NULL, 50

7202, 'TEST_USER2', 'CLERK', 7201, 2016-02-21 1800, NULL, 50

7203, 'TEST_USER3', 'ANALYST', 7201, 2016-04-11, 3400, NULL, 60

7204, 'TEST_USER4', 'SALESMAN', 7201, 2016-05-31, 2700, 300, 60

7205, 'TEST_USER5', 'CLERK', 7201, 2016-07-20, 2600, NULL, 70

7206, 'TEST_USER6', 'CLERK', 7201, 2016-09-08, 2600, NULL, 70

7207, 'TEST_USER7', 'LECTURER', 7201, 2016-10-28, 2300, NULL, 80

7208, 'TEST_USER8', 'STUDENT', 7201, 2018-03-09, 1200, NULL, 80

 

 

 

11. DEPT_TEMP 40번 부서기준으로 30번 부서와 통합하려한다 ( 이름과 지역을 동일시하자 )

UPDATE DEPT_TEMP SET (DNAME, LOC, deptno) = (SELECT DNAME, LOC, deptno

                                                                     FROM DEPT_TEMP

                                                                     WHERE DEPTNO = 30)

WHERE DEPTNO = 40;

 

그리고 EMP_TEMP 테이블과 DEPT_TEMP테이블의 변경된 정보를 확인하여라

 

 

12. EMP_TEMP에 속한 사원 중 50번 부서에서 근무하는 사원들의 평균 급여보다 많은 급여를 받고 있는 사원들을70번 부서로 옮기는 SQL문을 작성하세요

UPDATE EMP_TEMP SET DEPTNO = 70

WHERE SAL > (SELECT AVG(SAL)

                     FROM EMP

                     WHERE DEPTNO = 50);

 

 

 

13. EMP_TEMP에 속한 사원중, 60번 부서의 사원중에 입사일이 가장 빠른 사원보다

늦게 입사한 사원의 급여를 10% 인상하고 80번 부서로 옮기는 SQL문을 작성하시오

 

UPDATE EMP_TEMP SET SAL = SAL*1.1, DEPTNO = 80

WHERE HIREDATE > (SELECT MIN(HIREDATE)

                             FROM EMP_TEMP

                             WHERE DEPTNO = 60);

 

 

 

 

14. EMP_TEMP에 속한 사원 중, 급여 등급이 5인 사원을 삭제하는 SQL문을 작성하세요

DELETE FROM EMP_TEMP

WHERE EMPNO IN (SELECT E.EMPNO

                           FROM EMP_TEMP E, SALGRADE_TEMP S

                           WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL

                           AND S.GRADE = 5);

 

 

 

15. EMP_TEMP에서 부서별 제일 오래 근무한 사원을 찾으세요.

SELECT ename FROM EMP_TEMP

WHERE (hiredate, deptno) IN (SELECT min(hiredate), deptno

                                        FROM EMP_TEMP

                                         GROUP BY deptno)

 

 

 

16. 각 부서의 급여 최소가 900이상 최대가 10000이하인 부서의 사원들 중 1500이상의 급여를 받는

사원들의 평균 급여액을 출력하라

SELECT avg(sal) FROM emp

WHERE sal >= 1500

GROUP BY deptno

HAVING min(sal) >= 900 AND max(sal) <= 10000;

 

 

17. 급여 등급이 4등급인 사원들의 사원번호, 이름, 급여, 근무 부서이름, 근무지역을 가져온다.

SELECT e.empno

FROM emp e, detp d, salgrade s

WHERE e.deptno = d.deptno AND E.SAL BETWEEN S.LOSAL AND S.HISAL

AND S.GRADE = 4

 

 

 

18. SMITH 사원의 직속상관과 동일한 직무를 가지고 있는 사원들의 사원번호, 이름, 직무를 가져온다.

e1 : SMITH의 정보

e2 : SMITH의 직속상관 정보

e3 : 직속상관과 동일한 직무를 가지고 있는 사원들의 정보

 

SELECT empno, ename, job FROM EMP

WHERE job = (SELECT job

                    FROM EMP

                     WHERE empno = (SELECT mgr

                                               FROM EMP

                                               WHERE ename='SMITH')

                     )

 

SELECT e3.empno, e3.ename, e3.job FROM EMP e1, EMP e2, EMP e3

WHERE e1.mgr = e2.empno AND e2.job = e3.job

AND e1.ename = 'SMITH';