ms-SQL 일곱 번째 수업

 

 

http://www.dbguide.net/index.db

 

- 조인
 이퀴조인
 비이퀴조인 : 조인조건에 =이 아닌 연산자가 들어가는 조인
     구간등급표로 부터 값을 가져오고자 할 때
    
 셀프조인 : 자신의 테이블을 참조함
 외부조인

select * from 마일리지등급
select * from 고객

select 담당자명, 고객회사명, 마일리지, 등급명
from 고객, 마일리지등급

-- 비이퀴 조인

select 담당자명, 고객회사명, 마일리지, 등급.*
from 고객, 마일리지등급 등급
where 마일리지 between 하한값 and 상한값
and 담당자명 like '한석규'

-- 고객과 마일리지를 where 조건으로 연결함
-- 고객.마일리지 등급.등급명

1. 백화점 고객들에 대해서 고객회사명, 마일리지, 등급명을 보이세요

select 고객회사명, 마일리지, 등급명
from 고객, 마일리지등급 등급
where 마일리지 between 하한값 and 상한값

2. 등급명 별로 고객 수를 보이세요

select 등급명, count(*) '고객 수'
from 고객, 마일리지등급 등급
where 마일리지 between 하한값 and 상한값
group by 등급명
with rollup

3. 백화점 고객들에 대해서 등급명 별로 고객 수를 보이세요

select 등급명, count(*) '고객 수'
from 고객, 마일리지등급 등급
where 마일리지 between 하한값 and 상한값
and 고객회사명 like '%백화점%'
group by 등급명

4. 등급명 별로 주문금액 합을 보이세요. 이때 주문금액합이 천만원 이하인 것만 보이세요

select 등급명, sum(단가*주문수량) 주문금액
from 고객, 주문, 주문세부, 마일리지등급 등급
where 마일리지 between 하한값 and 상한값
and 고객.고객번호 = 주문.고객번호
and 주문.주문번호 = 주문세부.주문번호
group by 등급명
having sum(단가*주문수량) <=10000000

5. A등급고객이 주문한 제품들에 대해서 제품명 별로 주문수량합을 구하세요.

select 제품명, sum(주문세부.단가*주문세부.주문수량) 주문금액
from 고객, 주문, 주문세부, 제품, 마일리지등급 등급
where 마일리지 between 하한값 and 상한값
and 고객.고객번호 = 주문.고객번호
and 주문.주문번호 = 주문세부.주문번호
and 주문세부.제품번호 = 제품.제품번호
and 등급명 = 'A'
group by 제품명

6. F등급 고객이 주문한 제품명들을 중복없이 보이세요

select distinct 제품명
from 고객, 주문, 주문세부, 제품, 마일리지등급 등급
where 마일리지 between 하한값 and 상한값
and 고객.고객번호 = 주문.고객번호
and 주문.주문번호 = 주문세부.주문번호
and 주문세부.제품번호 = 제품.제품번호
and 등급명 = 'F'
group by 제품명

7. 대성 어묵을 주문한 고객들에 대해
담당자명, 전화번호, 마일리지, 등급명을 보이세요.

select distinct 담당자명, 전화번호, 마일리지, 등급명
from 고객, 주문, 주문세부, 제품, 마일리지등급 등급
where 마일리지 between 하한값 and 상한값
 and 고객.고객번호 = 주문.고객번호
 and 주문.주문번호 = 주문세부.주문번호
 and 주문세부.제품번호 = 제품.제품번호
 and 제품명 like '%대성 어묵%'

8. 등급명이 C인 고객 중에 서울에 사는 고객은 누구일까?

select 담당자명, 전화번호, 마일리지, 등급명
from 고객, 마일리지등급 등급
where 마일리지 between 하한값 and 상한값
 and 등급명 = 'C'
 and 도시 like '%서울%'

9. C등급 중에서 마일리지가 가장 높은 고객은 누구일까?

select top 1 담당자명, 마일리지, 등급명
from 고객, 마일리지등급 등급
where 마일리지 between 하한값 and 상한값
 and 등급명 = 'C'
order by 마일리지 desc

----------------------------------------------------------------

-- 셀프조인

select 사원번호, 이름, 상사번호 from 사원 사원
select 사원번호, 이름, 상사번호 from 사원 상사

select 부하.사원번호, 부하.이름, 부하.상사번호, 상사.이름 상사명
from 사원 부하, 사원 상사
where 부하.상사번호 = 상사.사원번호

-- 셀프조인은 공장에서 많이 씀(부품?)

1. 김덕훈의 부하명을 보이세요.

select 상사.이름 상사명, 부하.이름 부하명
from 사원 부하, 사원 상사
where 부하.상사번호 = 상사.사원번호
and 상사.이름 = '김덕훈'

2. 상사이름별로 부하 수를 보이세요

select 상사.이름 상사명, count(*) 부하수
from 사원 부하, 사원 상사
where 부하.상사번호 = 상사.사원번호
group by 상사.이름

 

3. 김덕훈의 부하들이 주문받은 주문횟수는 몇 번?

select 상사.이름 상사명, count(*) 주문횟수
from 사원 부하, 사원 상사, 주문
where 부하.상사번호 = 상사.사원번호
and 부하.사원번호 = 주문.사원번호
and 상사.이름 = '김덕훈'
group by 상사.이름

4. 김연아의 상사가 주문받은 날짜를 확인(주문일) 하세요.

select 주문일
from 사원, 주문
where 사원.사원번호 = 주문.사원번호
and 사원.사원번호 = (select 상사번호 from 사원 where 이름='김연아')

5. 한석규 고객이 주문한 것을 주문받은 사원은 누구?

select 사원.이름
from 사원, 고객, 주문
where 사원.사원번호 = 주문.사원번호
and 주문.고객번호 = 고객.고객번호
and 고객.담당자명 = '한석규'

6. 한석규 고객이 주문한 것을 주문받은 사원의 상사는 누구?

select 이름, 사원번호
from 사원
where 사원번호 in (select 상사번호
     from 사원, 고객, 주문
     where 사원.사원번호 = 주문.사원번호
     and 주문.고객번호 = 고객.고객번호
     and 고객.담당자명 = '한석규')

보너수 문제)
주문을 가장 많이 받은 사원의 상사명은 누구?

select top 1 상사.이름 상사명, 부하.이름 부하명, count(*) 주문횟수
from 사원 부하, 사원 상사, 주문
where 부하.상사번호 = 상사.사원번호
and 부하.사원번호 = 주문.사원번호
group by 상사.이름, 부하.이름
order by 3 desc