[빅데이터분석] SQL_TIL 11 (join on절, using절, natural join, left/right outer join, full outer join, union, intersect, minus)

★ 점심 시간 문제 

  select s.grade, listagg(e.ename, ',') within group (order by e.sal desc) 등급
    from salgrade s , emp e
    where e.sal between s.losal and s.hisal
    group by s.grade;

---

[TIL 11]240527

문제1. (복습 ) emp와 dept를 조인해서 이름과 부서위치를 출력하시오

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

 

---

62.  on절 

예제 62. 여러 테이블의 데이터를 조인해서 출력하기5 (on절)
Q1. 위의 조인문법을 on 절을 사용한 조인문법으로 수행하시오.

 select e.ename, d.loc
    from  emp e join dept d
    on (e.deptno=d.deptno);​

 

문제1. emp 와 dept 를 조인해서 이름과 월급과 부서위치, 부서번호를  출력하시오 !  ( on 절을 사용한 조인문법으로 수행해주세요)

 select e.ename, e.sal, d.loc, d.deptno
        from emp e join dept d
         on (e.deptno=d.deptno);

문제2. 직업이 salesman 인 사원들의 이름고 ㅏ월급과 부서위치를 출력하는데 equi join 으로 수행하시오

select e.ename, e.sal, d.loc
          from emp e, dept d
          where e.deptno=d.deptno and e.job ='SALESMAN';

문제3. 위의 결과를  ON절을 사용한 조인문법으로 수행하시오.

 select e.ename, e.sal, d.loc
          from emp e join dept d
          on(e.deptno = d.deptno)
          where e.job ='SALESMAN';

설명: on절에는 조인조건을 기술하고 where 절에는 검색조건을 기술한다.

문제4. 월급이1000-3000사이인 사원들의 이름과 월급, 부서위치를 출력하시오 (on사용)

select e.ename,e.sal, d.loc
          from emp e join dept d
          on (e.deptno=d.deptno)
          where e.sal between 1000 and 3000;​

문제5. 직업이 salesman 이고 월급이 1200이상인 사원들의 이름, 월급, 직업, 부서위치를 출력하시오(on사용)

select e.ename, e.sal, e.job, d.loc
    from emp e join dept d
    on (e.deptno = d.deptno)
    where e.job='SALESMAN' and e.sal >=1200;

문제6. (오라클 조인문법) emp 와 dept , salgrade 를 조인해서 이름, 월급, 부서위치, 급여등급을 출력하시오

select e.ename, e.sal, d.loc, s.grade
        from emp e, dept d, salgrade s
        where e.deptno=d.deptno 
        and e.sal between s.losal and s.hisal;

★ 문제7. 위의 결과를 on 절을 사용한 조인문법 수행하시오. (3개 테이블을 합칠때!!!)

 select e.ename, e.sal, d.loc, s.grade
        from emp e join dept d 
                    on( e.deptno = d.deptno )
                    join salgrade s
                    on( e.sal between s.losal and s.hisal);

문제8. (ON절)emp10 와 telecom_table을 조인해서 이름,나이,통신사 기본요금을 출력하는데 kt인 학생만 출력하시오. 

  select e.ename, e.age, t.t_price
     from emp19 e join telecom_table t
     on (lower(e.telecom) = t.telecom)
     where lower(e.telecom) ='kt';​

문제9.  (오라클 조인) 부서위치, 부서위치별 토탈 월급을 출력하시오

select d.loc, sum(e.sal)
        from emp e, dept d
        where e.deptno =d.deptno
        group by d.loc;

문제10. (on절) 위의 결과를 다시 수행하시오

 select d.loc, sum(e.sal)
        from emp e join dept d
        on(e.deptno =d.deptno)
        group by d.loc;

---

63. USING절 

예제 63.  여러테이블의 데이터를 조인해서 출력하기 5 (using 절)
Q1. 이름과 부서위치를 출력하는데 using절을 사용한 조인 문법으로 수행하시오.

select e.ename, d.loc
            from emp e join dept d
            using(deptno);

설명: using절에서 사용하는 컬럼 앞에 테이블 별칭을 사용하면 에러가 발생한다. 

문제1.   월급이 3000이상인 사원들의 이름,월급,부서위치를 출력하시오(using 절)

