2019/07/25 Table Join 테이블 조인

[전 수업] 테이블 조인을 하기위해 emp테이블과dept테이블을 만들어 놓았다

[본 수업] 테이블을 조인해 보자

---

Table join 테이블 조인

1. T1 과 T2 의 조인으로 T1의 x와 T2의 x가 같은경우

select T1.*,T2*
   from T1 join T2
   on T1.x=T2.x;

2. T1과 T2을 조인을 하고 T3와 또다시 join을 해주는데 T1 과  T3는 y가 같은경우

select T1.*,T2.*,T3.*
from T1 join T2
on T1.x=T2.x join T3
on T1.y=T3.y;

3. T1,T2,T3,T4를 조인할 때

select T1.*,T2.*,T3.*,T4.*
from T1 join T2
on T1.x=T2.x join T3
on T1.y=T3.y join T4
on T1.z=T4.z;

4. case when으로 comm(커미션)값 구하기

select emp.*,dept.dname,dept.loc,   case when job='사원' then sal*0.03        when job='주임' then sal*0.02       when job='과장' then sal*0.01       else 0       end "커미션"   from emp join dept   on emp.deptno = dept.deptno;

emp 테이블과 dept테이블을 deptno로 join을 해주고 case when으로 직책이 '사원','주임''과장'에 따라 커미션값이 다르게 나오게 한다

 

5. 직원코드,이름,부서코드,부서이름,급여를 
사원은 급여*0.03, 주임은 급여*0.02 ,과장은  급여 *0.01로 구하시오

select emp.empno,emp.ename,emp.deptno, case when job='사원' then sal*0.03      when job='주임' then sal*0.02     when job='과장' then sal*0.01     else 0     end "커미션"     ,dept.deptno,dept.dname from emp join dept on emp.deptno = dept.deptno;

emp테이블과 dept테이블을 조인하여 두테이블을 데이터를 조회

 

 

※2개 테이블에 중복되지 않은 칼럼명은 테이블명 생략가능

 

7.  테이블명을 alias 를 사용하여 '별명'을 줄수 있다

 

select EM.*,DE.* from emp EM, dept DE where EM.deptno=DE.deptno;

일반적이지 않은 join방법

 

※일반적인 방법은 from A join B on A.~~=B.~~ 으로 join한다

 

9.  새로운 논리적 테이블이 생성되어 이름을 붙여주어 사용할 수 있다

select AA.* from(     select empno,ename,em.deptno,dname,loc     from emp EM join dept DE     on EM.deptno=DE.deptno     )AA;

논리적 테이블을 생성한 후 생성한 테이블에 AA라는  별명을 지어 주어 사용하였다

 

※AA 알리아스 로 잡은 후, select 할때 AA.명칭을 생략해 줄 수 있다

 

11. 문제)각 부서의 인원수를 아래와 같이 조회 
     경리팀 2명
     연구팀 5명
     총무팀 4명
     --(group by)(count)(order by)

select          case when dname='경리팀' then '경리팀 '||count(*)||'명'                when dname='연구팀' then '연구팀 '||count(*)||'명'                when dname='총무팀' then '총무팀 '||count(*)||'명'               else ''             END "인원수"     from(     select empno,ename,em.deptno,dname,loc     from emp EM join dept DE                      on EM.deptno=DE.deptno     )AA     group by dname     order by count(*);

emp테이블과 dept테이블을 조인해서 논리적 테이블을 만든후 부서이름으로 그룹핑 시켜 case when으로 부서명에 맞게 조회를 해 보았다

 select dname,count(*)||'명' as 인원수
    from(
    select empno,ename,em.deptno,dname,loc
    from emp EM join dept DE                 
    on EM.deptno=DE.deptno
    )AA
    group by dname
    order by count(*);

 

 

12. 문제) 각 부서별 급여,커미션의 합계를 구한 후 
급여합계순으로 아래와 같이 조회 하시오

select dname,sum(sal) 합,sum(nvl(comm,0)) 커미션     from(     select empno,ename,EM.deptno,dname,sal,comm     from emp EM join dept DE                      on EM.deptno=DE.deptno     )AA     group by dname     order by 합 desc;

group by로 그룹핑을 시켜도 집계함수는 사용할 수 있음

 

13. 문제) 각 지역별 커미션합계를 구한후,
    그 커미션합계가 20이하(반올림해서 소수점없이) 지역만 지역순으로 아래와 같이 조회 하시오
    대구 15
    부산 13

--1 커미션 값을 그냥 합한 값        select AA.loc,sum(AA.comm)      from(          select dept.loc,emp.comm          from emp join dept          on emp.deptno=dept.deptno      )AA      group by AA.loc      order by AA.loc;

--2 커미션 값을 nvl함수로 null값이 나오면 1      select AA.loc,sum(nvl(AA.comm,0))      from(          select dept.loc,emp.comm          from emp join dept          on emp.deptno=dept.deptno      )AA      group by AA.loc      order by AA.loc;

--3        select AA.loc,round(sum(nvl(AA.comm,0)),0)      from(          select dept.loc,emp.comm          from emp join dept          on emp.deptno=dept.deptno      )AA      group by AA.loc      order by AA.loc;

--4      select AA.loc,round(sum(nvl(AA.comm,0)),0) 합      from(          select dept.loc,emp.comm          from emp join dept          on emp.deptno=dept.deptno      )AA      group by AA.loc      having round(sum(nvl(AA.comm,0)),0)<=20      order by AA.loc;