SERVER2005刘卫国 熊拥军主编课后习题答案.doc

《SERVER2005刘卫国 熊拥军主编课后习题答案.doc》由会员分享，可在线阅读，更多相关《SERVER2005刘卫国 熊拥军主编课后习题答案.doc（23页珍藏版）》请在三一办公上搜索。

1、数据库技术与应用-SQL SERVER2005刘卫国 熊拥军主编习题参考答案目录第一章 数据库系统概论- 3 -一、选择题- 3 -二、填空题- 3 -第二章 关系数据库基本原理- 3 -一、选择题- 3 -二、填空题- 3 -第三章 SQL SERVER2005系统基础- 3 -一、选择题- 3 -二、填空题- 4 -第四章 数据库的管理- 4 -一、选择题- 4 -二、填空题- 4 -四、应用题- 4 -第五章 表的管理- 5 -一、选择题- 5 -二、填空题- 5 -四、应用题- 5 -第六章 数据查询- 7 -一、选择题- 7 -二、填空题- 7 -四、应用题- 7 -第七章 索引与视

2、图- 10 -一、选择题- 10 -二、填空题- 10 -第八章 数据完整性- 11 -一、选择题- 11 -二、填空题- 11 -四、应用题- 11 -第九章 T-SQL程序设计- 12 -一、选择题- 12 -二、填空题- 12 -四、应用题- 12 -第十章 存储过程和触发器- 15 -一、选择题- 15 -二、填空题- 15 -四、应用题- 15 -第十一章 数据库的安全管理- 17 -一、选择题- 17 -二、填空题- 17 -四、应用题- 17 -第十二章 数据库的备份与还原- 17 -一、选择题- 17 -二、填空题- 17 -四、应用题- 18 -第一章 数据库系统概论一、选择

3、题ABDAD二、填空题载体、意义；数据集合；数据库管理系统；数据库系统；层次、网状、关系、关系模型第二章 关系数据库基本原理一、选择题D DACBD二、填空题1. 实体完整性、参照完整性、用户自定义完整性。2. （A,B） R1(A,D)和R2(A,B,C).3. 元子特性4. 外键5. F1（AB，AC，AD）6. 选择第三章 SQL SERVER2005系统基础一、选择题ACBCB二、填空题1. 企业版，标准版，开发版，工作组版，精简版2. WINDOWs身份验证模式和混合模式3. SQL Server管理平台、SQL Server配置管理器4. 已注册服务器窗口、对象资源管理器，文档窗口

4、5. 数据查询语言，数据定义语言，数据操纵语言和数据控制语言第四章 数据库的管理一、选择题ABDAC二、填空题1、数据文件和事务日志文件2、master , tempdb , model ,msdb和mssqlsystemresource3、数据文件，事务日志文件和文件组4、55、create database, alter database, drop database四、应用题1create database saleson primary(name=saldat01,filename=c:dbsaldat01.mdf),(name=saldat02,filename=c:dbsaldat

5、02.ndf),filegroup filegrp1(name=saldat11,filename=d:dbsaldat11.ndf),(name=saldat12,filename=d:dbsaldat12.ndf),(name=saldat13,filename=d:dbsaldat13.ndf)2alter database salesadd log file(name=sallog2,filename=c:dbsallog2.ldf)3alter database salesadd file(name=saldat03,filename=c:dbsaldat03.ndf,size=5,

6、filegrowth=20%)扩展alter database salesadd file(name=saldat14,filename=d:dbsaldat14.ndf,size=5,filegrowth=20%) to filegroup filegrp14alter database sales set single_user5drop database sales第五章 表的管理一、选择题CACCC二、填空题1、-2 -2-1， 0-255.2、输入存储字段小于100时按原字段存，大于100时截取100位。8个字节。3、日期时间数据类型，数字数据类型4、表名和字段名称5、insert，

