Quiet Time

6장. 서브쿼리에서의 데이터 검색

SQL 심화2017. 9. 7. 03:18
■ 6장. 서브쿼리에서의 데이터 검색
  • 6장 목차
  1. MULTIPLE COLUMN SUBQUERY
      • SUBQUERY 의 종류 3가지
      1. SINGLE ROW SUBQUERY
      2. MULTIPLE ROW SUBQUERY
      3. MUTIPEL COLUMN SUBQUERY
        1. - PAIR WISE 방식     ===> 오라클에 지원하는 방식
        2. - NON PAIR WISE 방식 ===> 오라클 , non 오라클 db지원
  3. SCALAR SUBQUERY
  4. 상호관련 서브쿼리 - SELECT 문
  5. 상호관련 서브쿼리 DML 문
  6. EXISTS 문 NOT EXISTS 문
  7. WITH 절

6.3 상호관련 서브쿼리 - select 문

문제507. 직업, 직업별 인원수를 출력하시오 !

select job , count(*)
from emp
group by job;

문제508. 이름, 직업, 월급을 출력하는데 자기가 속한 직업의 인원수가 3명 이상인것만 출력하시오


select ename, job, sal
from emp,(
select job , count(*) c
from emp
group by job)
where c > 3; --- 잘못짠 sql



 select ename, job, sal
from emp m
 where 3 <= (select count(*)
          from emp where m.job = job) ;


설명: 메인쿼리의 컬럼이 서브쿼리 안으로 들어가면 상호관련 서브쿼리문이 되고 실행은 메인쿼리부터 된다.

문제509. 지각을 3번이상 한 학생의 이름과 주소를 출럭하시오 


 select ename, address
from emp2 e
 where 3 <= (select count(late_date)
          from chulsuk_date
          where e.ename = ename) ;

6.3 상호관련 서브쿼리 - DML 문



  • DEPT 테이블의 DEPTNO_CNT 를 상호관련 서브쿼리 쿼리DML 문으로 수행

UPDATE DEPT D
SET DEPTNO_CNT = (SELECT COUNT(*)
                              FROM EMP
                              WHERE DEPTNO = D.DEPTNO);

문제510. 위의 DML문을 이용해서 DEPT 테이블의 SUMSAL과 AVGSAL을 각각 갱신하시오
UPDATE DEPT D
SET SUMSAL = (SELECT SUM(SAL)
                              FROM EMP
                              WHERE DEPTNO = D.DEPTNO
                              GROUP BY DEPTNO);

UPDATE DEPT D
SET AVGSAL = (SELECT AVG(SAL)
                              FROM EMP
                              WHERE DEPTNO = D.DEPTNO
                              GROUP BY DEPTNO);


 MERGE INTO DEPT D
     USING (SELECT DEPTNO, COUNT(*) DEPTNO_CNT
               FROM EMP
               GROUP BY DEPTNO) E
      ON(D.DEPTNO = E.DEPTNO)
      WHEN MATCHED THEN
      UPDATE SET D.DEPTNO_CNT = E.DEPTNO_CNT
      WHEN NOT MATCHED  THEN
     INSERT VALUES (E.DEPTNO,NULL,NULL,E.DEPTNO_CNT);


문제511. CHULSUK_CNT 테이블의 컬럼 late_cnt 와 absent_cnt 를 
위의 update문을 이용해서 갱신하시오!
chulsuk_date 테이블의 date를 이용해서 

 MERGE INTO chulsuk_cnt c
     USING (SELECT empno,ename, COUNT(late_date) late_cnt, count(absent_date) absent_cnt
               FROM chulsuk_date
               GROUP BY ename,empno) E
      ON(c.empno = e.empno)
      WHEN MATCHED THEN
      UPDATE SET c.late_cnt= E.late_cnt, c.absent_cnt = e.absent_cnt
      WHEN NOT MATCHED  THEN
     INSERT VALUES(e.empno,e.ename,e.late_cnt,e.absent_cnt);

문제512. 아래의 3개 테이블을 생성하시오

  1. 겨울왕국 대문 insert를 위한 winter 테이블 생성
  2. 감정분석을 위해 긍정단어들을 입력하기 위한 positive 테이블생성
  3. 감정분석을 위해 부정단어들을 입력하기 위한 negaitive 테이블생성


create table winter (
word varchar(20)
);


create table positive (
pword varchar(20)
);

create table negative (
nword varchar(20)
);
truncate table winter;
truncate table positive;
truncate table negative;

alter table winter modify(word varchar2(2000));
alter table positive modify(pword varchar2(2000));
alter table negative modify(nword varchar2(2000));

데이터 파일 db입력 방법

툴스에 로드툴!

달리기 눌러~



컴플릿!

문제513. 지각과 결석을 한번이라도 했던 학생들의 이름과 주소를 출력하시오 

select ename,
       address
  from emp2 e
 where exists (select 'X'
          from chulsuk_date c
         where ename = e.ename);

문제514. 지각을 한번이라도 했던 학생들의 이름과 주소를 출력하시오 


select ename,
       address
  from emp2 e
 where exists (select 'X'
          from chulsuk_date c
         where late_date is not null
and ename = e.ename);

문제515. 지각을 한번도 하지 않앗떤 학생들의 이름과 전공을 출력하시오

select ename,
       major
  from emp2 e
 where not exists (select 'X'
          from chulsuk_date c
         where late_date is not null
           and ename = e.ename);


감정분석 ---> 엘지 G6 폰에 대한 소비자들의 반응이 긍정적인지 부정적인지를 수치화 시키고 싶을때 사용

문제516. 겨울왕국 대본에는 긍정적인 단어가 몇개나 존재하는지 
               확인하시오 
 SELECT COUNT(*)
 FROM WINTER
 WHERE EXISTS (SELECT 'X'
            FROM POSITIVE
            WHERE WORD = PWORD)

문제516. 겨울왕국 대본에는 부정적인 단어가 몇개나 존재하는지 
               확인하시오 
 
SELECT WORD,
       COUNT(*)
  FROM WINTER
 WHERE EXISTS (SELECT 'X'
          FROM NEGATIVE
         WHERE WORD = NWORD)
 GROUP BY ROLLUP(WORD)
 ORDER BY COUNT(*) DESC;
문제517 . 사원테이블에 존재하지 않는 부서 번호를 부서 테이블에서 삭제하시오

 DELETE FROM DEPT D
WHERE NOT EXISTS (SELECT 'X'
                    FROM EMP
                    WHERE D.DEPTNO = DEPTNO);

■ 6장. 서브쿼리에서의 데이터 검색
  • 6장 목차
  1. MULTIPLE COLUMN SUBQUERY
      • SUBQUERY 의 종류 3가지
      1. SINGLE ROW SUBQUERY
      2. MULTIPEL ROW SUBQUERY
      3. MUTIPEL COLUMN SUBQUERY
        1. - PAIR WISE 방식     ===> 오라클에 지원하는 방식
        2. - NON PAIR WISE 방식 ===> 오라클 , non 오라클 db지원
  3. SCALAR SUBQUERY
  4. 상호관련 서브쿼리 - SELECT 문
  5. 상호관련 서브쿼리 DML 문
  6. EXISTS 문 NOT EXISTS 문
  7. WITH 절

6.6 WITH 절 

" 동일한 SQL이 반복적으로 사용될 때 성능을 높이기 위해 사용하는 SQL"

문제519. 입사한 년도(4자리), 입사한 년도별 평균월급을 출력하시오

SELECT TO_CHAR(HIREDATE,'RRRR') , AVG(SAL)
FROM EMP
GROUP BY TO_CHAR(HIREDATE,'RRRR')



문제520. 위 출력된 평균값들의 평균을 내 보자

