大三sql课后习题答案.docx

1、大三sql课后习题答案第二章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:DATA,size=5,ma

2、xsize=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 程序代码如下：- 创建表book的Transact-SQL语句：USE test01GOCREATE TABL

3、E 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, address nchar(30) NOT NULL) ON PRIMARY- 设置bo

4、ok中的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, selldate datetime NOT NULL) ON PRIMARY-利用ins

5、ert语句为表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 =m000110

6、3 程序代码如下：USE test01GOCREATE RULE sellnum_rule AS sellnum =0EXEC sp_bindrule sellnum_rule,04 程序代码如下：-删除年以前的数据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 ONDECL

7、ARE 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,star

8、tdate,GETDATE()SET NOCOUNT OFFGO03 程序代码如下：DECLARE count INT,SUM INTSET count =51SET SUM=0WHILE count =100 BEGIN IF (CEILING(count/ 75 and = group by ,go 04 程序代码如下：-输出student表中年龄大于女生平均年龄的男生的所有信息。use teachinggoselect * from studentwhere sex= 男 and DATEDIFF(year,birthday,getdate() ( select avg(DATEDIFF

9、(year,birthday,getdate() from student where sex= 女) 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

10、 = where (substring,1,2)=08 and (datediff(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 程序代码如下：-查询选修课程的少于门、或期末成绩含有分以下课程的学生的学号、姓名、电

11、话和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 countNUM3 go第七章 3 上机题练习01 程序代码如下：-在course表的cname列上创建非聚集索引IDX_cname。USE teachingGOCREATE NONCLUSTERED I

12、NDEX 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 student REBUILD WITH (PAD_INDEX = ON, FILL

13、FACTOR = 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 , , AVG AS averageFRO

14、M 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 CHECK OPTIONGO07 程序代码如下：-通过视图v_teacher向基本表teacher中分别插入数据(05039, 张馨月, 计算机应

15、用, 讲师, 计算机学院)和(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

16、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-创建存储过程CR

17、EATE 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 AN

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

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

20、 (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 f

21、or 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 ON AFTER INSERTAS BEGIN DECLARE msg nchar(30) SET msg=你插

22、入了一条新记录！ 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 f

23、inal 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_NotAll