大三 sql 课后习题答案

大三 sql 课后习题答案

第二章

3.上机练习题

02 程序代码如下：

CREATE DATABASE STUDENT1

ON PRIMARY

(NAME= STUDENT1_data,

FILENAME='E:\DATA\STUDENT1.mdf',

SIZE=3,

MAXSIZE=unlimited,

FILEGROWTH=15%)

LOG ON

(NAME= STUDENT1_log,

FILENAME='E:\DATA\STUDENT1.ldf',

SIZE=2,

MAXSIZE=30,

FILEGROWTH=2)

03 程序代码如下：

create database students

on primary

(name=students1,

filename='E:\DATA\students1.mdf',

size=5,

maxsize=75,

filegrowth=10%),

(name= students12,

filename='E:\DATA\students2.ndf',

size=10,

maxsize=75,

filegrowth=1)

log on

(name=studentslog1,

filename='E:\DATA\studentslog1.ldf',

size=5,

maxsize=30,

filegrowth=1),

(name=studentslog2,

filename='E:\DATA\studentslog2.ldf',

size=5,

大三 sql 课后习题答案

maxsize=30,

filegrowth=1)

第三章：

3 上机练习题

01 程序代码如下：

-- 创建表book的Transact-SQL语句：

USE test01

GO

CREATE 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 test01

GO

CREATE TABLE booksales(

book_id nchar(6) NOT NULL, sellnum int NOT NULL, selldate datetime NOT NULL

) ON PRIMARY

--利用insert语句为表booksales添加数据：

大三 sql 课后习题答案

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 =11

WHERE book_id ='m00011'

--利用delete语句删除表booksales的数据：

DELETE FROM booksales

WHERE book_id ='m00011'

03 程序代码如下：

USE test01

GO

CREATE RULE sellnum_rule

AS @sellnum >=0

EXEC sp_bindrule 'sellnum_rule','booksales.sellnum'

04 程序代码如下：

--删除年以前的数据

DELETE FROM booksales

WHERE selldate < '1/1/2009'

--删除所有数据

Truncate Table booksales

第四章

3 上机练习题

01 程序代码如下：

DECLARE @bookname nchar(16)

set @bookname = 'SQL Server数据库编程'

02 程序代码如下：

USE test01

大三 sql 课后习题答案

GO

SET NOCOUNT ON

DECLARE @startdate datetime,@enddate datetime

SET @startdate = '1/7/2008 12:12 AM'

SET @enddate = '11/10/2009 12:00 AM'

SELECT 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 OFF

GO

03 程序代码如下：

DECLARE @count INT,@SUM INT

SET @count =51

SET @SUM=0

WHILE @count <=100

BEGIN

IF (CEILING(@count/3.0)<= FLOOR(@count/3.0))

BEGIN

SET @SUM = @SUM + @count

END

SET @count = @count+2

END

PRINT '50到之间的所有能被整除的奇数之和'+CAST(@SUM AS NCHAR(4))

第五章

3 上机练习题

01 程序代码如下：

--查询course表中的所有记录。

use teaching

go

select * from course

go

大三 sql 课后习题答案

02 程序代码如下：

--查询student表中的女生的人数。

use teaching

go

select * from student

where sex='女'

go

03 程序代码如下：

--查询teacher表中每一位教授的教师号、姓名和专业名称。

use teaching

go

select teacherno,tname,major from teacher

where prof='教授'

go

04 程序代码如下：

--按性别分组，求出student表中的每组学生的平均年龄。

use teaching

go

select sex as '学生性别' , avg(DATEDIFF(year,birthday,getdate())) as '平均年龄'

from student

group by sex

go

05 程序代码如下：

--利用现有的表生成新表，新表中包括学号、学生姓名、课程号和总评成绩。

--其中：总评成绩=final*0.8+usually*0.2

use teaching

go

select

student.studentno,student.sname,score.courseno,final*0.8+usually*0.2 as '总评'