SELECT AVG(A)
  FROM (SELECT TO_CHAR(HIREDATE, 'RRRR') ,
               AVG(SAL) A
          FROM EMP
         GROUP BY TO_CHAR(HIREDATE,'RRRR'));
SELECT AVG(AVG(SAL) )
  FROM EMP
 GROUP BY TO_CHAR(HIREDATE,
               'RRRR')

문제521. 위의 2개를 활용해서 입사연도, 입사연도별 평균 월급을 출력하는데 입사 년도별 평균 웕브들의 평균값 보다 더 큰것만 출력하시오


SELECT TO_CHAR(HIREDATE, 'RRRR'),  AVG(SAL)
  FROM EMP
  GROUP BY TO_CHAR(HIREDATE,'RRRR')
 WHERE AVG(SAL) > (SELECT AVG(AVG(SAL) )
                  FROM EMP
                 GROUP BY TO_CHAR(HIREDATE,'RRRR'))


[잘못짠 SQL]
SELECT AVG(AVG(SAL) )
  FROM EMP
 GROUP BY TO_CHAR(HIREDATE,
               'RRRR');
 SELECT TO_CHAR(HIREDATE, 'RRRR'),
       AVG(SAL)
  FROM EMP
 WHERE SAL > (SELECT AVG(AVG(SAL) )
          FROM EMP
         GROUP BY TO_CHAR(HIREDATE,
                       'RRRR'))
 GROUP BY TO_CHAR(HIREDATE,
               'RRRR')

문제522. 위의 SQL을 WITH절로 변경하시오


WITH YEAR_AVG AS ( SELECT TO_CHAR(HIREDATE, 'RRRR') 년도, AVG(SAL) 평균월급
                    FROM EMP
                    GROUP BY TO_CHAR(HIREDATE,'RRRR') )

SELECT 년도, 평균월급
     FROM YEAR_AVG
     WHERE 평균월급 > (SELECT AVG(평균월급)
                        FROM YEAR_AVG) ;

문제523. 1부터 10까지 다 더하면 몇인가?

SELECT 1+2+3+4+5+6+7+8+9+10
FROM DUAL;

SELECT LEVEL AS RNUM
FROM DUAL
CONNECT BY LEVEL <= 10;



문제524. WITH절을 이용해서 위의 SQL을 TEMP에 저장하고 1부터 10까지의 총합을 출력하시오 !

WITH NUM_TABLE AS (SELECT LEVEL AS RNUM
                    FROM DUAL
                    CONNECT BY LEVEL <= 10 )
SELECT SUM(RNUM),AVG(RNUM), MAX(RNUM), MIN(RNUM)
FROM NUM_TABLE;



SELECT SUM(RNUM),AVG(RNUM), MAX(RNUM), MIN(RNUM)
FROM(
SELECT LEVEL AS RNUM
                    FROM DUAL
                    CONNECT BY LEVEL <= 10);



/*+ MATERIALIZE */
WITH NUM_TABLE AS (SELECT  LEVEL AS RNUM
                    FROM DUAL
                    CONNECT BY LEVEL <= 10 )
SELECT SUM(RNUM),AVG(RNUM), MAX(RNUM), MIN(RNUM)
FROM NUM_TABLE;


설명 : 
/*+ MATERIALIZE */ 힌트는 TEMP 테이블을 생성하라
/*+ INLINE */ 힌트는 TEMP 테이블 생성하지 말고 아래와 같이 INLINE 뷰로 수행하라는 힌트

문제525. 아래의 SQL을 WITH절로 변경하시오 

  SELECT JOB , SUM(SAL)
  FROM EMP
 GROUP BY JOB
 HAVING SUM(SAL) > (SELECT AVG( SUM(SAL) ) / 4
                         FROM EMP
                        GROUP BY JOB);


 WITH JOB_SUMSAL AS (SELECT JOB,
               SUM(SAL) 토탈
          FROM EMP
         GROUP BY JOB)
SELECT JOB,
       토탈
  FROM JOB_SUMSAL
 WHERE 토탈 > (SELECT AVG(토탈) /4
          FROM JOB_SUMSAL);

■ 임시 테이블 사용법
" 데이터를 임시로 저장하는 테이블 "
  • 언제까지 데이터를 저장하는가? 
  1.  COMMIT할 때까지               ON COMMIT DELETE ROWS
  2.  세션이 종료 될때까지          ON COMMIT PRESERVE ROWS

 CREATE GLOBAL TEMPORARY TABLE EMP_TEMP2
ON COMMIT DELETE ROWS AS =========> 커밋할때 지워라
SELECT *
  FROM EMP2
 WHERE 1=2;

 
 CREATE GLOBAL TEMPORARY TABLE EMP_TEMP2
ON COMMIT PRESERVE ROWS AS =========> 세션이 종료 될때 지워라
SELECT *
  FROM EMP2
 WHERE 1=2;
INSERT
  INTO EMP_TEMP2
SELECT *
  FROM EMP2;

SELECT *
  FROM EMP_TEMP2;

















'SQL 심화' 카테고리의 다른 글

5장. 다른 시간대에서의 데이터 관리  (0) 2017.09.07
4장 대형 데이터 집합 조작  (0) 2017.09.07
3장 데이터 딕셔너리를 통해서 객체 관리  (0) 2017.09.07
2장 스키마 객체 관리  (0) 2017.09.07
1장 유저 엑세스 제어  (0) 2017.09.07

5장. 다른 시간대에서의 데이터 관리

SQL 심화2017. 9. 7. 03:18


■ 지난 금요일에 배운 내용 정리

  1. merge 문 
          튜닝전 : 상호관련 서브 쿼리를 이용한 update문장

          튜닝후 :1. merge
                       2.updatable join view


  • Query도 많이 하지만
  • 오라클 --------------> 하둡 이관 프로젝트가 많아
                     데이터 이행

예전 버전 오라클 ----------> 새로운 버전 오라클 
                      데이터이행
  1. 상호관련 서브 쿼리 

  • 다른 시간대에서의 데이터 관리
         ---------------------
               ↑
  우리나라와 시간대가 다른 나라에도 DB가 있고 거기에 데이터를 입력하고 불러와서 봐야할 때 알아야하는 기능
  • 5장 목차
  1. 날짜를 확인하는 오라클 키워드
  2. 날짜형 데이터 타입
  3. 날짜형 함수

  1. 날짜를 확인하는 오라클 키워드

우리가 여태 알았던 것은 SLECT SYSDATE FROM DUAL ;

SELECT CURRENT_DATE FROM DUAL;
  • 오늘날짜 + 현재시간 확인하는 방법

SELECT SYSTIMESTAMP FROM DUAL;

     2017/03/20 13:53:12 +09:00

영국의 그리니치 천문대가 0시라고하면 우리나라는 아침 9시 인것이다
  • 우리나라 서울의 시간대를 확인하려면 
select distinct tzame
from v$timezone_names
where lower(tzname) like '%seoul%';

Asia/Seoul

  • 영국 런던의 시간대를 알아내기 위한 나라 도시명 확인
select distinct tzname
  from v$timezone_names
where lower(tzname) like '%london%';

Europe/London
  • 영국이 지금 몇시인지 확인하고 싶다면?
select localtimestamp from dual;


문제489. 현재 세션의 시간대를 다시 영국/런던으로 변경하고 
          아래 두개의 쿼리 결과를 확인하시오

select systimestamp from dual;   -------> 시스템의 시간

select localtimestamp from dual; ---------> 데이터 베이스의 시간


          5.2 날짜형 data type

  1. date
  2. timestamp
  3. timestamp with timezone
  4. timestamp with local time zone


cretae table date_table
(
hiredate1 date,
hiredate2     timestamp,
hiredate3     timestamp with time zone
hiredate4     timestamp with local time zone );

insert into date_table
values (sysdate, systimestamp, localtimestamp, localtimestamp);

