[Oracle ]days12 - join

ojdbc6.jar 경로

C:\app\oracle\product\11.2.0\server\jdbc\lib

 

 

리버스엔지니어링 

http://exerd.com:8081/help/index.jsp?lang=ko&topic=%2Fcom.tomato.exerd.help%2Fhtml%2Fproducts%2Fwhatsexerd.html 

 

 

 

 

문제

 

 

equi join 
두개 이상의 테이블에 관계되는 칼럼들의 값이 일치하는 경우  - 공통적인 것만 

 

 

dept, emp을 조인해서  empno, deptno, dname, ename, hiredate 칼럼 조회 

 1)
select e.deptno, dname, empno, ename, hiredate
from emp e join dept d on d.deptno = e.deptno; --equi조인, 네추럴 조인 

2) using 절은 별창,객체 사용 금지 
select deptno, dname, empno, ename, hiredate
from emp e join dept d using(deptno); 

3) 별칭사용 
select e.deptno, dname, empno, ename, hiredate
from emp e, dept d
where d.deptno = e.deptno;

equi join

두테이블에 다 있는 경우에만 나온다 

 

 

 

책ID, 책제목, 판매수량, 단가, 서점명, (단가*판매수량)판매금액 조회

 

book:b_id, title

panmai: p_su

danga: price

gogaek: g_name

select b.b_id, title, p_su, price, g_name, p_su*price 판매금액
from book b ,panmai p, gogaek g, danga d
where b.b_id=p.b_id and p.g_id=g.g_id and b.b_id= d.b_id;

join on 구문 
select  b.b_id, title, p_su, price, g_name, p_su*price 판매금액 
from book b join panmai p on b.b_id = p.b_id
    join gogaek g on g.g_id = p.g_id 
    join  danga d on  b.b_id=d.b_id;
    
using 사용 
select  b_id, title, p_su, price, g_name, p_su*price 판매금액 
from book b join panmai p using(b_id)
    join gogaek g using(g_id)
    join  danga d using(b_id);

 

출판된 책들이 각각 총 몇권 판매되엇는지 조회 ( 총 판매권수) 

 

---1
select b.b_id, title, sum(p_su) "각각판매권수" ,price
from panmai p join book b on b.b_id=p.b_id
              join danga d on b.b_id=d.b_id
group by b.b_id, title, price 
order by b.b_id asc;

---2
select b.b_id, title, sum(p_su) qty
        ,(select sum(p_su) from panmai) total_qty
from panmai p join book b on b.b_id = p.b_id
              join danga d on b.b_id =d.b_id 
group by b.b_id, title, price;

1

2

 

각각의 책이 전체판매량의 몇 퍼센트에 해당되는지 출력 

select b.b_id, title ,price 
    --id별로 group by 하였기에 각각의 판매권수 
    , sum(p_su) "각각판매권수" 
    ,(select sum(p_su) from panmai) "전체판매량수"
    ,round(sum(p_su)/ (select sum(p_su) from panmai),2)*100 ||'%' "퍼센트"
from panmai p join book b on b.b_id=p.b_id
              join danga d on b.b_id=d.b_id
group by b.b_id, title, price 
order by b.b_id asc;

판매가 된 적이 있는 책 정보 

with
a as (
    ---판매가 된 적 잇음 
    select distinct b_id
    from panmai
),
--모든 책정보 
b as(select b.b_id, title, price
from book b join danga d on b.b_id =d.b_id)
select b.b_id, title, price 
from a join b  on a.b_id=b.b_id;

--2

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


---3
select b.b_id, title, price
from book b join (select distinct b_id from panmai)p  on b.b_id=p.b_id
            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 b.b_id not in (select distinct b_id from panmai);

가장 많이 판매가 된 책의 정보를 조회 
책id, 제목 가격 총 판매량 

select t.*,rownum
from(
select b.b_id, title, price ,sum(p_su) qty
from book b join danga d  on b.b_id=d.b_id
            join panmai p on b.b_id=p.b_id
group by b.b_id, title, price 
order by qty desc)t
where rownum=1;

2: rank() 함수
28:00
select b_id, title, price, qty
from (select b.b_id, title, price ,sum(p_su) qty
,rank() over(order by sum(p_su) desc)qty_rank
from book b join danga d  on b.b_id=d.b_id
            join panmai p on b.b_id=p.b_id
group by b.b_id, title, price )t
where qty_rank=1;

 

가장 많이 판매가 된 책 top3의 정보를 조회 

-> 위 코드에서 where 조건절만 바꾸면 된다.

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

2: rank() 함수
28:00
select b_id, title, price, qty
from (select b.b_id, title, price ,sum(p_su) qty
,rank() over(order by sum(p_su) desc)qty_rank
from book b join danga d  on b.b_id=d.b_id
            join panmai p on b.b_id=p.b_id
group by b.b_id, title, price )t
where qty_rank<=3;

 

가장 적게 판매가 된 책 top3의 정보를 조회 

->판매가 되지 않은것도 포함된다. 

 

총 판매권수가 10권 이상 판매된 책의 정보 조회 

select b.b_id, title, price ,sum(p_su) qty
from book b join danga d  on b.b_id=d.b_id
            join panmai p on b.b_id=p.b_id
group by b.b_id, title, price
having sum(p_su)>=10 --처리순서 때문에 여기서는 qty 못씀 
order by qty desc;

 

non-equi join

관계되는 컬럼이 정확히 일치하지 않는 경우에 사용되는 JOIN의 형태이다.
WHERE 절에 BETWEEN ... AND ... 연산자를 사용한다.
오라클에서는 ON 절을 이용하여 NON-EQUI JOIN과 동일한 역할을 수행한다.

select *
from emp;

select *
from salgrade;

문제) emp/salgrade join 사원의 급여등급
select empno, sal, losal ||'~'|| hisal, grade 
from emp e join salgrade s on e.sal between s.losal and s.hisal;

 

inner join

join 이라고쓰면 그것이 equi 이고 inner 이다 .
simple join이라고도 하며, 둘 이상의 테이블에서 join condition을 만족하는 행만 반환한다.

 

outer join

JOIN 조건을 만족하지 않는 행을 보기 위한 추가적인 join의 형태이다.
일반적인 join으로 얻을 수 없는 데이터를 구하고 싶을 때 사용하며, '(+)' 연산자를 사용한다.
오라클에서는 LEFT(RIGHT) OUTER JOIN이 OUTER JOIN이다.
FULL OUTER JOIN은 이전 버전에서 UNION을 이용한 연산과 동일한 결과를 얻는다.

 

ㄱ. left [outer]  join

select empno, ename, nvl(dname,'부서없음')
from emp e join dept d on e.deptno=d.deptno(+);
from emp e left join dept d on e.deptno=d.deptno;

 

ㄴ.right [outer]  join 

select empno, ename, nvl(dname,'부서없음')
from emp e right join dept d on e.deptno=e.deptno;
from emp e join dept d on e.deptno(+)=d.deptno;

ㄷ.full [outer] join

select empno, ename, nvl(dname,'부서없음')
from emp e full join dept d on e.deptno=d.deptno;

 

각 부서별 사원수 조회 outer join 

 

 

 

cross join

 극히 드물게 사용됨 
Cartesian Product를 실행한 겻과 같은 결과를 출력한다.
cross join은 cartesian product를 위한 ANSI/ISO SQL-99의 표준 문법이다.
테이블 aa와 bb의 행 수가 각각 3, 2 이므로 출력 결과는 3x2=6가 출력된다