into stu_sorse

from student,score

大三 sql 课后习题答案

where student.studentno=score.studentno

go

select * from stu_sorse

go

06 程序代码如下：

--统计每个学生的期末成绩平均分。

use teaching

go

select studentno,sname,avg(总评)

from stu_sorse

group by studentno,sname

go

07 程序代码如下：

-- 输出student表中年龄最大的男生的所有信息。

use teaching

go

select * from student

where birthday=

(select min(birthday)

from student)

go

08 程序代码如下：

--查询teacher表中没有职称的职工的教师号、姓名、专业和部门。

use teaching

go

select teacherno,tname,major,department

from teacher

where prof is NULL

go

第六章

3 上机练习题

大三 sql 课后习题答案

01 程序代码如下：

--查询每一位教授的教师号、姓名和讲授的课程名称。

use teaching

go

select teacher.teacherno,tname,major,ame

from teacher,course,teach_class

where prof ='教授' and teacher.teacherno =teach_class.teacherno

and teach_class.courseno=course.courseno

go

02 程序代码如下：

--利用现有的表生成新表，新表中包括学号、学生姓名、课程名称和总评成绩。

--其中：总评成绩=final*0.9+usually*0.1

use teaching

go

select DISTINCT

student.studentno,student.sname,ame,score.final*0.9+ually*0.1 as '总评'

into stu_course

from student,course,teach_class,score

where student.studentno=score.studentno

and course.courseno=score.courseno

go

03 程序代码如下：

--统计每个学生的期末成绩高于分的课程门数。

use teaching

go

select student.studentno,student.sname,count(*) as '课程门数'

from student,score

where score.final > 75

and student.studentno=score.studentno

group by student.studentno,student.sname

go

04 程序代码如下：

大三 sql 课后习题答案

--输出student表中年龄大于女生平均年龄的男生的所有信息。

use teaching

go

select * from student

where sex= '男'

and DATEDIFF(year,birthday,getdate()) > (

select avg(DATEDIFF(year,birthday,getdate()))

from student

where sex= '女')

go

05 程序代码如下：

--计算每个学生获得的学分。

use teaching

go

select student.studentno,student.sname,sum(credit)

from student INNER JOIN score

ON student.studentno=score.studentno

INNER JOIN course

ON course.courseno=score.courseno

where score.final>60

group by student.studentno,student.sname

go

06 程序代码如下：

--获取入学时间在年到年的所有学生中入学年龄小于岁的学号、姓名及所修课程的课程名称。

use teaching

go

select student.studentno,student.sname,stu_ame

from 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

大三 sql 课后习题答案

07 程序代码如下：

--查询级学生的学号、姓名、课程名及学分。

use teaching

go

select student.studentno,student.sname,stu_ame

from student inner join stu_course

on student.studentno=stu_course.studentno

where substring(student.studentno,1,2)='09'

go

08 程序代码如下：

--查询选修课程的少于门、或期末成绩含有分以下课程的学生的学号、姓名、电话和Email。

use teaching

go

select studentno,count(*) as 'countNUM'

into count1

from score

group by studentno

GO

select student.studentno,sname,phone,Email

from student inner join score

on student.studentno=score.studentno

inner join count1

on student.studentno=count1.studentno

where score.final<60 and countNUM<3

go

第七章

3 上机题练习

01 程序代码如下：

--在course表的cname列上创建非聚集索引IDX_cname。

USE teaching

GO

CREATE NONCLUSTERED INDEX IDX_cname ON course(cname)

大三 sql 课后习题答案

02 程序代码如下：

USE teaching

GO

IF EXISTS(SELECT name FROM sysindexes WHERE name='UQ_stu')

DROP INDEX student.UQ_stu

GO

CREATE NONCLUSTERED INDEX UQ_stu ON student(studentno,classno)

GO

SELECT * FROM student

