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

数据库技术与应用-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 -第七章 索引与视图- 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 -第一章 数据库系统概论一、选择题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. 已注册服务器窗口、对象资源管理器，文档窗口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:dbsaldat02.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,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，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 datetime,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,order_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_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 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四、应用题-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 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 convert(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 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 score<60)-1-7select s_sex,avg(score) from student inner join sco on sco.s_no=student.s_no group by s_sex-128页应用题第二题create database rscreate table 部门( 部门号 char(8), 部门名 char(10), 负责人 char(8), 电话 char(10)insert into 部门 values('b001','教育学院','叶浩生','1338888888')create table 职工( 职工号 char(8), 部门号 char(8), 姓名 char(8), 性别 char(2), 出生日期 datetime)insert into 职工 values('z002','b001','孔维宏','男','1962-10-27')create table 工资( 职工号 char(8), 基本工资 money, 津贴 money, 奖金 money, 扣除 money)insert into 工资 values('z001',1300,500,800,600)-2-1select 职工.职工号,姓名,(基本工资+津贴+奖金-扣除) as 实发工资 from 职工 inner join 工资 on 职工.职工号=工资.职工号-2-2select * from 职工 where 出生日期='1962-10-27'select 职工号,姓名,性别,convert(varchar,出生日期,23) as 出生时间 from 职工 where 出生日期='1962-10-27'select 职工号,姓名,性别,convert(varchar,出生日期,23) as 出生时间,部门名 from 职工 inner join 部门 on 职工.部门号=部门.部门号 and 出生日期='1962-10-27'select 职工号,姓名,性别,convert(varchar,出生日期,23) as 出生时间,部门名 from 职工 inner join 部门 on 职工.部门号=部门.部门号 where 出生日期='1962-10-27'select 职工号,姓名,性别,convert(varchar,出生日期,23) as 出生时间,部门名 from 职工,部门 where 职工.部门号=部门.部门号 and 出生日期='1962-10-27'select 职工.职工号,姓名,性别,convert(varchar,出生日期,23) as 出生时间,部门名,(基本工资+津贴+奖金-扣除) as 实发工资 from 职工,部门,工资 where 职工.职工号=工资.职工号 and 职工.部门号=部门.部门号 and 出生日期='1962-10-27'select 职工.职工号,姓名,性别,convert(varchar,出生日期,23) as 出生时间,部门名,(基本工资+津贴+奖金-扣除) as 实发工资 from 部门 inner join 职工 on 职工.部门号=部门.部门号 inner join 工资 on 职工.职工号=工资.职工号 where 出生日期='1962-10-27'-2-3SELECT 部门名,MIN(出生日期) FROM 部门 JOIN 职工 ON 部门.部门号=职工.部门号 GROUP BY 部门名-2-4select 姓名,性别,(year(getdate()-year(出生日期) as 年龄 from 职工 where (year(getdate()-year(出生日期)>35-2-5select 部门名,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, unique3、视图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 table 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 sell_orderadd constraint k4 check (order_date>send_date),check (send_date>arriver_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='P002' 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 value<2begin select sign(value) select value=value+1end-4.1.3declare employee_cursor cursorforselect t.employee_id,s.costfrom employee t join sell_order s on t.employee_id=s.employee_idwhere s.cost>15000open employee_cursorFETCH NEXT FROM employee_cursorWHILE fetch_status = 0 BEGIN FETCH NEXT FROM employee_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 年龄段,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.fact2(6)-4-4 declare i int,j int,sum int select i = 1, j = 0,sum=0 while(i<100) 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 cur_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 prStoreOrderIDOrder_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,order_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<=100) 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(SELECT * 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_addlogin 'kwh','123456','sales'use salessp_grantdbaccess 'kwh','kwh2'sp_addrolemember 'db_datareader','kwh2'sp_revokedbaccess 'kwh2'sp_droplogin 'kwh'第十二章 数据库的备份与还原一、选择题ABAA二、填空题1、磁盘备份设备、磁带设备、命名通道设备2、backup database restore database3、完全、事务日志、差异、文件和文件组4、简单恢复模型、完全恢复模型、大容量日志记录恢复模型四、应用题-1.以文件形式(临时备份设备)进行备份与还原数据库backup database sales to disk='d:backmysales1.bak'restore database sales from disk='d:backmysales1.bak'-2.逻辑备份设备exec sp_addumpdevice 'disk','my2','d:backmysales2.bak'backup database sales to my2restore database sales from my2-覆盖现有的数据库restore database sales from my2 with replace-3.差异备份exec sp_addumpdevice 'disk','my3','d:backmysales3.bak'backup database sales to my3exec sp_addumpdevice 'disk','my4','d:backmysales4.bak'backup 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.bak'exec sp_addumpdevice 'disk','my6','d:backmysales6.bak'backup 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. 每学期开学前，这些奖学金的申请表格就会由学校发给每一个学生。(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)Answer: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. 这种新药的成功研制已经使许多疾病的治疗发生了根本性的变革。(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. 大学毕业后他成为了一名护士。他认为护士这一职业可能很有发展前途。(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 accident. 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