select e.ename, e.sal, d.loc
                from emp e join dept d
                using(deptno)
                where e.sal >= 3000;​

문제2. (오라클) emp 와 salgrade를 조인해서 이름 월급 급여등급을 출력하시오

 select e.ename, e.sal, s.grade
                from emp e, salgrade s
                where e.sal between s.losal and s.hisal;

설명: 오라클조인중이 non equi join 임

문제3. 위의 결과를 on절로 수행하시오

     select e.ename, e.sal, s.grade
                from emp e join salgrade s
                            on ( e.sal between s.losal and s.hisal );

문제4. (Sqld 시험용)  위 의 결과를 using을 이용하시오.

  select e.ename, e.sal, s.grade
                from emp e join salgrade s
                            using (?);

설명:  오라클 non equi join은 1999 ansi 의 using 절을 이용한 조인으로는 구현할 수 x

---

64.  NATURAL JOIN 

예제64. 여러테이블의 데이터를 조인해서 출력하기 6 (natural join)
- 점점 조인 문법이 더 간략해 지면서 심플해지는 문법이다.
- 조인 연결 조건을 일일히 신경쓰지 않으면서 오라클보고 알아서 조인해달라고 맡기는 조인문법

Q1. 이름과 부서위치를 출력하는데 natural join 으로 수행하시오.

select e.ename, d.loc
 from emp e natural join dept d;​

설명: 조인조건을 오라클이 알아서 찾아 조인해주는 문법

문제1. 위의 결과를 다시 출력하는데 이름, 부서위치, 부서번호를 출력하시오. 

 select e.ename, d.loc, e.deptno
    from emp e natural join dept d;

select e.ename, d.loc, deptno
    from emp e natural join dept d;

설명: 조인에 사용되는 deptno 컬럼은 앞에 별칭을 쓰면 안됨

---

65. LEFT/RIGHT OUTER JOIN

예제 65. 여러 테이블의 데이터를 조인해서 출력하기 7. (left/ right outer join)
Q1. (복습) 이름과 부서위치를 출력하는데 오라클 아우터 조인을 사용해서 부서위치 쪽에 BOSTON 도 출력하시오 

 select  e.ename, d.loc
    from  emp   e,  dept   d
    where   e.deptno (+)  =  d.deptno;

Q2. 위의 결과를 1999 ANSI 문법으로 수행하시오.

 select  e.ename, d.loc
    from  emp   e right outer join  dept   d
    on (  e.deptno  =  d.deptno );

설명 : BOSTON 과 같은 아웃사이더 데이터가 오른쪽에 있게 출력되어야한다면 RIGHT OUTER JOIN 을 쓰면된다.

문제1. 아래의 오라클 조인문법을 1999 ANSI 조인 문법으로 수행하시오

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

↓

 SELECT E.ENAME, D.LOC
        FROM EMP E LEFT OUTER JOIN  DEPT D
        ON ( E.DEPTNO =D.DEPTNO);

 

---

66. FULL OUTER JOIN

예제66. 여러 테이블의 데이터를 조인해서 출력하기 9. (full outer join)
Q1. 이름과 부서위치를 출력하는데 오라클 조인문법의 아우터 조인문법으로 수행하고 JACK 과 BOSTON 둘다 나오게 하시오.

select  e.ename, d.loc
   from  emp   e,  dept   d
   where   e.deptno (+) = d.deptno (+);

ORA-01468: outer-join된 테이블은 1개만 지정할 수 있습니다
01468. 00000 -  "a predicate may reference only one outer-joined table"
*Cause:    
*Action:
4행, 25열에서 오류 발생
= >오라클 조인문법으로는 위의 결과를 구현 할 수가 없습니다.  위의 결과를 1999 ansi  문법으로 해야합니다.

Q2. 1999 ansi 문법으로 위의 결과를 출력하시오 

   select e.ename, d.loc
    from emp e full outer join dept d
        on (e.deptno = d.deptno);

문제1.  다음과 같이 조인을 수행하시오.

select e.ename, d.loc
	from emp e, dept d;

- where 절 없이 조인했더니 전부 다 조인되고 있음

문제2. 위의 결과를 1999ansi 문법으로 수행하시오. (cross join) _where 절 없이 수행

