Quiet Time

8장 집합 연산자 사용

SQL 기본2017. 9. 7. 02:01

■어제까지배운내용복습
    1. SQL이란 무엇인가?
    2. SQL을 왜 배워야 하는거ㅏFULL OUTER JOIN
DATA를 다루는 기본언어를 잘 숙달하기 우해서
"머신 러닝과정에서 SQL이 왜 필요한가?"
 
머신러닝 알고리즘(수학 알고리즘)을 구현 가능하게
한게 빅데이터이다.
 
하둡이 나오면서 빅데이터를 저장하고 빅데이터를 분석할 수 있게 되었다.
 
빅데이터를 검색하려면 예전에는 JAVA를 잘 알야했다.
 
NoSQL이 java를 몰라도 빅데이터를 검색하고 분석할 수 있게해줍니다.
 
하둡 -> 자바 -> NoSQL(HIVE, pig...)
SQL ---> R --->python --->shell 스크립트 ---> java
 
SQL --> PLSQL --> PYTHON --> 리눅스 쉘 스크립트 --> 하둡 --> 텐서 플로우
 
    1. 기본 SQL의 6가지 절
select
  from
where
group by
having
order by
 
    1. 함수
    • 단일행 함수
      • 문자 , 숫자, 날짜 , 변환 , 일반

 
 
      -    복수행 함수
    • max , min , avg , sum , count
함수를 왜 알아야 하는가?
 
페이스 북 데이터를 스크롤링해서 하둡에 저장한 다음에
NoSQL을 날려서 이 키워드가 어느시간대에 가장 많이 글로 올라왔는지를 분석
 
 
페이스북에 올라온 글                                vs                               emp  table
빅데이터(비정형화 된 데이터 )                                                        small data

 
    • 조인
      • 오라클 조인 문법
        1. equi join
        2. non equi join
        3. outer join
        4. self join
 
    • 1999 ansi  문법
 
■ 분석함수의 종류
    • listagg
    • rank
    • dense_rank
    • ntile
    • 누적 데이터 출력하는 분석함수
 
 
문제189. 사원번호, 이름, 월급, 월급의 누적치를 출력하시오
select empno, ename, sal, sum(sal) over ( order by empno asc) 누적치
  from emp;
 
문제190. 부서번호, 이름, 월급, 월급의 누적치를 출력하는데
월급의 누적치가 부서번호 별로 각각 월급이 누적되게 하시오
 
select empno, ename, sal, sum(sal) over (partition by deptno order by empno asc) 누적치
  from emp;
 
 
문제191. 부서번호, 이름, 월급, 월급의 누적치를 출력하는데
월급의 누적치가 부서위치 별로 각각 월급이 누적되게 하시오
 
 
select d.loc, e.empno, ename, sal,
sum(sal) over (partition BY d.loc order by sal asc) 누적치
  from EMP e ,DEPT d
  WHERE e.deptno = d.DEPTNO AND sal IS NOT NULL ;
 
문제192. 이름, 부서위치를 출력하는 outer join을 사용해서
사원 이름쪽에는 데이터가 나오는데 부서위치쪽에는 데이터가 안나오는 결과를 출력하시오
 
select e.ename, d.loc
 from emp e, dept d
where e.deptno  = d.deptno (+) ;
 
문제193. 위의 결과를 다시 출력하는데 이름도 모자란것으로 보고싶고
부서위치도 모자란것으로 보이게 결과를 출력하시오
 
    • 오라클 조인 문법
      • equi join
      • non equi join
      • outer join
      • self join
 
    • 1999 ansi  (American National Standard Institute)조인 문법
 
 
full outer join   (10g  버전부터 가능)
 
select e.ename, d.loc
  from emp e full outer join dept d
  on ( e.deptno = d.deptno );

 
 
문제 194.학생번호 이름, 나이, 나이의 누적치를 출력해보시오
 
  SELECT empno, ename, age, SUM(age) OVER (ORDER BY empno asc)
  FROM EMP2;
 
 
 
문제 195 . 통신사 , 이름 , 나이 , 나이의 누적치를 출력하는데 통신사 별로 각각
나이의 누적치가 출력되게 하시오
 
 
  SELECT telecom, empno, ename, age, SUM(age) OVER (PARTITION BY telecom ORDER BY empno ) 누적치
  FROM EMP2;
 
