0615 Oracle SQL 4일차~

having

그룹함수를 조건으로 써야한다면 where이 아닌
having을 통해 구할 수 있다.

select PUBLISHER,avg(price) from book 
group by PUBLISHER having avg(price) >1600;

having은 group by 뒤에 써준다.

select PUBLISHER,avg(price) from book 
where day like '9%' group by PUBLISHER 
having avg(price)>=(select avg(price) from book)
order by publisher desc;

select depart,avg(salary) from members
group by depart 
having avg(salary)>(select avg(salary) from members);

group by 써야할때:
그룹 함수 뿐만 아니라 일반 칼럼도 같이 조회하기 위해선
그룹으로 묶어줘야한다.

join

두테이블을 하나로 합친 결과값을 보여주는것

insert into producer(p_code, p_name, p_price)
values('A01','운동화',1000);

insert into producer(p_code, p_name, p_price)
values('A02','원피스',2000);

insert into producer(p_code, p_name, p_price)
values('A03','바지',3000);

insert into consumer(c_code, c_name, c_price, c_addr)
values('A01','전지현',1100,'서울');

insert into consumer(c_code, c_name, c_price, c_addr)
values('A02','김수현',2100,'부산');

insert into consumer(c_code, c_name, c_price, c_addr)
values('A03','아이유',3100,'제주');

select p_code,p_name,c_name,c_addr
from producer,consumer
where p_code = c_code; 

교집합 join
p코드와 c코드가 같은 값을 가진것을 합쳐서 보여준다.

p_co       p_name c_name  c_addr
A01 운동화 전지현 서울
A02 원피스 김수현 부산
A03 바지 아이유 제주

select producer.code,p_name,c_name,c_addr 
from producer,consumer
where producer.code = consumer.code;

만약 같은 이름의 칼럼이 있다면 명시를 해주어야한다.

select pro.code,p_name,c_name,c_addr 
from producer pro,consumer con
where pro.code = con.code;

별칭을 통해서 호출을 할 수도 있다. as는 붙이면 안되고
명시는 별칭을 통해서 해주어야한다.

select pro.code,p_name,c_name,c_addr 
from producer pro inner join consumer con
on pro.code = con.code;

inner join 이란 교집합 join인데
쉼표가 아닌 inner join을 사용하려면
where가 아닌 on을 써주어야한다.

select pro.code,p_name,c_name,c_addr 

from producer pro left outer join consumer con
or
from producer pro right outer join consumer con

on pro.code = con.code;

left or right or full outer join : 합집합 join이다
왼쪽과 오른쪽중 기준을 정하고 합집합을 혹은 전부
합집합을 구해 어느 한쪽에만 있어도 조회해준다.


create table imsi 
as select pro.code,p_name,c_name,c_price
from producer pro,consumer con
where pro.code=con.code;

어떠한 조건을 담은 테이블을 생성할 수 있다.

natural join
교집합 join의 일종으로 조건식 없이 명시 없이 사용한다.
스스로 양쪽 테이블의 공통되는 칼럼을 조건으로 교집합한다.

select code,p_name,c_name,c_price
from producer natural join consumer;


self join

자신을 객체선언 하듯 분리하여 자체 join을 시킨다.

select a.ename 사원명,b.ename 매니저명
from emp a,emp b
where a.mgr=b.empno;


Inline View(Top N 절)

정의 
from 절에 select 구문이 나온다.

특수 컬럼(pseudo 컬럼)
rownum
테이블에 레코드가 들어간 순서를 기억하고 있는 특수 컬럼
시스템은 이것을 토대로 들어온 순서를 관리한다.
rowid
행을 구분하기 위한 고유 문자열(unique)
DB 전체에서 유일 무이하다.(인덱스에 사용)


drop table rowtest;
col name for a20
create table rowtest(
name varchar2(30),
jumsu number
);