select e.ename, d.loc
	from emp e cross join dept d;

문제3.(복습) 이름의 첫글자가 A로 시작하는 사원들의 이름과 부서위치와 직업, 월급을 출력하는데 월급이 높은 순으로 출력하시오. 

    select e.ename, d.loc, e.job, e.sal
        from emp e , dept d
        where e.deptno = d.deptno and e.ename like 'A%'
        order by e.sal desc;

문제4. (복습) comm 이 null인사원들의 이름, 월급, comm, 부서명을 출력하시오.

 select e.ename, e.sal,e.hiredate, e.comm, d.dname
            from emp e, dept d
            where e.deptno = d.deptno and comm is null;

※ 현업에서의 팁(tip) ! 
그냥 대부분 오라클 조인문법의 equi join으로 조인을 하고 오라클 조인문법으로는 구현할 수 없는 딱 하나의 기능인 full outer 조인만 1999 ansi 문법으로 구현하면 됩니다. 

 

---

67. UNION ALL

예제 67. 집합 연산자로 데이터를 위아래로 연결하기 1 (union all)
조인(JOIN) ---> 두 테이블의 결과를 양 옆으로 연결해주는 문법
집합연산자 ---> 두 테이블의 결과를 위 아래로 연결해서 보여주는 문법

※ 집합연산자의 종류 4가지
1) UNION ALL        : 합집합
2) UNION               : 합집합 + 중복제거
3) INTERSECT      : 교집합
4) MINUS               : 차집합

문제1. (복습)부서번호, 부서번호별 토탈월급을 세로로 출력하시오.

select deptno, sum(sal)
    from emp
    group by deptno;

문제2. (복습)직업, 직업별 토탈월급을 세로로 출력하시오

select job, sum(sal)
    from emp
    group by job;

문제3.  (복습) 입사한 연도(4자리) , 연도별 토탈 월급을 출력하시오(세로)

 select to_char(hiredate, 'RRRR') , sum(sal)
        from emp
        group by to_char(hiredate, 'RRRR');

문제4. 직업, 직업별 토탈월급, 입사한 년도, 입사한 년도별 토탈월급을 위아래로 같이 출력하시오.

 select  job, sum(sal)  
  from  emp
  group  by  job
  union  all
  select  to_char(hiredate,'RRRR'), sum(sal)
   from emp
   group  by  to_char(hiredate,'RRRR');

 

위와 같이 위의 sql의 결과와 아래의 sql의 결과를 같이 봐야하는 경우가 있습니다.
이럴때 union all 또는 union 을 써서 합집합 하면 됩니다 .

※  집합 연산자를 사용할 때 주의사항 !
 1. 위의 SQL 과 아래의 SQL의 컬럼의 갯수가 동일해야합니다.
 2. 위의 SQL 과 아래의 SQL의 컬럼의 데이터 유형이 동일해야 합니다. 
 3. 컬럼명은 맨 위의 SQL의 컬럼명으로 출력 됩니다.
 4. ORDER BY 절은 맨 아래의 SQL에만 사용할 수 있습니다. 

 select  job  as  job_hiredate, sum(sal)  as 토탈값
  from  emp
  group  by  job
  union  all
  select  to_char(hiredate,'RRRR'), sum(sal)
   from emp
   group  by  to_char(hiredate,'RRRR')
   order  by  job_hiredate asc;

문제5. 위에 있는 직업, 직업별 토탈월급이 출력되게하고, 아래에는 부서번호, 부서번호별 토탈월급이 출력되게하시오.
출력되는 컬럼명은 DEPTNO_JOB 과 토탈월급으로 출력되게하시오.

  select  job as DEPTNO_JOB, sum(sal)   as 토탈월급
  from  emp
  group  by  job
  union  all
  select to_char(deptno), sum(sal)
   from emp
   group  by  to_char(deptno);

 

문제 6. 위의 결과를 union all을 쓰지 않고 grouping sets 를 이용해서 출력하시오.

 select  job as DEPTNO_JOB, sum(sal)   as 토탈월급
         from  emp
            group by grouping sets( (job),(deptno));​

 

=> 완전 같은 값은 아님