03 程序代码如下：

USE teaching

GO

ALTER INDEX UQ_stu ON student REBUILD

WITH (PAD_INDEX = ON, FILLFACTOR = 80)

GO

04 程序代码如下：

--创建一个视图v_teacher，查询所有“计算机学院”教师的信息。

USE teaching

GO

CREATE VIEW v_teacher

AS

SELECT *

FROM teacher

WHERE department = '计算机学院'

GO

SELECT * FROM v_teacher

05 程序代码如下：

--创建一个视图v_avgstu，查询每个学生的学号、姓名及平均分，并且按照平均分降序排序。 USE teaching

GO

CREATE VIEW v_avgstu

大三 sql 课后习题答案

SELECT TOP(100) PERCENT student.studentno, student.sname,

AVG(score.final) AS 'average'

FROM student, score

WHERE student.studentno = score.studentno AND score.final IS NOT NULL GROUP BY student.studentno, student.sname

ORDER BY AVG(score.final) DESC

GO

SELECT * FROM v_avgstu

06 程序代码如下：

--修改v_teacher的视图定义，添加WITH CHECK OPTION选项。

USE teaching

GO

ALTER VIEW v_teacher

AS

SELECT *

FROM teacher

WHERE department = '计算机学院'

WITH CHECK OPTION

GO

07 程序代码如下：

--通过视图v_teacher向基本表teacher中分别插入数据('05039', '张馨月', '计算机应用', '讲师', '计算机学院')和('06018', '李诚', '机械制造', '副教授', '机械学院')，并查看插入数据情况。

USE teaching

GO

INSERT INTO v_teacher VALUES('05039','张馨月','计算机应用','讲师','计算机学院')

INSERT INTO v_teacher VALUES('06018','李诚','机械制造','副教授','机械学院')

GO

SELECT * FROM v_teacher

SELECT * FROM teacher

08 程序代码如下：

--通过视图v_teacher将基本表teacher中教师编号为'05039'的教师职称修改为'副教授

大三 sql 课后习题答案

'。

USE teaching

GO

UPDATE v_teacher

SET prof = '副教授'

WHERE teacherno = '05039'

GO

SELECT * FROM teacher

第八章

3 上机练习题

01 程序代码如下：

--创建一个名称为StuInfo的存储过程，要求完成以下功能：

--在student表中查询级学生的学号、姓名、性别、出生日期和电话个字段的内容

USE teaching

--查询是否已存在此存储过程，如果存在，就删除它

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'StuInfo' AND type = 'P')

DROP PROCEDURE StuInfo

GO

--创建存储过程

CREATE PROCEDURE StuInfo

AS

Select studentno,sname,sex,birthday,phone

FROM student

WHERE substring(studentno,1,2)='08'

GO

02 程序代码如下：

--创建一个存储过程ScoreInfo，

--完成的功能是在表student、表course和表score中查询以下字段：学号、姓名、性别、课程名称、期末分数。

USE teaching

--查询是否已存在此存储过程，如果存在，就删除它

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'ScoreInfo' AND type = 'P')

大三 sql 课后习题答案

DROP PROCEDURE ScoreInfo

GO

--创建存储过程

CREATE PROCEDURE ScoreInfo

AS

Select

student.studentno,student.sname,student.sex,ame,Score.final FROM student,course,score

WHERE student.studentno=score.studentno

and score.courseno=course.courseno

GO

03 程序代码如下：

--创建一个带有参数的存储过程Stu_Age，

--该存储过程根据输入的学号，在student表中计算此学生的年龄，

--并根据程序的执行结果返回不同的值，程序执行成功，返回整数，如果执行出错，则返回错误号。

--删除已存在的存储过程

USE teaching

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'Stu_Age' AND type = 'P')

DROP PROCEDURE Stu_Age

GO

--创建存储过程

USE teaching

GO

CREATE PROCEDURE Stu_Age

