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

3장 데이터 딕셔너리를 통해서 객체 관리

SQL 심화2017. 9. 7. 03:17
데이터 딕셔너리를 통해서 객체 관리

DATA 를 저장하는 테이블 리스트와 그 건수등을 조사할 때 필요하다.

  • 3장 목차

  1. 데이터 딕셔너리 소개
  2. 데이터 딕셔너리의 종류
  3. 데이터 딕셔너리 활용 방법

3.1 데이터 딕셔너리란 ?
     " db 에 생성되어있는 db object에 대한 정보가 들어있는 사전"

문제418. scott이 소유하고 있는 테이블 리스트를 확인하시오

scott> select table_name
          from user_tables;   ----> data ditionary
문제419. scott이 소유하고 있는 테이블 리스트와 그 건수를 조회하시오

exec dbms_stats.gather_schema_stats('SCOTT');

select table_name, num_rows
  from user_tables;


문제420. sh 계정이 가지고 있는 테이블명, 그 로우수를 출력하시오


select table_name, num_rows
  from user_tables
order by num_rows desc nulls last;



SH@orcl> select table_name, num_rows
  2    from user_tables
  3  order by num_rows desc nulls last;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
SALES                              918843
COSTS                               82112
CUSTOMERS                           55500
FWEEK_PSCAT_SALES_MV                11266
SUPPLEMENTARY_DEMOGRAPHICS           4500
TIMES                                1826
PROMOTIONS                            503
PRODUCTS                               72
CAL_MONTH_SALES_MV                     48
COUNTRIES                              23
CHANNELS                                5
SALES_TRANSACTIONS_EXT                  0
DIMENSION_EXCEPTIONS                    0
DR$SUP_TEXT_IDX$K
DR$SUP_TEXT_IDX$N
DR$SUP_TEXT_IDX$R
DR$SUP_TEXT_IDX$I


문제421. 그냥 scott에서 sh 계정이 가지고 있는 테이블명과 그 건수를 보고 싶다면 어떻게 해야하는가



          3.2 데이터 딕셔너리의 종류

  1. user_xxx : 해당 유저가 소유하고 있는 객체들
  2. all_xxx     : 해당 유저가 소유하고 있는 객체들 + 다른 유저들이 권한을 준것
  3. dba_xxx   : DB에서 만들어진 모든 객체들을  볼 수 있다.


     xxx ---> tables, view, indexes, synonyms,.......

SCOTT>  
select table_name, num_rows
               from dba_tables
               where owner = 'SH';



문제422. sh 계정이 가지고 있는 테이블 리스트, 그 건수, 순위를 출력하는데 순위가 건수가 높은것 부터 순위를 부여하시오!

select table_name, num_rows
               from dba_tables
               where owner = 'SH'
;


문제423. 위의 결과에서 순위가 1등, 2등, 3등 만 출력하세요 


SELECT *
  FROM
       (SELECT table_name,
              num_rows,
              rank() over (ORDER BY num_rows desc nulls last) 순위
         FROM dba_tables
        WHERE owner = 'SH'
       )
 WHERE 순위 in (1,2,3);



문제424. hr 계정이 가지고 있는 테이블들에 대한 상세한 설명이 되어있는 정보를 조회하시오


select table_name, comments
  from dba_tab_comments
where owner = 'HR';

문제425. scott 계정이 가지고 있는 테이블들에 대한 상세한 설명이 되어있는 정보를 조회하시오



select table_name, comments
  from dba_tab_comments
where owner = 'SCOTT';


  • 테이블에 대한 주석을 다는 방법
- COMMENT ON TABLE EMP
IS '사원에 대한 정보가 들어있는 테이블입니다.';

문제426. DEPT 테이블에 대한 COMMENT 를 생성하시오


  COMMENT ON TABLE EMP
IS '사원에 대한 정보가 들어있는 테이블입니다.';

   COMMENT ON TABLE DEPT
IS '부서에 대한 정보가 들어있는 테이블입니다.';


문제427. EMP 테이블이 언제 생성이 되었는지 확인하시오 .