문제7. union all 을 이용하여 sql을 작성하는데 우리반 테이블에서 성별별 평균나이 위쪽 sql로 출력하고 아래쪽 sql은 통신사별 평균나이로 출력되게하시오.

 select gender, sum(age)
    from emp19
    where gender is not null
    group by gender
    union all
select lower(telecom) , round(avg(age))
    from emp19
       where telecom is not null
     group by lower(telecom);

---

67. UNION

예제 68. 집합 연산자로 데이터를 위아래로 연결하기 2 (union)
- union       : 중복행을 제거하지 않는 합집합
- union all : 중복행을 제거한 합집합
Q1. 부서번호가 10,20번인 사원들의 이름과, 월급, 부서번호는 위쪽 SQL로 출력, 부서번호 20,30번인 사원들의 이름,월급,부서번호는 아래쪽 SQL로 출력하시오.

1)union all      
     select ename,sal,deptno
        from emp
        where deptno in (10,20)
    union all
      select ename,sal,deptno
        from emp
        where deptno in (20,30);
        
2) union
      select ename,sal,deptno
        from emp
        where deptno in (10,20)
    union
      select ename,sal,deptno
        from emp
        where deptno in (20,30);

왼) union all / 오) union

설명: 20번 부서번호가 중복행이 제거가 되어서 출력되었다. (중복제거가 필수가 아니라면 union all이 더 바람직함)

문제 1.(복습) 부서번호, 부서번호별 토탈월급을 출력하는데 맨아래쪽에 전체 토탈월급을 출력하시오.

 select deptno, sum(sal)
    from emp    
    group by rollup(deptno);

문제 2. 위의 결과를 rollup 이아닌 union 이나 union all를 써서 출력하시오.

 select deptno, sum(sal)
    from emp    
    group by deptno
    union all
 select null, sum(sal)
  from emp 
  ;

문제 3. 아래의 sql 결과를 union이나 union all로 수행하시오( cube 사용x)

select null, sum(sal)
    from emp
    union all
    select job, sum(sal)
    from emp
    group by job;

 

문제 4.  위 결과에서 직업이 ABC순으로 출력되게하시오.

    select null, sum(sal)
      from emp
    union all
    select job, sum(sal)
    from emp
    group by job
    order by 1 asc nulls first;

문제 5. 아래의 sql결과를 union all 또는 union 으로 수행하시오.

   select deptno, job, sum(sal)
        from emp
        group by grouping sets((deptno),(job));

  select to_number(null), job, sum(sal)
            from emp
            group by   job
       union all
         select deptno , to_char(null), sum(sal)
            from emp
            group by deptno;

 

문제 6. 아래의 sql을   union all 로 수행하시오

 select deptno, job, sum(sal)
                from emp
                group by grouping sets ((deptno), (job),())
                order by deptno asc, job asc nulls last;

   select deptno, to_char(null) as job, sum(sal)
                    from emp
                    group by deptno
                union all
                    select to_number(null)as deptno,job,sum(sal)
                    from emp
                    group by job
                union all
                 select to_number(null)as deptno, to_char(null)as job,sum(sal)
                    from emp
                    
                order by deptno asc, job asc nulls last;

 

문제7. (한진 해운sql) 아래 sql을 튜닝하시오.

select  gender,  to_char(null)  as  telecom,  round(avg(age))
                from  emp19
                where gender is not null
                group by gender
             union  all 
            select  to_char(null) as  gender,  lower(telecom) as telecom, round(avg(age))
                from  emp19
                where telecom is not null
                group  by  lower(telecom)
              union  all
            select  to_char(null)  as  gender, to_char(null) as  telecom, round(avg(age))
                from  emp19
               order  by  gender asc, telecom  asc  nulls last;

 select gender, lower(telecom)as teleom, round(avg(age)) as age
          from emp19
          where gender is not null and telecom is not null
          group by grouping sets ((gender),(lower(telecom)),())
          order by 1 asc, 2 asc nulls last;

---

69. INTERSECT (교집합)

예제69. 집합 연산자로 데이터의 교집합을 출력하기(INTERSECT)
: 두개의 집합간의 교집합을 구하는 집합연산자
Q1. 아래의 교집합을 구하는 sql을 수행하시오.

 select deptno, sum(sal)
        from emp
        where deptno in(10,20)
        group by deptno
    intersect
        select deptno, sum(sal)
        from emp
        where deptno in(20,30)
        group by deptno;

 