select * from date_table;






  1. months_between
  2. add_months
  3. next_day
  4. last_day
  5. extract
  6. to_yminterval
  7. to_dsinterval
  8. to_timestamp

select extract( year from sysdate) from dual;

문제490. 이름, 입사한 년도를 출력하시오 !

select ename, extract( year from hiredate) from emp;

문제491. 입사한 년도, 입사한 년도별 토탈 월급을 출력하시오 !
select extract( year from hiredate), sum(sal)
from EMP
group by extract( year from hiredate);

문제492. ALLEN이 입사한 달은 출력하시오 
select extract( MONTH from hiredate)
FROM EMP
WHERE ENAME = 'ALLEN';

문제493. 11일에 입사한 사원들의 이름과 입사일을 출력하시오 
select ENAME , from hiredate
FROM EMP
WHERE extract( DAY from hiredate) = 11;

문제494. 오늘부터 100달 뒤의 날짜를 출력하시오!

SELECT ADD_MONTHS(SYSDATE,100)
FROM DUAL;

문제495. 오늘부터 1년 2개월 후의 날짜를 출력하시오 !

SELECT ADD_MONTHS(SYSDATE,14)
FROM DUAL;


SELECT SYSDATE + TO_YMINTERVAL('01-02')
FROM DUAL;

문제496. 오늘부터 100일 10시간이 지난후의 날짜를 출력하시오 !

SELECT SYSDATE + TO_DSINTERVAL('100 10:00:00')
FROM DUAL;





          5.3 날짜형 함수 
  1. months_between
  2. add_months
  3. next_day
  4. last_day
  5. extract
  6. to_yminterval
  7. to_dsinterval
  8. to_timestamp

2017-03-20 오후 2:49
문제497. 아까 2시 30분쯩에 emp 테이블의 king의 데이터가 무엇이었는지 확인하시오

select * from emp as of timestamp to_timestamp('2017/03/20 14:30:00', 'RRRR/MM/DD HH24:MI:SS')
WHERE ENAME = 'KING';


■ 6장. 서브쿼리에서의 데이터 검색
  • 6장 목차
  1. MULTIPLE COLUMN SUBQUERY
  2. SCALAR SUBQUERY
  3. 상호관련 서브쿼리 - SELECT 문
  4. 상호관련 서브쿼리 DML 문
  5. EXISTS 문 NOT EXISTS 문
  6. WITH 절

6.1 MULTIPEL COLUMN SUBQUERY 

  • SUBQUERY 의 종류 3가지
  1. SINGLE ROW SUBQUERY
  2. MULTIPEL ROW SUBQUERY
  3. MUTIPEL COLUMN SUBQUERY
                    - PAIR WISE 방식
                    - NON PAIR WISE 방식

문제498 . 직업이 SALESMAN 인사원들과 월급이 같은 사원들의 이름과 월급을 출력하시오 !


SELECT ENAME, SAL 
  FROM EMP 
WHERE SAL IN ( SELECT SAL
                         FROM EMP 
                         WHERE JOB = 'SALESMAN');

문제499. 직업이 SALESMAN 인 사원들과 월급도 같고 그리고 커미션도 같은 사원들의 이름과 월급과 커미션을 출력하시오


UPDATE EMP
SET SAL = 2500,
WHERE ENAME = 'SMITH';

UPDATE EMP
SET COMM = 1400
WHERE ENAME = 'SMITH';


[ NON PAIR WISE]
SELECT ENAME, SAL
  FROM EMP
WHERE SAL IN ( SELECT SAL
                         FROM EMP
                         WHERE JOB = 'SALESMAN')
AND COMM IN ( SELECT COMM
                         FROM EMP
                         WHERE JOB = 'SALESMAN');


[PAIR WISE]

SELECE ENAME, SAL ,COMM
  FROM EMP
WHERE (SAL, COMM) IN (SELECT SAL,COMM
                                        FROM EMP
                                        WHERE JOB = 'SALESMAN');



설명 : PAIRWISE 방식은 그 월급에 그 커미션이어야 데이터가 검색이 되는데 
NON PAIR WISE 방식은 직업이 SALESMAN 인 사원들의 월급중에서 하나만 같기만 함녀 되고 커미션하나만 같아도

출력되는 방식이다.
PAIR WISE
3200    300
2500    500
2500    1400
3000  

PAIR WISE 방식            VS    NON PAIR WISE 방식

(1000, 250)                             (1000, 250)
(2000, 100)                             (1000, 100)
(4000, 300)                             (1000, 300)
(2400, 150)                             (1000, 150)
                                              (1000, 250)
                                              (1000, 100)
                                              (1000, 100)
                                              (1000, 300)
                                                    :
                                                    :

문제500. 나이가 30대인 학생들과 전공이 같고 통신사가 같은 학생들의 이름과 나이와 전공을 출력하시오

 [NON PAIR WISE]
SELECT ENAME,AGE,MAJOR
  FROM EMP2
WHERE TELECOM IN (SELECT TELECOM
                    FROM EMP2
                    WHERE AGE >=30)
AND MAJOR IN ( SELECT MAJOR
                         FROM EMP2
                     WHERE AGE >=30);

[PAIR WISE] (...... , .....) 이로케 세트로 같아야 해영!!!
SELECT ENAME,AGE,MAJOR
  FROM EMP2
WHERE (TELECOM,MAJOR) IN (SELECT TELECOM  ,MAJOR
                    FROM EMP2
                    WHERE AGE >=30);


6.1 SCALAR SUBQUERY
  • SELECT 절에서 SUBQUERY를 쓸 수 있는 절
SELECT           -- SCALAR SUBQUERY
FROM             -- IN LINE VIEW
WHERE           -- SUBQUERY 
GROUP BY       --X
HAVING           ---SUBQUIERY 
ORDER BY       --- SCALAR SUBQUERY

  • SCALAR SUBQUERY의 특징 ? 딱 하나의 값만 리턴한다

문제501. 사원이름, 월급, 사원 테이블의 최대월급,
                                        사원 테이블의 최소월급,
                                        사원 테이블의 토탈 월급을 출력하시오.


  • 분석함수 사용하지 않은 SQL
SELECT ENAME, SAL,    (SELECT MAX(SAL) FROM EMP ) 최대,
                      (SELECT MIN(SAL) FROM EMP ) 최소,
                      (SELECT SUM(SAL) FROM EMP ) 토탈
  FROM EMP;  --29개 블락읽음

  • 분석함수 사용한 SQL

SELECT ENAME, SAL , MAX(SAL) OVER (),
                    MIN(SAL) OVER (),
                    SUM(SAL) OVER()
FROM EMP;  -- 7개 블락 읽음


읽은 블락 확인 TRACE METHOD

PLAN 페이지 열기


OPTION -> TRACE METHOD -> LOCAL FILE (CHECK)



하나의 SQL만 넣고 F9 하하호호


EMP테이블 4번셀렉트

SQL 튜닝

SELECT ENAME, SAL , (SELECT MAX(SAL),MIN(SAL),SUM(SAL) FROM EMP)
FROM EMP;
SELECT ENAME,SAL (SELECT MAX(SAL)||MIN(SAL)||SUM(SAL) FROM EMP)
FROM EMP;


SELECT ENAME, SUBSTR(TOTAL,1,4) AS "MAX(SAL)" , SUBSTR(TOTAL,5,7) AS "MIN(SAL)", SUBSTR(TOTAL,8) AS "SUM(SAL)"
FROM (
SELECT ENAME,SAL, (SELECT MAX(SAL)||MIN(SAL)||SUM(SAL) FROM EMP) AS TOTAL
FROM EMP);

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2    0.000        0.000          0         15          0         14
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.000        0.000          0         15          0         14