7、update， truncate或delete。四、应用题-创建数据库create database sales-4.1-标识符列（自动增长的列）-identity(种子，增量)create table sell_order(order_id1 int identity(1,2),goods_id char(6) not null,employee_id char(4) not null,custmer_id char(4) not null,transporter_id char(4) not null,order_num float,discount float,order_date da

8、tetime,send_date datetime,arrival_date datetime,cost money)-删除标识符列alter table sell_orderdrop column order_id1-添加标识符列alter table sell_orderadd order_id1 int identity(1,2)-4.2alter table sell_order-drop column send_dateadd 发货日期 datetime-4.3-标识列自动增长insert sell_order (goods_id,employee_id,custmer_id,ord

9、er_num,discount,order_date)values(135,16,99,30,9.5,2009-2-26)insert sell_order values(135,16,99,30,9.5,2009-2-26,)-为空和null不同insert sell_order values(135,16,99,null,30,9.5,2009-2-26,null,null,null)-允许手动增长（显示声明）set identity_insert sales.dbo.sell_order on insert sell_order (order_id1,goods_id,employee_

10、id,custmer_id,order_num,discount,order_date) values(8,135,16,99,30,9.5,2009-2-26)set identity_insert sales.dbo.sell_order off -4.4insert sell_order values(26,02,6,10,200,8,2008-10-10,2008-12-12,200000,2008-12-1)-4.5insert sell_order values(26,29,100,10,200,8,2009-1-1,2008-12-12,null,2008-12-1)update

11、 sell_order set employee_id=16 where (employee_id=29 and cost is null)-4.6update sell_order set discount=discount*0.9 where(custmer_id=100 and cost is null)-4.7delete from sell_order where order_date=2009-1-1第六章 数据查询一、选择题CABBC二、填空题1、TOP/PERCENT2、UNION，查询数据/结果集3、嵌套查询/子查询4、内链接，外连接5、等值连接，自然连接6、into四、应用