SCOTT> DESC USER_TABLES;

SCOTT> DESC USER_OBJECTS;








ALTER SESSION SET nls_date_format='RR/MM/DD:HH24:MI:SS';


문제428. emp 테이블에 email 이라는 컬럼을 추가하세요


alter table EMP add email varchar2(50);


문제429. emp 테이블에 최근에 컬럼 추가/삭제와 같은 DDL 작업이 언제 발생햇는지 알아내시오 

SELECT LAST_DDL_TIME, OBJECT_NAME
  FROM USER_OBJECTS;
  --WHERE OBJECT_NAME= 'EMP';

문제430. EMP 테이블에 추가한 EMAIL 컬럼을 삭제하시오


  alter table emp drop column email;

문제431. sal 이라는 컬럼이 어느 테이블에 포함되어 있느닞 확인하시오

select table_name, column_name
  from dba_tab_columns
where column_name='SAL';



  • 휴지통을 비우는 명령어
SCOTT@orcl> purge recyclebin;

휴지통이 지워졌습니다.


  • data dictionary 를 조회하는 view 
select * from dictionary;

select * from dictionary
where table_name like '%VIEW%';

문제432. DB 에 INDEX가 총 몇개가 있는지 조회하시오 





SELECT COUNT(*) FROM ALL_INDEXES;






























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

6장. 서브쿼리에서의 데이터 검색  (0) 2017.09.07
5장. 다른 시간대에서의 데이터 관리  (0) 2017.09.07
4장 대형 데이터 집합 조작  (0) 2017.09.07
2장 스키마 객체 관리  (0) 2017.09.07
1장 유저 엑세스 제어  (0) 2017.09.07

2장 스키마 객체 관리

SQL 심화2017. 9. 7. 03:16
■ 어제 배운내용 정리 
  1. 고급 SQL 과정 1장 , DCL 문( GRANT, REVOKE)
  2. 객체 권한, 시스템 권한
        
     시스템 권한 : SELECT ANY TABLE 권한

          DB의 어느 테이블이든 다 조회 할 수 있다.

  1. ROLE (CONNECT, RESOURCE, DBA롤)
  2. FLASHBACK 기술
          - FLASHBACK QUERY
          - FLASHBACK TABLE 
  1. EXTERBAL TABLE

          외부의 TEXT, EXCEL 파일의 DATA를 DB에 INSERT 하지 않고 
          파일에 링크만 걸어서 바로 SELECT 할 수 있게 한 테이블

          국민은행 ----------------------> 외환은행
                         이체내역 엑셀 전달

               



          
생성된 DIRECTORY를 확인하는 방법  
          
SELECT * FROM DBA_DIRECTORIES;
생성된 DIRECTORY를 삭제하는 방법
DROP DIRECTORY EMP_DIR;
                    
SQL> create table ext_emp
  (emp_id number(3),
  emp_name varchar2(10),
  hiredate date)
--- 일반 테이블 생성 문법과 같다.


  organization external
-- external table 을 생성하겠다.
* 우리가 기존에 만들었던 테이블은 heap table 이라고 한다.


  (type oracle_loader      ---- external table 이 외부의 data를 불러올때 사용하는                      
                                                                      SQL*loader 로 하겠다 (text ---> table)


  default directory emp_dir       --- 위에서 만들었던 디렉토리 이름을 명시한다.

  access parameters               --- text 파일에 대한 문법설명
  (records delimited by newline
  fields terminated by ","            --- 컬럼과 컬럼의 구분은 , 이다.

  (emp_name char,                   --- emp_ename 은 char 형식이다.
  emp_id char,                      --- emp_id는 char 형식이다.
  hiredate date "yyyy/mm/dd") )
  location ('emp1.txt') );


확장자 보이기로 해야 한다. emp1.txt.txt가 될 수 있음








문제413. ext_emp와 emp 테이블을 조인해서 아래의 결과를 출력하시오 ! ( ename 으로 조인하시오 )