문제502. 아래의 결과에서 최대월급, 최소월급 , 토탈월급 만 살라대려면 어떻게 해야하는가?

SELECT ENAME,
       SUBSTR(TOTAL, 1, 4) AS "MAX(SAL)" ,
       SUBSTR(TOTAL, 5, 3) AS "MIN(SAL)",
       SUBSTR(TOTAL, 8) AS "SUM(SAL)"
  FROM (SELECT ENAME,
               SAL,
               (SELECT MAX(SAL)||MIN(SAL)||SUM(SAL)
                  FROM EMP) AS TOTAL
          FROM EMP);  --15개 블락 읽음

LPAD를 이용해서 좀더 쉽게 잘라보자!

select ename,
       substr(total, 1, 10) as 최대,
       substr(total, 11, 10) as 최소,
       substr(total, 21, 10) as 토탈
  from(select ename,
               sal,
               (select LPAD(max(sal), 10) || LPAD(min(sal), 10) || LPAD(sum(sal), 10)
                  from emp) as total
          from emp);  -- 15개 블락 읽음


문제503. 이름,나이, 우리반 최대나이,
                              우리반 최소나이,
                              우리반 평균나이를 출력하시오!

SELECT ENAME, AGE, MAX(AGE) OVER (),
                   MIN(AGE) OVER (),
                   AVG(AGE) OVER ()
FROM EMP2; ---3블락


select ename,
       substr(total, 1, 10) as 최대,
       substr(total, 11, 10) as 최소,
       substr(total, 21, 10) as 토탈
  from(select ename,
               AGE,
               (select LPAD(max(AGE), 10) || LPAD(min(AGE), 10) || LPAD(sum(AGE), 10)
                  from emp2) as total
          from emp2); --7 블락

■ 6장. 서브쿼리에서의 데이터 검색
      • 6장 목차
      1. MULTIPLE COLUMN SUBQUERY
      2. SCALAR SUBQUERY
      3. 상호관련 서브쿼리 - SELECT 문
      4. 상호관련 서브쿼리 DML 문
      5. EXISTS 문 NOT EXISTS 문
      6. WITH 절

6.3 상호관련 서브쿼리 SELECT 문장 

문제504. 이름, 월급, 부서번호, 부서위치를 출력하시오 
               (조인으로 수행)


SELECT E.ENAME, E.SAL, E.DEPTNO, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

문제505. 위의 결과를 SCALAR SUBQUERY 를 써서 수행하시오!

SELECT ENAME, SAL, DEPTNO, (SELECT LOC
        FROM DEPT
        WHERE DEPTNO = E.DEPTNO ) LOC
FROM EMP E;  -- 11블락


-- 스칼라 서브 쿼리
SELECT ENAME, SAL, DEPTNO, (SELECT LOC
                            FROM DEPT
                            WHERE DEPTNO = E.DEPTNO ) LOC
FROM EMP E; --13블락

문제506. 아래의 SQL을 분석함수를 사용하지 않고 SCALAR SUBQUERY 로 튜닝하시오 

SELECT ENAME,SAL,DEPTNO,   (SELECT MAX(SAL) FROM EMP WHERE DEPTNO=E.DEPTNO ) 최대,
                           (SELECT MIN(SAL) FROM EMP WHERE DEPTNO=E.DEPTNO ) 최소,
                           (SELECT SUM(SAL) FROM EMP WHERE DEPTNO=E.DEPTNO ) 토탈,
                     ROUND((SELECT AVG(SAL) FROM EMP WHERE DEPTNO=E.DEPTNO ),2)평균
  FROM EMP E
  ORDER BY DEPTNO;  --91블락
-- 분석함수에서 못하는 where절을 쓸 수있어

SELECT e.ename,
       sal,
       e.deptno,
       substr(ap,1,10) "max(sal)",
       substr(ap,11,10) "min(sal),",
       substr(ap,21,10) "sum(sal)",
       substr(ap,31,10) "avg(sal)"
  FROM emp e,
       (SELECT deptno,
              (SELECT lpad(max(sal), 10)|| lpad(min(sal), 10)|| lpad(sum(sal), 10)|| lpad(avg(sal), 10)
                FROM emp d
               WHERE d.deptno = s.deptno
               GROUP BY deptno
              ) ap
         FROM emp s
        GROUP BY deptno
       ) a
 WHERE e.deptno = a.deptno
ORDER BY deptno ; -- 35블락 -- 35블락


SELECT ENAME,SAL,DEPTNO,    MAX(SAL) over (partition by deptno) 최대,
                           MIN(SAL) over (partition by deptno)  최소,
                            SUM(SAL) over (partition by deptno) 토탈,
                      round(AVG(SAL) over (partition by deptno),2)평균
  FROM EMP E
  ORDER BY DEPTNO; --7블락

 
SQL 튜너 -- 굵직한 사례들을 암기 


■ 어제까지 배운 내용 복습
  1. flashback version Query
"특정 테이블의 data가 어떻게 변경되어왔는 이력 정보 "
  1. merge문으로 새로운 data를 입력하는 문자
  2. 날짜형 데이터 타입
    1. date
    2. timestamp
    3. timestamp with timezone
    4. timestamp with local time zone
          5.3 날짜형 함수 
  1. months_between
  2. add_months
  3. next_day
  4. last_day
  5. extract
  6. to_yminterval
  7. to_dsinterval
  8. to_timestamp



■ 6장. 서브쿼리에서의 데이터 검색
  • 6장 목차
  1. MULTIPLE COLUMN SUBQUERY
      • SUBQUERY 의 종류 3가지
      1. SINGLE ROW SUBQUERY
      2. MULTIPEL ROW SUBQUERY
      3. MUTIPEL COLUMN SUBQUERY
        1. - PAIR WISE 방식     ===> 오라클에 지원하는 방식
        2. - NON PAIR WISE 방식 ===> 오라클 , non 오라클 db지원
  3. SCALAR SUBQUERY
  4. 상호관련 서브쿼리 - SELECT 문
  5. 상호관련 서브쿼리 DML 문
  6. EXISTS 문 NOT EXISTS 문
  7. WITH 절









'SQL 심화' 카테고리의 다른 글

6장. 서브쿼리에서의 데이터 검색  (0) 2017.09.07
4장 대형 데이터 집합 조작  (0) 2017.09.07
3장 데이터 딕셔너리를 통해서 객체 관리  (0) 2017.09.07
2장 스키마 객체 관리  (0) 2017.09.07
1장 유저 엑세스 제어  (0) 2017.09.07

4장 대형 데이터 집합 조작

SQL 심화2017. 9. 7. 03:17
4장 목차
  1. 서브쿼리를 사용한 데이터 조작 
  2. INSERT, UPDATE 문에서 DEFAULT 사용법
  3. 다중 테이블 INSERT
  4. MERGE 문
  5. FLASHBACK VERSION QUERY


■ 4.1. 서브쿼리를 사용한 데이터 조작
  • SELECT 문의6가지 절에서 SUBQUERY 를 쓸 수 있는 절

 SELECT       ---- subquery
FROM           ---- subquery
WHERE         ---- subquery
GORUP BY    
HAVING         ---- subquery
ORDER BY     ---- subquery

  • insert 문에서 서브쿼리를 쓸 수 있는 절
inser     ---- subquery
values   ---- subquery

문제433. 우리반에서 sk 텔레콤을 이용하는 학생중 가장 나이가 어린 학생의 이름과 나이를 출력하시오

  1. 텔레콤, 이름, 나이 , 순위를 출력하느넫 순위가
    텔레콤별로 각 나이가 어린 학생순으로 순위를 부여하시오
    select telecom,ename, age , dense_rank() over (partition by telecom order by age asc) a
    from emp2

  1. 위의 결과를 다시 출력하는데 순위가 1등인 학생들만 출력하시오
       SELECT telecom, ename , age
         FROM
              (SELECT telecom,
                     ename,
                     age ,
                     dense_rank() over (partition BY telecom ORDER BY age ASC) a
                FROM emp2
              )
        WHERE a= 1 ;

 