문제1. 2017년도 서울시에 카페베네라는 상호명이 몇개있는지 출력하시오.

   select count(*)
            from market_2017
            where 상호명 like '%카페베네%' and 시도명 = '서울특별시';

문제2. market_2017년도에서 있었던 카페베네 매장인데 2022년도에도 폐업하지않은 매장의 상가업소번호와 상호명을 출력하시오.

 select 상가업소번호, 상호명
            from market_2017
            where 상호명 like '%카페베네%' and 시도명 = '서울특별시'
      intersect  
        select 상가업소번호, 상호명
            from market_2022
            where 상호명 like '%카페베네%' and 시도명 = '서울특별시'

---

70. MINUS

예제70. 집합 연산자로 데이터의 차이를 출력하기(MINUS)

          운영서버 --------------------------- 테스트 서버
                ↓                                                 ↓
         실제 데이터                           운영서버의 일부 데이터

1) 운영서버 
 - 리스너의 상태가 정상인지 확인 (  cmd 에 'lsnrctl status' 작성 )
 - 본인 아이피 주소를 테스트 서버에 알리기 (ipconfig -all)    혜인's ip (192.168.19.20)
 - 방화벽 해제
 - sqlnet.ora열어서 아래의 내용 맨 앞에 # 을 넣기

 C:\oracleinstall\homes\OraDB21Home1\network\admin

2) 테스트 서버
- cmd에 운영서버에 접속 sqlplus c##scott/tiger@192.168.19.20:1521/xe

- sql developer 로 가기
- db링크 생성 ( sql developer 에 작성)

create database link hyein
connect to c##scott
identified by tiger
using '192.168.19.20:1521/xe';

select * from emp@hyein;

 

문제1. hyein의 emp 테이블과 나의 emp 테이블 차이가 있는지 확인하시오.

select * from emp
minus
select * from emp@hyein;


select * from emp@hyein
minus
select * from emp;

문제2. minus 연산자를 사용한 sql 을 수행할때 차이가 생기게끔 텍스트 서버나 운영서버에 데이터를 입력하고나 수정하시오.

insert into emp(empno, ename, sal)
    values( 1234, 'aaaa', 3000);
    
    
 select * from emp
minus
select * from emp@hyein;

문제3. 짝꿍의 dept 테이블과 나의 dept테이블의 데이터 교집합을 구하시오.

select * from dept@hyein
intersect
select * from dept;​

문제4.(복습) 아래의 sql을 grouping sets 로 수행하시오.
튜닝전

   select deptno, sum(sal)
      from emp
      group by deptno
union all
    select to_number(null) as deptno, sum(sal)
        from emp
        order by deptno asc nulls last;

튜닝후

 select deptno, sum(sal)
                from emp
                group by grouping sets((deptno),())
                order by deptno asc nulls last;

 

문제5. (복습) 아래의 sql을 튜닝하시오
튜닝전

 select deptno,null as job, sum(sal)
    from emp
    group by deptno
    
    union all
    
select null as deptno, job, sum(sal)
    from emp
    group by job
    
    union all

select null as deptno, null as job, sum(sal)
    from emp
    order by deptno asc, job asc nulls last;

튜닝후

  select deptno, job, sum(sal)
    from emp
    group by grouping sets ((deptno),(job),())
    order by deptno asc, job asc nulls last;

★ 문제6.(마지막문제)  아래의 튜닝 SQL을 튜닝전  SQL로 변경하시오. (union all로)
튜닝후 

select deptno, job, sum(sal)
    from emp
    group by grouping sets ((deptno, job),(deptno),(job),())
    order by deptno asc, job asc nulls last;

튜닝전

select deptno, job, sum(sal)
 from emp
 group by (deptno, job)
union all
select deptno, to_char(null) as job, sum(sal)
 from emp
 group by deptno
union all
select to_number(null) as deptno, job, sum(sal)
 from emp
 group by job
union all
select to_number(null) as deptno, to_char(null) as job, sum(sal)
 from emp
order by deptno asc, job asc nulls last;