数据库课外作业参考答案.doc

第四章课外作业参考答案1、创建一个列表，显示每本书的书名以及出版社办公室中你再次订购每本书时需要与之联系的人的姓名和电话号码。1）SQL select title,contact,phone 2 from books b,publisher p 3 where b.pubid=p.pubid;2）SQL select title,contact,phone 2 from books b join publisher p 3 on b.pubid=p.pubid;2、确定哪些订单还没有发货以及下达各个订单的客户的姓名。将结果按下达订单的日期排序。1）SQL select order#,lastname,firstname 2 from orders o,customers c 3 where o.customer#=c.customer# 4 and shipdate is null 5 order by orderdate;2）SQL select order#,lastname,firstname 2 from orders o join customers c 3 on o.customer#=c.customer# 4 where shipdate is null 5 order by orderdate;3、列出已经购买了Fitness种类的图书的所有人的客户号和姓名。1）SQL select customers.customer#,lastname,firstname 2 from customers,books,orders,orderitems 3 where books.isbn=orderitems.isbn 4 and orderitems.order#=orders.order# 5 and orders.customer#=customers.customer# 6 and category=FITNESS;2）SQL select customers.customer#,lastname,firstname 2 from books join orderitems on books.isbn=orderitems.isbn 3 join orders on orderitems.order#=orders.order# 4 join customers on orders.customer#=customers.customer# 5 where category=FITNESS;4、确定Jake Lucas已经购买了哪些书。1）SQL select title 2 from customers,books,orders,orderitems 3 where books.isbn=orderitems.isbn 4 and orderitems.order#=orders.order# 5 and orders.customer#=customers.customer# 6 and lastname=LUCAS and firstname=JAKE;2）SQL select title 2 from books join orderitems on books.isbn=orderitems.isbn 3 join orders on orderitems.order#=orders.order# 4 join customers on orders.customer#=customers.customer# 5 where lastname=LUCAS and firstname=JAKE;5、确定销售给Jake Lucas的每一本书的利润。将结果按订单日期排序。如果订购了多本书，那么将结果按利润的降序排序。1）SQL select retail-cost 2 from customers,books,orders,orderitems 3 where books.isbn=orderitems.isbn 4 and orderitems.order#=orders.order# 5 and orders.customer#=customers.customer# 6 and lastname=LUCAS and firstname=JAKE 7 order by orderdate,1 desc;2）SQL select retail-cost 2 from books join orderitems on books.isbn=orderitems.isbn 3 join orders on orderitems.order#=orders.order# 4 join customers on orders.customer#=customers.customer# 5 where lastname=LUCAS and firstname=JAKE 6 order by orderdate,1 desc;6、哪一本书是由姓氏为Adams的作者编写的？1）SQL select title 2 from author,bookauthor,books 3 where author.authorid=bookauthor.authorid 4 and books.isbn=bookauthor.isbn 5 and lname=ADAMS;2）SQL select title 2 from author join bookauthor on author.authorid=bookauthor.authorid 3 join books on books.isbn=bookauthor.isbn 4 where lname=ADAMS;7、订购图书Shortest Poems的客户将收到什么礼品？1）SQL select gift 2 from books,promotion 3 where retail between minretail and maxretail 4 and title=SHORTEST POEMS;2）SQL select gift 2 from books join promotion 3 on retail between minretail and maxretail 4 where title=SHORTEST POEMS;8、确定Becca Nelson订购的图书的作者。1）SQL select lname,fname 2 from customers,orders,orderitems,bookauthor,author 3 where customers.customer#=orders.customer# 4 and orders.order#=orderitems.order# 5 and orderitems.isbn=bookauthor.isbn 6 and bookauthor.authorid=author.authorid 7 and firstname=BECCA and lastname=NELSON;2）SQL select lname,fname 2 from customers join orders on customers.customer#=orders.customer# 3 join orderitems on orders.order#=orderitems.order# 4 join bookauthor on orderitems.isbn=bookauthor.isbn 5 join author on bookauthor.authorid=author.authorid 6 where firstname=BECCA and lastname=NELSON;9、显示BOOKS表中所有图书的列表。如果某位客户已经订购了一本书，那么还要列出对应的订单号以及客户所在的州。1） SQL SELECT TITLE,ORDERS.ORDER#,STATE 2 FROM BOOKS LEFT JOIN ORDERITEMS 3 ON BOOKS.ISBN=ORDERITEMS.ISBN 4 JOIN ORDERS 5 ON ORDERITEMS.ORDER#=ORDERS.ORDER# 6 JOIN CUSTOMERS 7 ON ORDERS.CUSTOMER#=CUSTOMERS.CUSTOMER#;2）SQL SELECT TITLE,ORDERS.ORDER#,STATE 2 FROM BOOKS,ORDERITEMS,ORDERS,CUSTOMERS 3 WHERE BOOKS.ISBN=ORDERITEMS.ISBN(+) 4 and ORDERITEMS.ORDER#=ORDERS.ORDER# 5 and ORDERS.CUSTOMER#=CUSTOMERS.CUSTOMER#;10、生成居住在佛罗里达州并且订购计算机图书的所有客户的列表。1）SQL select lastname,firstname 2 from books,orderitems,orders,customers 3 where books.isbn=orderitems.isbn 4 and orderitems.order#=orders.order# 5 and orders.customer#=customers.customer# 6 and category=COMPUTER 7 and state=FL;2）SQLselect lastname,firstname 2 from books join orderitems on books.isbn=orderitems.isbn 3 join orders on orderitems.order#=orders.order# 4 join customers on orders.customer#=customers.customer# 5 where category=COMPUTER and state=FL;Oracle 9i实例JustLee Books的销售部正在准备年度促销活动。在促销期间下达订单的每一个客户都将根据他所购买的每一本书收到一份免费礼品，收到的实际礼品取决于图书的零售价。JustLee Books还参与了某些出版社的联合广告活动。如果出版社的名称包括在广告中，那么将向JustLee Books返还一定比例的广告成本。要想确定今年促销活动的预计成本，销售部需要JustLee Books库存中的