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

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

뷰어
댓글로
이전글
다음글

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

6장 목차

MULTIPLE COLUMN SUBQUERY
- - SUBQUERY 의 종류 3가지
1. 1. SINGLE ROW SUBQUERY
  2. MULTIPLE ROW SUBQUERY
  3. MUTIPEL COLUMN SUBQUERY
    1. - PAIR WISE 방식 ===> 오라클에 지원하는 방식
    2. - NON PAIR WISE 방식 ===> 오라클 , non 오라클 db지원
SCALAR SUBQUERY
상호관련 서브쿼리 - SELECT 문
상호관련 서브쿼리 DML 문
EXISTS 문 NOT EXISTS 문
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개 테이블을 생성하시오

겨울왕국 대문 insert를 위한 winter 테이블 생성
감정분석을 위해 긍정단어들을 입력하기 위한 positive 테이블생성
감정분석을 위해 부정단어들을 입력하기 위한 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장 목차

MULTIPLE COLUMN SUBQUERY
- - SUBQUERY 의 종류 3가지
1. 1. SINGLE ROW SUBQUERY
  2. MULTIPEL ROW SUBQUERY
  3. MUTIPEL COLUMN SUBQUERY
    1. - PAIR WISE 방식 ===> 오라클에 지원하는 방식
    2. - NON PAIR WISE 방식 ===> 오라클 , non 오라클 db지원
SCALAR SUBQUERY
상호관련 서브쿼리 - SELECT 문
상호관련 서브쿼리 DML 문
EXISTS 문 NOT EXISTS 문
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);

■ 임시 테이블 사용법

" 데이터를 임시로 저장하는 테이블 "

언제까지 데이터를 저장하는가?

COMMIT할 때까지 ON COMMIT DELETE ROWS
세션이 종료 될때까지 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

Quiet Time

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

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

최근에 올라온 글

최근에 달린 댓글

공지사항

글 보관함

링크

티스토리툴바

« 2024/05 »
일	월	화	수	목	금	토
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31