문제196. (점심시간문제)통신사, 통신사별로 해당 하는 사원들의 학생들의 출력하는데
sk  김명학(31), 길용현(30), 김경찬(28).........         
lg  김진희(33)
kt ............
 
 
  SELECT telecom, empno, ename, age, SUM(age) OVER (PARTITION BY telecom ORDER BY empno ) 누적치
  FROM EMP2;
 
  SELECT telecom, listagg(ename||'('||age||')', ',') within GROUP (ORDER BY age DESC) 이름
  FROM EMP2
  GROUP BY telecom
  ORDER BY telecom desc;
 
 
 
 
 
문제197. 아래의 오라클 조인 문법을 1999ansi 문법으로 변경하시오
 
--오라클 조인 문법:
select e.ename, d.loc
  from emp e, dept d
  where e.deptno = d.deptno (+);
 
-- 1999 ansi 문법
select e.ename, d.loc
  from emp e right outer join dept d
    on (e.deptno  = d.deptno);
 
 
                 select e.ename, d.loc
  from emp e right outer join dept d
    on (e.deptno  = d.deptno)
 
-- self join
 
mgr 컬럼 : 자기 직속상사의 사원번호
 

 
 
7902는 SMITH 사원에게는 MGR이지만
FORD라는  관리자에게는 EMPNO이다.
 
문제 198. 사원이름, 관리자인 사원(직속상사)의 이름을 출력하시오
SELECT 사원.ENAME, 관리자.ENAME MRG
  FROM EMP 사원,  EMP 관리자
  WHERE 사원.MGR = 관리자.EMPNO;
 
 
문제199. 사원이름, 사원월급, 관리자의 이름, 관리자의 월급을 출력하는데
관리자보다 더 많은 월급을 받는 사원들이 출력되게하시오!
 
문제200. 아래와 같이 결과를 출력하시오

 
  SELECT  관리자.ENAME 관리자 , LISTAGG(사원.ENAME,',') WITHIN GROUP (ORDER BY 사원.ENAME) 사원
  FROM EMP 사원,  EMP 관리자
  WHERE 사원.MGR = 관리자.EMPNO
  GROUP BY 관리자.ENAME;
 
 
문제 201 겨울왕국 대본을 단어별로 쪼개서 결과를 출력하시오
 
