[days10] join 사용하기 + SELF JOIN

<복습>

1. 각 부서별 , 직급별 사원수, 직급별 총급여합과 부서별 총사원수 및 총 사원수 출력

select e.deptno, dname, job, count(*) 사원수, sum(sal) 급여합
from emp e join dept d on e.deptno = d.deptno
group by rollup(e.deptno, dname, job)
order by deptno asc;

with temp as(
select deptno, job, count(*) 사원수, sum(sal) 급여합
from emp 
group by  rollup(deptno, job)
)
select d.dname, t.*
from temp t join dept d on t.deptno = d.deptno
order by t.deptno ;

취업 실기면접 많이 질문 ) 40번 부서도 출력되도록 하고자한다면 ? 
                                     -> inner join이 아니라 outer join을 사용하면 됩니다.

 

문제) 각각의 책들이 판매된 총 권수 조회 
      책id, 책제목, 단가, 총판매수량 qty

책들이 판매된 총 권수 조회
--책id, 책제목, 단가, 총판매수량 qty
select b_id, sum(p_su) qty --책id, 책별 판매수
from panmai
group by b_id;
--eXerd 보면서 붙혀나가기
select b.b_id, title, sum(p_su) qty --책제목
from panmai p join book b on p.b_id = b.b_id 
group by b.b_id, title;
--
select b.b_id, title, price, sum(p_su) qty --단가
from panmai p join book b on p.b_id = b.b_id join danga d on b.b_id = d.b_id
group by b.b_id, title, price;

문제) 가장 많이 판매된 책의 정보 (b_id, title, price, qty)  판매량top3  

--1 top N
select rownum no, t.*
from(--인라인 뷰
select b.b_id, title, price, sum(p_su) qty 
from panmai p join book b on p.b_id = b.b_id join danga d on b.b_id = d.b_id
group by b.b_id, title, price
order by qty desc
)t
where rownum<=3;


--2 rank()over() 
select t.*, rank()over(order by qty desc)
        ,dense_rank()over(order by qty desc)
        ,row_number()over(order by qty desc)
from(--인라인 뷰
select b.b_id, title, price, sum(p_su) qty 
from panmai p join book b on p.b_id = b.b_id join danga d on b.b_id = d.b_id
group by b.b_id, title, price
order by qty desc
)t ;

with temp as (
select  b.b_id , title, price, sum(p_su) qty,
	rank() over(order by sum(p_su) desc) rank
from panmai p join book b on p.b_id = b.b_id join danga d on b.b_id =d.b_id 
group by b.b_id, title, price
 )
select temp.*
from temp
where rank<=3;

문제) 판매된 적 없는 책들 정보

--1) minus 
select b_id 
from book 
minus 
select distinct b_id --판매된 적 있는 책 id만 가져와라 
from panmai; 
--2) join 
select  b.*, p_su 
from book b left join panmai p on b.b_id=p.b_id; 
-- left join에 의해 안팔린 책까지 다 나옴

- exists 연산자 : 서브 쿼리의 결과가 한 건이라도 존재하면 TRUE 없으면 FALSE를 리턴한다. 
- not exists 연산자 : 서브 쿼리에 일치하는 결과가 한 건이라도 있으면 쿼리를 더 이상 수행하지 않는다.

select *
from book b join danga d on b.b_id = d.b_id;

select b.b_id,title,price
from book b join danga d on b.b_id = d.b_id
where not exists( select distinct b_id  from panmai where b_id = b.b_id);
where exists( select distinct b_id  from panmai where b_id = b.b_id);

not exists

exists

SELF JOIN

select a.empno, a.ename, --직속상사의 사원번호, 사원명 알고싶다
        a.mgr, b.empno, b.ename --a.mgr==b.empno
from emp a join emp b on a.mgr=b.empno; -- b.mgr=a.empno해도 됨(순서 중요x)

 

다연아 프로그래머스 문제 풀어라