Sql应用 复习题

**

–Sql应用 复习题

**
–（1）利用Transact-SQL语句创建一个新的数据库，名称为store，该数据库包含一个数据文件和一个日志文件。数据文件的逻辑文件名为storeDB1_data，

–磁盘文件名为storeDB1-data.mdf，文件初始容量为5MB，最大容量为17MB，文件递增容量为1MB（数据库创建在D：\SERVER文件夹下），其它所有参数均取默认值。

Create database store

on(

name=storedb1_data,

filename='D:\SERVER\storedb1_data.mdf',

size=5mb,

maxsize=17mb,

filegrowth=1mb

)

–（2）利用Transact-SQL语句在store数据库中创建一个表orders，表结构如下：

列名 数据类型及长度 是否为空 备注

订单编号 nchar(10) 否 主键

订购日期 datatime 否 默认值为系统时间

金额 numeric(20,2) 否

USE store

GO

Create table orders

( 订单编号 nchar(10) primary key,

订购日期 datetime default getdate( ),

金额 numeric(10,2)

)

（3）利用Transact-SQL语句向表orders中添加一个“备注”字段，数据类型为nchar(40)。备注字段允许为NULL，并向表orders添加以下三条记录。

订单编号 订购日期 金额 备注

2019061801 2019年06月18日 200.32 加急

2019061810 2019年06月18日 356.66

2019121206 2019年12月12日 103.00

USE store

GO

Alter table orders add 备注 nchar(40);



Insert into orders values('2019061801', '2019-06-18',200.32,'加急');

Insert into orders values('2019061810', '2019-06-18',356.66,null);

Insert into orders values('2019121206', '2019-12-12',103.00,null)

–（4）利用Transact-SQL语句，查询表orders所有2019年的订单信息。

Select * from orders where year(订购日期)=2019

–（5）利用Transact-SQL语句，使用日期函数显示今天的日期：’今天的日期是**/**/****’。

PRINT '今天的日期是' + CONVERT(VARCHAR(12), GETDATE(),101)

–（6）利用Transact-SQL语句在store数据库上创建一个存储过程Procsum，对指定“订购日期”的金额求和。“订购日期”由输入参数给定，计算出的金额总和通过输出参数返回。若该存储过程已存在，则删除后重建。然后执行该存储过程，显示2019年6月18日的金额总和。

USE store

GO

IF EXISTS(SELECT * FROM sysobjects WHERE name=' Procsum' and type='P')

DROP PROCEDURE Procsum

GO

CREATE PROCEDURE Procsum

@orderdate datetime, @sum numeric(20,2) OUTPUT

AS

SELECT @sum =sum(金额)

FROM orders

WHERE 订购日期=@orderdate

GO



执行：

DECLARE @s numeric(20,2)

EXEC Procsum @orderdate = '2019-6-18', @sum =@s OUTPUT

SELECT @s

–（7）利用Transact-SQL语句创建一个AFTER触发器，要求实现以下功能：在orders表上创建一个插入、更新类型的触发器TR_expro，

–当在expro字段中插入或修改“金额”后触发该触发器，检查“金额”值是否大于500，如果“金额”值大于500则输出提示信息：“金额”值大于500，请注意！。

USE store

GO

CREATE TRIGGER TR_orders ON orders AFTER INSERT,UPDATE AS

BEGIN

DECLARE @money numeric(10,2)

SELECT @money=金额 FROM inserted

IF @money>500

print'“金额”值大于500，请注意！’

-raiserror('“金额”值大于500，请注意！',16,1)

END

（8）利用Transact-SQL语句添加登录名为CLASS，密码为BESTtoYOU的SQL登录账户，并映射为数据库store的数据库用户HANS。配置数据库用户HANS对数据库store的表orders有查询和删除的权限。

create login class with password=‘besttoyou’;

use store

go

create user HANS for login class

go

Grant select,delete on orders to hans



或者

Sp_addlogin ‘class’,‘besttoyou’

Sp_grantdbaccess ‘class’,‘hans’

Grant select,delete on orders to hans

数据库备份有关题目

1、 --练习对已有数据库teaching创建完整数据库备份和差异备份。 --完整数据库备份

BACKUP DATABASE teaching

TO DISK = N’D:\DATA\backteaching.bak’

WITH INIT;

GO

–差异备份

BACKUP DATABASE teaching

TO DISK = N’D:\DATA\backteachingdiff.bak’

WITH DIFFERENTIAL ,

EXPIREDATE = N’07/06/2017 00:00:00’,

NOFORMAT, NOINIT,

NAME = N’teaching-差异数据库备份’,

SKIP, NOREWIND, NOUNLOAD, STATS = 10

2、 --对数据库teaching进行恢复

RESTORE DATABASE teaching

FROM DISK = N’D:\DATA\backteaching.bak’

WITH REPLACE

GO

3、

–创建备份设备

USE master

GO

EXEC sp_addumpdevice ‘disk’, ‘device1’, ‘D:\DATA\device1’

GO

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

BACKUP LOG teaching

TO device1

WITH RETAINDAYS = 3, NOFORMAT, NOINIT,

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

SKIP, NOREWIND, NOUNLOAD,

STATS = 10

GO

161页

（3）

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