12、题-128页应用题第一题create database student2create table student( s_no char(8), s_name char(10), s_sex char(2), birthday datetime, polity char(8)insert into student values(s003,江鱼,女,2003-01-01,国民党)create table sco( s_no char(8), c_no char(8), score float)insert into sco values(s003,数据结构,85)-1-1select * from

13、 student order by s_no-1-2select * from student where s_sex=女 order by s_sex compute count(s_sex)-1-3select s_name,birthday,year(getdate()-year(birthday) as 年龄 from student where s_sex=男select s_name,convert(varchar,birthday,23),year(getdate()-year(birthday) as 年龄 from student where s_sex=男-select c

14、onvert(varchar,getdate(),23) 只截取系统当前日期-select convert(varchar,getdate(),8) 只截取系统当前时间 -1-4select s_name,birthday,year(getdate()-year(birthday) as 年龄,c_no,score from student,sco where student.s_no=sco.s_noselect s_name,birthday,year(getdate()-year(birthday) as 年龄,c_no,score from student inner join sco

15、 on student.s_no=sco.s_no-1-5select score from sco where s_no=(select s_no from student where s_name=江鱼)select sco.score,student.s-name from sco inner join student on sco.s_no=student.s_no where student.s_name=江于-1-6select s_name from student where s_no in(select s_no from sco where score35-2-5selec

16、t 部门名,count(职工号) as 职工人数 from 职工,部门 where 部门.部门号=职工.部门号 group by 部门名 having count(*)=2 order by count(*) descselect 部门名,count(*) as 职工人数 from 职工 inner join 部门 on 部门.部门号=职工.部门号 group by 部门.部门名 having count(*)=1 order by count(*) desc第七章 索引与视图一、选择题CCADB二、填空题1、alter table, drop index2、primary key, uniq

17、ue3、视图4、定义5、计算列6、修改，删除第八章 数据完整性一、选择题BCCAD二、填空题1、行2、create rule和create default3、唯一性4、primary key ，多5、检查，唯一性6、constraint，约束名四、应用题-第八章应用题-1alter table sell_orderadd order_id1 int identity(1,2)alter table sell_orderdrop column order_id1-2alter table sell_orderadd primary key(order_id1)-3use salesalter t

18、able customeradd primary key(customer_id)alter table sell_orderadd foreign key(custom_id) references customer(customer_id) on delete cascade on update cascade-4alter table customeradd constraint kk unique(customer_name)-5alter table sell_orderadd constraint k3 default (0)for discount-6alter table se

19、ll_orderadd constraint k4 check (order_datesend_date),check (send_datearriver_date)第九章 T-SQL程序设计一、选择题BCCDD二、填空题1、局部2、44,82,1263、gf4、/* */5、单条，begin-end6、break, continue7、declare cursor, select四、应用题-4.1.1SELECT goods_name as 商品名称, CASE WHEN classification_id=P001 THEN 笔记本计算机 WHEN classification_id=P0

20、02 THEN 激光打印机 WHEN classification_id=P003 THEN 喷墨打印机 WHEN classification_id=P004 THEN 交换机 END AS 商品类别, unit_price AS 单价,stock_quantity as 库存FROM goods-4.1.2declare value realset value=-1while value15000open employee_cursorFETCH NEXT FROM employee_cursorWHILE fetch_status = 0 BEGIN FETCH NEXT FROM em

21、ployee_cursorEND-4-2select employee_name, case (datediff(yy,birth_date,getdate()-1)/10 when 2 then 20到30岁 when 3 then 30到40岁 when 4 then 40到50岁 else 50岁以上 end as 年龄段,department_name as 所属部门from department,employee where department.department_id=employee.department_id order by 所属部门 ascselect nld as 年

22、龄段,count(*) as 人数 from (select case (datediff(yy,birth_date,getdate()-1)/10 when 2 then 20到30岁 when 3 then 30到40岁 when 4 then 40到50岁 else 50岁以上 end as nld,birth_date from employee) a GROUP BY nld-4-3create function fact2(x int)returns intasbegin if x=1 return 1 return x*dbo.fact2(x-1)endselect dbo.f

23、act2(6)-4-4 declare i int,j int,sum int select i = 1, j = 0,sum=0 while(i100) begin if(i%7=0) begin set j=j+1 set sum=sum+i end set i=i+1 end select j,sum-4-5declare cur_employee cursorforselect sex,count(*) from employee group by sexdeclare sex char(2),sex_num intopen cur_employeefetch next from cu

24、r_employee into sex,sex_numwhile fetch_status=0 begin select sex as 性别,sex_num as 人数 fetch next from cur_employee into sex,sex_num endclose cur_employeedeallocate cur_employee第十章 存储过程和触发器一、选择题CAADA二、填空题1、sp_2、存储过程3、commit transaction, rollback transaction4、数据封锁机制四、应用题-1CREATE PROC prStoreOrderIDOrde

25、r_id1 char(6),goods_id char(6),employee_id char(4),Custom_ID char(5),Transporter_ID char(4),order_date datetime,order_id2 char(6) OUTPUTASBEGIN INSERT INTO sell_order(Order_ID1,goods_id,employee_id,Custom_ID,Transporter_ID,order_date) VALUES(Order_id1,goods_id,employee_id,Custom_ID,Transporter_ID,or

26、der_date) SELECT order_id2=Order_ID1 FROM sell_order WHERE Order_ID1=Order_id1 ENDDECLARE order_id2 char(6)EXEC prStoreOrderID S00009,G00005,e010,C0009,T010,2013.01.01,order_id2 OUTPUTSELECT order_id2-2create proc chapter10_4_2as begin create table #temp (id int) declare n int select n=1 while (n=10

27、0) begin insert #temp values(n) select n=n+1 end select * from #tempendexec chapter10_4_2-3create proc prupdatename g_id char(6),g_name varchar(50)asbegin update goods set goods_name=g_name where goods_id=g_idendexec prupdatename G0008,IBM T51-4CREATE PROC prTest ASSELECT * FROM goodsIF EXISTS(SELEC

28、T * FROM sysobjects WHERE name=prTest) print 存在此存储过程ELSE print 不存在此存储过程-5EXEC sp_helptext prTest-6EXEC sp_depends prTest-7EXEC sp_rename prTest,npr_Test-8DROP PROC npr_Test第十一章 数据库的安全管理一、选择题CDBC二、填空题1、登录账号，角色2、window身份验证模式和混合模式3、对象、语句、隐含4、grant, revoke5、创建新的数据库角色、分配权限给创建的角色、将这个角色授予某个用户四、应用题sp_addlog

29、in kwh,123456,salesuse salessp_grantdbaccess kwh,kwh2sp_addrolemember db_datareader,kwh2sp_revokedbaccess kwh2sp_droplogin kwh第十二章 数据库的备份与还原一、选择题ABAA二、填空题1、磁盘备份设备、磁带设备、命名通道设备2、backup database restore database3、完全、事务日志、差异、文件和文件组4、简单恢复模型、完全恢复模型、大容量日志记录恢复模型四、应用题-1.以文件形式(临时备份设备)进行备份与还原数据库backup database

30、 sales to disk=d:backmysales1.bakrestore database sales from disk=d:backmysales1.bak-2.逻辑备份设备exec sp_addumpdevice disk,my2,d:backmysales2.bakbackup database sales to my2restore database sales from my2-覆盖现有的数据库restore database sales from my2 with replace-3.差异备份exec sp_addumpdevice disk,my3,d:backmysa

31、les3.bakbackup database sales to my3exec sp_addumpdevice disk,my4,d:backmysales4.bakbackup database sales to my4 with differentialrestore database sales from my3 with norecoveryrestore database sales from my4 with recovery-4.日志备份与还原exec sp_addumpdevice disk,my5,d:backmysales5.bakexec sp_addumpdevice

32、 disk,my6,d:backmysales6.bakbackup database sales to my5backup log sales to my6restore database sales from my5 with norecoveryrestore log sales from my6 with recovery以下是大学体验英语综合教程2(第三版)课后翻译答案Unit11. 任何年满18岁的人都有资格投票。(be eligible to, vote)Answer:Anyone over the age of 18 is eligible to vote.2. 每学期开学前，

33、这些奖学金的申请表格就会由学校发给每一个学生。(apply for, scholarship)Answer:A form to apply for these scholarships is sent by the university to every student before the start of every semester.3. 遵照医生的建议，我决定戒烟。(on the advice of)Answer:On the advice of my doctor, I decided to give up smoking.4. 公园位于县城的正中央。(be located in)A

34、nswer:The park is located right in the center of town.5. 这所大学提供了我们所需的所有材料和设备。(facilities)Answer:The university provides all the materials and facilities we desire.1. 他们花了多年的时间寻找内心的平静，但是收效甚微。(search for)Answer:They spent many years searching for peace of mind, but with little success.2. 这种新药的成功研制已经使许

35、多疾病的治疗发生了根本性的变革。(revolutionize)Answer:The successful development of the new drug has revolutionized the treatment of many diseases.3. 由于这个国家的经济不景气，这家公司濒于破产。(on the edge of)Answer:The company is on the edge of bankruptcy due to the economic depression in the country.4. 大学毕业后他成为了一名护士。他认为护士这一职业可能很有发展前途

36、。(rewarding)Answer:He became a nurse after college. He thought nursing could be a very rewarding career.5. 他像往常一样在文件上签了名。(just as)Answer:He signed his name on the paper just as he has always done it.Unit21. 警察们正忙着填写关于这场事故的各种表格。(fill out)Answer:The policemen are busy fi lling out forms about the acci

37、dent.2. 我想在还车之前把油箱加满。(fill up, fuel tank)Answer:I want to fi ll up the fuel tank before returning the car.3. 如果你要投诉，最好遵循正确的程序。(follow the procedure)Answer:If you want to make a complaint, youd better follow the correct procedure.4. 要不是约翰帮忙，我们绝不会这么快就完成实验。(without)Answer:We couldnt have fi nished the experiment so soon without Johns help.5. 暴风雨之后，岸边的人们焦急地搜索湖面以期发现小船的踪迹。(scan for)Answer:After the stor