SQL存储过程实例详解 - 数据库编程

LUES('F002','M007',77) INSERT INTO Score(FID,MID,Score)VALUES('F003','M008',65) INSERT INTO Score(FID,MID,Score)VALUES('F004','M007',48) INSERT INTO Score(FID,MID,Score)VALUES('F004','M009',75) INSERT INTO Score(FID,MID,Score)VALUES('F002','M009',88) 标准答案： -- 1)查询各个学生语文、数学、英语、历史课程成绩--

SELECT Member.MName AS 姓名,
英语 = SUM(CASE F.FName WHEN '语文' THEN Score.Score END), 
数学 = SUM(CASE F.FName WHEN '数学' THEN Score.Score END), 
语文 = SUM(CASE F.FName WHEN '英语' THEN Score.Score END), 
历史 = SUM(CASE F.FName WHEN '历史' THEN Score.Score END) 
FROM Score, Member,F 
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

SQL存储过程实例详解(三)