数据库练习题7--创建视图、存储过程和触发器对象

实验目的 掌握创建视图、存储过程和触发器对象的使用方法；

1.创建一个只选修8号课程的学生情况的视图信息

create view stu_8
as
select student.sno,student.sname,sage,ssex
from student,sc
where student.sno=sc.sno and cno=8

2.创建一个视图view_stu，只包含学生的学号，姓名，年龄信息

create view view_stu
as
select sno,sname,sage
from student

3.在上一题视图的基础上创建年龄在20岁以上的的学生的视图

select sno,sname,sage 
from view_stu
where sage>20

4.创建一个包含不及格同学及90分以上同学的学号、姓名、课程名称及成绩的视图

select student.sno,student.sname,cname,grade 
from student,sc,course
where student.sno=sc.sno and 
sc.cno=course.cno and 
(grade>90 or grade<60)

5.选择一个上面创建的视图，查看视图的信息

select sno,sname,sage
from stu_8

6. 选择一个上面创建的视图进行删除操作

drop view stu_8

7.从视图view_stu中查询19岁的男生记录

select * from view_stu
where sage=19

8.通过视图view_stu中插入一行数据。学号：2006010010，姓名：赵青，年龄：

insert into view_stu
values(200610,赵青,)
select * from view_stu
where sname=赵青

9.将视图view_stu中姓名为“李蕾”的年龄改为“22”

update view_stu
set sage=22
select * from view_stu
where sname=李蕾

10.通过视图 view_stu将姓名为“李贵”的记录删除

delete from view_stu
where sname=李贵

11.自行选择上面创建的一个视图插入一个记录。

insert into view_stu
values (20000203,米米,19)

12.自行选择一个视图根据一定的条件进行查询。

select * from view_stu
where sage=19

存储过程 1.创建存储过程返回全部男生选课及成绩等相关信息

create procedure  stu_j
as
select distinct a.sno,sname,cno,grade
from student a,sc
where a.sno=sc.sno and ssex=男
exec stu_j

2.创建一个带输入参数的存储过程，能够根据参数向学生表中插入一条记录

create procedure student_insert
(@sno char(9),@sname char(8),
@sage int,@ssex char(2),
@sdept char(5))
as
insert into student(sno,sname,sage,ssex,sdept)
values(@sno,@sname,@sage,@ssex,@sdept)
exec student_insert @sno=20000203,
@sname=子子,@sage=19,@ssex=男,@sdept=cs

3.创建一个带输入参数的存储过程，根据不同的学生名参数，返回该学生的所有课程成绩。

create proc M3 @sname char(10)
as 
select cno ,grade
from sc join student on sc.sno = student.sno
where sname = @sname
exec M3 @sname=刘备

4.创建一个带输入输出参数的存储过程，根据输入的系部名，统计该系学生的平均成绩并输出。

create proc M4 @sdept char(8), @avg int output
as 
select @avg=AVG(grade)
from sc join student on sc.sno = student.sno
where sdept=@sdept
declare @x int
exec M4 @sdept=CS,@avg=@x output
print @x

触发器 1.创建student表的DELETE触发器，当一个学生退学后，信息从student表中删除，触发在sc表中删除该生选课情况。

create trigger M1
on sc for delete
as
delete grade
from sc
where sno is null

2.创建一个触发器，检查在sc表中输入一门课程成绩，必须符合在0到100分范围内。

create trigger M2
on sc after insert ,update
as
if exists (
select * from inserted where grade not between 0 and 100 
)
rollback