ms-SQL 여섯 번째 수업

select * from 부서 --3
select * from 사원 --10

select * from 부서, 사원
where 이름 = '김소비'
and 부서.부서 = 사원.부서번호 --카르테시안 프로덕트

*이퀴조인 : 조인조건에 =이 들어가는 조인
   테이블을 n개 조인하면 조인조건은 n-1개
   
*비이퀴조인
*셀프조인
*외부조인

-조인의 기법
    -t-sql :
    select 부서명, 이름
    from 부서, 사원
    where 부서.부서 = 사원.부서번호
   
-ansi-sql :
    select 부서명, 이름
        from 부서 inner join 사원
        on 부서.부서 = 사원.부서번호

- 유미 간장을 주문한 주문금액합
sp_help
select * from 제품
select * from 주문세부
select * from 주문
select * from 고객


select sum(주문세부.단가*주문세부.주문수량) '유미간장을 주문한 금액'
from 주문세부, 제품
where 주문세부.제품번호=제품.제품번호 and 제품명 like '%유미 간장%'

- 유미 간장을 주문했던 주문일들

select  제품.제품명, 주문.주문일
from 주문, 주문세부, 제품
where 주문.주문번호=주문세부.주문번호
and 주문세부.제품번호=제품.제품번호
and 제품명 like '%유미 간장%'

- 유미 간장을 주문한 고객의 정보(담당자명, 전화번호, 고객회사명)

select  고객.담당자명, 고객.전화번호, 고객.고객회사명
from 주문, 주문세부, 제품, 고객
where 고객.고객번호=주문.고객번호
and 주문.주문번호=주문세부.주문번호
and 주문세부.제품번호=제품.제품번호
and 제품명 like '%유미 간장%'


1. 주문월별 주문금액 합을 보이고, 합이 높은 것부터 상위 3개만 보이기
select Top 3 month(주문일), sum(주문세부.단가*주문세부.주문수량)
from 주문, 주문세부 where 주문.주문번호 = 주문세부.주문번호 group by month(주문일) order by 2 desc

2. 도시별로 주문금액 합을 보이고 총 주문금액 합도 함께 보여라
select 고객.도시, sum(주문세부.단가*주문세부.주문수량)
from 고객, 주문, 주문세부
where 고객.고객번호=주문.고객번호
 and 주문.주문번호 = 주문세부.주문번호
 and year(주문일)=2008
group by 고객.도시
--with rollup
with cube
order by 2 desc


3. 광역시에 거주하는 고객들에 대해서
 제품명별로 주문수량 합과 주문금액 합을 보이되 주문금액이 높은 것 부터 보이기
select 제품.제품명, sum(주문세부.주문수량), sum(주문세부.단가*주문세부.주문수량)
from 고객, 주문, 주문세부, 제품
where 고객.고객번호=주문.고객번호
and 주문.주문번호 = 주문세부.주문번호
and 제품.제품번호 = 주문세부.제품번호
and 고객.도시 like '%광역시%'
group by 제품.제품명
order by 3 desc


4. 중국산 제품을 주문한 적인 고객들의 정보
select distinct 고객.*
from 고객, 주문, 주문세부, 제품
where 고객.고객번호=주문.고객번호
and 주문.주문번호 = 주문세부.주문번호
and 제품.제품번호 = 주문세부.제품번호
and 제품.생산지 like '%중국%'

select * from 제품 where 생산지 like '%중국%'

 

 


-실주문금액

1. 주문세부에 주문금액, 할인금액, 실주문금액 보이기
select * , 주문수량*단가 주문금액, floor(주문수량*단가*할인율) 할인액, floor(주문수량*단가*(1-할인율)) 실주문액
from 주문세부

2. 제품명별로 주문금액 합, 할인금액 합
select 제품명, sum(주문수량*주문세부.단가) 주문금액합,
 sum(주문수량*주문세부.단가*할인율) 할인액,
 sum(주문수량*주문세부.단가*(1-할인율)) 실주문액
 from 주문세부, 제품
 where 주문세부.제품번호=제품.제품번호
 group by 제품명

 
--case 함수
case when 조건 then 값 when 조건 then 값
when 조건 then 값 when 조건 then 값 else 값 end

고객
 마일리지가 100000 보다 높으면 vip고객
  10000보다 높으면 우수고객
  그 나머지는 일반고객으로 표시
 
 담당자명 고객회사명 마일리지 구분
 
select * from 고객

select 담당자명, 고객회사명, 마일리지,
case when 마일리지>=100000 then 'vip고객'
 when 마일리지 >=10000 then '우수고객'
 else '일반고객' end 구분
from 고객
 

서브쿼리
from 절에 쓰는 select 문을 inner view

select * from (select 담당자명, 마일리지
    from 고객
    where 담당자명 like '김%') 신고객

구분별로 주문횟수(주문테이블에서 count())
vip고객
우수고객

select 구분, count(*) '주문횟수'
from (select 고객번호, 담당자명, 고객회사명, 마일리지,
case when 마일리지>=100000 then 'vip고객'
 when 마일리지 >=10000 then '우수고객'
 else '일반고객' end 구분
from 고객) 구분테이블, 주문
where 구분테이블.고객번호=주문.고객번호
group by 구분

 


select 구분, sum(단가*주문수량) '주문금액합'
from (select 고객번호, 담당자명, 고객회사명, 마일리지,
case when 마일리지>=100000 then 'vip고객'
 when 마일리지 >=10000 then '우수고객'
 else '일반고객' end 구분
from 고객) 구분테이블, 주문, 주문세부
where 구분테이블.고객번호=주문.고객번호
and 주문.주문번호=주문세부.주문번호
group by 구분


select * from 주문
select * from 주문세부
select * from 고객
select * from 제품
select * from 사원

 

5. 김소미 사원이 주문받은 총 횟수를 보여라(조인, 하위쿼리 사용)

select 사원.이름, sum(주문세부.주문수량)
from 사원, 주문, 주문세부
where 사원.사원번호 = 주문.사원번호
and 주문.주문번호 = 주문세부.주문번호
and 사원.이름 like '%김소미%'
group by 사원.이름


10. 한석규 고객이 주문한 내역에 대해 제품명별로 주문수량합, 주문금액합, 재고를 보이기
(재고가 많이 남은 것 부터 순서대로 보이고, 재고가 같은 경우, 주문수량 합이 많은 것 부터 보임)

select * from 제품

select 고객.담당자명, 제품.제품명, sum(주문세부.주문수량) 주문수량, sum(주문세부.주문수량*주문세부.단가) 주문금액, 제품.재고
from 고객, 주문, 주문세부, 제품
where 고객.고객번호=주문.고객번호
and 주문.주문번호=주문세부.주문번호
and 주문.주문번호=주문세부.주문번호
and 제품.제품번호=주문세부.제품번호
and 고객.담당자명 like '%한석규%'
group by 고객.담당자명, 제품.제품명, 제품.재고
order by 5 desc, 4 desc


11. 아이스크림의 년도별 월별 주문수량 합

select year(주문.주문일), 제품명, sum(주문세부.주문수량)
from 제품, 주문, 주문세부, 고객
where 제품.제품번호=주문세부.제품번호
and 주문세부.주문번호=주문.주문번호
and 주문.고객번호=고객.고객번호
and 제품.제품명 like '%아이스크림%'
group by year(주문.주문일), 제품명



유미 돌김의 재고?

select 제품명, 재고 from 제품 where 제품명 like '%유미 돌김%'