학습 목표
– 시퀀스 활용과 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';