문제434. 이름, 월급, 사원 테이블 전체의 평균 월급을 출력하시오 
    select ename, sal, round((select avg(sal) from EMP)) 평균월급
        from EMP;


문제435. 이름, 월급 , 사원테이블 전체의 평균월급, 
                              사원테이블 전체의 토탈 월급,
                              사원 테이블 전체의 최대 월급,
                              사원 테이블 전체의, 최소월급을 출력하시오 


--튜닝전 
 select ename, sal , (select avg(sal) from EMP) 평균,
                                         (select sum(sal) from EMP) 토탈,
                                        (select max(sal) from EMP) 최대,
                                        (select min(sal) from EMP) 최소

                                        from EMP;

--튜닝후
  SELECT ename,sal,round(평균월급) 평균월급,
                 토탈월급,
                 최대월급,
                 최소월급
            FROM EMP,
                 (SELECT avg(sal) 평균월급,
                        sum(sal) 토탈월급,
                        max(sal) 최대월급,
                        min(sal) 최소월급
                   FROM EMP
                 ) ;       


select ename, sal , avg(sal) over () 평균,
                              sum(sal) over () 토탈,
                              max(sal) over () 최대,
                              min(sal) over () 최소
from emp;


select ename, sal , ( select avg(sal), sum(sal), max(sal), min(sal) from emp) from emp;
설명 : 슼라라 서브쿼리는 값을 딱 하나만 리턴한다 여러개를 리턴할 수 없다.

SELECT ename, sal , (
                              SELECT round(avg(sal))||' , '|| sum(sal)||' , '|| max(sal)||' , '|| min(sal)  FROM emp
)
            FROM emp;


문제436. 부서번호, 이름, 월급, 자기가 속한 부서번호의 평균월급을 
               출력하시오 !

select deptno, ename, sal , round(avg(sal)) over (partiton by deptno)
from emp;

문제436. 부서번호 , 이름, 월급, 자기가 속한 부서번호의 평균월급이
               출력되는데 자기의 월급이 자기가 속한 부서번호의 평균월급 보다 더 큰 사원들만 출력되게 하시오 

  SELECT *
            FROM
                 (SELECT deptno,
                        ename,
                        sal ,
                        round(avg(sal) over (partition BY deptno)) 부서평균
                   FROM emp
                 )
           WHERE sal > 부서평균;


문제438 아래와 같이 열과를 출력하시오 


select telecom, listagg(ename,',') within group (order by age desc)
from EMP2
where telecom ='sk'
group by telecom;





■ 데이터 분석함수
  1. rank
  2. dense_rank
  3. ntile
  4. listagg
  5. 누적치 sum(sal) over (  ..  )
  6. lag 와 lead

문제439. 
이름, 나이, 그 전행의 이름을 출력하시오 !
(     나이가 높은 순서대로 정렬해서 출력 )
  
 SELECT ename,
                 age,
                 lag(ename,1) over (ORDER BY age desc) 그전행,
                 lead(ename,1) over (ORDER BY age desc) 다음행 from emp2;

문제440. telecom , 텔레콤별로 각각 그 전 행의 이름,
                         텔레콤별로 각각 그 다음 행의
                    이름, 나이, 그 전행의 이름을 출력하시오 !
                              (     나이가 높은 순서대로 정렬해서 출력 )

 
SELECT telecom,
                 ename,
                 age,
                 lag(ename,1) over (partition BY telecom    ORDER BY age desc) 그전행,
                 lead(ename,1) over (partition BY telecom  ORDER BY age desc) 다음행 from emp2;


문제441. 위의 결과를 다시 출력하는데 나이가 같다면 생일이 먼저인 학생이 먼저 출력되게 해보자 


    SELECT telecom,
                 ename,
                 age,
                 lag(ename,1) over (partition BY telecom ORDER BY age desc, birth asc) 그전행,
                 lead(ename,1) over (partition BY telecom ORDER BY age desc,birth asc) 다음행 from emp2;



문제442. (오늘의 마지막 문제)
               아래와 같이 결과를 출력하시오 !
               (보험회사에서 보험금액을 책정할 때 도움이 되도록 데이터를 분석해서
               아래와 같이 결과를 출력하시오 ) 

SELECT 성별,
       listagg (암종||'('||rnk||'위)',', ') within
GROUP (
ORDER BY 환자수 desc ) 암종순위
  FROM
       (SELECT 성별,
              암종,
              환자수,
              rank() over (partition BY 성별 ORDER BY 환자수 desc) rnk
         FROM C2012_A
        WHERE 환자수 IS NOT NULL
              AND 암종 <>'모든암'
       )
 GROUP BY 성별 ;



■ 어제 배운 내용을 복습

  1. external table 생성하는 실습
          
          왜 external  table을 사용해야 하는가?
          오라클 DB insert 작업하는데 시간이 많이 걸린다(인건비, 시간, 오류, 디스크용량)
                -> 링크만 걸어서 select한다 insert하려면 시간과 비용이 든다.
 
  1. 데이터 딕셔너리를 활용하는 방법

          왜 data dictionary를 알아야 하는가? 
               1  내가 분석할 데이터가 들어있는 테이블을 알기위해
               ( 주석 처리가 다 되어있다.)
               2 테이블 건수를 미리 확인해볼 수 있기 때문에 SQL을 어떻게 작성해야 할지 계획을 세울 수 있음
  1. 서브쿼리 사용법
          - select 절의 서브 쿼리  --> 스칼라 서브 쿼리 
          - from 절의 서브 쿼리    --> 인라인 뷰 



4장 목차
  1. 서브쿼리를 사용한 데이터 조작 
  2. INSERT, UPDATE 문에서 DEFAULT 사용법
  3. 다중 테이블 INSERT
  4. MERGE 문
  5. FLASHBACK VERSION QUERY


문제443. 30번 부서번호에서 월급을 제일 많이 받는 사원의 
          이름과 월급과 순위를 출력하시오


select *
from (select ename, sal, rank() over (order by sal desc ) rnk
          from emp
          where deptno =30)
where rnk = 1;


  •  select 문에서 서브쿼리를 쓸 수 있는 절
  1. select 
  2. from  ------  인 라인 뷰
  3. where
  4. having
  5. order by

문제444.직업, 직업별 토탈월급을 출력하시오

SELECT job ,
       sum(sal)
  FROM emp
 GROUP BY job;

문제445. 위에서 출력된 직업별 토탈월급들의 평균값을 구하시오 !



SELECT avg(a)
  FROM
       (SELECT job ,
              sum(sal) a
         FROM emp
        GROUP BY job
       );   

SELECT sum(a)/5
  FROM
       (SELECT job ,
              sum(sal) a
         FROM emp
        GROUP BY job
       ); 

SELECT avg(sum(sal))
  FROM EMP
 GROUP BY job;
 

문제446. 직업, 직업별 토탈월급을 출력하는데 직업별 토탈월급들의 평균값(문제445) 보다 더 큰 값만 출력되게 하시오 

SELECT job,
       sum(sal)
  FROM emp
 GROUP BY job
HAVING sum(sal) >
       (SELECT avg(sum(sal))
         FROM emp
        GROUP BY job) ;
 
문제447. 텔레콤 , 텔레콤별 토탈나이를 출력하는 텔레콤별 토탈나이의 평균값보다 더 큰것만 출력하시오 !


select telecom , sum(age)
from emp2
group by telecom
having sum(age) > (select avg(sum(age))
                                   from emp2
                                   group by telecom);



4.2 insert 문에 서브쿼리를 쓸 수 있는 절 (p 4-7)

