大三sql课后习题答案

1、第二章3.上机练习题02 程序代码如下：CREATE DATABASE STUDENT1 ON PRIMARY(NAME= STUDENT1_data, FILENAME='E:DATA' , SIZE=3,MAXSIZE=unlimited , FILEGROWTH=15%) LOGON (NAME= STUDENT1_log, FILENAME='E:DATA' , SIZE=2,MAXSIZE=30, FILEGROWTH=2)03 程序代码如下：create database students on primary(name =students1 , f

2、ilename ='E:DATA' , size=5, maxsize=75, filegrowth =10%), (name = students12 , filename ='E:DATA' , size=10, maxsize=75, filegrowth =1) log on (name =studentslog1 , filename ='E:DATA' , size=5, maxsize=30, filegrowth =1), (name =studentslog2 , filename ='E:DATA' , siz

3、e=5,maxsize=30,filegrowth =1)第三章：3 上机练习题01 程序代码如下：-创立表 book 的 Transact-SQL语句：USEtest01GOCREATETABLEbook( book_id nchar(6)NOTNULL, book_name nchar(30) NULL, price numeric(10, 2) NULL,CONSTRAINTPK_book PRIMARYKEYCLUSTERED(book_id ASC)ON PRIMARY-创立表 uthor 的 Transact-SQL语句：CREATETABLE(anthor_name nchar(

4、4) NOTNULL, book_id nchar(6)NOTNULL, address nchar(30) NOTNULL)ON PRIMARY- 设置 book 中的 book_id 为主键, author 表中的 book_id 为外键ALTERTABLE WITH CHECKADD CONSTRAINTFK_ book_author FOREIGNKEY(book_id )REFERENCES(book_id )02 程序代码如下：-利用Transact-SQL语句创立表booksales的代码.USEtest01GOCREATETABLEbooksales( book_id ncha

5、r(6)NOTNULL, sellnum int NOTNULL, selldate datetime NOTNULL)ON PRIMARY-利用insert语句为表booksales添加数据：INSERTINTO booksales VALUES('m00011' ,7,20/12/2021)INSERTINTO booksales(book_id ,sellnum ,selldate ) VALUES('m00017' ,3,17/11/ 2021 ) -利用 update 语句为表 booksales 更新数据：UPDATE booksales SETse

6、llnum =11WHEREbook_id ='m00011'-利用 delete 语句删除表 booksales 的数据：DELETE FROM booksalesWHERE book_id ='m00011'03 程序代码如下：USEtest01GOCREATE RULE sellnum_ruleAS sellnum >=0EXECsp_bindrule 'sellnum_rule' ,''04 程序代码如下：-删除年以前的数据DELETE FROM booksalesWHEREselldate < '1/

7、1/2021'-删除所有数据Truncate Table booksales第四章3 上机练习题01 程序代码如下：DECLARE bookname nchar (16)setbookname = 'SQL Serve数据库编程'02 程序代码如下：USEtest01GOSETNOCOUNT ONDECLAREstartdate datetime ,enddate datetime SETstartdate = '1/7/2021 12:12 AM'SETenddate = '11/10/2021 12:00 AM' SELECTDATE

8、DIFF(year ,startdate ,enddate ) SELECTDATEDIFF(month ,startdate ,enddate ) SELECTDATEDIFF(day,startdate ,enddate ) SELECTDATEDIFF(minute ,startdate ,'1/8/2007 12:17 AM' ) SELECTDATEDIFF(minute ,startdate ,GETDATE() SETNOCOUNT OFFGO03 程序代码如下：DECLAREcount INT,SUM INT SETcount =51SETSUM=0WHILE

9、count <=100BEGINIF (CEILING(count /<= FLOOR(count /)BEGINSETSUM = SUM + count ENDSETcount = count +2ENDPRINT'50到之间的所有能被整除的奇数之和+CASTSUM ASNCHAR4)第五章3 上机练习题01 程序代码如下：-查询course表中的所有记录.use teachinggo select * from course go02 程序代码如下： -查询 student 表中的女生的人数.use teachinggoselect * from studentwher

10、e sex='女'go03 程序代码如下：-查询 teacher 表中每一位教授的教师号、姓名和专业名称.use teachinggoselect teacherno ,tname ,major from teacherwhere prof ='教授'go04 程序代码如下：-按性别分组,求出 student 表中的每组学生的平均年龄.use teachinggoselect sex as '学生性别 ' , avg(DATEDIFF(year,birthday ,getdate () as '平均年龄 from studentgroup

11、 by sexgo05 程序代码如下：-利用现有的表生成新表,新表中包括学号、学生姓名、课程号和总评成绩-其中：总评成绩 =final*+usually*use teachinggoselect ,final*+usually* as'总评'into stu_sorsefrom student ,scorewhere =goselect * from stu_sorse go06 程序代码如下：-统计每个学生的期末成绩平均分.use teachinggoselect studentno ,sname,avg(总评)from stu_sorsegroup by studentno

12、 ,snamego07 程序代码如下：-输岀student表中年龄最大的男生的所有信息.use teachinggoselect * from studentwhere birthday =(select min(birthday )from student )go08 程序代码如下：-查询teacher表中没有职称的职工的教师号、姓名、专业和部门use teachinggoselect teacherno ,tname,major,departmentfrom teacherwhere prof is NULLgo3 上机练习题01 程序代码如下：-查询每一位教授的教师号、姓名和讲授的课程名

13、称use teachinggoselect ,tname ,major , from teacher ,course ,teach_class where prof ='教授 ' and =and =go02 程序代码如下：-利用现有的表生成新表,新表中包括学号、学生姓名、课程名称和总评成绩-其中：总评成绩 =final*+usually*use teachinggoselect DISTINCT ,*+* as'总评'into stu_coursefrom student ,course,teach_class,scorewhere =and =go03 程序

14、代码如下：-统计每个学生的期末成绩高于分的课程门数.use teaching go select ,count(*) as '课程门数 ' from student ,score where> 75and = group by , go04 程序代码如下：-输出 student 表中年龄大于女生平均年龄的男生的所有信息.use teachinggoselect * from student where sex=' 男 'and DATEDIFF(year,birthday ,getdate () >( select avg(DATEDIFF(year

15、,birthday ,getdate () from student where sex= '女 ') go05 程序代码如下：-计算每个学生获得的学分.use teachinggoselect ,sum(credit ) from student INNERJOIN scoreON =INNERJOIN courseON =where >60 group by , go06 程序代码如下：-获取入学时间在年到年的所有学生中入学年龄小于岁的学号、姓名及所修课程的课程名称 use teachinggo select , from student inner join stu

16、_course on =where (substring ,1,2)='08' and (datediff (year,birthday ,'2021-01-01' )<19) or(substring ,1,2)='09' and (datediff (year,birthday ,'2021-01-01' )<19) go07 程序代码如下：-查询级学生的学号、姓名、课程名及学分.use teachinggoselect , from student inner join stu_courseon =where s

17、ubstring,1,2)='09'go08 程序代码如下：Email-查询选修课程的少于门、或期末成绩含有分以下课程的学生的学号、姓名、 和 use teachinggoselect studentno ,count (*) as 'countNUM'into count1 from score group by studentnoGO select ,sname,phone ,Email from student inner join score on = inner join count1 on = where <60 and countNUM &l

18、t;3 go第七章3 上机题练习01 程序代码如下：-在course表的cname列上创立非聚集索引 IDX_cname.USEteachingGOCREATENONCLUSTEREDINDEX IDX_cname ON course(cname)GO02 程序代码如下：USEteachingGOIFEXISTSS(ELECTname FROMsysindexesWHEREname='UQ_stu')DROPINDEXGOCREATENONCLUSTEREDINDEX UQ_stu ON student (studentno ,classno)GOSELECT* FROM st

19、udent03 程序代码如下：USEteachingGOALTERINDEXUQ_stu ON student REBUILDWITH (PAD_INDEX= ON, FILLFACTOR= 80)GO04 程序代码如下：-创立一个视图 v_teacher ,查询所有 “计算机学院 教师的信息.USEteachingGOCREATEVIEW v_teacherASSELECT*FROM teacherWHEREdepartment = '计算机学院 'GOSELECT* FROM v_teacher05 程序代码如下：-创立一个视图v_avgstu,查询每个学生的学号、姓名及平

20、均分,并且根据平均分降序排序.USEteachingGOCREATEVIEW v_avgstuASSELECTTOP(100) PERCENT, , AVGAS'average'FROM student , scoreWHERE = AND IS NOT NULLGROUPBY,ORDERBYAVG DESCGOSELECT* FROM v_avgstu06 程序代码如下：-修改v_teacher的视图定义,添加 WITH CHECK OPTION选项. USEteachingGOALTERVIEW v_teacherASSELECT*FROM teacherWHEREdepa

21、rtment = '计算机学院 'WITH CHECKOPTIONGO07 程序代码如下：-通过视图v_teacher向根本表teacher中分别插入数据'05039','张馨月','计算机应用','讲师','计算 机学院 '和'06018', ' 李诚', '机械制造 ', '副教授 ', '机械学院 ',并查看插入数据情况.USEteachingGOINSERTINTO v_teacher VALUES'05

22、039','张馨月','计算机应用','讲师','计算机学院'INSERTINTO v_teacher VALUES'06018','李诚','机械制造,'副教授','机械学院'GOSELECT* FROM v_teacherSELECT* FROM teacher08 程序代码如下：-通过视图v_teacher将根本表teacher中教师编号为'05039'的教师职称修改为副教授 USEteachingGOUPDATEv_teache

23、rSETprof = '副教授 'WHEREteacherno ='05039'GOSELECT* FROM teacher第八章3 上机练习题01 程序代码如下：-创立一个名称为 StuInfo 的存储过程,要求完成以下功能：-在student表中查询级学生的学号、姓名、性别、岀生日期和 个字段的内容USEteaching-查询是否已存在此存储过程,如果存在,就删除它 IFEXISTS(SELECTname FROMsysobjectsWHEREname = 'StuInfo' AND type = 'P')DROPPROCED

24、UREStuInfoGO-创立存储过程CREATEPROCEDUREStuInfoASSelect studentno ,sname,sex,birthday ,phoneFROM studentWHEREsubstring(studentno ,1,2)='08'GO02 程序代码如下：-创立一个存储过程 ScoreInfo ,-完成的功能是在表 stude nt、表course和表score中查询以下字段：学号、 姓名、性别、课程名称、 期末分数.USEteaching-查询是否已存在此存储过程,如果存在,就删除它IFEXISTS(SELECTname FROMsysobj

25、ectsWHEREname = 'ScoreInfo' AND type ='P')DROPPROCEDUREScoreInfoGO-创立存储过程CREATEPROCEDUREScoreInfoASSelect ,FROM student ,course,scoreWHERE=and=GO03 程序代码如下：-创立一个带有参数的存储过程 Stu_Age,-该存储过程根据输入的学号,在 student 表中计算此学生的年龄,-并根据程序的执行结果返回不同的值,程序执行成功,返回整数,如果执行出错,那么返回错误 号.-删除已存在的存储过程USEteachingIF

26、EXISTS(SELECTname FROM sysobjectsWHEREname = 'Stu_Age' AND type ='P')DROPPROCEDUREStu_AgeGO-创立存储过程USEteachingGOCREATEPROCEDUREStu_AgestudentNO nvarchar (10),Age int OUTPUTAS-定义并初始化局部变量,用于保存返回值DECLAREErrorValue intSETErrorValue =0-求此学生的年龄SELECTAge=YEAR(GETDATE()-YEAR(birthday )FROM st

27、udentWHEREstudentno =studentNO-根据程序的执行结果返回不同的值IF (ERROR<>0)SETErrorValue =ERRORRETURNErrorValueGO04 程序代码如下：- Template generated from Template Explorer using:- Create Trigger (New Menu).SQL- Use the Specify Values for Template Parameters- command (Ctrl-Shift-M) to fill in the parameter- values

28、below.- See additional Create Trigger templates for more- examples of different Trigger statements.- This block of comments will not be included in- the definition of the function.SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGO - Author: Name- Create date:- Description:CREATETRIGGERONAFTERINSERTASBEGINDEC

29、LAREmsg nchar(30)SET msg='你插入了一条新记录!PRINTmsgENDGO05 程序代码如下：-创立一个AFTER触发器,要求实现以下功能：-在score表上创立一个插入、更新类型的触发器TR_ScoreCheck,-100之间-当在score字段中插入或修改测试分数后,触发该触发器,检查分数是否在USEteachingGOCREATETRIGGERTR_ScoreCheckON scoreFORINSERT, UPDATEASIFUPDATE(final )PRINT'AFTER触发器开始执行 'BEGINDECLAREScoreValue r

30、ealSELECTScoreValue=(SELECTfinal FROM inserted )IF ScoreValue>100 OR ScoreValue<0PRINT'输入的分数有误,请确认输入的测试分数！ENDGO06 程序代码如下：-创立一个INSTEAD OF触发器,要求实现以下功能：-在course表上创立一个删除类型的触发器TR_NotAllowDelete ,-当在course表中删除记录时,触发该触发器,显示不允许删除表中数据的提示信息.USEteachingGOIF EXISTSS( ELECTname FROM sysobjectsWHEREname

31、 ='TR_NotAllowDelete' AND type = 'TR') DROPTRIGGERTR_NotAllowDeleteGOCREATETRIGGERTR_NotAllowDeleteON courseINSTEADOFDELETEASPRINT'INSTEAD OF触发器开始执行 'PRINT '本表中的数据不允许被删除！不能执行删除操作GO第九章3 上机练习题01 程序代码如下：-创立在score表上执行UPDATE语句的事务UP_score,并执行.USEteachingGOBEGINTRANUPDATESETusua

32、lly=77,final =88WHERE studentno ='07' AND courseno='c05109'COMMITTRANGO02 程序代码如下：-练习使用ROLLBACK TRANSACTION句回滚事务,并查看. USEteachingGOBEGINTRANINSERTINTO courseVALUES('c05133' ,' 国际贸易 ','必修 ',32,;SAVETRAN save1;DELETEFROM courseWHEREcourseno ='c05137' ;ROL

33、LBACKTRANsave1;COMMIT TRAN GO03 程序代码如下：-练习在 student 表上创立嵌套事务,分别在内层和外层设置回滚点,检测回滚对表数据的影响USEteachingGOBEGINTRAN tran1SAVETRAN save1;BEGINTRANtran2UPDATEstudentSETsname='赵平茵',point =997,phone ='0727'WHEREstudentno ='07'SELECT FROM student WHEREstudentno ='07'SAVETRAN save

34、2;COMMIT TRAN tran2 ;UPDATEstudentSETsname='孙释远',point =987,phone ='0727'WHEREstudentno ='07'ROLLBACKTRAN save1;COMMIT TRAN tran2 ;SELECT* FROM student WHEREstudentno ='07'GO04 程序代码如下：-练习在student表上创立嵌套事务,并利用系统变量TRANCOUNT编程,-检测嵌套事务的执行情况.USEteachingGOBEGINTRAN TR1 PRINT

35、'1st BEGIN TRAN: TRANCOUNT='+ CAST(TRANCOUNTAS NVARCHAR(10 ); SAVETRAN save1;BEGINTRAN TR2PRINT'2nd BEGIN TRAN: TRANCOUNT='+ CAST(TRANCOUNTASNVARCHAR(10);UPDATEstudentSETsname='赵平茵',point =997,phone ='0727'WHEREstudentno ='07'COMMIT TRAN TR2PRINT'1st COMMI

36、T TRAN: TRANCOUNT='+ CAST(TRANCOUNTAS NVARCHAR(10);-ROLLBACK TRAN save1;PRINT'ROLLBACK TRAN: TRANCOUNT='+ CAST(TRANCOUNTAS NVARCHAR(10);COMMITTRAN TR1;PRINT'AFTER COMMIT TRAN TR1: TRANCOUNT='+ CAST(TRANCOUNTAS NVARCHAR(10);SELECT* FROM student WHERE studentno ='07'GO05 程序

37、代码如下：-练习在 student 表上进行查询、插入和更新,然后使用视图查看锁的信息.USEteaching;GOBEGINTRANSELECTstudentno ,snameFROM student-WITH(holdlock, rowlock)WHERE studentno ='07'INSERTINTO studentVALUES'01','孙释远','女','1989-09-09' ,'090512' ,777,'76','');UPDATEstudentS

38、ETsname='孙释嘉'WHERE studentno ='07'SELECTS-为了查看事务中使用的锁的信息,使用动态治理视图.在查询窗口中键入并执行以下 句来获取锁信息并提交事务.SELECTresource_type , resource_associated_entity_id , request_status , request_mode , request_session_id , resource_descriptionFROMWHEREresource_database_id =DB_ID('teaching' );-提交事务C

39、OMMIT TRAN%丄左第十章3 上机练习题01 程序代码如下：USEmasterGOCREATELOGIN USER1WITH PASSWORD=' Abc!#213'03 程序代码如下：-练习在teachi ng数据库中为SQL Serve登录名USER添加数据库用户,并取名为USER2默认架构为 TEAC.USEteachingGOCREATEUSERUSER2FORLOGIN USER1WITH DEFAULT_SCHEMA=TEACGO05 程序代码如下：-练习将teaching数据库中创立表的权限授予用户USER2USEteachingGOGRANTCREATET

40、ABLETO USER2GO第十一章3 上机练习题01 程序代码如下：-完整数据库备份USEteachingGOBACKUPDATABASEteachingTO DISK= 第章 backteaching'WITH EXPIREDATE= N'12/22/2021 00:00:00' ,NAME = N'teaching- 完整数据库备份 ',STATS= 10GO-差异备份BACKUPDATABASEteachingTO DISK= 第章 backteaching'WITH DIFFERENTIAL,EXPIREDATE= N'12/2

41、2/2021 00:00:00' ,NOFORMAT, NOINIT,NAME = N'teaching- 差异数据库备份 ',SKIP, NOREWIND, NOUNLOAD, STATS= 10GOdeclare backupSetId asint select backupSetId = position from msdb . backupsetwhere database_name =N'teaching' and backup_set_id =(select max(backup_set_id ) from msdb.backupset wh

42、ere database_name =N'teaching' )if backupSetId is nullbeginraiserror(N'验证失败.找不到数据库“ teachi ng的备份信息.,16,1)endRESTOREVERIFYONLYFROM DISK=第章 backteaching'WITH FILE= backupSetId , NOUNLOAD, NOREWIND GO02 程序代码如下：-对数据库 teaching 进行恢复RESTOREDATABASEteachingFROM DISK=第章 backteaching'WITH

43、FILE= 2, NORECOVER,Y NOUNLOAD,STATS= 10GORESTOREDATABASEteachingFROM DISK=第章 backteaching'WITH FILE= 3, NOUNLOAD,STATS= 10GO03 程序代码如下：-创立备份设备USEmasterGOEXECdevtype = N'disk' ,logicalname = N'device1' ,physicalname = 第章 'GO-备份 teaching 数据库的事务日志BACKUPLOGteachingTO device1WITH R

44、ETAINDAYS= 3, NOFORMAT, NOINIT,NAME = N'teaching- 事务日志备份 ',SKIP, NOREWIND, NOUNLOAD,STATS= 10GO第十二章3 上机练习题02 程序代码如下：USEmsdbGODECLAREjobId BINARY(16)EXEC job_name =N'student' ,category_name =N'Uncategorized (Local)' ,owner_login_name =N'JIANG-N9VJWXJ11Administrator' ,

45、job_id = jobId OUTPUT GOEXEC job_name =N'student' , server_name = N'JIANG-N9VJWXJ11'GODECLAREschedule_id intEXEC job_name =N'student' , name=N'plan1',active_start_date =,active_end_date =,active_start_time =100000 ,active_end_time =235959, schedule_id = schedule_id OU

46、TPUTGO03 程序代码如下：USEmsdbGOEXEC name=N'alert1' ,message_id=0,severity =8,enabled=1,delay_between_responses =30,include_event_description_in =1,notification_message =N' 交易时间即将结束,请及时处理数据 ',job_id =N'db8abb83-8552-49b0-a613-0c223a0bbfdd'GOEXEC alert_name =N'alert1' , opera

47、tor_name =N'jiang' , notification_method = 1GO04 程序代码如下：USEmsdbGOEXEC name=N'operator' ,enabled=1, weekday_pager_start_time =80000,weekday_pager_end_time =180000 , pager_days=62,email_address =N''GOEXEC alert_name =N'alert1' , operator_name =N'operator' , noti

48、fication_method = 1GOEXEC alert_name =N'testAlert1' , operator_name =N'operator' , notification_method = 1 GO05 程序代码如下：USEmsdbGO-创立事务BEGINTRANSACTION DECLAREReturnCode INT SELECTReturnCode = 0 -创立维护方案 maintain1DECLAREjobId BINARY(16) EXECReturnCode = job_name =N'maintain1' ,e

49、nabled=1,notify_level_eventlog =2,notify_level_email =2,notify_level_netsend =0,notify_level_page =0,delete_level =0,description =N' 无描述. ',category_name =N'Database Maintenance' ,owner_login_name =N'JIANG-N9VJWXJ11Administrator' , notify_email_operator_name =N'jiang'

50、 , job_id = jobId OUTPUT IF (ERROR<> 0 OR ReturnCode <> 0) GOTO QuitWithRollback -创立子方案 day_planEXECReturnCode = job_id =jobld , step_name =N'子方案',step_id =1,cmdexec_success_code =0,on_success_action =1,on_success_step_id =0,on_fail_action =2,on_fail_step_id =0,retry_attempts =0,

51、retry_interval =0,os_run_priority =0, subsystem =N'SSlS,'command=N'/Server JlANG-N9VJWXJ11 /SQL "Maintenance Plansmaintain1" /set 子方案 .Disable;false"' ,flags=0lF(ERROR<> 0 OR ReturnCode <> 0) GOTO QuitWithRollback EXECReturnCode = job_id = jobld, start_step

52、_id = 1 lF(ERROR<> 0 OR ReturnCode <> 0) GOTO QuitWithRollback EXECReturnCode = job_id =jobld , name=N'day_plan' ,enabled=1,freq_type =4,freq_interval =1,freq_subday_type =1,freq_subday_interval =0,freq_relative_interval =0,freq_recurrence_factor =0,active_start_date =,active_end

53、_date =,active_start_time =0,active_end_time =235959 lF(ERROR<> 0 OR ReturnCode <> 0) GOTO QuitWithRollback EXECReturnCode = job_id = jobld, server_name = N'(local)'IF (ERROR<> 0 OR ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback :

54、IF (TRANCOUNT> 0) ROLLBACKTRANSACTIONEndSave:第十三章3 上机练习题01 程序代码如下：-练习对 teaching 数据库进行创立事务性发布.- 启用复制数据库use masterexec sp_replicationdboption dbname = N'teaching' ,optname = N'publish' , value = N'true' GOexec teaching . job_login = null,job_password = null, publisher_securi

55、ty_mode = 1 GO exec teaching . job_login = null,job_password = null, frompublisher = 1 GO- 添加事务性发布use teachingexec sp_addpublication publication = N'teac_pub' ,descriptio n = N'来自发布效劳器“ JIANN9VJWXJ11的数据库 “ teachi ng的事务性发布sync_method = N'concurrent' , retention = 0, allow_push =N&

56、#39;true', allow_pull =N'true',allow_anonymous = N'true' ,enabled_for_internet = N'false',snapshot_in_defaultfolder =N'true' ,compress_snapshot = N'false' ,ftp_port = 21, ftp_login = N'anonymous' ,allow_subscription_copy = N'false',add_to_a

57、ctive_directory = N'false' ,repl_freq = N'continuous' ,status = N'active' ,independent_agent =N'true' ,immediate_sync = N'true' ,allow_sync_tran = N'false' ,autogen_sync_procs = N'false' , allow_queued_tran = N'false' , allow_dts = N

58、9;false' , replicate_ddl = 1, allow_initialize_from_backup = N'false' , enabled_for_p2p = N'false' , enabled_for_het_sub = N'false' GO exec sp_addpublication_snapshot publication = N'teac_pub' , frequency_type = 1, frequency_interval = 0, frequency_relative_interv

59、al = 0, frequency_recurrence_factor = 0,frequency_subday = 0, frequency_subday_interval = 0, active_start_time_of_day = 0,active_end_time_of_day = 235959,active_start_date = 0, active_end_date = 0, job_login = null, job_password = null,publisher_security_mode = 1 exec sp_grant_publication_access GOpublication = N'teac_pub' , login = N'sa'exec sp_grant_publication_accessGOpublication = N'teac_pub' , login = N'NT AUTHORITYSYSTEM'exec sp_grant_publication_accessGOexec sp_grant_publication_accesspublication = N'teac_pub'