数据库课外作业参考答案

第四章课外作业参考答案

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#,STA TE

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）SQL>select 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库存中的每一本书的出版社名称、利润及免费礼品。创建一个备忘录，包括必要的SQL语句以及销售部所要求的输出。

参考答案：

SQL> select title,name "publisher name",retail-cost profit,gift

2 from books,publisher,promotion

3 where books.pubid=publisher.pubid

4 and books.retail between minretail and maxretail;