귤귤의 대모험

연습문제

11 오늘이 몇일인지 출력하시오

SELECT TO_CHAR(SYSDATE,‘DD’) FROM DUAL

12 오늘이 무슨요일인지 출력하시오

SELECT TO_CHAR(SYSDATE,‘DAY’) FROM DUAL

13 위의 11번과 12을 합쳐서 출력하시오 결과 예제 -> 03목요일

SELECT TO_CHAR(SYSDATE,‘DD’) || TO_CHAR(SYSDATE,‘DAY’) FROM DUAL

14 요일마다 LOGIN 암호를 자동으로 바꿔주는 프로그램을 만든다고 가정하자 월요일엔 해당일자에 01을 붙여서 4자리를 만들고 화요일엔 11, 수요일엔 21, 목요일엔 31, 금요일엔 41, 토요일엔 51, 일요일엔 61, 붙여서 만들고자한다

SELECT TO_CHAR(SYSDATE,'DD') || DECODE(TO_CHAR(SYSDATE,'DAY'), 'MONDAY', 11, 'TUESDAY', 12,

                              'WEDNESDAY', 13, 'THURSDAY', 14, 'FRIDAY', 15, 'SATURDAY', 16, 'SUNDAY', 17)

FROM DUAL

15 LECTURE 테이블에서 LEC_TIME과 LEC_POINT를 비교해보자 비교한 결과를 이용해 LEC_TIME이 크면 ’실험과목‘ LEC_POINT가 크면 ’기타과목‘, 둘이 같으면 ’일반과목’으로 값을 돌려받고자 한다.

SELECT DECODE(SIGN(LEC_TIME-LEC_POINT), 0, ‘일반과목’, 1, ‘실험과목’, -1, ‘기타과목’) FROM LECTURE

16 강의 시간과 학점이 같거나 강의시간이 학점보다 작으면 ‘일반과목’을 돌려받고 강의시간이 학점보다 큰경우만‘실험과목’을 돌려받고자 한다 두가지 방법 SIGN, LEAST를 사용하자

SELECT LEC_ID, DECODE(LEAST(LEC_TIME,LEC_POINT), LEC_TIME, '일반과목', '실험과목') FROM LECTURE

SELECT LEC_ID, DECODE(SIGN(LEC_TIME-LEC_POINT), 0, '일반과목', -1, '일반과목', 1, '실험과목') FROM LECTURE

17 LECTURE 테이블의 자료중 LEC_TIME과 LEC_POINT가 같다라는 조건을 만족하고 LEC_TIME=3 인 자료는 옆에 ‘중요과목’이라는 비고를 붙이고자한다

SELECT LEC_ID, DECODE(LEC_TIME, LEC_POINT, DECODE(LEC_TIME,3, '중요과목')) FROM LECTURE

18. TEMP의 행을 3개씩 묶어 하나의 번호를 부여하고, 해당 ROWNUM을 3으로 나눈 나머지가 1이면 사번 아니면 NULL, 또 한 번은 1이면 성명 아니면 NULL 이렇게 순차적으로 사번, 성명을 출력하고 이어서 나머지가 2, 0인 경우도 같은 조건으로 출력하여라

SELECT CEIL(ROWNUM/3) C0

,DECODE(MOD(ROWNUM,3), 1, EMP_ID, NULL) C1

,DECODE(MOD(ROWNUM,3), 1, EMP_ID, NULL) C2

,DECODE(MOD(ROWNUM,3), 2, EMP_ID, NULL) C3

,DECODE(MOD(ROWNUM,3), 2, EMP_ID, NULL) C4

,DECODE(MOD(ROWNUM,3), 0, EMP_ID, NULL) C5

,DECODE(MOD(ROWNUM,3), 0, EMP_ID, NULL) C6

FROM TEMP

19 위 예문의 결과를 이용하여 한 행에 사번 성명을 3명씩 보여주는 SQL을 작성하자 (열을 행으로 바꾸는 문제)

SELECT CEIL(ROWNUM/3) C0

, MAX(DECODE(MOD(ROWNUM,3), 1, EMP_ID, NULL)) C1

, MAX(DECODE(MOD(ROWNUM,3), 1, EMP_NAME, NULL)) C2

, MAX(DECODE(MOD(ROWNUM,3), 2, EMP_ID, NULL)) C3

