实验三具体要求

1、数据库系统实验二具体要求课程代码： 课程名称： 数据库系统实验名称： 数据库编程1.实验目的1) 熟悉存储过程的创建; 2) 熟悉存储过程的调用； 熟悉带输入参数和输出参数的存储过程。 2.实验工具MS SQL server3.实验要求(1) 查询学生表中的所有学生，并实现调用。CREATE PROCEDURE dbo.stu1ASBEGINSELECT * from studentEND(2) 根据学生的学号，查询该生的详细信息，并实现调用。CREATE PROCEDURE STU2stuno char(9)AS begin select * from student where Sno=s

2、tuno end调用：exec STU2 stuno=200215121(3) 根据学生的学号，查询学生的姓名（姓名为输出参数），并实现调用。create procedure stu6stuno char(9), stuname char(20) outputasbeginselect stuname=sname from student where Sno=stunoend调用：declare stuname1 char(20)exec stu6 200215122, stuname1 outputprint stuname1(4) 删除学号为sno的学生，并实现调用。create proc

3、edure stu7stuno char(9)As delete from student where sno=stuno调用：exec stu7 200215121(5) 修改学号为sno学生的姓名，性别，年龄，系别，并实现调用。create procedure stu8stuno char(9),stuname char(20),stusex char(2),stuage smallint,studept char(20)asbeginupdate studentset Sname=stuname, Ssex=stusex, Sage=stuage, Sdept=studeptwhere

4、sno=stunoend调用：exec stu8 stuno=200215121, stuname=hello, stusex=女, stuage=22, studept=MS(6) 向学生表中插入学生，并实现调用。create procedure stu9stuno char(9),stuname char(20),stusex char(2), stuage smallint,studept char(20)asbegininsert into student values(stuno, stuname, stusex, stuage, studept)end调用：exec stu9 st

5、uno=200215125, stuname=张军, stusex=男, stuage=20,studept=MA(7) 查询姓名为sname选修的数据库课程的成绩，并实现调用。create procedure stu10sname char(20)as beginselect gradefrom student,course,scwhere student.sno=sc.sno and o=o and cname=数据库 and sname=snameendexec stu10 sname=hello方法二：实现输出ALTER procedure dbo.stu12sname char(20

6、),gr smallint outputas beginselect gr=gradefrom student,course,scwhere student.sno=sc.sno and o=o and cname=数据库 and sname=snameenddeclare gr smallintexec stu12 hello, gr outputprint gr查询选修了课程名为cname并且成绩高于该门课程平均分的学生学号和成绩，并实现调用。SQL 语句：select sno,grade from scwhere grade=(select avg(grade) from sc wher

7、e cno in(select cno from course where cname=数据库)create procedure stu13cname char(20)asbeginselect sno,grade from scwhere grade=(select avg(grade) from sc where cno in(select cno from course wherecname=cname)end调用：exec stu13 数据库create procedure stu14cname char(20),noo char(8) output,gr smallint outputasbeginselect noo=sno,gr=grade from scwhere grade=(select avg(grade) from sc where cno in(select cno fr