SQL|使用SQL语句创建并执行存储过程

1、利用学生姓名查询该生选修的课程名、成绩，以及任课教师姓名。

USE jiaoxuedb
GO
CREATE PROCEDURE Pro_1
@Sname_in char(8) 
AS SELECT Cname,SCORE,Tname FROM Student,SC,Course,TC,Teacher
WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno AND SC.CNO=TC.Cno AND TC.Tno=Teacher.Tno
AND Sname=@Sname_in
GO 

DECLARE @Sname_in char(8)
SELECT @Sname_in='宋大方'
EXEC Pro_1 @Sname_in

2、查询某系的学生的最大年龄和最小年龄。

USE jiaoxuedb
GO
CREATE PROCEDURE Pro_2
@Dept_in char(8),
@Agemax_out int OUTPUT,
@Agemin_out int OUTPUT
AS SELECT @Agemax_out=MAX(Age), @Agemin_out=MIN(Age)FROM Student
GROUP BY Dept HAVING Dept=@Dept_in
GO
DECLARE 
@Dept_in char(8),
@Agemax_out int ,
@Agemin_out int 
SELECT @Dept_in='计算机'
EXEC Pro_2 @Dept_in,@Agemax_out OUTPUT,@Agemin_out OUTPUT
PRINT @Agemax_out
PRINT @Agemin_out

3、利用学生姓名和课程名检索该生该课程的成绩。

USE jiaoxuedb
GO
CREATE PROCEDURE Pro_3
@Sname_in char(8),
@Cname_in char(10),
@Score_out tinyint OUTPUT
AS SELECT @Score_out=Score FROM Student,Course,SC
WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno AND Sname=@Sname_in AND Cname=@Cname_in 
GO

DECLARE 
@Sname_in char(8),
@Cname_in char(10),
@Score_out tinyint 
SELECT @Sname_in='宋大方'
SELECT @Cname_in='计算机基础'
EXEC Pro_3 @Sname_in,@Cname_in,@Score_out OUTPUT
PRINT @Score_out

4、根据职称查询人数,并给出“副教授”的人数。

USE jiaoxuedb
GO
CREATE PROCEDURE Pro_4
@Prof_in char(10),
@Profn_out tinyint OUTPUT
AS SELECT @Profn_out=COUNT(Prof) FROM Teacher
WHERE Dept=@Dept_in
GO

DECLARE 
@Dept_in char(10),
@Profn_out tinyint 
SELECT @Prof_in='副教授'
EXEC Pro_4 @Dept_in,@Profn_out OUTPUT
PRINT @Profn_out

5、统计某系某职称的人数、平均年龄、平均工资、最高工资。

USE jiaoxuedb
GO
CREATE PROCEDURE Pro_5
@Dept_in char(10),
@Prof_in char(10),
@Count_out int OUTPUT,
@Avgage_out int OUTPUT,
@AvgSal_out int OUTPUT,
@MaxSal_out int OUTPUT
AS SELECT @Count_out=COUNT(Prof),@Avgage_out=AVG(Age),@AvgSal_out=AVG(Sal), @MaxSal_out=MAX(Sal) FROM Teacher
WHERE Dept=@Dept_in AND Prof=@Prof_in 
GO

DECLARE 
@Dept_in char(10),
@Prof_in char(10),
@Count_out int,
@Avgage_out int,
@AvgSal_out int,
@MaxSal_out int
SELECT @Dept_in='计算机'
SELECT @Prof_in='讲师'
EXEC Pro_5 @Dept_in,@Prof_in,@Count_out OUT,@Avgage_out OUT,@AvgSal_out OUT,@MaxSal_out OUT
PRINT @Count_out
PRINT @Avgage_out
PRINT @AvgSal_out
PRINT @MaxSal_out

6、查询某系的教师人数、平均年龄和学生人数。

USE jiaoxuedb
GO
CREATE PROCEDURE Pro_6
@Dept_in char(10),
@Count_out int OUTPUT,
@Avgage_out int OUTPUT,
@Snum_out int OUTPUT
AS SELECT @Count_out=COUNT(Prof),@Avgage_out=AVG(Teacher.Age)  FROM Teacher
WHERE Dept=@Dept_in 
SELECT @Snum_out=COUNT(Sno) FROM Student
WHERE Dept=@Dept_in
GO

DECLARE 
@Dept_in char(10),
@Count_out int,
@Avgage_out int,
@Snum_out int
SELECT @Dept_in='计算机'
EXEC Pro_6 @Dept_in,@Count_out OUT,@Avgage_out OUT,@Snum_out OUT
PRINT @Count_out
PRINT @Avgage_out
PRINT @Snum_out

7、利用课程名查询选修该课程的学生姓名、系别、成绩，并给出“程序设计” 课程的该查询信息。

USE jiaoxuedb
GO
CREATE PROCEDURE Pro_7
@Cname_in char(10)
AS SELECT Sname,Dept,Score FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno AND Cname=@Cname_in
GO
DECLARE 
@Cname_in char(10)
SELECT @Cname_in='程序设计'
EXEC Pro_7 @Cname_in

8、利用教师姓名和课程名检索该教师该任课的课程名、课时数、选课人数、平均成绩、最高成绩，并查询教师“张雪” 的“微机原理”课程的情况记录。

USE jiaoxuedb
GO
CREATE PROCEDURE Pro_8
@Tname_in char(10),
@Cname_in char(10),
@Cname_out char(10) OUTPUT,
@Chour_out int OUTPUT,
@Cstun_out int OUTPUT,
@AVGScore_out int OUTPUT,
@MAXScore_out int OUTPUT
AS SELECT @Cname_out=Cname,@Chour_out=Chour,@Cstun_out=COUNT(Sno) ,@AVGScore_out=AVG(Score), @MAXScore_out=MAX(Score) FROM Teacher,SC,Course,TC
WHERE Course.Cno=SC.Cno AND Teacher.Tno=TC.Tno AND TC.Cno=SC.Cno AND Tname= @Tname_in
GROUP BY Cname,Chour HAVING Cname=@Cname_in
GO
DECLARE 
@Tname_in char(10),
@Cname_in char(10),
@Cname_out char(10) ,
@Chour_out int ,
@Cstun_out int ,
@AVGScore_out int ,
@MAXScore_out int 
SELECT @Tname_in='张雪'
SELECT @Cname_in='微机原理'
EXEC Pro_8 @Tname_in,@Cname_in,@Cname_out OUT,@Chour_out OUT,@Cstun_out OUT,@AVGScore_out OUT,@MAXScore_out OUT
PRINT @Cname_out
PRINT @Chour_out
PRINT @Cstun_out
PRINT @AVGScore_out
PRINT @MAXScore_out