select * from ext_emp, EMP
where emp_name = ename;



  • EXTERNAL TABLE의 제약사항

  1. 인덱스가 안걸린다
  2. DML 작업이 안된다.


문제414.EXT_EMP 테이브렝 EMP_ID에 인덱스를 생서하시오!
CREATE INDEX EXT_EMP EXT_EMP_EMP_ID
ON EXT_EMP(EMP_ID);

문제415. EXT_EMP 테이블의 데이터를 DELETE로 전부 지워보시오
DELETE FROM EXT_EMP;



문제416. EXTERNAL TABLE 을 생성하는 작업을 수행하시오!
  1. SQL gate에서 dept 테이블을 쿼리하고 결과를 csv로 저장하시오
     (이름 : dept.txt)

  1. dept1.txt.를 엑세스 하기 위한 external 테이블을 생성하시오
     (이름 : dept_ext)


문제417. 점심시간 문제. 우리반 (emp2) 테이블을 external 테이블로 생성하시오

  1. emp2.csv 로 파일을 생성한다.

  1. emp2_ext 로 external table을 생성한다.


create table emp2_ext
  (  EMPNO        NUMBER(10),
ENAME        VARCHAR2(30),
AGE        NUMBER(10),
BIRTH        DATE,
MAJOR        VARCHAR2(50),
EMAIL        VARCHAR2(40),
MOBILE        VARCHAR2(20),
ADDRESS        VARCHAR2(100),
TELECOM        VARCHAR2(10))
  organization external
  (type oracle_loader

  default directory emp_dir

  access parameters
  (records delimited by newline
  fields terminated by ","

  (  EMPNO        char,
ENAME        char,
AGE        char,
BIRTH        DATE "rrrr/mm/dd",
MAJOR        char,
EMAIL        char,
MOBILE        char,
ADDRESS        char,
TELECOM        char ))
  location ('emp2.csv') );



















                                   

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

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

1장 유저 엑세스 제어

SQL 심화2017. 9. 7. 03:16
select *
from user_tables;
 
 
 
■ SQL 교재 구성
 
    • Fun 1 ---> 기본 SQL문
    • SELECT 문의 6가지절
    • 함수
    • 조인
    • 서브쿼리
    • 집합 연산자
    • 레포팅 함수
    • DML 문
    • TCL 문
    • DDL 문
    • Database object 5가지
 
 
 
    • Fun2 --> 고급 SQL 문
    • DCL 문
 
■ 1장. 유저 엑세스 제어
 
    • 1장 목차
      1. 유저생성
      2. 패스워드 변경하는 방법
      3. 시스템 권한
      4. 객체 권한
      5. 롤 사용법
DCL 문(Database Control Language)
 
Database 의 권한을 제어하는 언어 : grant , revoke
 
 
1.1 유저 생성
Create user smith
identified by tiger;
 
 
문제379. smith유저로 접속하시오 !
 
SQL> connect smith/tiger;
ERROR:
ORA-01045: user SMITH lacks CREATE SESSION privilege; logon denied

 
 
경고: 이제는 ORACLE에 연결되어 있지 않습니다.
SQL>
 
접속할 수 있는 권한이 없어서 나는 오류
 
 
SQL> connect smith/tiger;
ERROR:
ORA-01045: user SMITH lacks CREATE SESSION privilege; logon denied
 
 
경고: 이제는 ORACLE에 연결되어 있지 않습니다.
SQL> connect / as sysdba
연결되었습니다.
 
 
문제380. smith 유저가 접속을 할 수 있도록 sys유저에게
smith 유저에게 접속할 수 있는 권한을 주시오
 
SQL> grant connect to smith;
 
권한이 부여되었습니다.
 
문제381. KING 이라는 유저를 생성하고 접속 할 수 있는 권한을 부여하시오
CONNECT / AS SYSDBA
 
CREATE USER KING
IDENTIFIED BY TIGER;
 
GRANT CONNECT TO KING;
 
CONNECT KING/TIGER;
 
 1.2  패스워드를 변경하는 방법
alter user king
identified by oracle1234;
 
