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