SQLSEVER存储过程和触发器.doc

Student(S#,Sname,Sage,Ssex,Sdept, sselnum) 学生表 S5Course(C#,Cname,T#,score) 课程表 SC(S#,C#,grade) 成绩表 s1Teacher(T#,Tname) 教师表创建存储过程，实现以下操作：1、 统计每一个学生的平均成绩,要求显示学生姓名和平均成绩.If exists(select name from sysobjects where name=p1 and type=p) Drop pro p1Create procedure p1asSelect sname,avg(grade)From sc,studentWhere sc.s#=student.s#Group by s#2、统计学生所选课程所得的总学分,要求显示系科名,学号,姓名和总学分.Create procedure p2asSelect student.S#,Sname,Sage,Sdept,sum(score)From sc,course,studentWhere sc.c#=c.c# and student.s#=sc.s#Group by s#3、建立一存储过程,用户输入学生姓名,学号,如果student表中存在此项,则删除后再插入,若不存在此学生,则直接插入该记录.Create procedure p3 no char(8),name char(10)as begin if exists(select * from student where s#=no and sname=name) begin delete from student where s#=no and sname=name insert into student(S#,Sname) values(no,name)end elseinsert into student(S#,Sname) values(no,name)end4、根据用户键入的学生姓名,查询学生的年龄Create procedure p4 name char(10)as select sage from student where sname=name5、根据用户键入的学生学号和课程号,查询学生的成绩Create procedure p5 no char(8),cno char(4)as select grade from sc where s#=no and c#=cno6、 建立一存储过程,使用户输入系别,查询该系学生选择各课程的情况.(模糊查询)列出系别,课程号以及对应的人数,并按系别排序.【Sel_Course】Create procedure p6 de char(10)asSelect sdept,sc.c#,count(sc.c#)From student,sc,Where student.s#=sc.s# and sdept=dGropu by sc.c#Order by sc.c#7、建立一存储过程,使用户输入课程名时,列出该课程名,对应的课程号,选择该课程的学生总人数,平均分【Sel_Stu】Create procedure p7 cn char(10)asSelect sc.c#,cname,count(s#),avg(grade)From course,scWhere course.c#=sc.c# and cname=cn创建触发器，实现以下作用：1、 创建一个Insert触发器,功能是当在sc上插入一行数据时,自动使得student表对应学号上的sselnum字段的值加1Create trigger t1 on sc for insertas update student set sselnum=sselnum+1 where s#=(select s# from inserted)2、 创建一个Insert触发器,功能是当在sc上插入数据时,自动更新student表对应学号上的sselnum字段,应考虑成批修改数据的情况.3、 建立一触发器,当删除student表中某学生记录时,同时删除sc表中所有该学生的选课记录.Create trigger t3 on student for deleteAs Delete from sc Where s#=( select s# from deleted)4、 使用IF UPDATE()保留字,实现当修改Student表中的sno属性时,sc表中相应的sno也要修改Deleted insertedCreate trigger t4