SELECT regexp_substr(lower_substr(lower(win_text), '[!^ ]') aaa
from winter3;
 
 
 
 
문제 202. 겨울오아국 대본의 모든 단어를 테이블에 입력하시오
 
create table winter_kingdom
(win_text varchar2(100) );
 
 ■3개 이상의 테이블 조인
문제203. 사원이름, 부서위치, 월급, 급여등급(grade) 을 출력하시오
 
dept ------------------------emp -------------------------salgrade
 
 
select e.ename, d.loc, e.sal, s.grade
 FROM EMP e, DEPT d, SALGRADE s
 WHERE E.DEPtno = d.DEPTNO AND e.sal BETWEEN s.LOSAL AND s.HISAL;
 
문제204 bonus_table만드시오
CREATE TABLE bonus_table
AS
SELECT empno, sal * 1.5 AS bonus
      FROM EMP;
     
 
 
문제 205. DALLAS 에서 근무하는 사원들의
사원이름, 월급, 보너스, 부서위치를 출력하시오!
 
DEPT-------------------------EMP------------------- BONUS_TABLE
 
SELECT E.ENAME, E.SAL, B.BONUS, D.LOC
  FROM EMP E, DEPT D, BONUS_TABLE B
WHERE E.DEPTNO = D.DEPTNO AND E.EMPNO = B.EMPNO AND D.LOC = 'DALLAS';
 
 
 
■조인 문법 2가지
    • 오라클 조인 문법
    • EQUI JOIN
    • NON EQUI
    • OUTER
    • SELF JOIN
    • 1999 ANSI 문법
    • FULL OUTER JOIN
    • LEFT/RIGHT OUTER JOIN
    • ON 절을 사용한 조인
 
SELECT E.ENAME, D.LOC
   FROM EMP E JOIN DEPT D
  ON ( E.DEPTNO = D.DEPTNO);
 
 
문제206.위의 결과를 다시 출력하는데 월급이 3000 이상인 사원들만
출력하시오!
          SELECT E.ENAME, D.LOC
   FROM EMP E JOIN DEPT D
  ON ( E.DEPTNO = D.DEPTNO)  --조인조건
      WHERE E.SAL > =3000;                 -- 검색조건
 
 
 
문제207. 이름, 월급, 부서위치, 급여등급(GRADE)을 출력하는데
        ON절을 사용한 조인 문법으로 수행하시오
 
select e.ename, d.loc, s.grade
 FROM EMP e JOIN DEPT d JOIN SALGRADE s
  ON (E.DEPtno = d.DEPTNO )
  WHERE  e.sal BETWEEN s.LOSAL AND s.HISAL;
===> 이건 안뎀!
 
문제208. 겨울왕국 대본의 단어, 단어별 건수를
출력하시오!
SELECT WIN_TEXT , COUNT(*)
FROM WINTER_KINGDOM
GROUP BY WIN_TEXT
;
 
  문제 209. 위결과를 내림차순 건수로 출력하시오
 
 
SELECT WIN_TEXT , COUNT(*)
FROM WINTER_KINGDOM
GROUP BY WIN_TEXT
ORDER BY COUNT(*)  DESC;
 
문제210. 위 결과를 다시 출력하는데 단어, 선수, 순위를 출력하시오 (건수에대한순위)
  SELECT WIN_TEXT , COUNT(*) ,RANK() OVER ( ORDER BY COUNT(*) DESC) 순위
FROM WINTER_KINGDOM
GROUP BY WIN_TEXT
;
 
 
 
 
 
 
■ 조인문법
    • 오라클 조인 문법
    • 1999 ANSI 문법
    • full outer join
    • left/right outer join
    • on 절을 사용한 join
    • using 절을 사용한 join
    • natural join
    • cross join
 
  Using
select e.ename, d.loc
 from emp e join DEPT d
using (deptno);
 
 
Natural (지가 알아서!)
select e.ename, d.loc
 from emp e natural join dept d;
 
Cross (그냥 다 함)
select e.ename, d.loc                   
  from emp e cross join dept d;
 
이것과 같음
select e.ename, d.loc
  from emp e , dept d;
 
 
문제211. 부서위치, 부서위치별 토탈월급을 가로로 출력하시오!
(pivot문으로)
 
          select *
 from (select d.loc, e.sal
from emp e, dept d
            WHERE D.DEPtNO = E.DEPTNO )
 
pivot ( sum(sal) for loc in ('DALLAS' as "DALLAS",
'CHICAGO' as "CHICAGO",
'NEW YORK' as "NEW YORK",
'BOSTON' as "BOSTON"));
 
 
문제212. (오늘의 마지막 문제)
 
영화 겨울왕국에 나오는 단어중에 가장 긴 단어는 무엇인지 출력하시오


  1. SQL이란 무엇인가.
 데이터를 입력 , 조작, 검색할 때 사용하는 언어
 
 - SELECT 문의 6가지 절
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
 
  • 함수
  1. 단일행 함수
 
  1. 복수행함수
  • 조인
  1. 오라클 조인 문법
  • EQUI JOIN
  • NON EQUI JOIN
  • OUTER JOIN
  • SELF JOIN
 
  1. 1999 ANSI  문법
  • FULL OUTER JOIN
 
  • DATA 분석함수
  1. LISTAGG
  2. NTILE
  3. RANK
   4.    DENSE_RANK
  1. 누적데이터
 
 
  1. SQL을 왜 배워야하는지?
파이썬과 R을 활용한 머신러닝 전문가 과정
                                                  ↓
                                         머신이 스스로 (데이터) 학습하게끔 만드는 기능
                                         ↓
빅데이터 (원유)
 
 
인공지능 기술 연구의 선두기업들 : GOOGLE, FACEBOOK, 바이두 등
 
 2013년에 구글은 인공지능 연구를 위해서 딥러닝의 1인자인
토론토 대학 제프리 힌트 교수를 영입.
 
2014년에 영국의 딥마인드 테크놀로지스사를 매수했다.
사원수가 고작 12명인 회사인데 페이스북과 경쟁하여 4억달러(4200억원)에 인수해서 세상을 놀라게 했다.
 
딥러닝 기술로 구현 하고자 하는 여러 사업들중 대표적인 것이
  1. 이미지 학습 : 이미지를 인식하게 하고 학습시키는 방법
 ( 구글에서 이제 고양이 사진을 인식했다.)
 
  1. 음식사진을 계속 학습 시키는 프로그램 개발
 (100억 ~ 150억) 영국회사 + 우리나라회사(텐서플로우 이미지학습) + 서강대 (웹스크롤링)
 
 
파이썬 ( 프로그래밍 스킬 ) --> R (머신러닝 알고리즘)
                수학
  1. 미분
  2. 선형대수학
 --> 텐서플로우
 

 
  • 서브 쿼리 단원 목차
  1. Single Row Subquery
  2. multiple row subquery
  3. 서브쿼리에서의 null 값
  4. exists 문
 
■ 7.1 Single Row Subquery
"JONES 보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력하시오 "
 
SELECT E.ENAME, E.SAL
FROM (SELECT I.SAL FROM EMP I WHERE ENAME='JONES') A ,EMP E
WHERE E.SAL >= A.SAL;
 
 
 
월급이 2975보다 더 큰 사원들의 이름과 월급을 출력하라
SELECT ENAME, SAL
 FROM EMP
WHERE SAL > 2975;
 
 
SELECT ENAME, SAL  ---------------------- 메인 쿼리
 FROM EMP
WHERE SAL > ( SELECT SAL
FROM EMP             ------------------서브 쿼리
WHERE ENAME ='JONES') ;
 
 
 
문제213.SCOTT과 같은 월급을 갖는 사원들의 이름과 월급을 출력하시오
ELECT ENAME, SAL  ---------------------- 메인 쿼리
 FROM EMP
WHERE SAL = ( SELECT SAL
FROM EMP             ------------------서브 쿼리
WHERE ENAME ='SCOTT') ;
 
 
 
문제213.SCOTT과 같은 월급을 갖는 사원들의 이름과 월급을 출력하시오
ELECT ENAME, SAL  ---------------------- 메인 쿼리
 FROM EMP
WHERE SAL = ( SELECT SAL
FROM EMP             ------------------서브 쿼리
WHERE ENAME ='SCOTT') ;
 
 
문제214. SMITH 보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력하는데 월급이 높은 사원부터 출력하시ㅗㅇ
 
SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT SAL
               FROM EMP
  WHERE ENAME = 'SMITH')
