《数据库技术与应用-SQL SERVER2005》课后答案.doc 17页

'《数据库技术与应用-SQLSERVER2005》习题参考答案编辑整理：江枫《数据库技术与应用-SQLSERVER2005》刘卫国熊拥军主编习题参考答案目录第一章数据库系统概论一、选择题ABDAD二、填空题载体、意义；数据集合；数据库管理系统；数据库系统；层次、网状、关系、关系模型第二章关系数据库基本原理一、选择题DDACBD二、填空题1.实体完整性、参照完整性、用户自定义完整性。2.（A,B）R1(A,D)和R2(A,B,C).3.元子特性4.外键5.F1＝（A→B，A→C，A→D）．6.选择-17- 《数据库技术与应用-SQLSERVER2005》习题参考答案编辑整理：江枫第三章SQLSERVER2005系统基础一、选择题ACBCB二、填空题1.企业版，标准版，开发版，工作组版，精简版2.WINDOWs身份验证模式和混合模式3.SQLServer管理平台、SQLServer配置管理器4.已注册服务器窗口、对象资源管理器，文档窗口5.数据查询语言，数据定义语言，数据操纵语言和数据控制语言第四章数据库的管理一、选择题ABDAC二、填空题1、数据文件和事务日志文件2、master,tempdb,model,msdb和mssqlsystemresource3、数据文件，事务日志文件和文件组4、55、createdatabase,alterdatabase,dropdatabase四、应用题1createdatabasesalesonprimary(name=saldat01,filename="c:dbsaldat01.mdf"),(name=saldat02,filename="c:dbsaldat02.ndf"),filegroupfilegrp1(name=saldat11,filename="d:dbsaldat11.ndf"),-17- 《数据库技术与应用-SQLSERVER2005》习题参考答案编辑整理：江枫(name=saldat12,filename="d:dbsaldat12.ndf"),(name=saldat13,filename="d:dbsaldat13.ndf")2alterdatabasesalesaddlogfile(name=sallog2,filename="c:dbsallog2.ldf")3alterdatabasesalesaddfile(name=saldat03,filename="c:dbsaldat03.ndf",size=5,filegrowth=20%)扩展alterdatabasesalesaddfile(name=saldat14,filename="d:dbsaldat14.ndf",size=5,filegrowth=20%)tofilegroupfilegrp14alterdatabasesalessetsingle_user5dropdatabasesales第五章表的管理一、选择题CACCC二、填空题1、-2²¹-2²¹-1，0-255.2、输入存储字段小于100时按原字段存，大于100时截取100位。8个字节。3、日期时间数据类型，数字数据类型4、表名和字段名称5、insert，update，truncate或delete。-17- 《数据库技术与应用-SQLSERVER2005》习题参考答案编辑整理：江枫四、应用题--创建数据库createdatabasesales---4.1---标识符列（自动增长的列）--identity(种子，增量)createtablesell_order(order_id1intidentity(1,2),goods_idchar(6)notnull,employee_idchar(4)notnull,custmer_idchar(4)notnull,transporter_idchar(4)notnull,order_numfloat,discountfloat,order_datedatetime,send_datedatetime,arrival_datedatetime,costmoney)---删除标识符列altertablesell_orderdropcolumnorder_id1--添加标识符列altertablesell_orderaddorder_id1intidentity(1,2)----4.2altertablesell_order--dropcolumnsend_dateadd发货日期datetime----4.3---标识列自动增长insertsell_order(goods_id,employee_id,custmer_id,order_num,discount,order_date)values("135","16","99",30,9.5,"2009-2-26")insertsell_ordervalues("135","16","99","",30,9.5,"2009-2-26","","","")--为空和null不同insertsell_ordervalues("135","16","99",null,30,9.5,"2009-2-26",null,null,null)-17- 《数据库技术与应用-SQLSERVER2005》习题参考答案编辑整理：江枫--允许手动增长（显示声明）setidentity_insertsales.dbo.sell_orderoninsertsell_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")setidentity_insertsales.dbo.sell_orderoff----4.4insertsell_ordervalues("26","02","6","10",200,8,"2008-10-10","2008-12-12",200000,"2008-12-1")----4.5insertsell_ordervalues("26","29","100","10",200,8,"2009-1-1","2008-12-12",null,"2008-12-1")updatesell_ordersetemployee_id="16"where(employee_id="29"andcostisnull)----4.6updatesell_ordersetdiscount=discount*0.9where(custmer_id="100"andcostisnull)----4.7deletefromsell_orderwhereorder_date="2009-1-1"第六章数据查询一、选择题CABBC二、填空题1、TOP/PERCENT2、UNION，查询数据/结果集3、嵌套查询/子查询4、内链接，外连接5、等值连接，自然连接6、into-17- 《数据库技术与应用-SQLSERVER2005》习题参考答案编辑整理：江枫四、应用题--128页应用题第一题createdatabasestudent2createtablestudent(s_nochar(8),s_namechar(10),s_sexchar(2),birthdaydatetime,politychar(8))insertintostudentvalues("s003","江鱼","女","2003-01-01","国民党")createtablesco(s_nochar(8),c_nochar(8),scorefloat)insertintoscovalues("s003","数据结构",85)--1-1select*fromstudentorderbys_no--1-2select*fromstudentwheres_sex="女"orderbys_sexcomputecount(s_sex)--1-3selects_name,birthday,year(getdate())-year(birthday)as年龄fromstudentwheres_sex="男"selects_name,convert(varchar,birthday,23),year(getdate())-year(birthday)as年龄fromstudentwheres_sex="男"--selectconvert(varchar,getdate(),23)只截取系统当前日期--selectconvert(varchar,getdate(),8)只截取系统当前时间--1-4selects_name,birthday,year(getdate())-year(birthday)as年龄,c_no,scorefromstudent,scowherestudent.s_no=sco.s_noselects_name,birthday,year(getdate())-year(birthday)as年龄,c_no,scorefromstudentinnerjoinscoonstudent.s_no=sco.s_no--1-5selectscorefromscowheres_no=(selects_nofromstudentwheres_name="江鱼")selectsco.score,student.s-namefromscoinnerjoinstudentonsco.s_no=student.s_nowherestudent.s_name="江于"-17- 《数据库技术与应用-SQLSERVER2005》习题参考答案编辑整理：江枫--1-6selects_namefromstudentwheres_noin(selects_nofromscowherescore<60)--1-7selects_sex,avg(score)fromstudentinnerjoinscoonsco.s_no=student.s_nogroupbys_sex--128页应用题第二题createdatabaserscreatetable部门(部门号char(8),部门名char(10),负责人char(8),电话char(10))insertinto部门values("b001","教育学院","叶浩生","1338888888")createtable职工(职工号char(8),部门号char(8),姓名char(8),性别char(2),出生日期datetime)insertinto职工values("z002","b001","孔维宏","男","1962-10-27")createtable工资(职工号char(8),基本工资money,津贴money,奖金money,扣除money)insertinto工资values("z001",1300,500,800,600)--2-1select职工.职工号,姓名,(基本工资+津贴+奖金-扣除)as实发工资from职工innerjoin工资on职工.职工号=工资.职工号--2-2select*from职工where出生日期="1962-10-27"select职工号,姓名,性别,convert(varchar,出生日期,23)as出生时间from职工where-17- 《数据库技术与应用-SQLSERVER2005》习题参考答案编辑整理：江枫出生日期="1962-10-27"select职工号,姓名,性别,convert(varchar,出生日期,23)as出生时间,部门名from职工innerjoin部门on职工.部门号=部门.部门号and出生日期="1962-10-27"select职工号,姓名,性别,convert(varchar,出生日期,23)as出生时间,部门名from职工innerjoin部门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部门innerjoin职工on职工.部门号=部门.部门号innerjoin工资on职工.职工号=工资.职工号where出生日期="1962-10-27"--2-3SELECT部门名,MIN(出生日期)FROM部门JOIN职工ON部门.部门号=职工.部门号GROUPBY部门名--2-4select姓名,性别,(year(getdate())-year(出生日期))as年龄from职工where(year(getdate())-year(出生日期))>35--2-5select部门名,count(职工号)as职工人数from职工,部门where部门.部门号=职工.部门号groupby部门名havingcount(*)>=2orderbycount(*)descselect部门名,count(*)as职工人数from职工innerjoin部门on部门.部门号=职工.部门号groupby部门.部门名havingcount(*)>=1orderbycount(*)desc-17- 《数据库技术与应用-SQLSERVER2005》习题参考答案编辑整理：江枫第七章索引与视图一、选择题CCADB二、填空题1、altertable,dropindex2、primarykey,unique3、视图4、定义5、计算列6、修改，删除第八章数据完整性一、选择题BCCAD二、填空题1、行2、createrule和createdefault3、唯一性4、primarykey，多5、检查，唯一性6、constraint，约束名四、应用题--第八章应用题--1altertablesell_orderaddorder_id1intidentity(1,2)altertablesell_order-17- 《数据库技术与应用-SQLSERVER2005》习题参考答案编辑整理：江枫dropcolumnorder_id1--2altertablesell_orderaddprimarykey(order_id1)--3usesalesaltertablecustomeraddprimarykey(customer_id)altertablesell_orderaddforeignkey(custom_id)referencescustomer(customer_id)ondeletecascadeonupdatecascade--4altertablecustomeraddconstraintkkunique(customer_name)--5altertablesell_orderaddconstraintk3default(0)fordiscount--6altertablesell_orderaddconstraintk4check(order_date>send_date),check(send_date>arriver_date)第九章T-SQL程序设计一、选择题BCCDD二、填空题1、局部2、44,82,1263、gf4、/**/5、单条，begin---end6、break,continue7、declarecursor,select四、应用题--4.1.1-17- 《数据库技术与应用-SQLSERVER2005》习题参考答案编辑整理：江枫SELECTgoods_nameas商品名称,CASEWHENclassification_id="P001"THEN"笔记本计算机"WHENclassification_id="P002"THEN"激光打印机"WHENclassification_id="P003"THEN"喷墨打印机"WHENclassification_id="P004"THEN"交换机"ENDAS商品类别,unit_priceAS单价,stock_quantityas库存FROMgoods--4.1.2declare@valuerealset@value=-1while@value<2beginselectsign(@value)select@value=@value+1end--4.1.3declareemployee_cursorcursorforselectt.employee_id,s.costfromemployeetjoinsell_ordersont.employee_id=s.employee_idwheres.cost>15000openemployee_cursorFETCHNEXTFROMemployee_cursorWHILE@@fetch_status=0BEGINFETCHNEXTFROMemployee_cursorEND--4-2selectemployee_name,case(datediff(yy,birth_date,getdate())-1)/10when2then"20到30岁"when3then"30到40岁"when4then"40到50岁"else"50岁以上"endas年龄段,department_nameas所属部门fromdepartment,employeewheredepartment.department_id=employee.department_idorderby所属部门asc-17- 《数据库技术与应用-SQLSERVER2005》习题参考答案编辑整理：江枫selectnldas年龄段,count(*)as人数from(selectcase(datediff(yy,birth_date,getdate())-1)/10when2then"20到30岁"when3then"30到40岁"when4then"40到50岁"else"50岁以上"endasnld,birth_datefromemployee)aGROUPBYnld--4-3createfunctionfact2(@xint)returnsintasbeginif@x=1return1return@x*dbo.fact2(@x-1)endselectdbo.fact2(6)--4-4declare@iint,@jint,@sumintselect@i=1,@j=0,@sum=0while(@i<100)beginif(@i%7=0)beginset@j=@j+1set@sum=@sum+@iendset@i=@i+1endselect@j,@sum--4-5declarecur_employeecursorforselectsex,count(*)fromemployeegroupbysexdeclare@sexchar(2),@sex_numint-17- 《数据库技术与应用-SQLSERVER2005》习题参考答案编辑整理：江枫opencur_employeefetchnextfromcur_employeeinto@sex,@sex_numwhile@@fetch_status=0beginselect@sexas性别,@sex_numas人数fetchnextfromcur_employeeinto@sex,@sex_numendclosecur_employeedeallocatecur_employee第十章存储过程和触发器一、选择题CAADA二、填空题1、sp_2、存储过程3、committransaction,rollbacktransaction4、数据封锁机制四、应用题--1CREATEPROCprStoreOrderID@Order_id1char(6),@goods_idchar(6),@employee_idchar(4),@Custom_IDchar(5),@Transporter_IDchar(4),@order_datedatetime,@order_id2char(6)OUTPUTASBEGININSERTINTOsell_order(Order_ID1,goods_id,employee_id,Custom_ID,Transporter_ID,order_date)-17- 《数据库技术与应用-SQLSERVER2005》习题参考答案编辑整理：江枫VALUES(@Order_id1,@goods_id,@employee_id,@Custom_ID,@Transporter_ID,@order_date)SELECT@order_id2=Order_ID1FROMsell_orderWHEREOrder_ID1=@Order_id1ENDDECLARE@order_id2char(6)EXECprStoreOrderID"S00009","G00005","e010","C0009","T010","2013.01.01",@order_id2OUTPUTSELECT@order_id2--2createprocchapter10_4_2asbegincreatetable#temp(idint)declare@nintselect@n=1while(@n<=100)begininsert#tempvalues(@n)select@n=@n+1endselect*from#tempendexecchapter10_4_2--3createprocprupdatename@g_idchar(6),@g_namevarchar(50)asbeginupdategoodssetgoods_name=@g_namewheregoods_id=@g_idendexecprupdatename"G0008","IBMT51"--4CREATEPROCprTestASSELECT*FROMgoodsIFEXISTS(SELECT*FROMsysobjectsWHEREname="prTest")print"存在此存储过程"ELSE-17- 《数据库技术与应用-SQLSERVER2005》习题参考答案编辑整理：江枫print"不存在此存储过程"--5EXECsp_helptextprTest--6EXECsp_dependsprTest--7EXECsp_rename"prTest","npr_Test"--8DROPPROCnpr_Test第十一章数据库的安全管理一、选择题CDBC二、填空题1、登录账号，角色2、window身份验证模式和混合模式3、对象、语句、隐含4、grant,revoke5、创建新的数据库角色、分配权限给创建的角色、将这个角色授予某个用户四、应用题sp_addlogin"kwh","123456","sales"usesalessp_grantdbaccess"kwh","kwh2"sp_addrolemember"db_datareader","kwh2"sp_revokedbaccess"kwh2"sp_droplogin"kwh"-17- 《数据库技术与应用-SQLSERVER2005》习题参考答案编辑整理：江枫第十二章数据库的备份与还原一、选择题ABAA二、填空题1、磁盘备份设备、磁带设备、命名通道设备2、backupdatabaserestoredatabase3、完全、事务日志、差异、文件和文件组4、简单恢复模型、完全恢复模型、大容量日志记录恢复模型四、应用题--1.以文件形式(临时备份设备)进行备份与还原数据库backupdatabasesalestodisk="d:backmysales1.bak"restoredatabasesalesfromdisk="d:backmysales1.bak"--2.逻辑备份设备execsp_addumpdevice"disk","my2","d:backmysales2.bak"backupdatabasesalestomy2restoredatabasesalesfrommy2--覆盖现有的数据库restoredatabasesalesfrommy2withreplace--3.差异备份execsp_addumpdevice"disk","my3","d:backmysales3.bak"backupdatabasesalestomy3execsp_addumpdevice"disk","my4","d:backmysales4.bak"backupdatabasesalestomy4withdifferentialrestoredatabasesalesfrommy3withnorecoveryrestoredatabasesalesfrommy4withrecovery--4.日志备份与还原execsp_addumpdevice"disk","my5","d:backmysales5.bak"execsp_addumpdevice"disk","my6","d:backmysales6.bak"backupdatabasesalestomy5backuplogsalestomy6-17- 《数据库技术与应用-SQLSERVER2005》习题参考答案编辑整理：江枫restoredatabasesalesfrommy5withnorecoveryrestorelogsalesfrommy6withrecovery-17-'