use pubs
select * from sales
3-1.
select t.title_id, title, sum(qty) as 합계수량
from titles t join sales s on t.title_id=s.title_id
group by t.title_id, title
having sum(qty) < (select avg(qty)
from sales)
3-2.
select stor_id, sum(qty),
cast(round(cast(sum(qty) as float)/(select sum(qty) from sales)*100,0) as char(3))+'%' as 비율
from sales
group by stor_id
3-3.
[subquery 이용]
select stor_id, stor_name, city,state
from stores
where stor_id in (select stor_id
from sales
group by stor_id
having count(*) >= 2)
[join 이용]
select t.stor_id, stor_name, city,state
from stores t join sales a on t.stor_id=a.stor_id
group by t.stor_id, stor_name, city,state
having count(*) >= 2
3-4.
select stor_id, title_id,qty
from sales s
where qty = (select max(qty)
from sales
where s.stor_id = stor_id)
3-5.
[join 이용]
select distinct pub_name
from publishers p join titles t on p.pub_id=t.pub_id
where type = 'business'
[correlated 이용]
select pub_name
from publishers p
where exists (select *
from titles
where p.pub_id = pub_id and type = 'business')
3-6.
select stor_id, stor_name, city, state,'서점'
from stores
union all
select pub_id, pub_name, city, state,'출판사'
from publishers
order by 1
use pubs
select stor_id, stor_name, city, state,'서점'
from stores
union
select stor_id, stor_name, city, state,'서점'
from stores
order by 1