大三 sql 课后习题答案.docx

1、大三 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

2、,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=3

3、0,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_n

4、ame 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 test01GOCRE

5、ATE 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 boo

6、ksales 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

7、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,sta

8、rtdate,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 BEGIN IF (CEILING(count/3.0) 75 and student.studentno=score.stu

9、dentno 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 stud

10、ent.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.st

11、udentno,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 程序代码如下：-查询级学生的学号、课程名及

12、学分。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

13、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 teachingGOCREA

14、TE 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 REB

15、UILD 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)

16、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选

17、项。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_teache

18、r 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表中查询级学生的学号、性别、出

19、生日期和个字段的容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，-完成的功能

20、是在表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 studen

21、t,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

22、 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 diffe