ORDER BY SAL DESC;
 
 
 
문제215. ALLEN보다 늦게 입사한 사원들의 이름과 입사일을 출력하시오
SELECT ENAME, SAL
FROM EMP
WHERE HIREDATE > (SELECT HIREDATE
               FROM EMP
  WHERE ENAME = 'ALLEN');
                                
문제216. 직업이 SALESMAN 인 사원들중에서의 최대 월급돠 더 큰 월급을 받는 사원들의
이름과 월급을 출려하시오
 
SELECT ENAME, SAL
FROM  EMP
WHERE  SAL > (SELECT MAX(SAL)
FROM EMP
WHERE JOB ='SALESMAN');
 
 
문제217. 최대 월급을 받는 사원의 이름과 월급을 출력하시오!
 
SELECT ENAME, SAL
FROM  EMP
WHERE  SAL = (SELECT MAX(SAL)
FROM EMP
);
 
 
 
 
 
문제218. 등록금이 가장 비싼 대학교를 출력하라
   select university, tuition
            from univ
            where  tuition = (select max(tuition)
                                                    from univ);
 
 
문제219. 그 동안 내가 만들었던 테이블 리스트를 조회 하시오.
 SELECT * FROM USER_TABLES;
select * from crime_cause2;
 
문제220. 살인의 가장 큰 원인이 무엇인지 알아내시오 기타를 제외하고
 
