SQLServer2000测试题
1请讲出身份验证模式与登录账号的关系及如何用各种账号进行登录，并画出示意图身份验证模式分为两种,一种是windows身份验证,另一种是混合验证.Windows验证模式:SQLSserver数据库通常是运行在NT服务器平台或者基于NT架构的windows2000上,而NT作为网络操作系统,本身就具有管理登陆,验证用户合法性的能力,所以windows认证正是利用这一用户安全性和账号管理的机制,允许SQLSERVER也可以使用NT用户名和密码,在该模式下,用户只要通过windows的认证就可以链接到SQLServer.在混合认证模式下,用户既可以使用NT认证,也可以使用SQLServer认证.,在SQLServer认证模式下用户在连接SQLSserver时必须提供登录名和密码,这些登陆信息存储在系统表syslogins中,与NT的登陆账号无关,SQLServer自己执行认证处理,如果输入的登陆信息与系统表syslogins中的某条记录相匹配,则表明登陆成功.
NT用户名和密码登陆NT系统SQLServerWindows登陆认证
以下是SQLServer认证
NT系统SQLServer账号和密码SQLServer
2请讲出登录账号、数据库用户及数据库角色之间的关系，并画出示意图当尝试登陆到服务器是，SQLSERVER就验证登陆帐号，当尝试连接数据库是，SQLSERVER验证数据库用户，数据库角色中包括数据库用户。3请讲出数据库用户、数据库角色与数据库对象之间的关系，并画出直接对用户授权与间接对用户授权（系统权限与对象权限）的方法
数据库用户用来指出那一个人可以访问哪个数据库，是对用户直接授权，而数据库角色是数据库直接授权给数据库角色，再把数据库角色指派给数据库用户，而不用直接对数据库用户直接授权，数据库对象是指数据库用户或角色可以访问指定数据库中全部或者部分表，视图等数据库对象的权限。4请讲出服务器角色、数据库角色、标准角色与应用程序角色的区别与验证其权限的方法所有预定义的数据库角色和管理者自定义的某一角色都是标准角色，应用程序角色是应用程序间接地存取数据库中数据而使用的角色，应用角色不具有组的角色。5请讲出数据库还原模型对数据库的影响简单还原模型：将数据库还原到上次备份时点完全恢复模型：提供将数据库恢复到故障点或特定时点的能力大容量日志记录恢复模型：提供对媒体故障的防范。6有一个执行关键任务的数据库，请设计一个数据库备份策略具体情况具体分析，如果数据库的大小不是很大，数据更新速度不是很频繁，就使用完全备份，每天一次，如果数据库很大，数据更新频繁，则可以使用差异备份。7请使用檔与档组恢复的方式恢复数据库略8请使用事务日志恢复数据库到一个时间点略9请设计作业进行周期性的备份数据库用SQLSERVER代理来运行周期性备份作业10如何监控数据库的阻塞，并实现数据库的死锁测试设置查询超时来检测数据库的阻塞，死锁由一个称为锁监视器的线程的单独线程来执行。11如何监控数据库的活动，并能使用索引优化向导生成索引略12理解数据库框图的作用并可以设计表与表之间的关系略
二SQLSERVER的实现部分
1有订单表，需要实现它的编号，格式如下：200211030001……200222039999等Replace(substring(convert(varchar(20),getdate(),120),1,10),’-’,’‘)+max(订单号)+12有表T1,T2，现有一事务，在向表T1添加数据时，同时也必须向T2也添加数据，如何实现该事务BegintransacitonInsertintotable1,insertintotable2CommittransactionExceptrollbacktransaction3如何向T1中的编号字段（codevarchar(20)）添加一万条记录，不充许重复，规则如下：编号的数据必须从小写的a-z之间取值将编号字段设为主键，并设置规则。4如何删除表中的重复数据,请使用光标与分组的办法Deletefromaawhererowidin(Selectrowidfromaawhererowidnotin(Selectmax(rowid)fromaagroupbyahavingcount(a)>1)andin(Selectafromaagroupbyahavingcount(a)>1))5如何求表中相邻的两条记录的某字段的值之差略6如何统计数据库中所有用户表的数据，显示格式如下：表名sales略7如何删除数据库中的所有用户表（表与表之间有外键关系）略8表Aeditor_id123123lb2_id000003记录数23
123456456表Blb2_id000003006007显示ab
003007006lb2_nameabcd共1条（表A内lb2_id为000的条数）共2条（表A内lb2_id为003的条数）
Selectcount(a.lb2_id)fromAJoinBOnA.lb2_id=B.lb2_idwhere(B.lb2_id=’000’)9人员情况表（employee）:里面有一字段文化程度（wh）：包括四种情况（本科以上，大专，高中，初中以下）,现在我要根据年龄字段查询统计出：表中文化程度为本科以上，大专，高中，初中以下，各有多少人，占总人数多少。SELECTwhAS学历,ageas年龄,Count(*)AS人数,Count(*)*100/(SELECTCount(*)FROMemployee)AS百分比FROMemployeeGROUPBYwh,age学历年龄人数34333310050百分比1413134020
本科以上20大专高中2020
初中以下20本科以上21
10现在有三个表student:（FID学生号，FName），subject:（FSubID课程号，FSubName课程名）,
Score(FScoreId成绩记录号,FSubID怎么能实现这个表：英语数学语文历史张萨78王强89676789847696
课程号,FStdID
学生号,FScore
成绩)
SELECTa.FNameAS,英语=SUM(CASEb.FSubNameWHEN'英语'THENc.FScoreEND),数学=SUM(CASEb.FSubNameWHEN'数学'THENc.FScoreEND),语文=SUM(CASEb.FSubNameWHEN'语文'THENc.FScoreEND),历史=SUM(CASEb.FSubNameWHEN'历史'THENc.FScoreEND)FROMStudenta,Subjectb,ScorecWHEREa.FID=c.FStdIdANDb.FSubID=c.FsubIDGROUPBYa.FName
11原始表的资料如下：PIDPTime111111111111222222333333111111222222333333PNo
2003-01-2804:30:092003-01-2818:30:002003-01-2804:31:092003-01-2804:32:092003-02-0903:35:252003-02-0903:36:252003-02-0903:37:25
查询生成表PDate2003-01-282003-01-2811111104:30:0918:30:0022222204:31:09333333............
04:32:09
2003-02-09
03:35:25
03:36:25
03:37:25
......
ifexists(select*fromsysobjectswherename='crosstab'andtype='P')dropprocedurecrosstabgocreateprocedurecrosstab--定义参数@strtabvarchar(50),@strgroupvarchar(50),@strcasevarchar(50),@strthenvarchar(50),@strfirvarchar(50)asdeclare@strsqlvarchar(1000),@strtmpvarchar(1000)set@strsql='select'+@strfir--定义游标declarecross_cursorcursorforselectdistinctsubnamefromcrosstable--打开游标opencross_cursorwhile(0=0)beginfetchnextfromcross_cursorinto@strtmpif(@@fetch_status<>0)breakset@strsql=@strsql+',sum(case'+@strcase+'when'''+@strtmp+'''then'+@strthen+'else0end)as'''+@strtmp+''''print@strtmpend
set@strsql=@strsql+'from'+@strtab+'groupby'+@strgroupprint@strsqlexecute(@strsql)closecross_cursordeallocatecross_cursor
12表一(AAA)商品名称mc商品总量slAB100120
表二(BBB)商品名称mc出库数量slAABBB1020102030
用一条SQL语句算出商品A,B目前还剩多少？一declare@AAAtable(商品名称varchar(10),商品总量int)insertinto@AAAvalues('A',100)insertinto@AAAvalues('B',120)
declare@BBBtable(商品名称varchar(10),出库数量int)insertinto@BBBvalues('A',10)insertinto@BBBvalues('A',20)insertinto@BBBvalues('B',10)
insertinto@BBBvalues('B',20)insertinto@BBBvalues('B',30)
selectTA.商品名称,A-BAS剩余数量FROM(select商品名称,sum(商品总量)ASAfrom@AAAgroupby商品名称)TA,(select商品名称,sum(出库数量)ASBfrom@BBBgroupby商品名称)TBwhereTA.商品名称=TB.商品名称二select商品名称,sum(商品总量)剩余数量from(select*from@aaaunionallselect商品名称,-出库数量from@bbb)agroupby商品名称
13优化这句SQL语句UPDATEtblExlTempYearSETtblExlTempYear.GDQC=tblExlTempMonth.GDQCFROMtblExlTempYear,tblExlTempMonthwheretblExlTempMonth.GDXM=tblExlTempYear.GDXMandtblExlTempMonth.TXDZ=tblExlTempYear.TXDZ
（1）、加索引：tblExlTempYear（GDXM，TXDZ）tblExlTempMonth（GDXM，TXDZ）（2）、删除无用数据（3）、转移过时数据（4）、加服务器内存，升级服务器
（5）、升级网络系统
UPDATEtblExlTempYearSETtblExlTempYear.GDQC=tblExlTempMonth.GDQCFROMtblExlTempYear(indexindexY),tblExlTempMonth(indexindexM)wheretblExlTempMonth.GDXM=tblExlTempYear.GDXMandtblExlTempMonth.TXDZ=tblExlTempYear.TXDZ
14品种
日期
数量
P00012002-1-1010P00012002-1-1011P00012002-1-1050P00012002-1-12P00012002-1-12P00012002-1-12P00022002-10-109875
P00022002-10-107P00022002-10-120.5P00032002-10-105P00032002-10-127P00032002-10-129
结果要先按照品种汇总,再按照日期汇总,结果如下:P00012002-1-10P0001P0002P0002P00032002-1-127124
2002-10-10122002-10-120.52002-10-105
P0003
2002-10-1216
SQLSERVER能做出这样的汇总吗…Selectpid,pdate,sum(pcount)fromtablegroupbypid,pdateorderbypid
15在分组查循中with{cube|rollup}的区别是什么?如:usepanguselectfirm_id,p_id,sum(o_price_quantity)assum_valuesfromordersgroupbyfirm_id,p_idwithcube与usepanguselectfirm_id,p_id,sum(o_price_quantity)assum_valuesfromordersgroupbyfirm_id,p_idwithrollup的区别是什么?
CUBE和ROLLUP之间的区别在于：CUBE生成的结果集显示了所选列中值的所有组合的聚合。ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚合。例如，简单表Inventory中包含：ItemColorQuantity
-----------------------------------------------------------------TableTableBlueRed124223
ChairChair
BlueRed
101210
下列查询将生成小计报表：SELECTCASEWHEN(GROUPING(Item)=1)THEN'ALL'ELSEISNULL(Item,'UNKNOWN')ENDASItem,CASEWHEN(GROUPING(Color)=1)THEN'ALL'ELSEISNULL(Color,'UNKNOWN')ENDASColor,SUM(Quantity)ASQtySumFROMInventoryGROUPBYItem,ColorWITHROLLUP
Item
Color
QtySum
-----------------------------------------------------------------ChairChairChairTableTableTableALLBlueRedALLBlueRedALLALL101.00210.00311.00124.00223.00347.00658.00
(7row(s)affected)
如果查询中的ROLLUP关键词更改为CUBE，那么CUBE结果集与上述结果相同，只是在结果集的末尾还会返回下列两行：ALLALLBlueRed225.00433.00
CUBE操作为Item和Color中值的可能组合生成行。例如，CUBE不仅报告与Item值Chair相组合的Color值的所有可能组合（Red、Blue和Red+Blue），而且报告与Color值Red相组合的Item值的所有可能组合（Chair、Table和Chair+Table）。对于GROUPBY子句中右边的列中的每个值，ROLLUP操作并不报告左边一列（或左边各列）中值的所有可能组合。例如，ROLLUP并不对每个Color值报告Item值的所有可能组合。ROLLUP操作的结果集具有类似于COMPUTEBY所返回结果集的功能；然而，ROLLUP具有下列优点：ROLLUP返回单个结果集；COMPUTEBY返回多个结果集，而多个结果集会增加应用程序代码的复杂性。ROLLUP可以在服务器光标中使用；COMPUTEBY不可以。有时，查询优化器为ROLLUP生成的执行计划比为COMPUTEBY生成的更为高效。
16假如我有两个表表1（电话号码，是否存在）表2（电话号码，是否拨打）想查找表1中的电话号码是否在表2中存在，如果存在就更新表1中的是否存在字段为1。
UPDATE表1SET是否存在=1WHEREEXISTS(SELECT*FROM表2WHERE表2.电话号码=表1.电话号码)17用存储过程调用外部程序.做成com控件用sp_OACreate存储过程)DECLARE@objectintDECLARE@hrintDECLARE@srcvarchar(255),@descvarchar(255)
EXEC@hr=sp_OACreate'SQLDMO.SQLServer',@objectOUTIF@hr<>0BEGINEXECsp_OAGetErrorInfo@object,@srcOUT,@descOUTSELECThr=convert(varbinary(4),@hr),Source=@src,Description=@descRETURNEND