SQL> connect king/oracle1234
연결되었습니다.
SQL> password
KING에 대한 비밀번호를 변경합니다.
이전 비밀번호:
새 비밀번호:
새 비밀번호 다시 입력:
비밀번호가 변경되었습니다.
SQL>
 
문제382. scott의 password를 복잡하게 변경하시오
 
connect / as sysdba
 
alter user scott
identified by oracle1234
 
 
 
1.3 시스템 권한
database 에서 특정 작업을 수행할 수 있는 권한
 
예: create table, create view, create session
 
문제383. king으로 접속해서 아래의 테이블을 생성하시오!
create table emp383
(  empno number(10),
   ename varchar2(20) );
 
 
    • sys 유저에서 king 에게 create table 권한을 부여하시오
SQL> grant create table to king;
 
    • 나에게 있는 시스템 권한이 무엇인지 확인하는 방법
select *
  from session_privs;
 
문제384. sys 유저가 king에게 create view, create sequence  권한을 부여하고 권한이
잘 들어갔는지 확인하오
 
SQL> grant create view, create sequence to king;
 
권한이 부여되었습니다.
 
 
 
SQL> select * from session_privs;
 
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE VIEW
CREATE SEQUENCE
 
 
 1.4 객체 권한
 
" 특정 테이블의 데이터를 엑세스 할 수 있는 권한 "
 
문제385. SCOTT유저에서 KING 유저에게 SCOTT의 EMP 테이블을 SELECT 할 수있는 권한을 주시오
 
 
가 > 스콧에게
SQL> grant create public synonym to scott;
 
권한이 부여되었습니다.
 
스콧이 > 킹에게
 
SQL> create public synonym emp for scott.emp;
 
동의어가 생성되었습니다.
 
킹이 >
 
 
SQL> select * from emp;
 
     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM
---------- ---------- --------- ---------- -------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 80/12/17        800
        20
 
 
 
 
 
문제387. scott 유저가 king에게 scott의 emp 테이블을 update하고 delete 할 수 잇는 권한 (객체권한)
을 주시오
 
SQL> grant update, delete on emp to king;
 
권한이 부여되었습니다.
 
    • 권한의 종류 2가지
      1. 시스템 권한 : DB에서 특정 작업을 수행 할 수 있는 권한
 예: create table, create view, create sequene, ...
 
관련 데이터 사전 : select * from sesscion_privs;
 
 
    • 객체 권한 : 특정 data를 엑세스하거나 조작 할 수 있는 권한
 예: select on emp, update on emp, ...
 
관련 데이터 사전 : select * from user_tab_privs;
 
 
■ 권한 취소 명령어 : revoke
 
문제388. scott 유저가 king 에게 부여한 emp 테이블을 delete 할 수 있는 권한을 취소하시오
scott>
revoke delete on emp from king;
 
 
 
문제389. sqlplus 프롬프트 창에서 앞에 유저명이 뜨게하려면
      어떻게 해야하는거?
 
glogin.sql 스크립트를 열어서 그 안에 아래의 내용을 추가한다.
 
Path에서 찾음
 
C:\app\stu\product\11.2.0\dbhome_1\sqlplus\admin
 
glogin.sql 파일을 !
SET SQLPROMPT '&_USER.@&_CONNECT_IDENTIFIER.> '
 
 
    1. 롤사용법
 
role 이란? 권한의 집합
 
권한을 하나씩 일일이 주기가 번거로우니까 권한을 모아 놓은
role을 생성해 놓았다가 role을 부여함 권한을 한번에 !!!!!!!!!!!!
 
문제390. allen 이란 유저를 만들고 allen 유저에게 접속할 수 있는 권한도 주고 테이블을 생성
할 수 있는 권한도 주고, 기본적인 권한들을 부여하고 싶다면 ?
 
create user allen
  identified by tiger;
 
grant connect, resource to allen;
               ↑                  ↑
              role               role
 
호롤롤롤
 