@studentNO nvarchar(10),@Age int OUTPUT

AS

--定义并初始化局部变量，用于保存返回值

DECLARE @ErrorValue int

SET @ErrorValue=0

--求此学生的年龄

SELECT @Age=YEAR(GETDATE())-YEAR(birthday)

FROM student

WHERE studentno=@studentNO

--根据程序的执行结果返回不同的值

IF (@@ERROR<>0)

SET @ErrorValue=@@ERROR

RETURN @ErrorValue

GO

大三 sql 课后习题答案

04 程序代码如下：

-- ================================================

-- 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 ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Name

-- Create date:

-- Description:

-- =============================================

CREATE TRIGGER dbo.TR_Stu_Insert

ON dbo.student

AFTER INSERT

AS

BEGIN

DECLARE @msg nchar(30)

SET @msg='你插入了一条新记录！'

PRINT @msg

END

GO

05 程序代码如下：

--创建一个AFTER触发器，要求实现以下功能：

--在score表上创建一个插入、更新类型的触发器TR_ScoreCheck，

--当在score字段中插入或修改考试分数后，触发该触发器，检查分数是否在-100之间。

USE teaching

大三 sql 课后习题答案

GO

CREATE TRIGGER TR_ScoreCheck

ON score

FOR INSERT, UPDATE

AS

IF UPDATE(final )

PRINT 'AFTER触发器开始执行……'

BEGIN

DECLARE @ScoreValue real

SELECT @ScoreValue=(SELECT final FROM inserted)

IF @ScoreValue>100 OR @ScoreValue<0

PRINT '输入的分数有误，请确认输入的考试分数！'

END

GO

06 程序代码如下：

--创建一个INSTEAD OF触发器，要求实现以下功能：

--在course表上创建一个删除类型的触发器TR_NotAllowDelete，

--当在course表中删除记录时，触发该触发器，显示不允许删除表中数据的提示信息。

USE teaching

GO

IF EXISTS(SELECT name FROM sysobjects

WHERE name ='TR_NotAllowDelete' AND type = 'TR')

DROP TRIGGER TR_NotAllowDelete

GO

CREATE TRIGGER TR_NotAllowDelete

ON course

INSTEAD OF DELETE

AS

PRINT 'INSTEAD OF 触发器开始执行……'

PRINT '本表中的数据不允许被删除！不能执行删除操作!'

GO

第九章

3 上机练习题

01 程序代码如下：

--创建在score表上执行UPDATE语句的事务UP_score，并执行。

大三 sql 课后习题答案

USE teaching

GO

BEGIN TRAN

UPDATE dbo.score

SET usually=77,final=88

WHERE studentno='0824113307' AND courseno='c05109'

COMMIT TRAN

GO

02 程序代码如下：

--练习使用ROLLBACK TRANSACTION语句回滚事务，并查看。

USE teaching

GO

BEGIN TRAN

INSERT INTO course

VALUES('c05133','国际贸易','必修',32,2.0);

SAVE TRAN save1;

DELETE FROM course

WHERE courseno='c05137';

ROLLBACK TRAN save1;

COMMIT TRAN

GO

03 程序代码如下：

--练习在student表上创建嵌套事务，分别在内层和外层设置回滚点，检测回滚对表数据的影响。

USE teaching

GO

BEGIN 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'

大三 sql 课后习题答案

WHERE studentno='0828261367';

ROLLBACK TRAN save1;

COMMIT TRAN tran2;

SELECT * FROM student WHERE studentno='0828261367';

GO

04 程序代码如下：

--练习在student表上创建嵌套事务，并利用系统变量@@TRANCOUNT编程，

--检测嵌套事务的执行情况。

USE teaching

GO

BEGIN 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';

GO

05 程序代码如下：

--练习在student表上进行查询、插入和更新，然后使用sys.dm_tran_locks视图查看锁的信息。

USE teaching;

GO