insert into (서브쿼리)
(서브쿼리)

insert into ( select empno, ename , sal , deptno
                         from emp
                         where deptno = 20 )
values ( 1234, 'JACK', 3500, 20);

select * from emp;
설명: 아래와 같이 with  check option 을 써서 data 입력하면 where절에서 기술한 조건에 위배되게 data를 입력할 수 없다.
insert into ( select empno, ename , sal , deptno
                         from emp
                         where deptno = 20
                         with check option )
values ( 1234, 'JACK', 3500, 20);
select * from emp;

문제448. 사원번호, 이름, 월급, 부서번호를 물어보게해서 data가 입력될 수 있도록 SQL 을 작성하는데 월급 9000이상으로
               입력하려 하면 data가 입력안되게 막으시오 

insert into (select empno, ename, sal, deptno
                from EMP
                where sal < 9000
                with check option)

values(&empno,'&ename',&sal,&deptno);


4.2 insert, update 문에서 default  사용법


create table emp448
( empno number(10),
     ename varchar2(20),
sal number(10) default 3000,
job varchar2(20) default 'salesman');

insert into emp448
values(2912,'jone',default,default);



문제448. 아래의 DATA를 입력하고 UPDATE를 수행하는데
              이름이 JANE 인 사원의 월급을 DEFAULT로 UPDATE하시오 

