<2018.5.28 - join>

하나이상의 테이블을 특정 컬럼을 사용하여
하나의 테이블로 합치는 것 (옆으로...)


사원(사번,이름,부서번호,월급)
부서(부서번호,부서명)


사번,이름,부서명

select 사번,이름,부서명
from 사원 join 부서 using(부서번호);

select 사번,이름,부서명
from 사원 join 부서
	on(사원.부서번호 = 부서.부서번호);

select 사번,이름,부서명
from 사원, 부서
where 사원.부서번호 = 부서.부서번호;



outer join

left out join
-조인하는 왼쪽에 있는 테이블의 자료는 다 나옴

select 사번, 이름, 부서명
from 사원 right outer join 부서 using(부서번호);

select 사원,이름,부서명
from 사원 full outer join 부서 using (부서번호);

여러테이블 조인---------------------


select 사번,이름,부서명,직급명
from (사원 join 부서 using(부서번호))
temp join 직급 on(temp.직급번호 = 직급.직급번호);

테이블 이름이 길 경우
별명을 주어 편리하게 사용가능
=> 성능 향상 기대

select 사원.사번,사원.이름, 부서.부서명, 직급.직급명
from (사원 join 부서 usgin(부서번호)) join 직급 using(직급번호);

select a.사번,a.이름, b.부서명, 직급.직급명
from (사원 a join 부서 b usgin(부서번호)) join 직급 cusing(직급번호);

create table dept 
as 
select * from "20152518".dept;

create table emp
as
select * from "20152518".emp;


select last_name,dname
from emp e join dept d
	on(e.department_id = d.deptno);

--------------------------

서브 쿼리: sub qeury

-메인쿼리안에 있는 서브 쿼리
-서브쿼리는 모든 절에 올 수 있따
-서브쿼리는 ()로 감싼다
-서브쿼리 먼저 실행하여 메인 쿼리로 결과를 넘긴다.

update 사원
set 월급 = 평균월급
where 사번 =123;

'Chen' 과 같은 부서에 근무하는 사원의 이름

select last_name
from emp
where department_id = (select department_id
			from emp
			where last_name='Chen');

서브 쿼리의 결과가 다중행일 경우
다중행 연산자를 사용한다.
-in
-any,all

select last_name
from emp
where department_id = (select department_id
			from emp
			group by department_id
			having avg(salary)>=8000);

그밖의 사용빈도 높은 함수
nvl (컬럼명,null 일경우 대신 표시할값)

select last_name,
	salary,
	salary *commission_pct as 판매수당,
	salary + (salary * commission_pct) as실지급액
from emp;

case 문

사원이름,월급,보너스
단,보너스 부서별로 다르게 지급하고자 한다.
   30 부서는 200% ,40부서는 300% 
   나머지 부서는 100 %

select last_name,
	salary, 
	case department_id when 30 then salary *2
			   when 40 then salary *3
	else
		salary * 1
	end bonus
from emp;

----------------------
고객(고객번호,고객이름,고객등급,마일리지,생일,가입일,성별)
상품(상품번호,상품명,상품종류,등록일,가격,원산지,할인율)
매출(고객번호,매출일자,매출총금액,결재방법)
매출세부(매출번호,상품번호,수량,할인액,개별상품,매출금액)

1.
2.
3.
4.카드 또는 마일리지로 결제한 매출정보 검색
	select *
	from 매출
	where 결재방법 in(2,3);

5.가격이 1000원 미만이거나 원산지가 표시 안된 상품정보
 	select *
	from 상품
	where 가격< 1000 or 원산지 is null;

6.생일 월별 고객 인원수 단, 인원수가 많은 월부터 출력
select to_char(생일,'mm')as 생일월,
	count(*) as 월별고객수
	from 고객
	group by to_char(생일,'mm')
	order by count(*) desc;

7.월별 매출 건수 단,매출건수 많은 월부터 출력
select to_char(매출일자,'mm')as 매출월,
	count(*) as 월별매출건수
	from 매출
	group by to_char(매출일자,'mm')
	order by count (*) desc;

8.오늘 기준 2주 이내 등록된 신상품의 상품이름,원산지명,가격
상품(상품번호,상품명,상품종류,등록일, 가격, 원산지,할인율)

select b.상품이름,a.원산지명,b.가겨
from 원산지a join( select 상품이름, 원산지, 가격
		   from 상품
		   where 등록일 > sysdate -14 ) b
     on(a.원산지코드 = b.원산지)
order by 등록일 desc;

9.30%이상 할인하는 상품의 정보

select *
from 상품
where 할인율>=0.3;

10.원산지가 '중국' 인 상품정보

select *
from 상품
where 원산지 =(select 원산지코드
	       from 원산지
	       where 원산지명 ='중국');

11.상품별 판매수량의 합계
단, 판매수량이 많은 상품부터 출력...
출력예) 상품이름, 총판매개수

select a.상품번호, b.매출수량
from 상품 a join(
			select 상품번호, sum(수량) as 매출수량
			from 매출세부
			group by 상품번호
			having sum(수량)>1000
			order by sum(수량) desc ) b
		using(상품번호);
12.'이종석' 2017년 3월에 구입한 매출정보

select * 
from 매출
where 고객번호=(select 고객번호
		from 고객
		where 고객이름 ='이종석'
	and to_char(매출일자,'yyyymm') ='201703';