select term
  from crime_cause2
 where ( select max(
select * from user_tables;
 
 
문제221. 가정불화로 인해 생기는 가장 큰 범죄가 무엇인지 ?
 
select crime_type
from crime_cause2
where cnt = (select max(cnt)
  from crime_cause2
              where term ='가정불화')
              and term = '가정불화';
 
 
문제222. 방화사건의 가장 큰 원인이  무엇인지 출력하시오 ?
 
select term
from crime_cause2
where cnt = (select max(cnt)
  from crime_cause2
              where crime_type = '방화')
              and crime_type = '방화';
 
 
 
7.2 multiple row subquery
 
문제223. 직업이 SALESMAN 인 사원들과 월급이 같은 사원들의 이름과 월급을 출력하시오
SELECT ENAME, SAL
  FROM EMP
 WHERE SAL = ( SELECT SAL
FROM EMP
WHERE JOB = 'SALESMAN');
 
 
  
   SELECT ENAME, SAL
  FROM EMP
 WHERE SAL  IN ( SELECT SAL
FROM EMP
WHERE JOB = 'SALESMAN');
 
 
 
설명 : 서브쿼리의 종류 3가지
  1. single row subquery : 서브쿼리에서 메인쿼리로 하나의 로우가 리턴
 연산자 : = ., >, < , >= , <= , !=, <> ,^=
  1. multiple row subquery : 서브쿼리에서 메인쿼리로 여러개의 로우가 리턴
 연산자 : in, not in, >all, <all, >any, <any
  1. multiple column subquery : 서브쿼리에서 메인쿼리로 여러개의 컬럼값이 리턴되는 경우
  
연산자 : 위의 연산자들을 용도에 따라 사용
 
 
문제224. 월급이 1000에서 3000사이인 사원들과 같은 부서 번호에서 근무하는 사원들의
이름과 월급과 직업을 출력하시오
   select ename, sal , job
 from emp
where deptno in (select deptno
    from emp
where sal between 1000 and 3000);
 
 
문제225. KING에게 보고하는 사원들의 이름을 출력하시오
(KING의 직속 부하사원들을 출력하시
오 )
SELECT ENAME
  FROM EMP
WHERE MGR = (SELECT EMPNO
FROM EMP
WHERE ENAME = 'KING');
문제226. 관리자 인 사원들의 이름을 뽑으시오
 
SELECT ENAME
  FROM EMP
WHERE EMPNO IN  (SELECT MGR
FROM EMP);
 
문제227.
SELECT ENAME
  FROM EMP
WHERE EMPNO  noT IN  (SELECT MGR
FROM EMP);
 
 
ANY
 
 
7.3  서브쿼리에서 NULL 값처리
※ 서브쿼리에서 NOT IN 사용시 주의할 점!
SELECT ENAME
  FROM EMP
WHERE EMPNO IN  (SELECT NVM(MGR,0)
FROM EMP);
 
SELECT ENAME
  FROM EMP
WHERE EMPNO IN  (7369, 7698,...);
=ANY (7369 OR 7698 OR NULL)
 T          OR          T            OR         NULL
                                                           T/F    == T
 
 
 
SELECT ENAME
  FROM EMP
WHERE EMPNO NOT IN  (7369, 7698,...);
!=ALL (7369 OR 7698 OR NULL)
T         AND       T        AND        T       AND   NULL
       T/F     == NULL
 
 
SELECT ENAME
  FROM EMP
WHERE EMPNO != ALL(SELECT NVL(MGR,0)
FROM EMP);
 
 
혹은
SELECT ENAME
  FROM EMP
WHERE EMPNO NOT IN  (SELECT MGR
FROM EMP
WHERE MGR IS NOT NULL);
 
 
※  서브 쿼리에서 NOT IN을 사용 할 때는 반드시 NULL 처리를 해줘야 한다.
/
문제228. 서울시 물가데이터에서 가장 비싼물건의 이름과 가격을 출력하시오
SELECT A_NAME, A_PRICE
 FROM PRICE
WHERE A_PRICE = (SELECT MAX(A_PRICE)
FROM PRICE );
 
 
7.2 MULTIPLE ROW SUBQUERY의 연산자 ( P 7.17쪽)
IN, NOT IN , >ALL, <ALL, >ANY, <ANY
 
문제229. 직업이 SALESMAN 인 사원들에 최대 월급보다
더 많은 월급을 받는 사원들의 이름과 월급을 출력 하시오
select ENAME, SAL
FROM EMP
WHERE SAL > ( SELECT MAX(SAL)
FROM EMP
WHERE JOB = 'SALESMAN');
 
 
 
 
 
 
select ENAME, SAL
FROM EMP
WHERE SAL >ALL ( SELECT MAX(SAL)
FROM EMP
WHERE JOB = 'SALESMAN');
 
 
 
※오라클이 내부적으로 SQL을 다시 조인문으로 변경해서 수행 조인 방법중에서
SORT MERGE 조인을 수행함
 
select ENAME, SAL
FROM EMP
WHERE SAL >ALL ( SELECT MAX(SAL)
FROM EMP
WHERE JOB = 'SALESMAN');
 
 >ALL (1250, 1600, 1500, 1250);
 
>ALL 은 가장 큰 값보다 크다 1600 보다 큰 값
 
문제230  직업이 SALESMANE 인 사원들중에서 가장 작은 월급을 받는
사원보다 더 큰 월급을 받는 사원들의 이름과 월급을 출력하시오
SELECT ENAME, SAL
 FROM EMP
WHERE SAL > ( SELECT MIN(SAL)
        FROM EMP
      WHERE JOB = 'SALESMAN');
 
SELECT ENAME, SAL
 FROM EMP
WHERE SAL >ANY ( SELECT SAL
        FROM EMP
      WHERE JOB = 'SALESMAN');
 
>ANY( 1250, 1600, 1500, 1250)
 
 
설명 : 그 어느값보다 크기만 하면 된다.
 
 
 
7.4 EXISTS 문!
 
문제231. 방금 배웠던 IN을 이용해서 문제를 푸는데
      부서 테이블에서 부서위치를 출력하는데 사원 테이블에 존재하는
부서번호에 해당하는 부서 위치를 출력하시오 !
SELECT  D.LOC
FROM EMP E, DEPT D
WHERE D.DEPTNO IN E.DEPTNO
 
SELECT LOC
FROM DEPT
WHERE DEPTNO IN ( SELECT DEPTNO
FROM EMP );
 
 
문제232. 위의 결과를 다시출력하는데 사원 테이블에
존재하지 않는 부서번호에 해당하는 부서위치를 출력하시오
SELECT LOC
FROM DEPT
WHERE DEPTNO nOT IN ( SELECT DEPTNO
FROM EMP );
 
설명: 만약에 EMP 테이블이 2억건이 있었는데 2억건을 모두 조회하여 검색된 결과가 3건이면
비효율적이다.
SQL을 달리 작성하라 exists
"exists 문을 이용하면 된다. "
 
 
문제233. 아래의 SQL을 exists 문으로 변경하시오
SELECT LOC
FROM DEPT
WHERE DEPTNO nOT IN ( SELECT DEPTNO
FROM EMP );
 
                                 ↓
   
   select loc
 from dept d
where exists ( select 'X'
from emp e
where e.deptno = d.deptno );
설명 : exists 문은 메인 쿼리의 데이터가 서브쿼리에서 존재하면 찾는걸 멈추기 때문에 메인 쿼리의
데이터가ㅓ 많은 경우에 좋은 성능을 보인다.
 
 
문제234. 위의 SQL을 다시 수행하는데 이번에는 부서 테이블에는 존재하는데
사원 테이블에는 존재하지 않는 부서번호에 대한 부서위치를 출력하시오
 
   select loc
 from dept d
where not exists ( select 'X'
from emp e
where e.deptno = d.deptno );
 
 
 
 
 
■8장 집합연산자
  • 8장 목사
  1. union all
  2. union
  3. intersect
  4. minus
 
 
 
delete from emp
where hiredate is null;
 
 
8.1 union all
 
"합집합 연산자로 중복을 허용하면서 집합을 합치는 연산자"
select job, sum(sal)
 from emp
group by job;
 
 
 
문제235. 아래의 결과를 출력하시오!
select '전체토탈:' ,sum(sal)
 
 from emp
 
 
 
문제236.  아래의 결과를 출력하시오 !

select job, sum(sal)
  from emp
group by job
 
union all
 
select '전체토탈: ', sum(sal)
from emp;
 
※ 집합 연산자 사용시 주의사항
  1. 집합 연산자 위 아래의 컬럼의 갯수가 동일해야한다
  2. 집합 연산자 위 아래의 컬럼의 데이터 타입이 동일해야한다.
  3. 컬럼 별칭을 사용해야 데이터 정렬할 때 편하다.
 
 
문제237. 아래의 결괄르 출력하시오

 
 
select to_char(deptno) , sum(sal)
from emp
group by deptno
 
union all
 
select '전체토탈:' , sum(sal)
from emp
;
 
 
문제238. 아래와 같이 결과를 출력하시오 !

 
ORDER BY 절은 맨 밑에만 쓸 수 있고 컬럼명이 같아야(별명을 같게 지정) 한다. !
 
 
select to_char(deptno) deptno , sum(sal)
from emp
group by deptno
 
union all
 
select '전체토탈:' deptno , sum(sal)
from emp
order by deptno asc
 
;
 
 
문제239. 입사 연도별(4자리), 입사 연도별 토탈 월급 을 출력하는데 전체 토탈 월급을 출력하시오
SELECT TO_CHAR(HIREDATE,'RRRR'),SUM(SAL)
  FROM EMP
GROUP BY TO_CHAR(HIREDATE,'RRRR')
UNION ALL
SELECT '토탈월급:', SUM(SAL)
FROM EMP;
 
문제240. 통신사, 통신사별 인원수를 출력하는데 맨 아래쪽에
전체 인원수가 출력되게 하시오
 
SELECT TELECOM, COUNT(*)
 FROM EMP2
GROUP BY TELECOM
 
UNION ALL
SELECT '전체인원 수:', COUNT(*)
FROM EMP2;
 
 
 
 
 
 
문제241. 부서위치, 부서위치별 인원수를 출력하는데
맨 아래쪽에 전체인원수도 출력하시오.
 
select d.loc, count(*)
from emp e, dept d
where e.deptno = d.deptno
group by d.loc
union all
select '전체 인원수:', count(*)
from EMP;
 
 
문제242. 직업, 직업별 토탈월급을 출력하는데
맨 밑에 전체 토탈월급을 출력하시오
 
select job as 직업 , sum(sal)
  from emp
group by job
 
union all
 
select '토탈월급' 직업, sum(sal)
from emp
order by 직업 desc;
 
 
배운사람은 ! 이렇게
 
문제243. 아래의 결괄르 rollup으로 수행하시오

 
select decode(deptno,NULL,'전체토탈:',deptno) deptno,
to_char(sum(sal),'999,999') "SUM(SAL)"
from emp group by rollup(deptno);


 
 
 
 
 
■ 어제 배운 내용
 
  1. 서브쿼리
  • single row subquery
 연산자 : >, <, >=, <=, !=, <> , ^-
  • multiple row subquery
 연산자: in, not in, >all, <all, >any, <any
  • multiple column subquery
 
 
  1. 집합 연산자
  • 합집합 : union ( 중복제거) , union all (중복허용)
  • 교집합 : intersect
  • 차집합 : minus
 
 
SQL을 처음에 배워야 하는 이유?
 
회사가면 여러분들이 데이터 분석으로 가도 마찬가지고
많은 sql을 보게 될 것입니다 삼성 SQL
 
문제244. 텔레콤 , 텔레콤 별 평균 나이
 
 
select telecom, avg(age)
from emp2
group by rollup(telecom);
 
 
select telecom, round(avg(age))
from emp2
group by telecom
 
union all
 
select '전체평균나이:', round(avg(age))
from emp2;
 
문제245. 위 결괄르 rollup으로 수행하라 !
 
 
select nvl(telecom,'전체평균나이') as telecom , round(avg(age))
from emp2
group by rollup(telecom);
 
문제.246. 부서번호별, 직업별 토탈월급을 출력하시오!
 
select deptno, job, sum(sal)
from emp
group by rollup(deptno, job);
 
문제.247 위의 결과를 union all로 수행해보자
 
select deptno, job, sum(sal)
from EMP
group by deptno, job
 
union all
 
select  deptno, null job, sum(sal)
from EMP
group by deptno
 
union all
 
select null deptno, null job, sum(sal)
from EMP
order by deptno, job;
 
 
select deptno, job, sum(sal) a
from emp
where deptno = 10
group by deptno,job
 
 
union all
 
select deptno, '' job, sum(sal) a
from EMP
where deptno = 10
group by deptno
 
union all
 
select deptno, job, sum(sal) a
from emp
where deptno = 20
group by deptno,job
 
union all
 
select deptno, '' job, sum(sal) a
from EMP
where deptno =20
group by deptno
 
union all
 
select deptno, job, sum(sal)
from emp
where deptno = 30
group by deptno,job
 
union all
 
select deptno, '' job, sum(sal) a
from EMP
where deptno =30
group by deptno
 
union all
 
select  null deptno, null job, sum(sal) a
from EMP
 
order by deptno ,job, a;
 
 
 
 
 
SELECT TO_CHAR(deptno) deptno,job,SUM(sal) FROM EMP GROUP BY deptno,job
UNION ALL SELECT TO_CHAR(deptno) deptno,NVL(NULL,'부서별'),SUM(sal) FROM EMP GROUP BY deptno,NULL
UNION SELECT NVL(NULL,'전체 월급') deptno,NVL(NULL,'전체 월급'),SUM(sal) FROM EMP GROUP BY null,null;
 
 
 
 
■ rollup 의 결과 데이터 집합이 나오는 형태
 
select deptno, sum(sal)
from emp
group by rollup(deptno);
↓
rollup 괄호안에 컬럼의 갯수가 n개 이면
결과 집합의 갯수는 n + 1 개
 
  1. deptno 별 토탈월급
  2. 전체 토탈월급
 
 
 
select deptno, sum(sal)
from emp
group by rollup(deptno,job);
              ↓
  1. deptno, job
  2. deptno
  3. 전체
문제248. 아래의 SQL결과 집합이 무엇이 나오는지 마음 편안하게 예상하시오.
 
select mgr, deptno, job, sum(sal)
  from emp
group by rollup(mgr, deptno, job);
 
결과 집합 4개
  1. mgr,deptno, job
  2. mgr, deptno,
  3. mgr
  4. 전체
 
 
문제249. 텔레콤, 전공별, 인원수를 출력하는데 아래의 세 가지
      데이터 결과집합이 출력되게 하시오.
  1. 텔레콤, 전공별
  2. 텔레콤
  3. 전체
 
select telecom, major, count(*)
  from emp2
group by rollup(telecom, major);
 
 
  • 레포팅 함수 3가지
  1. rollup
  2. grouping sets
  3. cube
 
  • rollup보다 사용하기가 편한 grouping sets 함수를 사용하는 방법
 
문제250. 문제 249번의 결과를 grouping sets로 수행하시오
 
select telecom, major , count(*)
  from emp2
group by grouping sets ( ( telecom,major), (telecom), ());
         ↑
전체를 뜻함
문제251. 아래의 결과를 grouping sets로 수행하시오
 
select deptno, job, sum(sal)
from emp
group by grouping sets( (job), (deptno));
 
 
문제252. 전공별, 텔레콤, 주소별 인원수가 아래와 같이 출력되게 하시오 .
 
select major, telecom, substr(address,1,3), count(*)
from emp2
group by grouping sets( (major), (telecom), (substr(address,1,3)) );
 
 
문제253. 아래의 sql결과를 grouping sets로 수행하시오!
select detpno , sum(sal)
  from emp
group by rollup(detpno);
 
 
select deptno , sum(sal)
  from emp
group by grouping sets((deptno),());
 
 
문제254. (점심시간 문제) 아래의 결과를 출력하시오
 

select deptno ,decode(grouping(job)+grouping(deptno),1,'부서토탈:',2,'전체토탈:',job) job,sum(sal)                   
  from emp
group by rollup(deptno,job);
 
 
■ 레포팅함수 4가지
  1. rollup
  2. grouping sets
  3. grouping
  4. cube
 
select deptno, sum(sal)
from emp
group by cube(deptno);
 
 
 
 
문제255. 위의 결과를 union all로 수행하시오
 
 
select null as deptno,sum(sal)
from emp
union all
 
select deptno, sum(sal)
from EMP
group by deptno
order by deptno nulls first;
 
 
문제256. 아래와 같이 결과를 출력하시오

select deptno, ename,sum(sal)
from EMP
group by grouping sets((deptno,ename),());
 
문제257. 아래의 결과를 출력하시오

select deptno, ename,sum(sal) 월급
from EMP
group by grouping sets((deptno,ename),(deptno),());
 
 
■ 집합 연산자
  1. 합집합 : union all, union
  2. 교집합: intersect
  3. 차집합 : minus
 
■ union
union all과 union의 차이는 ?
  1. 중복제거
  2. 데이터 정렬
 
select deptno, sum(sal)
  from emp
group by deptno
 
select null as deptno, sum(sal)
funion all                                         ------------------데이터 정렬이 안됨
rom emp;
 
 
select deptno, sum(sal)
  from emp
group by deptno
 
union                                        ---------------------데이터 정렬됨
select null as deptno, sum(sal)
from emp;
 
■ 2. 교집합 : intersect
  select ename, major
  from emp2
intersect
select ename, major
  from emp3;
 
 
  • 개발 서버에서 운영서버의 데이터를 읽을 수 있게
 database link를 생성
 
운영서버 4가지 정보
 
  1. 운영서버의 ip 주소
  2. 운영서버의 sid 이름(xe)
  3. 운영서버의 port(1521)
 
C:\Users\stu>lsnrctl status
 
LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 07-3월 -2017 14:22:47
 
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
Start Date                07-3월 -2017 08:50:34
Uptime                    0 days 5 hr. 32 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\stu-PC\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stu-PC)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stu-PC)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully
 
 
 
