数据库原理与技术简明教程(第二版)课后习题参考答案

selectstudent.*,course.*

from student join sc on student.sno=sc.sno join course on course.cno=sc.cno

wheresno in (select sno from sc group by sno having count(cno) >2) 派生关系(没调试,可以不讲): selectstudent.*,course.*

from student join sc on student.sno=sc.sno join course on course.cno=sc.cno

join (select sno from sc group by sno having count(cno) >3) as tt(sno) on tt.sno=student.sno 39(方法可以有很多种)

select * from student where sno in (

selectsno from sc where grade=(

select max(grade) from sc where cno =(select cno from course where cname=’数据库原理’

) and cno =(select cno from course where cname=’数据库原理’)

) )

40select * from student where sno in ( selectsno from sc join (

select max(grade),cno from sc group by cno) as max_grade(m_grade,cno) onsc.cno=max_grade.cno and sc.grade=max_grade.m_grade ) 41

select * from student where sno in ( selectsno from sc group by sno having count(*) >=all (

select count(*) from sc group by sno

) ) 42 略 43 略

44 select top 3 with ties sname,sdept,grade from student join sc on Student.sno=sc.sno join course on course.cno=sc.cno where cname=’VB’ order by grade desc

45 select sname,sdept from student where sno not in (select sno from sc where cno=’c01’)

Select sname,sdept from student where not exists(select * from sc where sno=student.sno and cno=’c01’)

46 select Top 3 sname,sage,sdept from student order by sage desc 47 select cno,count(*) as total,avg(grade) as avggrade,max(grade) as maxgrade,min(grade)

as

mingrade

from

student

join

sc

on

student.sno=sc.sno where sdept=’计算机系’ group by cno

48 select c1.cname,c1.credit from course c1 join course c2 on c1.credit=c2.credit where c2.cname=’数据结构’

49 select c.cno,cname from course c left join sc on c.cno=sc.cno where sc.cno is null

50 select sname,sex from student where sno not in (

Select sno from sc join course on sc.cno=course.cno where cname=’VB’) And sdept=’计算机系’ 习题: 单选题:

B B A D A / C B A A D / B D D A B / C CC B B 简述题:

已知有顾客购买商品信息的三张表:顾客表Customer、定购表Order、商品表Commodity。

按要求创建三张表: 1. 表名:Customer

属性:ID 字符型 最大10个字符 ——顾客编号 NAME 字符型 最大16个字符 ——顾客姓名 SEX 字符型 最大2个字符 ——性别

MOBILE 字符型 最大11个字符 ——移动电话 ADDRESS 字符型 最大50个字符 ——家庭住址

约束: ID——主码; NAME——非空属性; SEX——取值“男”或“女”; MOBILE——唯一性; ADDRESS——默认为UNKOWN; 表名:OrderBook

属性:CSID 字符型 最大10个字符 ——顾客编号 CMID 字符型 最大12个字符 ——商品编号 COUNT 整型 ——定购数量 BOOKDATE 日期型 ——订货日期 TAKEDATE 日期型 ——交货日期

约束:CSID,CMID——主码; 定购数量要大于0; 订货日期要小于交货日期;

CSID——外码,引用Customer表的ID; CMID——外码,引用Commodity表的ID; 表名:Commodity

属性:ID 字符型 最大12个字符 ——商品编号 NAME 字符型 最大20个字符 ——商品名称 MANUFACTURE 字符型 最大20个字符 ——生产厂商

PRICE 小数型 最大不超过4位数,保留2位小数 ——商品单价 约束:ID——主码; NAME——非空; 针对上面的三个基本表做如下练习:

2.往基本表Customer中插入顾客元组(”0421F901”,”WU”,”女”,13980011001)

3.往基本表Commodity中插入一条商品记录(“03110408591”,“牙膏”,“保洁

公司”,5.00)

4.修改“WANGYAN”顾客定购商品的记录交货日期为2005-12-25。

Update order set taketime=’2005-12-25’ where csid in (select id from customer where name=’wangyan’) 5. 查询“ANAN”顾客的手机号和住址。

Select mobile, address from customer where name=’anan’ 6. 查询商品的平均价格高于75元钱的厂商名称。

Select manufacture from commodity group by manufacture having avg(price) >75

7. 查询顾客的定购信息,并按订货日期升序排列,若订货日期相同,则按定购数量降序排列。

Select * from orderbook order by bookdate,countdesc 8. 查询定购数量超过100的顾客姓名、电话和住址。

Select name, mobile, address from customer where id in(select csidfrom orderbook where count>100)

9. 查询没有订单的商品编号、名称和厂商。

Select id,name, manufacture from comodity where id not in(select csid from orderbook)

10. 查询定购了商品号为“M900532481”的顾客总人数和最高数量。 Select count(*) ,max(count) from order where cmid=’m900532481’ 11. 查询定购了“可比克”商品的顾客编号、姓名。

Select id,name from customer where id in(select csid from order where cmid in(select id from comodity where name=’ 可比克’))

12. 查询商品名中包含“糖”的所有商品的编号、厂商、单价。

Select id,manufacture ,price from comodity where name like ‘%糖%’ 13. 查询至少有两位顾客定购的商品的名称。

Select name from comodity where id in(select cmid from order group by cmid having count(*)>=2)

14. 查询截至2005年底所有商品订单最晚的交货日期。

SELECT taketime from order where taketime = (select max(taketime) from order where taketime<’2005-12-31’) 15. 查询住址中含有“软件园”三个字的顾客人数。

Select count(*) from customer where address like’%软件园%’ 16.查询订购了“保洁公司”生产的产品中价格最高的商品的客户信息。 Select

customer.*

from

customer,OrderBook,Commodity

where

customer.id=orderbook.CSID and Commodity.id=orderbook.cmid and Commodity.id in

(Select id from Commodity where MANUFACTURE =“保洁公司” and price=( Select max(price) from Commodity where MANUFACTURE =“保洁公司”)) 17.查询2007年3月份住址在“大连”的顾客对“上好佳”商品的定购信息,列出顾客姓名,商品数量和定购日期。

Select customer.name,count,BOOKDATE from customer,OrderBook,Commodity where

customer.id=orderbook.CSID and Commodity.id=orderbook.cmid and ADDRESS like ‘%大连%’ and Commodity.name=’上好佳’ and BOOKDATE between 2007-03-01 and 2007-03-31

18.查询订单最多的顾客姓名。

Select name from customer where id in(

Select CSID fromOrderBookgroup by csid having count(*)>=all(select Count(*) from orderbook group by csid))

19.查询定购了“保洁公司”的商品最多的客户信息。 Select * from customer where id in(

Select csid from orderbook where cmid in(Select id from Commodity where

MANUFACTURE =“保洁公司”) group by csid having count(*)>=all(select

count(*) from orderbook where cmid in(Select id from Commodity where

MANUFACTURE =“保洁公司”) group by csid ))

20.把“雀巢奶粉”的定购商品记录全部删去。

Delete from order where cmid in (select id from commodity where name=’雀巢奶粉’)

联系客服:779662525#qq.com(#替换为@) 苏ICP备20003344号-4