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

1、第 1 章单选题：B B B B C/D C A B A简述题：1略2星期 节数 课程星期一 1 语文星期二 1 数学星期一 2 数学星期一 3 英语。 。 。 。 。 。 。 。 。 。 。 。 。 。 。 。 。 。姓名 养老保险 失业保险 医疗保险 住房公积金张三 300 50 75 400李四 270 50 80 380王五 280 50 90 5003略4略5略6略7略8略9略第 2 章实践环节设计：1CREATE TABLE Student (Sno char ( 7 ) PRIMARY KEY,Sname char ( 10 ) NOT NULL,Ssex char (2) CH

2、ECK (Ssex = 男 OR Ssex = 女), Sage tinyint CHECK (Sage = 15 AND Sage 0),Semester tinyint CHECK (Semester 0),Period int CHECK (Period 0),PRIMARY KEY(Cno) ) CREATE TABLE SC (Sno char(7) NOT NULL,Cno char(10) NOT NULL,Grade tinyint,CHECK (Grade = 0 AND Grade = 2010 SELECT Sname, Sdept, Sage FROM Student

3、WHERE Sage BETWEEN 20 AND 23此句等价于：SELECT Sname, Sdept, Sage FROM Student WHERE Sage =20 AND Sage226 SELECT Sno, AVG(Grade) 平均成绩, COUNT(*) 修课门数FROM SC GROUP BY Sno HAVING COUNT(*) = 227 SELECT Sname, Cno, Grade FROM Student JOIN SC ON Student.Sno = SC.SnoWHERE Sdept = 计算机系28 SELECT Sname, Cname, Grad

4、eFROM Student s JOIN SC ON s.Sno = SC. SnoJOIN Course c ON c.Cno = SC.CnoWHERE Sdept = 信息系 AND Cname = VB29 SELECT S2.Sname, S2.SdeptFROM Student S1 JOIN Student S2 ON S1.Sdept = S2.SdeptWHERE S1.Sname = 刘晨AND S2.Sname != 刘晨或 SELECT Sname, Sdept FROM StudentWHERE Sdept IN(SELECT Sdept FROM Student W

5、HERE Sname = 刘晨)30 SELECT Student.Sno, Sname, Cno, Grade FROM Student LEFT OUTER JOIN SCON Student.Sno = SC.Sno也可以用右外连接实现：SELECT Student.Sno, Sname, Cno, Grade FROM SC RIGHT OUTER JOIN StudentON Student.Sno = SC.Sno31SELECT Sno, Sname FROM StudentWHERE Sno IN( SELECT Sno FROM SCWHERE Cno IN(SELECT C

6、no FROM CourseWHERE Cname = 数据库原理) )用多表连接实现：SELECT Student.Sno, Sname FROM Student JOIN SC ON Student.Sno = SC.SnoJOIN Course ON Course.Cno = SC.CnoWHERE Cname = 数据库原理32 SELECT Sno , Grade FROM SCWHERE Cno = c02 AND Grade (SELECT AVG(Grade) FROM SC WHERE Cno = c02)33 INSERT INTO Student VALUES (9521

7、105, 陈冬, 男, 18, 信息系)34 UPDATE Student SET Sage = 21WHERE Sno = 951210135 DELETE FROM SC WHERE Grade 2)派生关系(没调试，可以不讲)：select student.*,course.*from student join sc on student.sno=sc.snojoin course on o=ojoin (select sno from sc group by sno having count(cno) 3) as tt(sno) on tt.sno=student.sno39(方法可以

8、有很多种)select * from student where sno in (select sno 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 (select sno from sc join (select max(grade),cno from sc gro

9、up by cno) as max_grade(m_grade,cno) on o=max_o and sc.grade=max_grade.m_grade)41select * from student where sno in (select sno 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.

10、sno join course on o=o where cname=VB order by grade desc45 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 sa

11、ge desc47 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 cno48 select ame,c1.credit from course c1 join course c2 on c1.credit=c2.credit where ame=数据结构49 select o,cname from course

12、 c left join sc on o=o where o is null50 select sname,sex from student where sno not in (Select sno from sc join course on o=o where cname=VB) And sdept=计算机系习题：单选题：B B A D A / C B A A D / B D D A B / C C C B B简述题：已知有顾客购买商品信息的三张表：顾客表 Customer、定购表 Order、商品表Commodity。按要求创建三张表：1 表名：Customer属性：ID 字符型 最大

13、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

14、； 订货日期要小于交货日期；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 中插入

15、一条商品记录（“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=anan6. 查询商品的平均价格高于 75 元钱的厂商名称。Select manufacture from c

16、ommodity group by manufacture having avg(price) 757. 查询顾客的定购信息，并按订货日期升序排列，若订货日期相同，则按定购数量降序排列。Select * from orderbook order by bookdate,count desc8. 查询定购数量超过 100 的顾客姓名、电话和住址。Select name, mobile, address from customer where id in(select csid from orderbook where count100)9. 查询没有订单的商品编号、名称和厂商。Select id

17、,name, manufacture from comodity where id not in(select csid from orderbook)10. 查询定购了商品号为“M900532481”的顾客总人数和最高数量。Select count(*) ,max(count) from order where cmid=m90053248111. 查询定购了“可比克”商品的顾客编号、姓名。Select id,name from customer where id in(select csid from order where cmid in(select id from comodity

18、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=all(select Count(*) from orderbook group by csid)19查询定购了“保洁公司”的商品最多的客户信息。Select * from customer where id in(