SQLServer2000实用教程(第三版)习题参考答案

SQLServer20001SQLServer1A2.C3.B4.B5.D6.C7.A8.C123451n(n:1)n:m671DBA2NULLSQLServer2000331.3.121CB2.DA3.B4.BC5.C6.BD112346212481mastertempdbmodel2(T)F53.sql4(W)(C)5ALTERDATABASEREMOVEFILE631D2.BAD3.C4.D5.B6.A7.C8.B9.B10.A(7)113457234561112imagesql_variant3dicimal(7,2)numeric(7,2)4timestamp561249710089ALTERTABLEteacherADDbirthdaysmalldatetimeNULL10UPDATEcourseSETc_score=c_score+1WHEREc_name=12CHECK1CHECKCREATETABLEALTERTABLE2CHECK3CHECK43timestamptimestamptimestamptimestamp4DELETEDROPTABLE52496010010041B2.D3.D4.B5.D6.D7.C8.C9.C10.CD1DISTINCTALL2ORDERBY3FROMWHERE456%_[]7UNION8EXISTSNOTEXISTS91010241USEteachdbSELECTc_nameAS,c_scoreASFROMcourseWHEREc_nameLIKE'%%'2USEteachdbSELECTs_nameAS,s_departmentAS,''AS'',YEAR(GETDATE())-YEAR(s_birthday)ASFROMstudentWHEREs_birthday='1982-10-01'ORDERBYs_birthdays_departmentt3USEteachdbSELECTt_name,c_noINTOinquireFROMteacherJOINteachingONteacher.t_no=teaching.t_no4USEteachdbSELECTc_nameAS,s_noASFROMchoiceRIGHTJOINcourseONchoice.c_no=course.c_nos_no5USEteachdbGOCREATEVIEWV_EM1ASSELECTc_nameAS,s_noASFROMchoiceRIGHTJOINcourseONchoice.c_no=course.c_no6USEteachdbSELECT=c_name,=s_name,=scoreFROMcourse,choice,studentWHEREcourse.c_no=choice.c_noANDstudent.s_no=choice.s_noANDcourse.c_name=''ANDchoice.score(selectAVG(score)FROMchoice,courseWHEREchoice.c_no=course.c_noANDcourse.c_name='')7USEtest1SELECTAS,ASFROMprojectRIGHTJOINemployeesONproject.=employees.USEtest1SELECTAS,ASFROMproject,employeesWHEREproject.=*employees.10test1238USEtest1SELECT,FROMemployeesWHEREISNOTNULLORDERBYDESCCOMPUTEAVG()9USEtest1SELECTTOP1,,FROMproject,employeesWHEREproject.=employees.ORDERBYDESC10USEtest1GOCREATEVIEWV_EM2WITHENCRYPTIONASSELECTTOP100PERCENT,FROMemployeesWHEREISNOTNULLORDERBYDESCSELECTTOP100PERCENT100%SQLServer2000,TOPORDERBY5Transact-SQL1A2.C3.C4.CD5.D6.BC7.D8.D9.DC10.B1234521234571DECLARE@XMONEYSET@X=500SELECT@X=50023.1423219.0045mastersp_67CREATEPROCEDUREWITHRECOMPILEEXECUTEWITHRECOMPILE8AFTERINSTEADOF9inserteddeleteddeletedinserted10ALTERTABLEOrdersDISABLETRIGGEROrderInsert1@@xSQLServerSQLServer@@xSQLServerSQLServer2SQLServer2000SELECT@@345AFTERDELETEUPDATE1USEteachdbDECLARE@sexCHAR(2)SET@sex=''SELECTs_no,s_nameFROMstudentWHEREs_sex=@sex2DECLARE@avgREALUSEteachdbSELECT@avg=AVG(score)FROMchoiceJOINcourseONchoice.c_no=course.c_noWHEREcourse.c_name=''IF@avg80PRINT'80'3USEteachdbGOIFEXISTS(SELECTnameFROMsysobjectsWHEREname='proc1'ANDtype='P')DROPPROCEDUREproc1GOCREATEPROCEDUREproc1@student_noCHAR(4)ASSELECTstudent.s_no,s_name,c_nameFROMstudent,choice,courseWHEREstudent.s_no=choice.s_noANDcourse.c_no=choice.c_noANDstudent.s_no=@student_noGOEXECproc1101EXECproc11024USEteachdbIFEXISTS(SELECTnameFROMsysobjectsWHEREname='trig1'ANDtype='TR')DROPTRIGGERtrig1GOCREATETRIGGERtrig1ONteachingAFTERINSERTASIFEXISTS(SELECT*FROMteachingWHEREteaching.t_noNOTIN(SELECTteacher.t_noFROMteacher)ORteaching.c_noNOTIN(SELECTcourse.c_noFROMcourse))BEGINRAISERROR(',',16,1)ROLLBACKTRANSACTIONEND5USEteachdbGOCREATEFUNCTIONsum_m(@mASint)RETURNSintBEGINDECLARE@sint,@iintSET@s=0SET@i=1WHILE@i=@mBEGINSET@s=@s+@iSET@i=@[e-mail]_m(4)AS'1+2+3+4'SELECTdbo.sum_m(100)AS'SUM(1~100)'SELECTdbo.sum_m(-5)61BD2.B3.B4.D5.DB6.A7.D8.A9.AD10.B1WindowsSQLServerWindowsWindowsSQLServer23BUILTIN\Administratorssa4dboguest5mastersysxloginssysprotects1~12867public8msdb9101sp_revokeloginWindowsWindowsWindowssp_grantloginSQLServerWindowsSQLServersp_denyloginSQLServerWindows26.3.1-136.4.34571A2.D3.B4.B5.BC6.BD7.B8.D9.C10.DB12ROLLBACKTRANSACTIONROLLBACKWORK345RIDX6-17/850000910SQLServer1SQLServer2000SQLServer20002SQLServer20003SQLServer2000SQLServer7.2.1-347.3.25WindowsTransactSQLActiveX6SQLServer20008SQLServer12//(ODBC)DSN3ADODB45setA2.ActiveConnection=A16CONNECTIONCOMMAND7Execute(ASPServer,,)8Fieldscount9rs.movenext10HTML1