《数据库技术与应用-SQL-SERVER》课后标准答案.doc

数据库技术与应用-SQL-SERVER课后答案 作者： 日期：数据库技术与应用-SQL SERVER2005刘卫国 熊拥军主编习题参考答案目录第一章 数据库系统概论一、选择题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 score35-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_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=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 value15000open 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(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 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,salesuse salessp_grantdbaccess kwh,kwh2sp_addrolemember db_datareader,kwh2sp_revokedbaccess kwh2sp_droplogin kwh第十二章 数据库的备份与还原一、选择题ABAA二、填空题1、磁盘备份设备、磁带设备、命名通