insert into rowtest values('소녀시대', 30 );
insert into rowtest values('원더걸스', 20 );
insert into rowtest values('블랙핑크', 50 );
insert into rowtest values('애프터스쿨', 80 );
insert into rowtest values('비스트', 70 );
insert into rowtest values('레드벨벳', 10 );
insert into rowtest values('티아라', 40 );
insert into rowtest values('EXO', 60 );
commit;

-- rowtest 테이블에 인서트시 [소녀시대] 데이터가 가장 먼저 인서트 되었다.
select rownum, name, jumsu from rowtest;


   ROWNUM NAME                      JUMSU
--------- -------------------- ----------
        1 소녀시대                     30
        2 원더걸스                     20
        3 블랙핑크                         50
        4 애프터스쿨                   80
        5 비스트                       70
        6 레드벨벳                         10
        7 티아라                       40
        8 EXO                         60


-- 원더걸스의 rownum는 얼마인가요?  2
select rowid, rownum, name, jumsu
from rowtest;

delete from rowtest where name = '소녀시대' ;

-- 소녀시대가 사라진 후 원더걸스의 rownum는 얼마인가요?  1
select rowid, rownum, name, jumsu
from rowtest;

ROWID                  ROWNUM NAME                      JUMSU
------------------ ---------- -------------------- ----------
AAADYFAABAAAKYyAAB          1 원더걸스                     20
AAADYFAABAAAKYyAAC          2 블랙핑크                         50
AAADYFAABAAAKYyAAD          3 애프터스쿨                   80
AAADYFAABAAAKYyAAE          4 비스트                       70
AAADYFAABAAAKYyAAF          5 레드벨벳                         10
AAADYFAABAAAKYyAAG          6 티아라                       40
AAADYFAABAAAKYyAAH          7 EXO                         60


-- rownum은 그때 그때 수시로 바뀔수 있지만, 인서트 된 순서는 흐트러지지 않는다.

rollback ;

-- 중요 : order by를 사용해도 rownum은 변함이 없다.
select rownum, name, jumsu from rowtest order by jumsu desc;

    ROWNUM NAME                      JUMSU
---------- -------------------- ----------
         3 애프터스쿨                   80
         4 비스트                       70
         7 EXO                         60
         2 블랙핑크                         50
         6 티아라                       40
         1 원더걸스                     20
         5 레드벨벳                         10



-- rownum을 인위적으로 변경하려면 인라인(Inline) 뷰를 사용해야 한다.
-- Inline View 필수 요소
from 절에 사용되는 서브 쿼리(select 절)
사용 가능한 비교 연산자 : <, <=

결과를 하나의 테이블로 사용하여 rownum 재배치

select rownum as rank,name,jumsu
from (select rownum,name,jumsu
from rowtest
order by jumsu desc);

재배치한 결과를 테이블로 사용하여 상위 3개만 조회하기

select rank,name,jumsu
from (select rownum as rank,name,jumsu
from (select rownum,name,jumsu
from rowtest
order by jumsu desc))
where rank<=3;

등수를 구하는 함수

DENSE_RANK() OVER
      (ORDER BY

RANK() OVER
      (ORDER BY (공동 순위가 생기면 공동 순위 만큼
이후 등수를 비운다.)

select rownum,name,jumsu
from rowtest order by jumsu desc;

select rownum as rank,name,jumsu
from (select rownum,name,jumsu
from rowtest order by jumsu desc);

select rank,name,jumsu
from(select rownum as rank,name,jumsu
from (select rownum,name,jumsu
from rowtest order by jumsu desc))
where rank between 2 and 3;
이런식으로 짜면 공동 3등인데 3등이 한명만 나온다.

select name,jumsu, DENSE_RANK() OVER
      (ORDER BY jumsu DESC) rank
      from rowtest; 
위의 결과값을 기준으로 테이블을 만들어준다.

select name,jumsu,rank
from(select name,jumsu, DENSE_RANK() OVER
      (ORDER BY jumsu DESC) rank
      from rowtest)
where rank between 2 and 3;
원래는 공동 순위는 하나만 나왔는데 이렇게 짜면
공동 순위도 다 조회시켜준다.