select teacherno,tname,major from teacher

where prof=‘教授’

go

（4）

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

select sex as ‘学生性别’,

avg(DATEDIFF(year,birthdate,getdate())) as ‘平均年龄’

from student

group by sex

go

（5）

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

–其中：总评成绩=final0.8+daily0.2

select student.studentno,student.sname,score.courseno,final0.8+daily0.2 as ‘总评’

into stu_sorse

from student,score

where student.studentno=score.studentno

go

select * from stu_sorse

go

（6）

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

select studentno,sname,avg(总评)

from stu_sorse

group by studentno,sname

go

（7）

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

select * from student

where birthdate=

(select min(birthdate)

from student)

go

（8）

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

select teacherno,tname,major,department

from teacher

where prof is NULL

go

第七章186页

（1）

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

select teacher.teacherno,tname,major,course.cname

from teacher,course,teach_class

where prof =‘教授’ and teacher.teacherno =teach_class.teacherno

and teach_class.courseno=course.courseno

go

（2）

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

–其中：总评成绩=final0.9+daily0.1

select DISTINCT student.studentno,student.sname,course.cname,score.final0.9+score.daily0.1 as ‘总评’

into stu_course

from student,course,teach_class,score

where student.studentno=score.studentno

and course.courseno=score.courseno

go

（3）

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

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

（4）

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

select * from student

where sex= ‘男’

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

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

from student

where sex= ‘女’)

go

（5）

–计算每个学生获得的实验学时。

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

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

（6）

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

select student.studentno,student.sname,stu_course.cname

from student inner join stu_course

on student.studentno=stu_course.studentno

where (substring(student.studentno,1,2)=‘17’ and (datediff(year,birthdate,‘2018-01-01’)<19))

or(substring(student.studentno,1,2)=‘17’ and (datediff(year,birthdate,‘2017-01-01’)<19))

go

（7）

–查询18级学生的学号、姓名、课程名。

select student.studentno,student.sname,course.cname

from student,score,course

where student.studentno=score.studentno and course.courseno=score.courseno and substring(student.studentno,1,2)=‘18’

（8）

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

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

P220、索引和视图

3、上机题

（1）

CREATE NONCLUSTERED INDEX IDX_cname ON course(cname)

GO

（2）

IF EXISTS(SELECT name FROM sysindexes WHERE name=‘UQ_stu’) DROP INDEX student.UQ_stu

GO

CREATE unique INDEX UQ_stu ON student(studentno,classno)

GO

SELECT * FROM student

（3）

ALTER INDEX UQ_stu ON student REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 80)

GO

（4）

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

CREATE VIEW v_avgstu

AS

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

（5）

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

ALTER VIEW v_teacher

AS

SELECT *

FROM teacher

WHERE department = ‘计算机学院’

WITH CHECK OPTION

GO

（6）

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

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

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

GO

SELECT * FROM v_teacher

SELECT * FROM teacher

（7）

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

UPDATE v_teacher

SET prof = ‘副教授’

WHERE teacherno = ‘05039’

GO

SELECT * FROM teacher

P238、触发器

（1）

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

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

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

IF EXISTS (SELECT name FROM sysobjects

WHERE name = ‘StuInfo’ AND type = ‘P’)

DROP PROCEDURE StuInfo

GO

–创建存储过程

CREATE PROCEDURE StuInfo

AS

Select studentno,sname,sex,birthdate,phone

FROM student

WHERE substring(studentno,1,2)=‘18’

GO

（2）

–创建一个存储过程ScoreInfo，

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

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

IF EXISTS (SELECT name FROM sysobjects

WHERE name = ‘ScoreInfo’ AND type = ‘P’)

DROP PROCEDURE ScoreInfo

GO

–创建存储过程

CREATE PROCEDURE ScoreInfo

AS

Select student.studentno,student.sname,student.sex,course.cname,Score.final

FROM student,course,score

WHERE student.studentno=score.studentno

and score.courseno=course.courseno

GO

（3）

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

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

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

–删除已存在的存储过程

IF EXISTS (SELECT name FROM sysobjects

WHERE name = ‘Stu_Age’ AND type = ‘P’)

DROP PROCEDURE Stu_Age

GO

–创建存储过程

USE teaching2016

GO

CREATE PROCEDURE Stu_Age

@studentNO nvarchar(10),@Age int OUTPUT

AS

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

DECLARE @ErrorValue int

SET @ErrorValue=0

–求此学生的年龄

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

FROM student

WHERE studentno=@studentNO

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

IF (@@ERROR<>0)

SET @ErrorValue=@@ERROR

RETURN @ErrorValue

GO

（4）

CREATE TRIGGER dbo.TR_Stu_Insert

ON dbo.student

AFTER INSERT

AS

BEGIN

DECLARE @msg nchar(30)

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

PRINT @msg

END

GO

（5）

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

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

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

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

（6）

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

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

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

IF EXISTS(SELECT name FROM sysobjects

WHERE name =‘TR_NotAllowDelete’ AND type = ‘TR’)

DROP TRIGGER TR_NotAllowDelete

GO

CREATE TRIGGER TR_NotAllowDelete

ON course

AFTER DELETE

AS

PRINT ‘INSTEAD OF 触发器开始执行……’

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

GO