ms-SQL 다섯 번째 수업

서브쿼리와 조인(join)

 

부서->주문->주문세부 <-제품

select * from 주문
select * from 주문세부
select * from 제품

select * from 고객 order by 고객회사명

 

1. 백화점 (고객회사명) 고객들에 대해
담당자직위별로 고객 수와 평균 마일리지를 보이세요
(평균 마일리지가  5000점 미만은 제외)

select 담당자직위, avg(마일리지) as '평균 마일리지', count(*) as '고객 수'
from 고객
where 고객회사명 like '%백화점%'
group by 담당자직위
having avg(마일리지) >= 5000
order by 3

 

1. 주문년도가 2006년인 주문의 주문건수를 보여라

select count(*) '8월 주문건수' from 주문 where month(주문일) like '8'

2. 년도별 주문 건수를 보여라

select month(주문일) 월, count(*) 주문건수 from 주문 group by month(주문일)
order by 1

3. VINET 고객이 주문한 내역에 대해 월별로 주문건수를 보여라

select month(주문일) 월, count(*) 건수 from 주문 where 고객번호 like '%VINET%'
group by month(주문일)
order by 1

4. 월별 주문건수를 보이되 주문건수가 5건 이상인 것만 보여라

select month(주문일) 주문월, count(*) 주문건수 from 주문 group by month(주문일)
having count(*) >= 5
order by 1

5. 월별 고객번호별 주문건수를 보여라

select month(주문일) 주문월, 고객번호, count(*) 주문건수 from 주문
group by month(주문일), 고객번호
with cube

 

주문 건수를 보여라..
select count(*) from 주문

고객 번호별 주문 건수를 보여라
select 고객번호, count(*)
from 주문
group by 고객번호

소계, 총계
with rollup
with cube

-월별, 고객번호별
-월별
-고객번호별
-

2. 도시별 담당자직위별 고객수를 보여라
이때 도시별, 담당자직위별 소계 및 전체 고객 수도 한번에 보여라

주문년도별로 주문건수를 그래프로 만들어라

select month(주문일) 주문년도, count(*) from 주문
group by month(주문일)
order by 1

지역별 고객 수를 보이고 전체 고객 수도 보여라
select 지역, count(*) 고객수, grouping(지역) 구분
from 고객
group by 지역
with rollup

---------------------------------------------------------------
서브쿼리

select
 from (select ...)
where 연산자 (select ...)

1. 평균 마일리지보다 마일리지가 높은 고객을 찾아라
select count(*) from 고객
where 마일리지>=(select avg(마일리지) from 고객)

2. 마일리지가 제일 높은 고객을 찾아라
select * from 고객
where 마일리지=(select max(마일리지) from 고객)

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

------------------------------------
ex)부산광역시에 사는 고객이 주문한 주문일을 보여라
select 주문일
from 주문
where 고객번호 IN (select 고객번호
     from 고객
     where 도시='부산광역시')
     

1. 한석규(담당자명)가 주문한 주문일을 보여라
select * from 주문 where 고객번호=(select 고객번호 from 고객 where 담당자명='한석규' )

2. 한석규(담당자명)가 주문한 주문금액(단가*주문수량) 합을 보여라

select 제품번호, sum(단가*주문수량) 주문금액 from 주문세부
where 주문번호 IN (select 주문번호 from 주문
where 고객번호=(select 고객번호 from 고객 where 담당자명='한석규') )
group by 제품번호

3. 한석규(담당자명)가 주문한 제품명을 보여라

select 제품번호, 제품명 from 제품
where 제품번호 IN (select 제품번호 from 주문세부
where 주문번호 IN (select 주문번호 from 주문
where 고객번호=(select 고객번호 from 고객
where 담당자명='한석규') ))

select 제품.제품번호, 제품.제품명
from 고객, 주문, 주문세부, 제품
where 고객.고객번호=주문.고객번호 and
주문.주문번호=주문세부.주문번호 and
주문세부.제품번호 = 제품.제품번호 and
담당자명 = '한석규'

 

 

create database 테스트
use 테스트

create table 학과(
 학과번호 char(1) primary key,
 학과명 varchar(10))
 
 
create table 학생(
 학번  char(5) primary key,
 이름 varchar(10),
 학과번호 char(1) references 학과(학과번호),
 전화번호 varchar(20))

create table 게시판(
 번호 int identity primary key,
 학번 char(5) references 학생(학번),
 제목 varchar(20))