문제391. jones 라는 유저를 생성하고 jones 유저에게 기본적인 권한을 부여하고 jones 유저로 접혹해서emp 테이블을 생성하고 부서 번호와 부서번호별 토탈 월급을 출력하는데 맨 아래 쪽에 전체토탈월급을
아래와 같이 출력하시오
 
 select decode(deptno,null,'전체토탈',deptno) as deptno, sum(sal) from emp group by grouping sets ((deptno),());
 
DEPTNO                                     SUM(SAL)
---------------------------------------- ----------
10                                             8750
20                                            10875
30                                             9400
전체토탈                                      29025
 
1.6 계정 잠금과 잠금해제
 
오라클은 설치를 하고 나면 기본적으로 만들어지는 유저들이 sys와 system을 제외하고는 다 잠겨있다.
--> 접속이 안되어있다
    • db에 있는 유저들 확인
 
sys> select username, account_status
   from dba_users;
 
    • role 중에 가장 많은 권한이 있는 dba롤을 scott에게 부여
 
SYS@orcl> grant dba to scott;
 
권한이 부여되었습니다.
 
문제392. sh 계정의 패스워드를 sh로 변경하시오
 
SYS@orcl> alter user sh
  2  identified by sh;
 
사용자가 변경되었습니다.
--------------------------------------------------------------------------------------
SYS@orcl> connect sh/sh
ERROR:
ORA-28000: the account is locked
 
==> 계정이 잠기었네요 풀어주세요
 
문제394. sys유저에서 잠긴 sh 계정의 락을 해제 하시오!
 
 
SYS@orcl> alter user sh
  2    account unlock;
 
사용자가 변경되었습니다.
2. 유저 gangs를 만드시오
 
3. gangs로 접속해서 고백 테이블을 만드는데 sys에서 gangs로 권한부여해서 emp테이블의 sal중 가장 높은
사원의 sal을 입력하여 고백테이블을 만드시오.
 
4. gangs가 wontae에게 권한을 부여하시오
 
5. wontae로 접속해서 사귐 테이블을 만드는데 고백 테이블을 시타스로해서 생성하시오.(gangs의 권한부여 필요)
 
문제395. hr의 계정의 락을 해제하고 패스워드를 hr로 변경하시오.
 
■■ 2장. 스키마 객체 관리
    • 2장 목차
    • 제약조건추가
    • create table 시 인덱스 생성
    • 함수 기반 인덱스
    • 컬럼삭제 및 컬럼 감추기
    • Flash back 타임머신을 타고 과거로 되돌아가는 Flash back 테이블
    • external table 사용법 ( 하둡의 hive의 테이블과 비교 )
 
■ 2.1 제약 조건 추가
    • 제약을 사용하는 이유?
"데이터의 품질을 높이기 위해서 "
 
    • 사원테이블에 ename 에 unique 제약을 걸어라!
alter table emp
add constraint emp_ename_un unique(ename);
 
문제396. 사원 테이블의 월급이 9000 이상의 데이터가 입력 또는 수정
될 수 없게 check제약을 거시오
 
 
alter table emp
add constraint emp_sal_ch check (sal < 9000);
 
 
문제397. 사원 테이블에 부서번호에 not null 제약을 걸어보세요
alter table emp
add constraint emp_deptno_nn not null(deptno);
 
설명 : not null 제약은 위와 같은 방법으로는 안되고 아래와 같이 작성해야한다.
alter table emp
modify deptno constraint emp_deptno_nn not null;
 
 
 update emp set deptno = null;
               *
1행에 오류:
ORA-01407: NULL로 ("SCOTT"."EMP"."DEPTNO")을 갱신할 수 없습니다
 
문제398. 사원 테이블에 ename에 not null 제약을 거시오
 
alter table emp
modify ename constraint emp_ename_nn not null;
 
 
문제399. 사원테이블에 걸린 모든 제약을 다 삭제하시오
 
SCOTT@orcl> select constraint_name
  2    from user_constraints
  3  where table_name = 'EMP';
 
CONSTRAINT_NAME
------------------------------
EMP_ENAME_UN
EMP_SAL_CH
EMP_DEPTNO_NN
EMP_ENAME_NN
PK_EMP
FK_DEPTNO
 
