[SQL] 복습문제 - Join

Q_ 이름과 부서위치를 출력하시오!

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

 

Q_ 위의 결과를 다시 출력하는데 부서위치가 DALLAS인 사원들만 출력하시오!

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

 

Q_ 직업이 SALESMAN인 사원들의 이름과 월급과 직업과 부서위치를 출력하시오!

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

// 아래와 같이 select절의 deptno에 테이블 alias를 안 적으면 값이 출력되지 않는다.

// e.deptno 헉은 d.deptno 둘중 아무거나 상관없다.

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

 

Q_ 위의 결과를 다시 출력하는데 이름과 월급과 직업과 부서위치, 부서번호를 출력하시오!

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

 

Q_ 월급이 3000 이상인 사원들의 이름과 월급과 부서위치를 출력하시오!

select e.ename, e.sal, d.loc
from emp e, dept d
where e.deptno = e.deptno
and e.sal >= 3000;

 

Q_ 부서위치, 이름, 월급, 순위를 출력하는데 순위가 월급이 높은 순서대로 순위를 출력하시오!

※ rank : 중복 값에 대해 동일한 순위로 출력하고, 중복 값 다음 순위에 대해서는 앞에 중복된 값 개수만큼 제외하고(건너뛰고) 그 다음 순위를 출력한다.

※ dense_rank() (order by 컬럼) : 중복 값에 대해 동일한 순위로 출력하고, 중복 값 다음 순위에 대해서 다음 순위를 순차적으로 출력한다.

select loc, ename, sal
    , dense_rank() over (order by sal desc) rank
from emp e, dept d
where e.deptno = d.deptno;

 

Q_ 위의 결과를 다시 출력하는데 부서위치별로 각각 순위가 출력되게하시오 !

※ partition by : 전체 행에 순위를 출력하는게 아니라, ~~별 순위를 출력되게 하고 싶을 때

select loc, ename, sal
    , dense_rank() over (partition by loc order by sal desc) rank
from emp e, dept d
where e.deptno = d.deptno;

 

Q_ 부서명, 해당 부서에 근무하는 사원들의 이름을 가로로 출력하시오 !

※ listagg ( [합칠 컬럼명] , [구분자] ) within group(order by [정렬 컬럼명] ) 

select d.dname
    , listagg(e.ename, ' ') within group(order by ename)
from emp e, dept d
where e.deptno = d.deptno
group by d.dname;

 

Q_ emp 테이블과 salgrade 테이블을 서로 조인해서 이름, 월급, 등급(grade) 를 출력하시오 !

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

 

Q_ 부서위치, 부서위치별 토탈월급을 출력하시오 !

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

 

Q_ 위의 결과를 다시 출력하는데 부서위치가 DALLAS 는 제외하고 출력하시오 !

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

 

Q_ 부서위치, 부서위치별 토탈월급을 출력하는데 가로로 출력하시오!

select 
     sum(decode(loc,'NEW YORK', sal)) as newyork
    ,sum(decode(loc,'DALLAS',sal)) as dallas
    ,sum(decode(loc,'CHICAGO', sal)) as chicago
    ,sum(decode(loc,'BOSTON', sal)) as boston
from emp e, dept d
where e.deptno = d.deptno;

vs

select loc,
     sum(decode(loc,'NEW YORK', sal)) as newyork
    ,sum(decode(loc,'DALLAS',sal)) as dallas
    ,sum(decode(loc,'CHICAGO', sal)) as chicago
    ,sum(decode(loc,'BOSTON', sal)) as boston
from emp e, dept d
where e.deptno = d.deptno
group by loc;

 

※ PIVOT 연산자 이용하기, from절에 subquery로 가상의 테이블을 만들어 놓은뒤 PIVOT이용

select *
from ( select d.loc, e.sal
       from emp e, dept d
       where e.deptno = d.deptno )
pivot ( sum(sal) for loc in ('NEW YORK','DALLAS','CHICAGO','BOSTON'));

 

Q_ 어느 부서위치에 사원이 배치 되지 않았는지 확인하시오 !

select d.deptno, nvl(count(e.ename),0)
from emp e, dept d
where e.deptno (+) = d.deptno
group by d.deptno
order by 1;

 

Q_ 양쪽에 다 아웃터 조인 사인을 써서 결과를 보게하려면 어떻게 해야하는가 ?

FULL OUTER JOIN : 양쪽 테이블의 모든 값을 출력

LEFT OUTER JOIN : 왼쪽 테이블의 모든 값을 출력

RIGHT OUTER JOIN : 오른쪽 테이블의 모든 값을 출력

select *
from emp e full outer join dept d
on e.deptno = d.deptno;

 

Q_ 부서위치, 부서위치별 토탈월급을 출력하시오 !

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

 

Q_ 부서위치, 부서위치별 인원수를 출력하는데 사원이 없는 부서위치는 인원수가 0 으로 출력되게 하시오 !

select loc, nvl(count(e.ename),0) cnt
from emp e, dept d
where e.deptno (+) = d.deptno
group by loc;

 

Q_ 사원이름, 관리자의 이름을 출력하시오

select e.ename 사원, m.ename 관리자
from emp e, emp m
where e.mgr = m.empno;

 

Q_ 사원 이름, 관리자 이름을 출력하는데 관리자 보다 더 많은 월급을 받는 사원들만출력하시오 !

select e.ename 사원, m.ename 관리자
from emp e, emp m
where e.mgr = m.empno
and e.sal > m.sal;

 

Q_ 사원이름, 관리자 이름을 출력하는데 관리자보다 먼저 입사한 사원들만 출력하시오!

select e.ename 사원, m.ename 관리자
from emp e, emp m
where e.mgr = m.empno
and e.hiredate < m.hiredate;

 

Q_ 사원이름, 부서위치, 월급, 급여등급(grade) 를 출력하시오

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

 

Q_ 이름과 부서위치를 출력하는데 on 절을 사용한 조인으로 구현 하시오 !

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

 

Q_ 직업이 SALESMAN 인 사원들의 이름과 부서위치와 직업을 출력하시오 !(ON 절을 사용한 조인으로 수행)

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

 

Q_ 이름, 월급, 부서위치, 급여등급을 출력하는데 ON 절을 사용한 조인으로 수행하시오 !

select e.ename, 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;

 

Q_ 이름과 부서위치를 출력하는데 using 절을 사용한 조인으로 수행하시오 !

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

 

Q_ 이름과 부서위치를 출력하는데 naturual 조인으로 수행하시오 !

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

 

Q_ 이름과 부서위치를 출력하는데 cross join 하시오

※ 한 쪽 테이블의 모든 행과 다른 테이블의 모든 행을 join함

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

=> 56개의 행 출력

 

Q_ 이름과 부서위치를 출력하는 아래의 SQL 을 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 right outer join dept d
on e.deptno = d.deptno ;