大三 sql 课后习题答案

第二章3.上机练习题02 程序代码如下：CREATE DATABASE STUDENT1ON PRIMARY(NAME= STUDENT1_data,FILENAME=E:DATASTUDENT1.mdf,SIZE=3,MAXSIZE=unlimited,FILEGROWTH=15%)LOG ON(NAME= STUDENT1_log,FILENAME=E:DATASTUDENT1.ldf,SIZE=2,MAXSIZE=30,FILEGROWTH=2)03 程序代码如下：create database studentson primary(name=students1,filename=E:DATAstudents1.mdf,size=5,maxsize=75,filegrowth=10%),(name= students12,filename=E:DATAstudents2.ndf,size=10,maxsize=75,filegrowth=1)log on (name=studentslog1,filename=E:DATAstudentslog1.ldf,size=5,maxsize=30,filegrowth=1),(name=studentslog2,filename=E:DATAstudentslog2.ldf,size=5,maxsize=30,filegrowth=1)第三章：3 上机练习题01 程序代码如下：- 创建表book的Transact-SQL语句：USE test01GOCREATE TABLE book(book_id nchar(6) NOT NULL,book_name nchar(30) NULL,price numeric(10, 2) NULL, CONSTRAINT PK_book PRIMARY KEY CLUSTERED ( book_id ASC ) ) ON PRIMARY- 创建表uthor的Transact-SQL语句：CREATE TABLE dbo.author(anthor_name nchar(4) NOT NULL,book_id nchar(6) NOT NULL,address nchar(30) NOT NULL) ON PRIMARY- 设置book中的book_id为主键，author表中的book_id为外键ALTER TABLE dbo.author WITH CHECK ADD CONSTRAINT FK_ book_author FOREIGN KEY(book_id)REFERENCES dbo.book (book_id)02 程序代码如下：-利用Transact-SQL语句创建表booksales的代码。USE test01GOCREATE TABLE booksales(book_id nchar(6) NOT NULL,sellnum int NOT NULL,selldate datetime NOT NULL) ON PRIMARY-利用insert语句为表booksales添加数据：INSERT INTO booksales VALUES (m00011,7,20/12/2008)INSERT INTO booksales(book_id,sellnum,selldate) VALUES (m00017,3,17/11/2008 ) -利用update语句为表booksales更新数据：UPDATE booksales SET sellnum =11WHERE book_id =m00011-利用delete语句删除表booksales的数据：DELETE FROM booksales WHERE book_id =m0001103 程序代码如下：USE test01GOCREATE RULE sellnum_rule AS sellnum =0EXEC sp_bindrule sellnum_rule,booksales.sellnum04 程序代码如下：-删除年以前的数据DELETE FROM booksales WHERE selldate 1/1/2009-删除所有数据Truncate Table booksales第四章3 上机练习题01 程序代码如下：DECLARE bookname nchar(16)set bookname = SQL Server数据库编程02 程序代码如下：USE test01GOSET NOCOUNT ONDECLARE startdate datetime,enddate datetimeSET startdate = 1/7/2008 12:12 AMSET enddate = 11/10/2009 12:00 AMSELECT DATEDIFF(year,startdate,enddate)SELECT DATEDIFF(month,startdate,enddate)SELECT DATEDIFF(day,startdate,enddate)SELECT DATEDIFF(minute,startdate,1/8/2007 12:17 AM)SELECT DATEDIFF(minute,startdate,GETDATE()SET NOCOUNT OFFGO03 程序代码如下：DECLARE count INT,SUM INTSET count =51SET SUM=0WHILE count =100 BEGINIF (CEILING(count/3.0) 75 and student.studentno=score.studentno group by student.studentno,student.snamego 04 程序代码如下：-输出student表中年龄大于女生平均年龄的男生的所有信息。use teachinggoselect * from studentwhere sex= 男 and DATEDIFF(year,birthday,getdate() ( select avg(DATEDIFF(year,birthday,getdate() from student where sex= 女) go 05 程序代码如下：-计算每个学生获得的学分。use teachinggoselect student.studentno,student.sname,sum(credit)from student INNER JOIN score ON student.studentno=score.studentno INNER JOIN course ON course.courseno=score.coursenowhere score.final60group by student.studentno,student.sname go 06 程序代码如下：-获取入学时间在年到年的所有学生中入学年龄小于岁的学号、姓名及所修课程的课程名称。use teachinggoselect student.studentno,student.sname,stu_amefrom student inner join stu_course on student.studentno=stu_course.studentno where (substring(student.studentno,1,2)=08 and (datediff(year,birthday,2008-01-01)19) or(substring(student.studentno,1,2)=09 and (datediff(year,birthday,2009-01-01)19)go 07 程序代码如下：-查询级学生的学号、姓名、课程名及学分。use teachinggoselect student.studentno,student.sname,stu_amefrom student inner join stu_course on student.studentno=stu_course.studentno where substring(student.studentno,1,2)=09 go08 程序代码如下：-查询选修课程的少于门、或期末成绩含有分以下课程的学生的学号、姓名、电话和Email。use teachinggoselect studentno,count(*) as countNUM into count1from scoregroup by studentno GOselect student.studentno,sname,phone,Emailfrom student inner join score on student.studentno=score.studentno inner join count1 on student.studentno=count1.studentno where score.final60 and countNUM3 go第七章 3 上机题练习01 程序代码如下：-在course表的cname列上创建非聚集索引IDX_cname。USE teachingGOCREATE NONCLUSTERED INDEX IDX_cname ON course(cname)GO02 程序代码如下：USE teachingGOIF EXISTS(SELECT name FROM sysindexes WHERE name=UQ_stu) DROP INDEX student.UQ_stuGOCREATE NONCLUSTERED INDEX UQ_stu ON student(studentno,classno)GOSELECT * FROM student03 程序代码如下：USE teachingGOALTER INDEX UQ_stu ON student REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 80)GO04 程序代码如下：-创建一个视图v_teacher，查询所有“计算机学院”教师的信息。USE teachingGOCREATE VIEW v_teacherASSELECT *FROM teacherWHERE department = 计算机学院GOSELECT * FROM v_teacher05 程序代码如下：-创建一个视图v_avgstu，查询每个学生的学号、姓名及平均分，并且按照平均分降序排序。USE teachingGOCREATE VIEW v_avgstuASSELECT TOP(100) PERCENT student.studentno, student.sname, AVG(score.final) AS averageFROM student, scoreWHERE student.studentno = score.studentno AND score.final IS NOT NULLGROUP BY student.studentno, student.snameORDER BY AVG(score.final) DESCGOSELECT * FROM v_avgstu06 程序代码如下：-修改v_teacher的视图定义，添加WITH CHECK OPTION选项。USE teachingGO ALTER VIEW v_teacherASSELECT *FROM teacherWHERE department = 计算机学院WITH CHECK OPTIONGO07 程序代码如下：-通过视图v_teacher向基本表teacher中分别插入数据(05039, 张馨月, 计算机应用, 讲师, 计算机学院)和(06018, 李诚, 机械制造, 副教授, 机械学院)，并查看插入数据情况。USE teachingGO INSERT INTO v_teacher VALUES(05039,张馨月,计算机应用,讲师,计算机学院)INSERT INTO v_teacher VALUES(06018,李诚,机械制造,副教授,机械学院)GOSELECT * FROM v_teacherSELECT * FROM teacher08 程序代码如下：-通过视图v_teacher将基本表teacher中教师编号为05039的教师职称修改为副教授。USE teachingGO UPDATE v_teacherSET prof = 副教授WHERE teacherno = 05039GOSELECT * FROM teacher第八章3 上机练习题01 程序代码如下：-创建一个名称为StuInfo的存储过程，要求完成以下功能：-在student表中查询级学生的学号、姓名、性别、出生日期和电话个字段的内容USE teaching-查询是否已存在此存储过程，如果存在，就删除它IF EXISTS (SELECT name FROM sysobjects WHERE name = StuInfo AND type = P) DROP PROCEDURE StuInfoGO-创建存储过程CREATE PROCEDURE StuInfo ASSelect studentno,sname,sex,birthday,phone FROM studentWHERE substring(studentno,1,2)=08GO02 程序代码如下：-创建一个存储过程ScoreInfo，-完成的功能是在表student、表course和表score中查询以下字段：学号、姓名、性别、课程名称、期末分数。USE teaching-查询是否已存在此存储过程，如果存在，就删除它IF EXISTS (SELECT name FROM sysobjects WHERE name = ScoreInfo AND type = P) DROP PROCEDURE ScoreInfoGO-创建存储过程CREATE PROCEDURE ScoreInfo ASSelect student.studentno,student.sname,student.sex,ame,Score.final FROM student,course,scoreWHERE student.studentno=score.studentno and score.courseno=course.coursenoGO03 程序代码如下：-创建一个带有参数的存储过程Stu_Age，-该存储过程根据输入的学号，在student表中计算此学生的年龄，-并根据程序的执行结果返回不同的值，程序执行成功，返回整数，如果执行出错，则返回错误号。-删除已存在的存储过程USE teachingIF EXISTS (SELECT name FROM sysobjects WHERE name = Stu_Age AND type = P) DROP PROCEDURE Stu_AgeGO-创建存储过程USE teachingGOCREATE PROCEDURE Stu_Age studentNO nvarchar(10),Age int OUTPUTAS -定义并初始化局部变量，用于保存返回值DECLARE ErrorValue intSET ErrorValue=0-求此学生的年龄SELECT Age=YEAR(GETDATE()-YEAR(birthday) FROM studentWHERE studentno=studentNO-根据程序的执行结果返回不同的值IF (ERROR0) SET ErrorValue=ERRORRETURN ErrorValueGO04 程序代码如下：- =- 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 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.- =SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO- =- Author:Name- Create date: - Description:- =CREATE TRIGGER dbo.TR_Stu_Insert ON dbo.student AFTER INSERTAS BEGINDECLARE msg nchar(30) SET msg=你插入了一条新记录！ PRINT msgENDGO05 程序代码如下：-创建一个AFTER触发器，要求实现以下功能：-在score表上创建一个插入、更新类型的触发器TR_ScoreCheck，-当在score字段中插入或修改考试分数后，触发该触发器，检查分数是否在-100之间。USE teachingGOCREATE TRIGGER TR_ScoreCheckON scoreFOR INSERT, UPDATE ASIF UPDATE(final )PRINT AFTER触发器开始执行BEGIN DECLARE ScoreValue real SELECT ScoreValue=(SELECT final FROM inserted) IF ScoreValue100 OR ScoreValue0 PRINT 输入的分数有误，请确认输入的考试分数！ENDGO06 程序代码如下：-创建一个INSTEAD OF触发器，要求实现以下功能：-在course表上创建一个删除类型的触发器TR_NotAllowDelete，-当在course表中删除记录时，触发该触发器，显示不允许删除表中数据的提示信息。USE teachingGOIF EXISTS(SELECT name FROM sysobjects WHERE name =TR_NotAllowDelete AND type = TR) DROP TRIGGER TR_NotAllowDeleteGOCREATE TRIGGER TR_NotAllowDeleteON courseINSTEAD OF DELETEAS PRINT INSTEAD OF 触发器开始执行 PRINT 本表中的数据不允许被删除！不能执行删除操作!GO第九章3 上机练习题01 程序代码如下：-创建在score表上执行UPDATE语句的事务UP_score，并执行。USE teachingGOBEGIN TRAN UPDATE dbo.score SET usually=77,final=88 WHERE studentno=0824113307 AND courseno=c05109 COMMIT TRANGO02 程序代码如下：-练习使用ROLLBACK TRANSACTION语句回滚事务，并查看。USE teachingGOBEGIN TRAN INSERT INTO course VALUES(c05133,国际贸易,必修,32,2.0); SAVE TRAN save1; DELETE FROM course WHERE courseno=c05137; ROLLBACK TRAN save1;COMMIT TRANGO03 程序代码如下：-练习在student表上创建嵌套事务，分别在内层和外层设置回滚点，检测回滚对表数据的影响。USE teachingGOBEGIN TRAN tran1 SAVE TRAN save1; BEGIN TRAN tran2 UPDATE student SET sname=赵平茵,point=997,phone=053179372727 WHERE studentno=0828261367; SELECT * FROM student WHERE studentno=0828261367; SAVE TRAN save2; COMMIT TRAN tran2 ; UPDATE student SET sname=孙释远,point=987,phone=053179372727 WHERE studentno=0828261367; ROLLBACK TRAN save1;COMMIT TRAN tran2;SELECT * FROM student WHERE studentno=0828261367;GO04 程序代码如下：-练习在student表上创建嵌套事务，并利用系统变量TRANCOUNT编程，-检测嵌套事务的执行情况。USE teachingGOBEGIN TRAN TR1 PRINT 1st BEGIN TRAN: TRANCOUNT= + CAST(TRANCOUNT AS NVARCHAR(10); SAVE TRAN save1; BEGIN TRAN TR2 PRINT 2nd BEGIN TRAN: TRANCOUNT= + CAST(TRANCOUNT AS NVARCHAR(10); UPDATE student SET sname=赵平茵,point=997,phone=053179372727 WHERE studentno=0828261367; COMMIT TRAN TR2; PRINT 1st COMMIT TRAN: TRANCOUNT= + CAST(TRANCOUNT AS NVARCHAR(10); -ROLLBACK TRAN save1; PRINT ROLLBACK TRAN: TRANCOUNT= + CAST(TRANCOUNT AS NVARCHAR(10);COMMIT TRAN TR1;PRINT AFTER COMMIT TRAN TR1: TRANCOUNT= + CAST(TRANCOUNT AS NVARCHAR(10); SELECT * FROM student WHERE studentno=0828261367;GO05 程序代码如下：-练习在student表上进行查询、插入和更新，然后使用sys.dm_tran_locks视图查看锁的信息。USE teaching;GOBEGIN TRAN SELECT studentno,sname FROM student-WITH(holdlock, rowlock) WHERE studentno=0828261367; INSERT INTO student VALUES(1028261001,孙释远,女,1989-09-09,090512,777,1328909876,); UPDATE student SET sname=孙释嘉 WHERE studentno=0828261367;-为了查看事务中使用的锁的信息，使用动态管理视图sys.dm_tran_locks。在查询窗口中键入并执行以下SELECT语句来获取锁信息并提交事务。SELECT resource_type, resource_associated_entity_id, request_status, request_mode, request_session_id, resource_descriptionFROM sys.dm_tran_locksWHERE resource_database_id=DB_ID(teaching);-提交事务COMMIT TRAN第十章3 上机练习题01 程序代码如下：USE masterGOCREATE LOGIN USER1 WITH PASSWORD = Abc!#21303 程序代码如下：-练习在teaching数据库中为SQL Server登录名USER1添加数据库用户，并取名为USER2，默认架构为TEAC。USE teachingGOCREATE USER USER2 FOR LOGIN USER1WITH DEFAULT_SCHEMA =TEACGO 05 程序代码如下：-练习将teaching数据库中创建表的权限授予用户USER2。USE teachingGOGRANT CREATE TABLE TO USER2GO第十一章3 上机练习题01 程序代码如下：-完整数据库备份USE teaching GOBACKUP DATABASE teaching TO DISK = NF:sqlprogram_ex第章backteaching WITH EXPIREDATE = N12/22/2009 00:00:00, NAME = Nteaching-完整数据库备份,STATS = 10GO-差异备份BACKUP DATABASE teaching TO DISK = NF:sqlprogram_ex第章backteaching WITH DIFFERENTIAL , EXPIREDATE = N12/22/2009 00:00:00, NOFORMAT, NOINIT, NAME = Nteaching-差异数据库备份, SKIP, NOREWIND, NOUNLOAD, STATS = 10GOdeclare backupSetId as intselect backupSetId = position from msdb.backupset where database_name=Nteaching and backup_set_id=(select max(backup_set_id) from msdb.backupset where database_name=Nteaching ) if backupSetId is null begin raiserror(N验证失败。找不到数据库“teaching”的备份信息。, 16, 1) endRESTORE VERIFYONLY FROM DISK = NF:sqlprogram_ex第章backteaching WITH FILE = backupSetId, NOUNLOAD, NOREWINDGO02 程序代码如下：-对数据库teaching进行恢复RESTORE DATABASE teaching FROM DISK = NF:sqlprogram_ex第章backteaching WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 10GORESTORE DATABASE teaching FROM DISK = NF:sqlprogram_ex第章backteaching WITH FILE = 3, NOUNLOAD, STATS = 10GO03 程序代码如下：-创建备份设备USE masterGOEXEC master.dbo.sp_addumpdevice devtype = Ndisk, logicalname = Ndevice1, physicalname = NF:sqlprogram_ex第章back_device1.bakGO-备份teaching数据库的事务日志BACKUP LOG teaching TO device1 WITH RETAINDAYS = 3, NOFORMAT, NOINIT, NAME = Nteaching-事务日志备份, SKIP, NOREWIND, NOUNLOAD, STATS = 10GO第十二章3 上机练习题02 程序代码如下：USE msdbGODECLARE jobId BINARY(16)EXEC msdb.dbo.sp_add_job job_name=Nstudent, category_name=NUncategorized (Local), owner_login_name=NJIANG-N9VJWXJ11Administrator, job_id = jobId OUTPUTGOEXEC msdb.dbo.sp_add_jobserver job_name=Nstudent, server_name = NJIANG-N9VJWXJ11GODECLARE schedule_id intEXEC msdb.dbo.sp_add_jobschedule job_name=Nstudent, name=Nplan1, active_start_date=20091222, active_end_date=99991231, active_start_time=100000, active_end_time=235959, schedule_id = schedule_id OUTPUTGO03 程序代码如下：USE msdbGOEXEC msdb.dbo.sp_add_alert name=Nalert1, message_id=0, severity=8, enabled=1, delay_between_responses=30, include_event_description_in=1, notification_message=N交易时间即将结束，请及时处理数据, job_id=Ndb8abb83-8552-49b0-a613-0c223a0bbfddGOEXEC msdb.dbo.sp_add_notification alert_name=Nalert1, operator_name=Njiang, notification_method = 1GO04程序代码如下：USE msdbGOEXEC msdb.dbo.sp_add_operator name=Noperator, enabled=1, weekday_pager_start_time=80000, weekday_pager_end_time=180000, pager_days=62, email_address=NGOEXEC msdb.dbo.sp_add_notification alert_name=Nalert1, operator_name=Noperator, notification_method = 1GOEXEC msdb.dbo.sp_add_notification alert_name=NtestAlert1, operator_name=Noperator, notification_method = 1GO05 程序代码如下：USE msdbGO-创建事务BEGIN TRANSACTIONDECLARE ReturnCode INTSELECT ReturnCode = 0-创建维护计划maintain1DECLARE jobId BINARY(16)EXEC ReturnCode = msdb.dbo.sp_add