SQL存储过程实例(3)

WHERE F.FID = Score.FID AND Member.MID =Score.MID GROUP BY Member.MName -- 2)查询四门课中成绩低于70分的学生及相对应课程名和成绩-- select 姓名=(select MName from Member where MID=Score.MID),课程名=(select FName from F where FID=Score.FID),成绩=Score from Score where Score<70 -- 3)统计各个学生四课程的平均分，且按平均分数由高到底排序-- select 姓名=(select MName from Member where MID=Score.MID),平均分=Avg(Score) from Score group by MID order by 平均分 desc -- 4)创建存储过程-- if exists (select * from sysobjects where name='P_stu') drop procedure P_stu GO create procedure P_stu @num int As print'参加'+convert(varchar(5),@num)+'门课考试的学生姓名及学号：' select 姓名=(select MName from Member where MID=Score.MID),学号=MID from Score group by MID having count(*)=@num --调用存储过程-- exec P_stu @num=2