pubs DB
sales, titles, stores
select * from sales
select * from titles
select * from stores
1. stor_name, title_id, qty, ord_date를 출력하시오.
--sales테이블을 기준으로 조인
select stores.stor_name, sales.title_id, sales.qty, sales.ord_date
from titles
inner join sales
on titles.title_id = sales.title_id
inner join stores
on sales.stor_id = stores.stor_id
2. 책종류별 qty의 합계를 구하시오. - group by, join
출력될때는 type, sum(qty)로 출력
select titles.type, sum(qty) as[총계] from titles inner join sales on sales.title_id=titles.title_id
group by titles.type
3. 2번에서 sum(qty)값이 50보다 큰것만을 구하시오. - having
select titles.type, sum(qty) as[총계] from titles inner join sales on sales.title_id=titles.title_id
group by titles.type
having sum(qty) >50
4. 'business' type의 책은 모두 몇권이 판매되었는가? - join
select titles.type, sum(qty) as[총계]
from sales inner join titles on titles.title_id = sales.title_id
group by titles.type
having type='business'
5. 'WA' state에 있는 stores가 판매한 내역을 출력하시오. - join(3개테이블)
stor_name,title,qty
select stores.state, sales.qty, stores.stor_name, titles.title
from titles
inner join sales
on titles.title_id = sales.title_id
inner join stores
on sales.stor_id = stores.stor_id
and stores.state='wa'
select stores.state, sales.qty, stores.stor_name, titles.title
from titles
inner join sales
on titles.title_id = sales.title_id
inner join stores
on sales.stor_id = stores.stor_id
where stores.state='WA'
having은 group by뒤에 나와야 한다.
'.net expert > sql 2005' 카테고리의 다른 글
sql 2005 (0) | 2007.03.05 |
---|---|
sql 2005 (0) | 2007.03.03 |
sql 2005 (0) | 2007.02.28 |
데이터 모델링 작성절차 (0) | 2007.02.27 |
ERD Modeling (2) | 2007.02.27 |