1. ๋ชจ๋ ์ฃผ๋ฌธ ์ ๋ณด๋ฅผ ๋ณด์ด์ธ์(oid,odate,title) - ์ค์นผ๋ผ ๋ถ์์ง์
select oid, odate, (select title
from book
where book.bid = orders.bid) as title
from orders;
2. ๋ชจ๋ ์ฃผ๋ฌธ ์ ๋ณด๋ฅผ ์ฑ ๋ณ๋ก ๋ณด์ด์ธ์(bid,title,๊ฑด์) - ์ค์นผ๋ผ ๋ถ์์ง์
select bid, title, (select count(*)
from orders
where book.bid = orders.bid) as ๊ฑด์
from book;
3. mac ์ถํ์ฌ์์ ์ถ๊ฐํ ์ฑ ์ ์ฃผ๋ฌธ์ ๋ณด๋ฅผ ๋ณด์ด์ธ์(title,oid,odate) - ์ค์นผ๋ผ ๋ถ์์ง์+ ์ค์ฒฉ์ง์
select (select title
from book
where book.bid = orders.bid) as title, oid, odate
from orders
where bid in (select bid
from book
where publisher = 'mac');
4. orders ํ ์ด๋ธ์ ๊ณ ๊ฐ์ด๋ฆ(cname)์ ์ถ๊ฐํ๊ณ , ๊ฐ ์ฃผ๋ฌธ์ cid์ ํด๋นํ๋ ์ด๋ฆ์ ์ ๋ฐ์ดํธํ์ธ์.(์ค์นผ๋ผ ๋ถ์์ง์ update ๊ตฌ๋ฌธ)
alter table orders add cname varchar(20);
update orders
set cname = (select cname
from cust
where cust.cid = orders.cid);
5. ์ฃผ๋ฌธ๋ด์ญ์ ๋ณด์ด์ธ์(name,title,odate) - ์ง์์ ๋ชจ๋ ํ ์ด๋ธ์ ๋ํด ํ์ํ ์์ฑ๋ง ํฌํจํ๋๋ก ์ธ๋ผ์ธ ๋ทฐ๋ฅผ ์์ฑ
select cname, title, odate
from (select cname, cid
from cust) cs,
(select title, bid
from book) b,
(select odate, cid, bid
from orders) od
where cs.cid = od.cid and b.bid = od.bid;
6. ํ๊ท ์ฃผ๋ฌธ๊ธ์ก๋ณด๋ค ์ ๊ฒ ์ฃผ๋ฌธํ ๊ณ ๊ฐ์ ์์ด๋์ ์ฃผ๋ฌธ๊ธ์ก ํฉ๊ณ๋ฅผ ๋ณด์ด์ธ์.(cid, ํฉ๊ณ) - ์ค์ฒฉ์ง์+์กฐ์ธ
select cid, sum(price) as ํฉ๊ณ
from (select cid, price
from orders natural join book) t1
where t1.price < (select avg(price)
from (select cid, price
from orders natural join book) t2 natural join book)
group by cid;
7. ์ถํ์ฌ๋ณ ํ๊ท ์ฃผ๋ฌธ๊ธ์ก๋ณด๋ค ๋ ๋น์ผ ์ฑ ์ ์ฃผ๋ฌธํ ๊ณ ๊ฐ์ ์์ด๋์ ์ฃผ๋ฌธ๊ธ์ก์ ๋ณด์ด์ธ์.(cid,price) - ์๊ด๋ถ์์ง์
select cid, price
from (select cid, price
from orders natural join book) t1
where t1.price > (select avg(price)
from (select cid, price
from orders natural join book) t2 natural join book);
8. mac ์ถํ์ฌ์์ ์ถ๊ฐํ ์ฑ ๋ณด๋ค ๋ ๋น์ผ ์ฑ ์ ๊ตฌ๋งคํ ๋ด์ญ์ ๋ณด์ด์ธ์(oid,title, price)
select oid, title price
from (select oid, title price
from orders natural join book) t1
where t1.price > (select max(price)
from (select oid, title, price
from orders natural join book) t2 natural join book
where publisher = 'mac');
9. mac ์ถํ์ฌ์์ ์ถ๊ฐํ ์ฑ ๋ณด๋ค ๋ ์ผ ์ฑ ์ ๊ตฌ๋งคํ ๋ด์ญ์ ๋ณด์ด์ธ์(oid, title, price)
select oid, title price
from (select oid, title price
from orders natural join book) t1
where t1.price < (select min(price)
from (select oid, title, price
from orders natural join book) t2 natural join book
where publisher = 'mac');