大三 sql 课后习题答案

BEGIN TRAN

SELECT studentno,sname

FROM student

--WITH(holdlock, rowlock)

WHERE studentno='0828261367';

INSERT INTO student

VALUES('1028261001','孙释远','女

','1989-09-09','090512',777,'1328909876','sdqd@');

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_description

FROM sys.dm_tran_locks

WHERE resource_database_id=DB_ID('teaching');

--提交事务

COMMIT TRAN

第十章

3 上机练习题

01 程序代码如下：

USE master

GO

CREATE LOGIN USER1 WITH PASSWORD =' Abc!@#213'

03 程序代码如下：

--练习在teaching数据库中为SQL Server登录名USER1添加数据库用户，并取名为USER2，默认架构为TEAC。

USE teaching

GO

CREATE USER USER2 FOR LOGIN USER1

WITH DEFAULT_SCHEMA =TEAC

GO

大三 sql 课后习题答案

05 程序代码如下：

--练习将teaching数据库中创建表的权限授予用户USER2。

USE teaching

GO

GRANT CREATE TABLE TO USER2

GO

第十一章

3 上机练习题

01 程序代码如下：

--完整数据库备份

USE teaching

GO

BACKUP DATABASE teaching

TO DISK = N'F:\sqlprogram_ex\第章\backteaching'

WITH EXPIREDATE = N'12/22/2009 00:00:00',

NAME = N'teaching-完整数据库备份',

STATS = 10

GO

--差异备份

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-差异数据库备份',

SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

declare @backupSetId as int

select @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

begin

raiserror(N'验证失败。找不到数据库“teaching”的备份信息。', 16, 1) end

大三 sql 课后习题答案

RESTORE VERIFYONLY

FROM DISK = N'F:\sqlprogram_ex\第章\backteaching'

WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

GO

02 程序代码如下：

--对数据库teaching进行恢复

RESTORE DATABASE teaching

FROM DISK = N'F:\sqlprogram_ex\第章\backteaching'

WITH FILE = 2, NORECOVERY, NOUNLOAD,

STATS = 10

GO

RESTORE DATABASE teaching

FROM DISK = N'F:\sqlprogram_ex\第章\backteaching'

WITH FILE = 3, NOUNLOAD,

STATS = 10

GO

03 程序代码如下：

--创建备份设备

USE master

GO

EXEC master.dbo.sp_addumpdevice

@devtype = N'disk',

@logicalname = N'device1',

@physicalname = N'F:\sqlprogram_ex\第章\back_device1.bak'

GO

--备份teaching数据库的事务日志

BACKUP LOG teaching

TO device1

WITH RETAINDAYS = 3, NOFORMAT, NOINIT,

NAME = N'teaching-事务日志备份',

SKIP, NOREWIND, NOUNLOAD,

STATS = 10

GO

第十二章

3 上机练习题

大三 sql 课后习题答案

02 程序代码如下：

USE msdb

GO

DECLARE @jobId BINARY(16)

EXEC msdb.dbo.sp_add_job @job_name=N'student',

GO

EXEC msdb.dbo.sp_add_jobserver @job_name=N'student', @server_name = N'JIANG-N9VJWXJ11'

GO

DECLARE @schedule_id int

EXEC msdb.dbo.sp_add_jobschedule @job_name=N'student', @name=N'plan1',

GO

03 程序代码如下：

USE msdb

GO

EXEC msdb.dbo.sp_add_alert @name=N'alert1',

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'alert1',

@operator_name=N'jiang', @notification_method = 1

GO

04程序代码如下：

USE msdb

GO @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' @active_start_date=20091222, @active_end_date=99991231, @active_start_time=100000, @active_end_time=235959, @schedule_id = @schedule_id OUTPUT @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'JIANG-N9VJWXJ11\Administrator', @job_id = @jobId OUTPUT

本文来源：https://www.bwwdw.com/article/f2b4.html