6 개의 행이 선택되었습니다.
drop co
 
■ 2.2. create table 시 인덱스 생성
 
문제400.  사원 테이블에 월급에 인덱스를 생성하시오
 
create index emp emp_sal
on emp(sal);
 
select index_name, column_name
  from user_ind_columns
where table_name= 'EMP';
 
문제401.  사원 테이블에 empno에 primary key 제약을 거시오.
 
select index_name, column_name
  from user_ind_columns
where table_name= 'EMP';
 
SCOTT@orcl> alter table emp
  2  add constraint emp_empno_pk primary key(empno);
 
 
 
 
문제402. 사원 테이블에empno에 걸린 primary key 제약을 삭제하시오
alter table emp
drop constraint emp_empno_pk;
 
문제403. 사원 테이블에 empno 에 다시 primary key 제약을 거는 데
이번에는 자동으로 생성되는 인덱스 이름이 emp_empno 라고 되게하시오
 
alter table emp
add constraint emp_empno_pk primary key(empno)
using  index ( create index emp_empno
on emp(empno) );
 
문제404. empno에 primary key 제약을 삭제하면 인덱스도 같이 삭제되나?
같이 삭제 안됨
 
문제405. 아래의 SQL을 튜닝하시오 !
CREATE INDEX EMP_SAL
ON EMP(SAL);
 
튜닝전
SELECT ENAME, SAL * 12 연봉
FROM EMP
WHERE SAL * 12 = 36000;
 
튜닝후
SELECT ENAME, SAL * 12 연봉
FROM EMP
WHERE SAL  = 36000 / 12;
 
문제406. 아래의 SQL을 튜닝하세요
이름이 JACK인 사원의 이름과 월급을 출력하시오
 
create index emp_ename
on emp(ename);
 
insert into emp(empno, ename, sal) values (1023, ' jack ', 4000);
 
튜닝전 :
 SELECT ENAME, SAL
    FROM EMP
WHERE TRIM(ENAME) = 'JACK';
 
 
 
 SELECT ENAME, SAL
    FROM EMP
WHERE ENAME LIKE '%JACK%';
 
 
튜닝후:
 
설명 :  튜닝하는 방법은 함수 기반 인덱스를 생성하면 된다.
 
CREATE INDEX EMP_ENAME_FUN
  ON EMP( TRIM(ENAME) );
 
 SELECT ENAME, SAL
    FROM EMP
WHERE TRIM(ENAME) = 'JACK';
 
 
문제407. 아래의 SQL을 튜닝하시오 !  ( ENAME의 DATA가 대소문자가 섞여있는 상황)
튜닝전 : SELECT ENAME, SAL , DEPTNO
        FROM EMP
     WHERE UPPER(ENAME) = 'SCOTT';
 
튜닝후 :  CREATE INDEX EMP_ENAME_UPPER
ON EMP ( UPPER(ENAME) );
 
SELECT ENAME, SAL
  FROM EMP
WHERE UPPER(ENAME) = 'SCOTT';
 
 
    • 2장 목차
      1. 제약조건추가
      2. create table 시 인덱스 생성
      3. 함수 기반 인덱스
      4. 컬럼삭제 및 컬럼 감추기
 
 
문제408. 사원 테이블에 sal 칼럼을 삭제하시오 !
alter table emp
drop column sal;
 
 
select * from emp;
 
문제409. 우리반 테이블을 ctas로 백업하고 우리반 테이블에서
핸드폰번호를 삭제하시오
 
 
alter table EMP2
drop column mobile;
 
 
    • 컬럼 감추는 방법
문제 410. emp 테이블에 ename 컬럼을 감추시오
 
alter table emp
  set unused column ename;
 
alter table emp
drop unused columns;
 
 ■ Flash back 타임머신을 타고 과거로 되돌아가는 Flash back 테이블
 
Flash back ?  ?
특정테이블을 과거로 돌리는 기능
 
    • 1분전에 emp table 에 데이터가 있었는지 무엇이었는지 조회를 해보자!
 