INSERT INTO EMP448
          VALUES(2013,'JANE',4500,'ANALYST);

 UPDATE EMP448
          SET SAL = DEFAULT ,JOB= DEFAULT
          WHERE ENAME = 'JANE';


4장 목차
  1. 서브쿼리를 사용한 데이터 조작 
  2. INSERT, UPDATE 문에서 DEFAULT 사용법
  3. 다중 테이블 INSERT
  4. MERGE 문
  5. FLASHBACK VERSION QUERY



                    4.3 다중 INSERT 문

 원래는 우리가 DATA를 INSERT 할 때 한번에 한나의 테이블에만 INSERT 할 수 있는데

다중 INSERT 문은 한번에 여러개의 테이블에 동신에 INSERT 할 수 있다.

예:
CREATE TABEL EMP2_SK
     AS
          SELECT * FROM EMP WHERE 1=2;



문제450. 우리반 테이블에 텔레콤이 SK 인 학생들의 DATA를 EMP2_SK 테이블에 입력하셍 
INSERT INTO EMP2_SK
SELECT * FROM EMP2 WHERE TELECOM='sk';

문제451. 다중insert 문을 이용해서 동시에 3개의 테이블에 data가 입력되게 하시오 

           sk    ------------------> emp2_sk
emp     lg    ------------------> emp2_lg
           kt    ------------------> emp2_kt


insert all 
          when telecom='sk' then into emp2_sk
          when telecom-='lg' then into emp2_lg
          when telecom-='kt' then into emp2_kt
select * from emp2;


**다중 insert 문의 종류 4가지

1. 무조건 all insert 문



insert all
    into emp2_sk
    into emp2_sk
    into emp2_sk
    select * from EMP2;

truncate table emp2_sk;
truncate table emp2_kt;
truncate table emp2_lg;

2. 조건부 all insert 문

insert all 
          when telecom='sk' then into emp2_sk
          when telecom-='lg' then into emp2_lg
          when telecom-='kt' then into emp2_kt
select * from emp2;

3. 조건부 first insert 문

4. pivoting insert 문

문제452. 아래의 3개의 테이블을 생성하고 데이터를 한번에 입력하세오!
     emp2_30     (30대 이상 다)
     emp2_27     (27살 ~ 29살)
     emp2_24     (24살 ~ 26살)

create table emp2_20
as select * from emp2;




  • 조건부 first insert 문
insert first

when age > 30 then into emp2_sk
when telecom='lg' then into emp2_lg
when telecom='kt' tehn into emp2_kt

selet * from emp2;



4.4 pivoting insert 문

"컬럼 ----> 데이터가 되어서 insert가 되는 구문" 
문제453. 부서번호, 부서번호별 토탈월급을 출력하시오 !

- 가로

select
sum(decode(deptno,10,sal,0)) 10,
sum(decode(deptno,20,sal,0)) 20,
sum(decode(deptno,30,sal,0)) 30
from emp;

 ---------------> unpivot 

문제454. ctas를 이용해서 테이블을 생성하시오 emp454


create table emp454
as (
select *
from (select deptno,sal
     from emp)
pivot (sum(sal) for deptno in (10,20,30)));
문제455.  pivot 문으로 아래의 결과를 출력하시오 
select *
from (select deptno,sal
     from emp)
pivot (sum(sal) for deptno in (10,20,30));

문제456. unpivot 문을 이용해서 emp454 테이블의 data를 세로로 출력하시오

select *
  from emp454
  unpivot ( sumsal for deptno in ("10","20","30")); ---> 숫자여서 더블 쿼테이션 마크를 사용한 것이고
                                                  문자는 그냥 쓰면 된다. ( 예: in (SMITH, ALLEN, CLARK))

문제457. 아래와 같이 결과를 출력하시오 


  SELECT *
  FROM CRIME_AGE
  UNPIVOT (CNT FOR AGE IN (UNDER_6,UNDER_12,UNDER_15,UNDER_20,UNDER_30,UNDER_40,UNDER_50,UNDER_60,OVER_60));
 문제458. 서울시에서 2014 년에 살인 사건이 가장 많이 일어난 지역과 그 순위를 아래와 같이 출력하시오 !

select local, sum(sum_tot), 
              dense_rank()over(order by sum(sum_tot) desc)
from (
select local, sum_tot
from crime_age
where type = '살인' and year=2014 )
group by local;
문제459. unpivot 문을 이용해서 crime_age 를 아래와 같이 출력하시오 !

select *
from crime_age 
unpivot( cnt for age in (under_6, UNDER_12, UNDER_15, UNDER_20,
UNDER_30, UNDER_40, UNDER_50, UNDER_60, OVER_60));


문제460. 2014년도에 살인을 일으킨 나이대, 나이대별 살인건수를 출력하시오 !

SELECT age, SUM(cnt)
FROM (SELECT *FROM crime_age
unpivot (cnt FOR age IN (UNDER_6,
UNDER_12,UNDER_15,UNDER_20,UNDER_30,UNDER_40,UNDER_50,UNDER_60,OVER_60) ) )
WHERE TYPE = '살인' AND year = 2014
GROUP BY age
ORDER BY SUM(cnt) DESC;



4.4 MERGE 문 
     "INSERT , UPDATE, DELETE 를 한번에 수행하는 명령어"


문제461. 사원테이블에 loc컬럼을 추가하시오 
alter table emp 
add loc varchar2(10);

문제462. 사원이름, 사원 테이블의 부서위치, 부서 테이블의 부서위치를 출력하시오



select e.name, e.loc, d.loc
from emp e, dept d
where e.deptno = d.deptno;

문제463. 위의 결과를 view로 생성하시오 ! (view 이름 : view463)


create view emp463
as
     select ename, e.loc as emp_loc, d.loc as dept_loc
        from emp e, dept d
where e.deptno = d.deptno;

문제464. 위의 view결과를 이용해서 update하는데emp_loc를 dept_loc컬럼의 값으로 갱신하시오 


update emp463
set emp_loc = dept_loc;



 update
(select ename, e.loc as emp_loc, d.loc as dept_loc
        from emp e, dept d
where e.deptno = d.deptno)
set emp_loc = dept_loc;


- dept 테이블에 primary key 생성

alter tabele dept
add constraint dept_deptno_pk primary key(deptno);

- emp 테이블에 foreign key 생성하면서 dept 테이블에 deptno 참조

alter table emp
add constraint emp_deptno_fk foreign key(deptno)
references dept(deptno);


emp 테이블 loc 컬럼을 아래와 같이 update 하는 구문을 "updatable join view" 라고 한다.

문제465. 카페에서 데이터를 내려받아서 cafe 테이블을 생성하고 아래와 같이 전국에서
               카페가 가장 많이 생긴 지역을 구 단위까지 출력하시오! 

SELECT SUBSTR(OLD_ADD,1,INSTR(OLD_ADD,' ',2,1)) FROM CAFE;

**다중 insert 문의 종류 4가지

1. 무조건 all insert 문
2. 조건부 all insert 문
3. 조건부 first insert 문
4. pivoting insert 문





■ 어제 배웠던 내용 복습

     1.다중 insert 문

          -무조건 all insert문
          -조건부 all insert문
          -조건부 first insert문
          -pivoting insert

     2.pivot과 unpivot

          -pivot :data를 column으로

          -unpivot: column을 data로

     3.updatable join view ---->merge 문
 updatable join view? 수정가능한 조인뷰 /복합뷰
create view emp_dept
as
     select ename, e.loc emp_loc,
                         d.loc dept_loc
     from emp e, dept d
     where e.deptno = d.deptno;


update emp_dept
set emp_loc = dept_loc;

dept ------------------------------emp
1쪽 집합                               m(다) 쪽 집합
 ↑
primary key 

위와 같이 성능이 좋은 같은 SQL? merge문

MERGE 문?

     - INSERT , UPDATE, DELETE를 한번에 수행하는 명령어.


CREATE TABLE EMP90
AS
     SELECT *
     FROM EMP
     WHERE JOB='SALESMAN';

UPDATE EMP
SET SAL = SAL *2;


문제 466. EMP90 테이블을 MERGE하는데
      EMP 테이블에는 존재하고 EMP90테이블에는
      없는 데이터는 새로 INSERT 하시오!


SELECT EMPNO FROM EMP
MINUS
SELECT EMPNO FROM EMP90;

SELECT *
FROM EMP
WHERE EMPNO IN (

SELECT EMPNO FROM EMP
MINUS
SELECT EMPNO FROM EMP90);

SELECT * FROM EMP
MINUS
SELECT * FROM EMP90;

SELECT *
FROM EMP
WHERE EMPNO NOT IN (
SELECT EMPNO FROM EMP90);

문제467. EMP90 테이블의 SAL을EMP 테이블의 SAL 로 값을 갱신하시오

UPDATE EMP90
SET SAL = (SELECT SAL
                              FROM EMP);

설명 : 위의 UPDATE 문이 가능하게 되려면 상호관련 서브쿼리(6장)를 써야한다


UPDATE EMP90 E9
     SET SAL = (SELECT SAL
                         FROM EMP E
                         WHERE E.EMPNO = E9.EMPNO);
메인쿼리의 테이블을 먼저 읽고 

문제468. emp 테이블에 loc 컬럼을 추가하시오

alter table emp 
     add loc varchar2(20);

문제.469. 상호관련 서브쿼리를 이용해서 emp 테이블의 loc컬럼의 data를 dept 테이블의 해당 부서번호의 loc 데이터로
값을 갱신하시오 


update emp
set loc = (select loc
            from DEPT
            where emp.deptno = dept.deptno);

            select * from EMP;

but 상호관련 서브쿼리는 악성 SQL이다

이것을 튜닝하는 것이 merge문이다.



문제470. 위의 작업을 merge문으로 수행하시오 
MERGE INTO EMP E
USING DEPT D
ON (D.DEPTNO = e.DEPTNO)
WHEN MATCHED THEN
UPDATE SET E.LOC = D.LOC;


문제471. merge문을 위해서 아래의 환경을 구성하시오 !!

create table sales100
  as
     select * from sh.sales;

create table sales200
as
     select rownum rn, s.*
          from sh.sales s;

create table sales300
as
     select *
          from sales200;

          select * from seles200;
          select * from seles300;
          drop table sales100;
          drop table sales200;
          drop table sales300;

select 'ALTER TABLE SALES300 '||
    'DROP CONSTRAINT '||constraint_name||';'
  from user_constraints
  where table_name='SALES300';

ALTER TABLE SALES300 DROP CONSTRAINT SYS_C0011137;
ALTER TABLE SALES300 DROP CONSTRAINT SYS_C0011138;
ALTER TABLE SALES300 DROP CONSTRAINT SYS_C0011139;
ALTER TABLE SALES300 DROP CONSTRAINT SYS_C0011140;
ALTER TABLE SALES300 DROP CONSTRAINT SYS_C0011141;
ALTER TABLE SALES300 DROP CONSTRAINT SYS_C0011142;
ALTER TABLE SALES300 DROP CONSTRAINT SYS_C0011143;

--제약조건 삭제 업데이트 가능


UPDATE SALES300
SET PROD_ID  = NULL;
SELECT * FROM SALES100;
SELECT * FROM SALES200;
SELECT * FROM SALES300;


문제472. (점심시간 문제)

SALES200테이블을 이용해서 SALES300 테이블의 PROD_ID
의 값을 갱신하시오 (연결고리는 RN컬럼)

<튜닝후>
MERGE INTO SALES300 S3
USING SALES200 S2
ON(S3.RN = S2.RN)
WHEN MATCHED THEN
UPDATE SET S3.PROD_ID = S2.PROD_ID;






SQLPLUS > SET TIMING ON

문제473. SALES200과 SALES300을 조인해서
              rn, sales200 의 prod_id, sales300의 prod_id를 출력하시오!

      
select s2.rn, s2.prod_id s2prod, s3.prod_id s3prod
                from sales200 s2, sales300 s3
               where s2.rn = s3.rn;

문제474. updatable join view 를 이용해서 
              sales300의 prod_id를 sales200의 prod_id의 값으로 갱신하시오 
              
create view views 
as 
s2.rn, s2.prod_id s2prod, s3.prod_id s3prod
                from sales200 s2, sales300 s3
               where s2.rn = s3.rn;
               
문제475. 아래의 2개의 테이블을 생성하고 값을 입력하시오 !

create  table  chulsuk_date
(  empno    number(10),
   ename    varchar2(20),
  late_date   date,
  absent_date   date );


create  table  chulsuk_cnt
 (  empno   number(10),
    ename   varchar2(20),
    late_cnt   number(10),
   absent_cnt   number(10) );


문제476. 이름, 지각건수, 결석건수를 출력하시오 !


select ename, count(late_date), count(absent_date)
from chulsuk_date
group by ename;

문제477. 위의 결과를 다시 출력하는데 emp2 와 조인을 해서 지각과 결석을 한번도 하지 않은 학생들은 
0으로 출력되게 하시오 

SELECT e.ename,
       sum(decode(late_date,null,0,emp1)) as 지각,
       sum(decode(absent_date,null,0,1)) as 결석
 FROM emp2 e,
       chulsuk_date c
       WHERE e.empno = c.empno(+)
 GROUP BY e.ename;


-----------------------------------
--SQLGATE에서 안됨
 select ename,count(l), count(c)
 from (SELECT e.ename,
       late_date l,
       absent_date c
 FROM emp2 e,
       chulsuk_date c
       WHERE e.empno = c.empno(+)
       )
 GROUP BY ename;
-------------------------------------
--SQLGATE에서 안됨
 SELECT e.ename,
       count(late_date),
       count(absent_date)
 FROM emp2 e,
       chulsuk_date c
       WHERE e.empno = c.empno(+)
        GROUP BY e.ename;

문제468. 아래 쿼리의 결과를 chulsuk_cnt 테이블에 입력하시오 

insert into chulsuk_cnt
select empno,ename, count(late_date), count(absent_date)
from chulsuk_date
group by ename,empno;

문제479. chulsuk_cnt 테이블의 컬럼 2개 late_cnt와 absent_cnt  컬럼을 0으로 만드세요

update chulsuk_cnt
set late_cnt = null , absent_cnt = null;

문제480. 아래의 쿼리의 결과를 이용해서 chulsuk_cnt 테이블의 late_cnt와 absent_cnt 컬럼을 갱신하는 merge문을 작성하시오


merge into chulsuk_cnt c using

    (SELECT empno d_empno ,
           ename d_name,
           count(late_date) lc,
           count(absent_date) ac
      FROM chulsuk_date
     GROUP BY ename,
           empno
    ) on (d_empno=c.empno )
WHEN MATCHED THEN
       UPDATE
              SET c.late_cnt= lc,
              c.absent_cnt =ac ;
       commit;


문제481. 이름, 지각건수, 결석건수를 출력하시오 

select empno,ename, count(late_date), count(absent_date)
from chulsuk_date
group by ename,empno;



문제483. 업데이트와 오늘 결석을 해서 새롭게 데이터가 생긴 혜승양의 DATA를 
위의 MERGE 문을 이용해서 INSERT하시오 

MERGE
  INTO CHULSUK_CNT C USING (select empno,
               ename,
               count(late_date) LATE_CNT,
               count(absent_date) ABSENT_CNT
          from chulsuk_date
         group by ename,
               empno) V ON (C.EMPNO = V.EMPNO)
       WHEN MATCHED THEN
UPDATE
   SET C.LATE_CNT = V.LATE_CNT ,
       C.ABSENT_CNT = V.ABSENT_CNT
       WHEN NOT MATCHED THEN
INSERT VALUES(V.empno,V.ename,V.LATE_CNT,V.ABSENT_CNT);

설명: MERGE문으로 INSERT, UPDATE, DELETE 를 한번에 수행할 수 잇어서 다음과 같은 장점있다.
  1. 코드가 심플해진다.
  2. 성능이 좋아진다.

문제484. DEPT 테이블에 DEPTNO_CNT 라는 컬럼을 추가하고 
              해당 부서번호의 인원수로 값을 갱신하시오


ALTER TABLE DEPT
     ADD DPETNO_CNT NUMBER(10);


     MERGE INTO DEPT D
     USING (SELECT DEPTNO, COUNT(*) DEPTNO_CNT
               FROM EMP
               GROUP BY DEPTNO) E
      ON(D.DEPTNO = E.DEPTNO)
      WHEN MATCHED THEN
      UPDATE SET D.DEPTNO_CNT = E.DEPTNO_CNT;

문제485. 사원테이블에 아래의 데이터를 입력하고 다시 DEPT TABLE에 merge 하시오 !

INSERT INTO EMP ( EMPNO , ENAME, SAL , DEPTNO)
VALUES ( 1256, 'jack', 3500, 60);

INSERT INTO EMP ( EMPNO , ENAME, SAL , DEPTNO)
VALUES ( 1257, 'jane', 4900, 60);



alter table emp
drop constraint emp_deptno_fk cascade;

 MERGE INTO DEPT D
     USING (SELECT DEPTNO, COUNT(*) DEPTNO_CNT
               FROM EMP
               GROUP BY DEPTNO) E
      ON(D.DEPTNO = E.DEPTNO)
      WHEN MATCHED THEN
      UPDATE SET D.DEPTNO_CNT = E.DEPTNO_CNT
      WHEN NOT MATCHED  THEN
     INSERT VALUES (E.DEPTNO,NULL,NULL,E.DEPTNO_CNT);


4.5 FLASHBACK VERSION QUERY

"테이블이 그 동안 어떻게 변경해 왔는지 그 이력을 확인하는 쿼리"
  • FLASHBACK 기술
  1. flashback table                ----> 테이블을 과거로 되돌리는 기능
  2. flashback Query              ----> 과거에 데이터를 확인하는 쿼리
  3. flashback drop                ----> 휴지통 속에서 복원하는 기능
  4. flashback version Query   ----> 그 동안 데이터가 어떻게 변경되어 왔는지 이력정보 확인

문제486. 아래의 작업을 순서대로 진행하시오 

  1. 현재시간과 scn (system change number) 확인
select current_scn from v$database;
select systimestamp from dual;

※ scn ?database에 변경이 일어날때마다 1씩 증가하는 번호  
  1. 아래의 update 문은 순서대로 실행하면서 commit하시오


Update emp
set sal = 9000;
where ename = 'KING';

COMMIT;

UPDATE EMP SET DEPTNO = 30
WHERE ENAME = 'KING';
COMMIT;

DELETE FROM EMP WHERE ENAME ='KING';
COMMIT;



  1. KING의 데이터가 그 동안 어덯게 변경이 되어왔는지 알아내시오

SELECT VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_OPERATION,
ENAME, SAL, DEPTNO
FROM EMP
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE ENAME = 'KING';


  1. 위에서 확인한 SCN 번호를 이용해서 그때 당시 KING 의 DATA가 무엇이었는지 확인한다.
          (FLASH BACK 쿼리)

SELECT *
  FROM EMP AS OF SCN 1481089
WHERE ENAME='KING';

  1. SCN 번호로 시간을 알아내는 함수

SELECT SCN_TO_TIMESTAMP('1481089')
FROM DUAL;


11시 8 분대의 CSN 번호 확인

SELECT * FROM EMP AS OF SCN 1481037
WHERE ENAME = 'KING';

  1. FLASHBACK TABLE 로 EMP 테이블을 SCN 7241771 번호대로 되돌리시오!

SE

ALTER TABLE EMP ENABLE ROW MOVEMENT; -- EMP 테이블을 FLASHBACK 가능한 상태로 설정한다.

FLAShback TABLE EMP TO SCN 1481037;


OR


FLASHBACK TABLE EMP TO TIMESTAMP TO_TIMESTAMP('2017/03/20 11:08:06','RR/MM/DD HH24:MI:SS');


문제487. 짝꿍과 자리를 바꿔서 우리반 테이블을 마음대로 갱신하고 삭제를 일부하고 자리로 돌아온다.


  SELECT VERSIONS_STARTSCN, VERSIONS_1482815ENDSCN, VERSIONS_OPERATION,
ENAME, empno
FROM EMP2
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

ALTER TABLE EMP2 ENABLE ROW MOVEMENT; -- EMP 테이블을 FLASHBACK 가능한 상태로 설정한다..

FLASHback TABLE EMP2 TO SCN 1482815;



문제488. (점심시간문제)
DEPT 테이블에 SUMSAL 과 AVGSAL 컬럼을 추가하고 MERGE문을 이용해서 해당 부서번호의 토탈월급과 평균월급으로 값을 
갱싱하시오 


 ALTER TABLE DEPT ADD SUMSAL NUMBER(10);
 ALTER TABLE DEPT ADD AVGS NUMBER(10);

MERGE
  INTO DEPT D USING (SELECT DEPTNO,
               COUNT(*) DEPTNO_CNT,
               SUM(SAL) SUMSAL,
               ROUND(AVG(SAL)) AVGS
          FROM EMP
         GROUP BY DEPTNO) E ON(D.DEPTNO = E.DEPTNO)
       WHEN MATCHED THEN
UPDATE
   SET D.DEPTNO_CNT = E.DEPTNO_CNT,
       D.SUMSAL = E.SUMSAL,
       D.AVGS = E.AVGS;

















'SQL 심화' 카테고리의 다른 글

6장. 서브쿼리에서의 데이터 검색  (0) 2017.09.07
5장. 다른 시간대에서의 데이터 관리  (0) 2017.09.07
3장 데이터 딕셔너리를 통해서 객체 관리  (0) 2017.09.07
2장 스키마 객체 관리  (0) 2017.09.07
1장 유저 엑세스 제어  (0) 2017.09.07

티스토리툴바