同济大学数据库-实验六答案

1．定义视图并在视图上完成数据的查询功能。

1）建立“查询有选课记录的学生学号、课程号、课程名称、成绩信息”操作的视图。

CREATE VIEW S_score

AS

SELECT sc.snum,https://www.360docs.net/doc/20263045.html,um,

https://www.360docs.net/doc/20263045.html,ame, sc.score

FROM sc join sections on

sc.secnum=sections.secnum Join

course on https://www.360docs.net/doc/20263045.html,um=https://www.360docs.net/doc/20263045.html,um

2）在上述视图的基础上完成查询：查询所有学生都及格的课程名称。

SELECT cname AS'所有学生都及格的课名称' FROM S_score

WHERE cname NOT IN(SELECT cname

FROM S_score WHERE score<60)

或

SELECT cname AS所有学生都及格的课程名称FROM S_score

GROUP BY cname Having min(score)>=60

2．存储过程的建立与执行

1）建立存储过程，其功能为显示所有学生的基本信息CREATE PROCEDURE学生信息

AS

SELECT*FROM student

==========

exec 学生信息

2）建立存储过程，其功能是查询给定出生年份信息（已知出生日期的前4位的信息）的学生信息

CREATE PROC student_proc

@_year INT

AS

SELECT*

FROM student

WHERE YEAR(birthday)=@_year

===========

exec student_proc 1988

3．建立一组触发器，并设计一组必要的数据操作，验证触发器的功能

1）自定义一个触发器，完成选课表sc属性snum的参照完整性控制。

CREATE TRIGGER SC_insert ON sc

FOR INSERT,UPDATE AS

BEGIN

IF((SELECT snum FROM inserted)NOT IN (SELECT snum FROM student))

BEGIN

PRINT '没有该学生，撤销操作'

ROLLBACK

END

IF((SELECT secnum FROM inserted)NOT IN (SELECT secnum FROM sections)) BEGIN

PRINT'没有该班级，撤销操作'

ROLLBACK

END

END

========

测试1:插入一个不存在的学生的选课记录

insert into sc

values('s009','11601',78)

测试2:插入一个不存在的班级的选课记录

insert into sc

values('s008','11622',78)

2）自定义一个触发器，完成教课表sections的数据完整性控制。

CREATE TRIGGER sections_delete

ON sections FOR DELETE AS

BEGIN

If exists(select*from sc where

secnum in(select secnum from deleted)) begin

Raiserror('该班级有学生选课，

同时删除学生的选课记录！',16,1)

DELETE FROM sc WHERE secnum in

(select secnum from deleted)

end

else

Raiserror('该班级无学生选课，

删除该班级！',16,1)

END

测试1:删除一个有学生选课的班级.

delete from sections where secnum='11601'

测试2:删除一个无学生选课的班级.

首先插入一个新开班级14001:

insert into sections

values('14001','c120','p005')

然后删除14001班级：

delete from sections where secnum='14001'