아래의 작업을 개발 서버에서 한다
create public database link dblink7
connect to scott
identified by tiger
using '192.168.19.7:1521/xe';
 
 
select * from emp@dblink7; ->짝꿍의 emp table에 접속함
 
 
리스너!
 
운영서버 도스 창에서
C:\Users\stu>                 lsnrctl start
리스너를 열어준다!!!
 
문제258. 운영서버인 학새잉 자신의emp테이블을 변경한다
 
update emp
  set sal = 0
where ename = 'BLAKE';
 
 
문제259. 개발서버에서 MINUS 를 사용한 쿼리를 수행하는데
운영서버의EMP 테이블과 개발서버의EMP테이들에
어떠한 데이터 차이가 있는지 조회하시오 !
 
SELECT *
FROM EMP
MINUS
SELECT *
FROM EMP@DBLINK7;
 
 
 
 
SELECT *
FROM EMP@DBLINK7
MINUS
SELECT *
FROM EMP;
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

'SQL 기본' 카테고리의 다른 글

10장 DDL문을 사용해서 테이블을 생성 및 관리  (0) 2017.09.07
9장 데이터 조작 언어 (DML문)  (0) 2017.09.07
7장 Subquery 사용  (0) 2017.09.07
6장 조인 문법  (0) 2017.09.07
5장 복수행 함수 변환 함수 및 조건부 표현식 사용  (0) 2017.09.07

티스토리툴바