网络数据库课外作业参考答案

1、第七章课外作业参考答案1确定哪些图书的零售价低于JustLee Books销售的所有图书的平均零售价。SQL select title,retail2 from books3 where retail select title,cost,a.category,avgcost2 from books b,3 (select category,avg(cost) avgcost4 from books5 group by category) a6 where b.category=a.category7 and cost select order#2 from orders n atural jo

2、in customers3 where state=4 (select state5 from orders n atural join customers6 where order#=1014)7 and order#!=1014;4、 确定哪些订单的总应付款项比订单1008更高。SQL select order#,sum(qua ntity*retail)2 from books n atural join orderitems3 group by order#4 havi ng sum(qua ntity*retail)5 (select sum(qua ntity*retail)6 f

3、rom books n atural join orderitems7 where order#=1008);5、 确定哪一位或哪些作者编著了JustLee Books的客户最经常购买的图书。SQL select authorid,l name,fname2 from author n atural join bookauthor3 where isb n in (select isb n4 from orderitems5 group by isbn6 having sum(quantity)=(7 select max(sum(quantity)8 from orderitems9 gro

4、up by isbn);6、列出与客户 1007 以前购买的图书属于同一种类的所有图书的书名，不要包括这个客户已经购买的图书。SQL select title2 from books3 where category in4 (select category5 from books natural join orderitems6 natural join orders7 where customer#=1007)8 and title not in9 (select title10 from books natural join orderitems11 natural join orders

5、12 where customer#=1007);7、列出发货延迟时间最长的客户所在的城市和州。SQL select customer#,state,city2 from customers3 where customer#=4 (select customer#5 from orders6 where shipdate-orderdate=7 (select max(shipdate-orderdate)8 from orders);select customer#,lastname,firstnamefrom customers where customer# in(select cust

6、omer# from orderswhere (nvl(shipdate,sysdate)-orderdate)=(select max(nvl(shipdate,sysdate)-orderdate) from orders)8、确定哪些客户订购了 JustLee Books 销售的最便宜(就零售价而言)的图书。SQL select customer#,lastname,firstname2 from customers3 where customer# in4 (select customer#5 from orders natural join orderitems6 natural j

7、oin books7 where retail=8 (select min(retail)9 from books);9、确定多少个不同的客户订购了James Austin 编著或合著的图书。SQL select customer#,lastname,firstname2 from customers3 where customer# in4 (select customer#5 from orders natural join orderitems6 where isbn in7 (select isbn from bookauthor8 natural join author9 where

8、 lname=AUSTIN and fname=JAMES);10、确定哪些书是由 The Wok Way to Cook 的出版社出版的。SQL select title2 from books3 where pubid=4 (select pubid5 from books6 where title=THE WOK WAY TO COOK);Oracle 9i 实例目前， JustLee Books 通过在发货时随每一个订单装入一张发票来将账单交给客户。然 后客户需要在 10 天内付费。当然这就使公司必须将一些债务列为“无法收回”的。相反， 其他大多数在线书店都是在购买时通过客户的信用卡进

9、行付费。虽然接受信用卡就可以在 24 小时内将应付款项存入 JustLee Books 的银行账号，但这种方法也存在一个缺点。当公司 接受信用卡支付时，处理信用卡销售的公司(通常称为“信用卡交易所” )将从信用卡销售 的总金额中扣除 1.5%的手续费。JustLee Books的管理人员尝试确定信用卡交易的额外费用是否比向客户投递发票时“无法收回”的费用更多。 根据以往的情况来看， 对于应付款项高于平均值的订单， JustLee Books 损失的平均数量大约是总应付款项的 4%，换句话说，通常是那些订购的图书超过平均金额 的客户不履行付款的义务。为了确定接受信用卡付费将丢失 (或赚到) 多少

10、钱， 管理人员已经要求你完成下面这些 工作：1. 确定如果使用信用卡付费，那么目前下达的所有订单需要多少额外的费用。2. 根据最近下达的、应付款项超过最近下达的所有订单的平均值的订单，确定预计将被 视为“无法收回”的总金额。根据这两个计算的结果， 你应该可以确定通过接受信用卡付费， 公司是否会赔钱。 在一 个提交给管理人员的备忘录中说明你的发现。 包括计算预计的额外费用和语句的 “无法收回” 款项所需要的 SQL 语句。参考答案：1.SQL select sum(retail*quantity)*0.0152 from books natural join orderitems3 natura

11、l join orders;SQL select ；un(tail*quantitp *U, U152 From books natural jain orderitems3 natural join orders;SUN(RETAILQUAHTITV)e.O1525.896752.SQL select order#,sum(retail*qua ntity)*O.O42 from books n atural join orderitems3 n atural join orders4 group by order#5 hav ing sum(retail*qua ntity)6 (sele

12、ct avg(sum(retail*qua ntity)7 from books n atural join orderitems8 n atural join orders9 group by order#);SQL select ordertt,sun(retail*quantity)*0。即2 from bDDl占 natural join orderitems3 natural join orders4 group Dy ordertt5 hauing sumCretail*quantity)6 (select sum(retail*quant1 fron books natural

13、join orderitens8 natural join orders9 group t)y ordertt);ORDER菲 SUIKRETAIL*QUftHTITV)0.041001 U S761002 4,4761003 h.27h10017.1961Q0713 附 1B11 1012 10163.598fi.8363.598己选择8行。SQL select sum(sum(retail*qua ntity)*0.04)2 from books n atural join orderitems3 n atural join orders4 group by order#5 hav ing sum(retail*qua ntity)6 (select avg(sum(retail*qua ntity)7 from books n atural join orderitems8 n atural join orders9 group by order#);SQL select sun(sun(retail*quantit9)*0.04)2 fron books natural join orderitens3 natural