大三 sql 课后习习题答案

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

2、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',size=5,maxsize=30,filegrowth=1)第三章：3 上机练习题01 程序代码如下

3、：- 创建表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 (anthor_name nchar(4) NOT NULL,book_id nchar(6) NOT NULL,a

4、ddress nchar(30) NOT NULL) ON PRIMARY- 设置book中的book_id为主键，author表中的book_id为外键ALTER TABLE WITH CHECK ADD CONSTRAINT FK_ book_author FOREIGN KEY(book_id)REFERENCES (book_id)02 程序代码如下：-利用Transact-SQL语句创建表booksales的代码。USE test01GOCREATE TABLE booksales(book_id nchar(6) NOT NULL,sellnum int NOT NULL,sell

5、date 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'-

6、利用delete语句删除表booksales的数据：DELETE FROM booksales WHERE book_id ='m00011'03 程序代码如下：USE test01GOCREATE RULE sellnum_rule AS sellnum >=0EXEC sp_bindrule 'sellnum_rule',''04 程序代码如下：-删除年以前的数据DELETE FROM booksales WHERE selldate < '1/1/2009'-删除所有数据Truncate Table booksa

7、les第四章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 AM'SET enddate = '11/10/2009 12:00 AM'SELECT DATEDIFF(year,startdate,enddate)SELECT

8、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/<= FL

9、OOR(count/) BEGIN SET SUM = SUM + count END SET count = count+2 ENDPRINT '50到之间的所有能被整除的奇数之和'+CAST(SUM AS NCHAR(4)第五章3 上机练习题01 程序代码如下：-查询course表中的所有记录。use teachinggoselect * from coursego02 程序代码如下：-查询student表中的女生的人数。use teachinggoselect * from studentwhere sex='女'go03 程序代码如下：-查询teache

10、r表中每一位教授的教师号、姓名和专业名称。use teachinggoselect teacherno,tname,major from teacherwhere prof='教授'go04 程序代码如下：-按性别分组，求出student表中的每组学生的平均年龄。use teachinggoselect sex as '学生性别' , avg(DATEDIFF(year,birthday,getdate() as '平均年龄' from studentgroup by sexgo05 程序代码如下：-利用现有的表生成新表，新表中包括学号、学生姓名

11、、课程号和总评成绩。-其中：总评成绩=final*+usually*use teachinggoselect ,final*+usually* as '总评'into stu_sorsefrom student,scorewhere =goselect * from stu_sorsego06 程序代码如下：-统计每个学生的期末成绩平均分。use teachinggoselect studentno,sname,avg(总评)from stu_sorsegroup by studentno,snamego07 程序代码如下：- 输出student表中年龄最大的男生的所有信息。u

12、se teachinggoselect * from studentwhere birthday= (select min(birthday) from student)go08 程序代码如下：-查询teacher表中没有职称的职工的教师号、姓名、专业和部门。use teachinggoselect teacherno,tname,major,departmentfrom teacherwhere prof is NULLgo第六章3 上机练习题01 程序代码如下：-查询每一位教授的教师号、姓名和讲授的课程名称。use teachinggoselect ,tname,major,from te

13、acher,course,teach_classwhere prof ='教授' and = and =go02 程序代码如下：-利用现有的表生成新表，新表中包括学号、学生姓名、课程名称和总评成绩。-其中：总评成绩=final*+usually*use teachinggoselect DISTINCT ,*+* as '总评'into stu_coursefrom student,course,teach_class,scorewhere = and =go03 程序代码如下：-统计每个学生的期末成绩高于分的课程门数。use teachinggoselect

14、,count(*) as '课程门数'from student,scorewhere > 75 and = group by ,go 04 程序代码如下：-输出student表中年龄大于女生平均年龄的男生的所有信息。use teachinggoselect * from studentwhere sex= '男' and DATEDIFF(year,birthday,getdate() > ( select avg(DATEDIFF(year,birthday,getdate() from student where sex= '女'

15、) go 05 程序代码如下：-计算每个学生获得的学分。use teachinggoselect ,sum(credit)from student INNER JOIN score ON = INNER JOIN course ON =where >60group by , go 06 程序代码如下：-获取入学时间在年到年的所有学生中入学年龄小于岁的学号、姓名及所修课程的课程名称。use teachinggoselect ,from student inner join stu_course on = where (substring,1,2)='08' and (dat

16、ediff(year,birthday,'2008-01-01')<19) or(substring,1,2)='09' and (datediff(year,birthday,'2009-01-01')<19)go 07 程序代码如下：-查询级学生的学号、姓名、课程名及学分。use teachinggoselect ,from student inner join stu_course on = where substring,1,2)='09' go08 程序代码如下：-查询选修课程的少于门、或期末成绩含有分以下课

17、程的学生的学号、姓名、电话和Email。use teachinggoselect studentno,count(*) as 'countNUM' into count1from scoregroup by studentno GOselect ,sname,phone,Emailfrom student inner join score on = inner join count1 on = where <60 and countNUM<3 go第七章 3 上机题练习01 程序代码如下：-在course表的cname列上创建非聚集索引IDX_cname。USE t

18、eachingGOCREATE NONCLUSTERED INDEX IDX_cname ON course(cname)GO02 程序代码如下：USE teachingGOIF EXISTS(SELECT name FROM sysindexes WHERE name='UQ_stu') DROP INDEX GOCREATE NONCLUSTERED INDEX UQ_stu ON student(studentno,classno)GOSELECT * FROM student03 程序代码如下：USE teachingGOALTER INDEX UQ_stu ON st

19、udent 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_avg

20、stuASSELECT TOP(100) PERCENT , , AVG AS 'average'FROM student, scoreWHERE = AND IS NOT NULLGROUP BY , ORDER BY AVG DESCGOSELECT * FROM v_avgstu06 程序代码如下：-修改v_teacher的视图定义，添加WITH CHECK OPTION选项。USE teachingGO ALTER VIEW v_teacherASSELECT *FROM teacherWHERE department = '计算机学院'WITH CHE

21、CK OPTIONGO07 程序代码如下：-通过视图v_teacher向基本表teacher中分别插入数据('05039', '张馨月', '计算机应用', '讲师', '计算机学院')和('06018', '李诚', '机械制造', '副教授', '机械学院')，并查看插入数据情况。USE teachingGO INSERT INTO v_teacher VALUES('05039','张馨月',

22、9;计算机应用','讲师','计算机学院')INSERT INTO v_teacher VALUES('06018','李诚','机械制造','副教授','机械学院')GOSELECT * FROM v_teacherSELECT * FROM teacher08 程序代码如下：-通过视图v_teacher将基本表teacher中教师编号为'05039'的教师职称修改为'副教授'。USE teachingGO UPDATE v_teacherS

23、ET prof = '副教授'WHERE teacherno = '05039'GOSELECT * FROM teacher第八章3 上机练习题01 程序代码如下：-创建一个名称为StuInfo的存储过程，要求完成以下功能：-在student表中查询级学生的学号、姓名、性别、出生日期和电话个字段的内容USE teaching-查询是否已存在此存储过程，如果存在，就删除它IF EXISTS (SELECT name FROM sysobjects WHERE name = 'StuInfo' AND type = 'P') DRO

24、P PROCEDURE StuInfoGO-创建存储过程CREATE PROCEDURE StuInfo ASSelect studentno,sname,sex,birthday,phone FROM studentWHERE substring(studentno,1,2)='08'GO02 程序代码如下：-创建一个存储过程ScoreInfo，-完成的功能是在表student、表course和表score中查询以下字段：学号、姓名、性别、课程名称、期末分数。USE teaching-查询是否已存在此存储过程，如果存在，就删除它IF EXISTS (SELECT name F

25、ROM sysobjects WHERE name = 'ScoreInfo' AND type = 'P') DROP PROCEDURE ScoreInfoGO-创建存储过程CREATE PROCEDURE ScoreInfo ASSelect , FROM student,course,scoreWHERE = and =GO03 程序代码如下：-创建一个带有参数的存储过程Stu_Age，-该存储过程根据输入的学号，在student表中计算此学生的年龄，-并根据程序的执行结果返回不同的值，程序执行成功，返回整数，如果执行出错，则返回错误号。-删除已存在的存

26、储过程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(

27、GETDATE()-YEAR(birthday) FROM studentWHERE studentno=studentNO-根据程序的执行结果返回不同的值IF (ERROR<>0) SET ErrorValue=ERRORRETURN ErrorValueGO04 程序代码如下：- =- Template generated from Template Explorer using:- Create Trigger (New Menu).SQL- Use the Specify Values for Template Parameters - command (Ctrl-Shif

28、t-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: -

29、Description:- =CREATE TRIGGER ON AFTER INSERTAS BEGINDECLARE msg nchar(30) SET msg='你插入了一条新记录！' PRINT msgENDGO05 程序代码如下：-创建一个AFTER触发器，要求实现以下功能：-在score表上创建一个插入、更新类型的触发器TR_ScoreCheck，-当在score字段中插入或修改考试分数后，触发该触发器，检查分数是否在-100之间。USE teachingGOCREATE TRIGGER TR_ScoreCheckON scoreFOR INSERT, UPDATE

30、 ASIF UPDATE(final )PRINT 'AFTER触发器开始执行'BEGIN DECLARE ScoreValue real SELECT ScoreValue=(SELECT final FROM inserted) IF ScoreValue>100 OR ScoreValue<0 PRINT '输入的分数有误，请确认输入的考试分数！'ENDGO06 程序代码如下：-创建一个INSTEAD OF触发器，要求实现以下功能：-在course表上创建一个删除类型的触发器TR_NotAllowDelete，-当在course表中删除记录时，

31、触发该触发器，显示不允许删除表中数据的提示信息。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 '本表中的数据不允许被删除！不能执行删除操

32、作!'GO第九章3 上机练习题01 程序代码如下：-创建在score表上执行UPDATE语句的事务UP_score，并执行。USE teachingGOBEGIN TRAN UPDATE SET usually=77,final=88 WHERE studentno='07' AND courseno='c05109' COMMIT TRANGO02 程序代码如下：-练习使用ROLLBACK TRANSACTION语句回滚事务，并查看。USE teachingGOBEGIN TRAN INSERT INTO course VALUES('c051

33、33','国际贸易','必修',32,; 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='赵平茵'

34、;,point=997,phone='0727' WHERE studentno='07' SELECT * FROM student WHERE studentno='07' SAVE TRAN save2; COMMIT TRAN tran2 ; UPDATE student SET sname='孙释远',point=987,phone='0727' WHERE studentno='07' ROLLBACK TRAN save1;COMMIT TRAN tran2;SELECT * FROM

35、 student WHERE studentno='07'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 A

36、S NVARCHAR(10); UPDATE student SET sname='赵平茵',point=997,phone='0727' WHERE studentno='07' COMMIT TRAN TR2; PRINT '1st COMMIT TRAN: TRANCOUNT=' + CAST(TRANCOUNT AS NVARCHAR(10); -ROLLBACK TRAN save1; PRINT 'ROLLBACK TRAN: TRANCOUNT=' + CAST(TRANCOUNT AS NVARCH

37、AR(10);COMMIT TRAN TR1;PRINT 'AFTER COMMIT TRAN TR1: TRANCOUNT=' + CAST(TRANCOUNT AS NVARCHAR(10); SELECT * FROM student WHERE studentno='07'GO05 程序代码如下：-练习在student表上进行查询、插入和更新，然后使用视图查看锁的信息。USE teaching;GOBEGIN TRAN SELECT studentno,sname FROM student-WITH(holdlock, rowlock) WHERE st

38、udentno='07' INSERT INTO student VALUES('01','孙释远','女','1989-09-09','090512',777,'76',''); UPDATE student SET sname='孙释嘉' WHERE studentno='07'-为了查看事务中使用的锁的信息，使用动态管理视图。在查询窗口中键入并执行以下SELECT语句来获取锁信息并提交事务。SELECT resource_type

39、, resource_associated_entity_id, request_status, request_mode, request_session_id, resource_descriptionFROM WHERE resource_database_id=DB_ID('teaching');-提交事务COMMIT TRAN第十章3 上机练习题01 程序代码如下：USE masterGOCREATE LOGIN USER1 WITH PASSWORD =' Abc!#213'03 程序代码如下：-练习在teaching数据库中为SQL Server登

40、录名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 = N'F:sqlprogram_ex第章bac

41、kteaching' WITH EXPIREDATE = N'12/22/2009 00:00:00', NAME = N'teaching-完整数据库备份',STATS = 10GO-差异备份BACKUP DATABASE teaching TO DISK = N'F:sqlprogram_ex第章backteaching' WITH DIFFERENTIAL , EXPIREDATE = N'12/22/2009 00:00:00', NOFORMAT, NOINIT, NAME = N'teaching-差异

42、数据库备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10GOdeclare backupSetId as intselect backupSetId = position from msdb.backupset where database_name=N'teaching' and backup_set_id=(select max(backup_set_id) from msdb.backupset where database_name=N'teaching' ) if backupSetId is null begi

43、n raiserror(N'验证失败。找不到数据库“teaching”的备份信息。', 16, 1) endRESTORE VERIFYONLY FROM DISK = N'F:sqlprogram_ex第章backteaching' WITH FILE = backupSetId, NOUNLOAD, NOREWINDGO02 程序代码如下：-对数据库teaching进行恢复RESTORE DATABASE teaching FROM DISK = N'F:sqlprogram_ex第章backteaching' WITH FILE = 2,

44、NORECOVERY, NOUNLOAD, STATS = 10GORESTORE DATABASE teaching FROM DISK = N'F:sqlprogram_ex第章backteaching' WITH FILE = 3, NOUNLOAD, STATS = 10GO03 程序代码如下：-创建备份设备USE masterGOEXEC devtype = N'disk', logicalname = N'device1', physicalname = N'F:sqlprogram_ex第章'GO-备份teachin

45、g数据库的事务日志BACKUP LOG teaching TO device1 WITH RETAINDAYS = 3, NOFORMAT, NOINIT, NAME = N'teaching-事务日志备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10GO第十二章3 上机练习题02 程序代码如下：USE msdbGODECLARE jobId BINARY(16)EXEC job_name=N'student', category_name=N'Uncategorized (Local)', owner_login

46、_name=N'JIANG-N9VJWXJ11Administrator', job_id = jobId OUTPUTGOEXEC job_name=N'student', server_name = N'JIANG-N9VJWXJ11'GODECLARE schedule_id intEXEC job_name=N'student', name=N'plan1', active_start_date=, active_end_date=, active_start_time=100000, active_end

47、_time=235959, schedule_id = schedule_id OUTPUTGO03 程序代码如下：USE msdbGOEXEC 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'GO

48、EXEC alert_name=N'alert1', operator_name=N'jiang', notification_method = 1GO04程序代码如下：USE msdbGOEXEC 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', operato

49、r_name=N'operator', notification_method = 1GOEXEC alert_name=N'testAlert1', operator_name=N'operator', notification_method = 1GO05 程序代码如下：USE msdbGO-创建事务BEGIN TRANSACTIONDECLARE ReturnCode INTSELECT ReturnCode = 0-创建维护计划maintain1DECLARE jobId BINARY(16)EXEC ReturnCode = job_n

50、ame=N'maintain1', enabled=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

51、_name=N'jiang', job_id = jobId OUTPUTIF (ERROR <> 0 OR ReturnCode <> 0) GOTO QuitWithRollback-创建子计划day_planEXEC ReturnCode = job_id=jobId, 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,

52、retry_attempts=0, retry_interval=0, os_run_priority=0, subsystem=N'SSIS', command=N'/Server JIANG-N9VJWXJ11 /SQL "Maintenance Plansmaintain1" /set "Package子计划.Disable;false"', flags=0IF (ERROR <> 0 OR ReturnCode <> 0) GOTO QuitWithRollbackEXEC ReturnCo

53、de = job_id = jobId, start_step_id = 1IF (ERROR <> 0 OR ReturnCode <> 0) GOTO QuitWithRollbackEXEC ReturnCode = job_id=jobId, 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,

54、 active_start_date=, active_end_date=, active_start_time=0, active_end_time=235959IF (ERROR <> 0 OR ReturnCode <> 0) GOTO QuitWithRollbackEXEC ReturnCode = job_id = jobId, server_name = N'(local)'IF (ERROR <> 0 OR ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback:IF (TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:第十三章3 上机练习题01 程序代码如下：-练习对teaching数