select *
from emp 
as of timestamp(systimestamp - interval '3' minute);
 
 
    • emp 테이블 6분전으로 되돌리는 작업 수행
 
 
alter table emp enable row movement;
 
설명 : emp 테이블을 flashback 할 수 있게 설정하는 방법
 
flashback table emp to timestamp
(systimestamp - interval '10' minute);
 
설명: emp 테이블을 10분전으로 flashback 하는 명령
 
 
    • emp table 을 drop 하시오!
 
=> 휴지통 속으로 들어감
 
 
    • 휴지통을 조회하시오
show recyclebin;        ----->cmd창에서
 
 
select *
from user_recyclebin; -------> sqlgate에서
 
flashbak table emp to before drop;
 
 
문제411. dept 테이블을 delete 하고 commit 하시오 !
 
delete from dept;
commit;
 
 
문제412. dept 테이블이 지워지기 전 시간이 어떻게 되는지 flashbak query 로 확인해 보시오
 
 
    • 현재 시간을 확인 해본다.
delete from EMP;
commit;
 
 
    • flashback query 로 특정 시간의 dept 테이블을 조회해본다.
 
select *
  from dept
as of timestamp to_timestamp('17/03/14 16:13:00', 'RR/MM/DD HH24:MI:SS');
 
    • DEPT 테이블을 FLASHBACK TABLE 해서 17/03/14 16:13:00 으로 되돌리시오~
 
ALTER TABLE DEPT ENABLE ROW MOVEMENT;
 
 
 
flashback table DEPT TO TIMESTAMP to_timestamp('17/03/14 07:13:06', 'RR/MM/DD HH24:MI:SS');
 
 
 
 
CMD창에서 FLASHBACK 시간 확인
 
SHOW PARAMETER UNDO_RETENTION;
 
SQLGATE에서는
 
SELECT *
FROM V$PARAMETER
WHERE NAME='undo_retention';   --언두 리텐션을 소문자로 !
 
 
 
■ external table 이란?
↓
       외부
 
대학 등록금 테이블을 생성하려고 csv 파일을 오라클에 입력해서 테이블을 생성했는데 external table은
오라클에 csv data를 입력하지 않고 os 에 csv 파일을 그대로 두고 거기에 링크를 걸어서 data를 엑세스하는 테이블
 
 
 
External Table 실습예제
 
1. emp1.txt 편집(/home/oracle/emp1.txt)
 
SMITH,101,2001/03/15
JOHN,102,2002/04/15
SMITH,101,2001/03/15
JOHN,102,2002/04/15
SMITH,101,2001/03/15
JOHN,102,2002/04/15
SMITH,101,2001/03/15
JOHN,102,2002/04/15
SMITH,101,2001/03/15
JOHN,102,2002/04/15
SMITH,101,2001/03/15
JOHN,102,2002/04/15
 
2. Directory 생성
 
 SQL> connect   scott/tiger
 
SQL> create directory emp_dir as '/home/oracle/';
 
3. External table 생성
 
 SQL> create table ext_emp
         (emp_id number(3),
           emp_name varchar2(10),
           hiredate date)
           organization external
          (type oracle_loader
           default directory emp_dir
           access parameters
           (records delimited by newline
            fields terminated by ","
           (emp_name char,
            emp_id char,
            hiredate date "yyyy/mm/dd") )
            location ('emp1.txt') );
 
4. Select & DML 테스트
 
SQL> select * from ext_emp;
 
 
EMP_ID      EMP_NAME   HIREDATE
---------   ----------     ------------
101            SMITH           15-MAR-01
102            JOHN             15-APR-02
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

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

6장. 서브쿼리에서의 데이터 검색  (0) 2017.09.07
5장. 다른 시간대에서의 데이터 관리  (0) 2017.09.07
4장 대형 데이터 집합 조작  (0) 2017.09.07
3장 데이터 딕셔너리를 통해서 객체 관리  (0) 2017.09.07
2장 스키마 객체 관리  (0) 2017.09.07

티스토리툴바