, MAX(DECODE(MOD(ROWNUM,3), 2, EMP_NAME, NULL)) C4

, MAX(DECODE(MOD(ROWNUM,3), 0, EMP_ID, NULL)) C5

, MAX(DECODE(MOD(ROWNUM,3), 0, EMP_NAME, NULL)) C6

FROM TEMP

GROUP BY CEIL(ROWNUM/3)

20 사원 테이블에서 사원을 골라 해당 사원의 연봉이 동일한 직급을 가진 사원의 평균 연봉보다 많은 사원을 출력하라

SELECT A.SALARY, A.EMP_ID, A.EMP_NAME ,B.AVG_SAL

FROM TEMP A ,(SELECT LEV, AVG(SALARY) AVG_SAL

                                  FROM TEMP

                                  GROUP BY LEV ) B

WHERE A.LEV = B.LEV

AND A.SALARY > B.AVG_SAL

학습 목표

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

< 서브쿼리 >

ㅇ 서브쿼리의 개념

ㅇ 서브쿼리의 위치에 따른 명칭

• SELECT문에 있는 서브쿼리 : 스칼라 서브쿼리
• FROM절에 있는 서브쿼리 : 인라인 뷰
• WHERE절에 있는 서브쿼리 : 서브쿼리

@인라인 뷰  임시적으로 생성되는 동적인 뷰이기 때문에 해당 정보가 저장되지않는다

ROWNUM과 INLINE VIEW의 특성을 이용하여 페이징 처리 등의 작업을 수행할 수 있다.

ㅇ 서브쿼리의 반환 값에 따른 서브쿼리 종류

- 단일 행 서브쿼리(Single-Row Subquery) 

서브 쿼리의 실행 결과가 항상 1건 이하인 것을 의미한다

단일 행 비교 연산자에는 =, <, <=, >, >=, <>이 있다

- 다중 행 서브쿼리(Multiple-Row Subquery)

    서브쿼리의 결과가 여러 행일 때를 의미한다

다중 행 비교 연산자에는 IN, ALL, ANY, SOME, EXISTS가 있다

- IN(서브쿼리)

    서브쿼리의 결과에 존재하는 임의의 값과 동일한 조건을 의미

- 비교연산자 ALL(서브쿼리) 

    메인쿼리는 서브쿼리 결과 값의 모든 값을 만족해야하므로 비교연산자 <,>올 때 다른값 반환한다

서브쿼리의 값이 1,2,3 일 때 비교연산자 > 일 때 서브쿼리는 3의 값으로 비교 된다

- 비교연산자 ANY(서브쿼리)

    메인쿼리는 서브쿼리의 결과 값들 중 어떤 값이라도 만족하면 되므로 비교연산자 <,>올 때 다른값 반환한다

서브쿼리의 값이 1,2,3 일 때 비교연산자 > 일 때 서브쿼리는 1의 값으로 비교 된다

- EXISTS (서브쿼리)

    EXISTS는 존재하는지 여부를 확인하여 정보를 조회하는 경우사용된다, 즉 필요한 정보는

메인쿼리에 정보가 존재하지만 필요한 조건에 해당하는 다른 테이블에 있을 때 사용한다

그래서 항상 연관 서브 쿼리로 사용

- 다중 컬럼 서브쿼리(Multi-Column Subquery) :

서브 쿼리의 실행결과로 여러 칼럼을 반환한다

메인 쿼리에서 비교하고자 하는 칼럼 개수와 칼럼의 위치가 동일해야한다

메인 쿼리의 조건절에 여러 칼럼을 동시에 비교할 수 있다

다중 행 비교 연산과 같은 의미를 가지고 있으며 동시 비교가 가능하다

- HAVING 절 서브쿼리

 그룹함수와 함께 사용될 때 그룹핑된 결과에 대해 부가적인 조건을 주기 위해서 사용한다.

ㅇ 스칼라 서브쿼리(Scala Subquery)

- SELECT문에서 사용하는 서브쿼리로 1행만 반환

ㅇ 상호연관 서브쿼리(Correlated Subquery)

- 메인쿼리의 값을 서브쿼리가 사용하고, 서브쿼리의 값을 받아서 메인쿼리가 계산하는 구조의 쿼리