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

第1章单选题：B B B B C/D C A B A简述题：1略2星期节数课程星期一1语文星期二1数学星期一2数学星期一3英语。姓名养老保险失业保险医疗保险住房公积金张三3005075400李四2705080380王五28050905003略4略5略6略7略8略9略第2章实践环节设计：1CREATE TABLE Student ( Sno char ( 7 ) PRIMARY KEY, Sname char ( 10 ) NOT NULL, Ssex char (2) CHECK (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 = 100), PRIMARY KEY ( Sno, Cno ), FOREIGN KEY ( Sno ) REFERENCES Student ( Sno ), FOREIGN KEY ( Cno ) REFERENCES Course ( Cno ) )2Insert into student values(9512101,李勇,男,19,计算机系)Insert into course values(co1,数据库原理,3,4,64)Insert into sc values(9512101,co1,90)3Drop TABLE student4 ALTER TABLE SC ADD XKLB char(4) 5 ALTER TABLE SC ALTER COLUMN XKLB char(6)6 Alter table sc drop column xklb7 SELECT Sno，Sname FROM Student 8 SELECT Sname FROM Student WHERE Sdept = 计算机系9 SELECT Sname, Sage FROM Student WHERE Sage = 2010 SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23此句等价于：SELECT Sname, Sdept, Sage FROM Student WHERE Sage =20 AND Sage=2311 SELECT Sname, Ssex FROM Student WHERE Sdept IN (信息系, 数学系, 计算机系)此句等价于：SELECT Sname, Ssex FROM Student WHERE Sdept = 信息系 OR Sdept = 数学系 OR Sdept = 计算机系12 SELECT * FROM Student WHERE Sname LIKE 张%13 SELECT * FROM Student WHERE Sname LIKE 张李刘%14 SELECT Sname, Sno FROM Student WHERE Sname LIKE _小大%15 SELECT Sno, Cno FROM SC WHERE Grade IS NOT NULL16 SELECT Sname FROM Student WHERE Sdept=计算机系 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.Sno WHERE Sdept = 计算机系28 SELECT Sname, Cname, Grade FROM Student s JOIN SC ON s.Sno = SC. Sno JOIN Course c ON c.Cno = SC.Cno WHERE Sdept = 信息系 AND Cname = VB29 SELECT S2.Sname, S2.Sdept FROM Student S1 JOIN Student S2 ON S1.Sdept = S2.Sdept WHERE S1.Sname = 刘晨 AND S2.Sname != 刘晨 或SELECT Sname, Sdept FROM StudentWHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname = 刘晨)30 SELECT Student.Sno, Sname, Cno, Grade FROM Student LEFT OUTER JOIN SC ON Student.Sno = SC.Sno 也可以用右外连接实现： SELECT Student.Sno, Sname, Cno, Grade FROM SC RIGHT OUTER JOIN Student ON Student.Sno = SC.Sno31SELECT Sno, Sname FROM StudentWHERE Sno IN ( SELECT Sno FROM SCWHERE Cno IN (SELECT Cno FROM CourseWHERE Cname = 数据库原理) )用多表连接实现： SELECT Student.Sno, Sname FROM Student JOIN SC ON Student.Sno = SC.Sno JOIN Course ON Course.Cno = SC.Cno WHERE Cname = 数据库原理32 SELECT Sno , Grade FROM SC WHERE Cno = c02 AND Grade ( SELECT AVG(Grade) FROM SC WHERE Cno = c02)33 INSERT INTO Student VALUES (9521105, 陈冬, 男, 18, 信息系)34 UPDATE Student SET Sage = 21 WHERE Sno = 951210135 DELETE FROM SC WHERE Grade 6036 DELETE FROM SC WHERE Grade 60 AND Sno IN ( SELECT Sno FROM Student WHERE Sdept = 计算机系 )用多表连接实现 DELETE FROM SC FROM SC JOIN Student ON SC.Sno = Student.Sno WHERE Sdept = 计算机系AND Grade 2)派生关系(没调试，可以不讲)： select student.*,course.* from student join sc on student.sno=sc.sno join course on course.cno=sc.cnojoin (select sno from sc group by sno having count(cno) 3) as tt(sno) on tt.sno=student.sno39(方法可以有很多种)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 group by cno) as max_grade(m_grade,cno) on sc.cno=max_grade.cno 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.sno join course on course.cno=sc.cno 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 sage 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 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 null50 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 C C 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=anan6. 查询商品的平均价格高于75元钱的厂商名称。 Select manufacture from commodity 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,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 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(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=雀巢奶粉)第3章1实体完整性和参照完整性。2主码唯一 、 外码参考。3主码。4实体完整性和参照完整性5属性列6投影 选择7选择 投影 连接 除8Where select9a 1+b110a 2*b211（student）12（student）13（student）14（course）15. (（student）)16. ( (student sc)17. (student sc course)18. ( (student sc)( (student sc course)- ( (student)第4章1. 基本表或视图、定义、数据2With check option3数据库结构、列、逻辑指针清单4聚集索引、非聚集索引51）create view s_c_view(sno,sname,sdept,cno,cname,ccredit,grade) as Select student.sno,sname,sdept,course.cno,cname,ccredit,grade from student,sc,course where student.sno=sc.sno and course.cno=sc.cno2) select sno,sname,cno,cname,grade from s_c_view where grade=603)create view compstu_view (学号,姓名,总成绩) as Select sno,sname,sum(grade) from s_c_view where sdept=计算机系 Group by sno,sname having count(*)=24)create view cs_view as select sno,sname,ssex,sage from student Where sdept=计算机系 and sage23 with check option5)drop view s_c_view6)create index index1 on sc(cno,grade desc)6视图是一个或几个基本表（或视图）导出的表。他与基本表不同，是一个虚表，可以和基本表一样能被查询，被删除，但对视图的增、删、改操作则有一定限制。、表是内模式，视图是外模式视图的建立和删除只影响视图本身，不影响对应的基本表。7略8略9索引的优点。1 加大数据的检索速度，也是最主要的原因2 通过创建唯一确保每行数据的唯一性。3 加速表与表之间的连接。4 提高查询中分组和排序的效率。索引的缺点。创建索引和维护索引要耗费时间，而且这种时间会随着数据量的增加而增加。索引要占据数据库的物理空间，索引越多，占据的空间越多。维护索引要花费很多的时间，尤其是在增加、删除和修改表中数据的时候。第5章1一、一、多2物理3浏览器、客户端4概念模式也称模式，是数据库中全部数据的逻辑结构和特征的描述。外模式也称用户模式或子模式。是用户与数据库系统的接口，是用户用到的那部分数据的描述。内模式也称存储模式。是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式。定义所有的内部记录类型、索引和文件的组织方式，以及数据控制方面的细节。5略第6章单选题：A B B A B / D B A D B简述题：1 .候选码（学号） 第二范式非主属性（姓名、所在系、班号、班主任、系主任）分解成如下三个第三范式的表:学生表（学号，姓名，班号，系别）班级表（班号，班主任）系别表（系别，系主任）2 .候选码为：（课程号，授课教师号） 第一范式非主属性有：课程名、学分、教师名、授课时数分解成如下三个第三范式的表:课程表（课程号，课程名，学分）教师表（授课教师号，教师名）授课表（课程号，授课教师号，授课时数）3.满足BCNF4 .候选码为：职工号 第二范式非主属性有：职工名，年龄，性别，单位号，单位名分解成如下两个第三范式的表:职工表（职工号，职工名，年龄，性别，单位号）单位表（单位号，单位名）5.略6.略7.略8 .候选码为：学号 第二范式非主属性有：姓名，出生日期，所在系，宿舍楼分解成如下两个第三范式的表:学生表（学号，姓名，出生日期，所在系）宿舍（所在系，宿舍楼）9.一定、一定、一定、不一定10一定、一定、一定、一定、一定第7章单选题：B C C C D/C D D B D/B D简述题：1-8简答略(教材中有答案)。9(1)分支机构员工贷款客户工作nmnmnnn1储蓄发放借贷1储蓄账户名字资产Costumer-id姓名街道城市账户号开始工作日期亲属电话号码姓名经理idEmployee-id利率余额最近访问日期金额贷款号n1nnmnnn1(2)转换成关系模式并指出每个模式的主码，外码表名属性主码外码分支机构名字，城市，资产名字贷款贷款号，金额，分支机构名称贷款号分支机构名称储蓄账户账户号，金额，利率，最近访问日期，透支额账户号客户costumer-id，姓名，街道，城市，employee-idcostumer-idemployee-id员工employee-id，姓名，经理id，电话号码，开始工作日期，亲属employee-id借贷贷款号，costumer-id贷款号，costumer-id贷款号，costumer-id储蓄账户号，costumer-id账户号，costumer-id账户号，costumer-id10. (1) 根据上述规则设计 E-R 模型。(2) 将E-R模型转换成关系数据模型，并指出每个关系的主键和外键。部门（部门号，部门名）职工（职工号，职工名，性别，部门号）项目（工程号，项目名，部门号）参与（职工号，工程号，酬金）单独的职工号和工程号是 外码(3) 每一个关系模式是第三范式。第8章单选题：B B D C D / A D B简答与操作题略(教材中可找出答案)。第9章单选题：C A D A D /A C B D D / B C D A D简答题略(教材中可找出答案)。第10章单选题：C C A D B / C B D简单题：1 完全备份2-13操作略。第11章1 简答略(教材中可找出答案)。2（1）略（参见课件类似题目）。（2）略（参见课件类似题目）。（3）alter table readeradd telephone char(13)create trigger checktelon readerfor insert,updateasdeclare telephone char(13)select telephone=telephone from insertedif LEN(telephone)!=LEN(0000-00000000) and telephone not like _-_beginrollbackraiserror(格式错误,16,1)endupdate reader set telephone=1234-12345678where name=王旭（4）create proc search()asselect name as 书名,author as 作者,price as 价格,publish as 出版社from BOOK where price 35goexec search (5) create table product(col1 int,col2 int)create trigger rideon productfor insert,updateasbeginselect col1,col2,(col1*col2)as 乘积 from insertedselect col1,col2,(col1*col2)as 乘积 from productendinsert into product values (5,6)select * from product(6)略（参见课件类似题目）。(7)create proc search1(tableName varchar(20),id varchar(20)asbegin if tableName=Book select * from book where book_id=id else if tableName=readerselect * from reader where reader_id=id else print 不适用本函数end测试：exec search1 book,A32DT00002exec search1 borrow,A32DT00002(8)create proc search2(tableName varchar(20),id varchar(20)=NULL)asbegin if tableName=Book begin if(id IS null) select top 1 * from Book ELSE SELECT * FROM BOOK WHERE book_ID=ID end else if tableName=reader begin if(id IS null) select top 1 * from reader ELSE SELECT * FROM READER WHERE Reader_ID=ID end else print 不适用本函数end测试：exec search2 book,A32DT00002exec search2 borrow,A32DT00002exec search2 book