[Oracle] days14- 조인문제

book panmai danga gogaek 을 조인하여 다음을 출력한다 
책이름 고객명 년도 수량 단기 금액 
단 년도 내림차순 출력

select title, g_name,p_date, p_su, price
from book b join panmai p on b.b_id=p.b_id
            join danga d on b.b_id=d.b_id
            join gogaek g on p.g_id=g.g_id
order by p_date desc;

 

book테이블의 모든 행을 보이게 해라 (outer join)

select b.b_id, title, g.g_id, g.g_name, p_su
from book b left join panmai p on b.b_id=p.b_id --판매안된것도 나오게 =left 
            join danga d on b.b_id=d.b_id
            join gogaek g on p.g_id=g.g_id
order by p_date desc;

 

--left 조인을 햇는데도 나오지 않는다 
--left 를 처음만 적으면 안되었다.

select b.b_id, title, g.g_id, g.g_name, p_su
from book b left join panmai p on b.b_id=p.b_id --판매안된것도 나오게 =left 
            left join gogaek g on p.g_id=g.g_id
order by p_date desc;

--left 조인을 햇는데도 나오지 않는다 
--left 를 처음만 적으면 안되었다.

 

 

 

 

 

 

 

년도 ,월별 판매현황 

3. 년도 ,월별 판매현황 
--1.먼저 체크하기
select p.b_id, p_su,price, p_date 
from panmai p join danga d on p.b_id =d.b_id;

--2.연도별 판매금액 
select to_char(p_date,'yyyy'),sum(p_su*price) --년도별 판매금액 
from panmai p join danga d on p.b_id =d.b_id
group by to_char(p_date,'yyyy');

--3. 월별로 
select to_char(p_date,'yyyy'), to_char(p_date, 'mm'),sum(p_su*price) --년도별 판매금액 
from panmai p join danga d on p.b_id =d.b_id
group by to_char(p_date,'yyyy'), to_char(p_date, 'mm')
order by to_char(p_date,'yyyy'), to_char(p_date, 'mm');

--부분합까지 원한다면 rollup이나 cube 
select to_char(p_date,'yyyy'), to_char(p_date, 'mm'),sum(p_su*price) --년도별 판매금액 
from panmai p join danga d on p.b_id =d.b_id
group by rollup(to_char(p_date,'yyyy'), to_char(p_date, 'mm'));

 

 

 

서점별 년도별 판매현황 구하기  -매출 

오류

select to_char(p_date,'yyyy'), g.g_id, g.g_name, sum(p_su*price)
from  panmai p join danga d on p.b_id=d.b_id
            join gogaek g on p.g_id=g.g_id
group by to_char(p_date,'yyyy'),g.g_id,g.g_name;

 

 

올해 가장 판매가 많은책 (수량을 기준)

rank 오류

--1.
select b.b_id, title, p_su
from book b join panmai p on b.b_id=p.b_id
order by b.b_id;
--2.
select b.b_id, title, sum(p_su) "총 권수 "
from book b join panmai p on b.b_id=p.b_id
group by b.b_id, title
order by b.b_id;

--top n 방식
select t.*
from (
select b.b_id, title, sum(p_su) "총 권수 "
from book b join panmai p on b.b_id=p.b_id
where to_char(sysdate,'yyyy')=to_char(sysdate, 'yyyy')
group by b.b_id, title
order by b.b_id
)t
where rownum=1;

--rank 방식 
select b.b_id, title, sum(p_su) "총 권수 ", r
from (
select b.b_id, title, sum(p_su) "총 권수 "
, rank() over(order by sum(p_su) "r"
from book b join panmai p on b.b_id=p.b_id
where to_char(sysdate,'yyyy')=to_char(sysdate, 'yyyy')
group by b.b_id, title
order by b.b_id
)t
where r=1;

서점별 총판매액/ 전체판매액 매출 비율

select g.g_id, g_name, sum(p_su*price) 총판매액 
,(select sum(p_su*price) from panmai p join danga d on p.b_id=d.b_id) 전체판매액
,round(sum(p_su*price)/(select sum(p_su*price) from panmai p join danga d on p.b_id=d.b_id)*100,2)
from gogaek g join panmai p on g.g_id=p.g_id
              join danga d on p.b_id=d.b_id
group by g.g_id, g_name;