建立一个存储过程要求根据部门参数,显示该部门中拿单项奖学金的男生人数和女生人数,拿学期奖学金的男生人数和女生人数,需要补考的男生人数和女生人数以及补考课程数,以及该部门该学期的总学分数:(5%) 条件: 单门成绩超过90分(包含)的有单项奖学金 所有参加的考试都超过90分(包含)得有学期奖学金单门成绩低于60分(不包含)的需要补考,并无学分
求同部门同学科的成绩相同的人数
求同部门同学科的成绩相同的人数
SC student course
三个表相互关联
STUDENT/ SNO SNAME SSEX SAGE SDEPT
COURSE/ CNO CNAME CCREDIT (学分)
create procedure p_findStudent
@SDEPT varchar(5)
as
begin
declare @str nvarchar(100)
declare @man_cnt int
declare @woman_cnt int
declare @course_cnt int
--单项奖学金
select
@man_cnt = sum(case when SSEX = '男' then 1 else 0 end)
,@woman_cnt = sum(case when SSEX = '女' then 1 else 0 end)
from STUDENT a
where exists(select 1 from SC where SNO = a.SNO and GRADE >= 90)
and a.SDEPT = @SDEPT
--输出信息
print('部门 ' + @SDEPT + ' 获得单项奖学金共计:男 ' + cast(isnull(@man_cnt,0) as varchar)
+ ' 人, 女 ' + cast(isnull(@woman_cnt,0) as varchar) + ' 人。'); --学期奖学金
select
@man_cnt = sum(case when SSEX = '男' then 1 else 0 end)
,@woman_cnt = sum(case when SSEX = '女' then 1 else 0 end)
from STUDENT a
where not exists(select 1 from SC where SNO = a.SNO and GRADE < 90)
and a.SDEPT = @SDEPT --输出信息
print('部门 ' + @SDEPT + ' 获得学期奖学金共计:男 ' + cast(isnull(@man_cnt,0) as varchar)
+ ' 人, 女 ' + cast(isnull(@woman_cnt,0) as varchar) + ' 人。'); --补考学生
select
@man_cnt = sum(case when SSEX = '男' then 1 else 0 end)
,@woman_cnt = sum(case when SSEX = '女' then 1 else 0 end)
from STUDENT a
where exists(select 1 from SC where SNO = a.SNO and GRADE < 60)
and a.SDEPT = @SDEPT select @course_cnt = count(distinct CNO)
from STUDENT a inner join SC b on a.SNO = b.SNO
where a.SDEPT = @SDEPT and b.GRADE < 60 --输出信息
print('部门 ' + @SDEPT + ' 补考学生共计:男 ' + cast(isnull(@man_cnt,0) as varchar)
+ ' 人, 女 ' + cast(isnull(@woman_cnt,0) as varchar)
+ ' 人, 补考课程共计 ' + cast(@course_cnt as varchar) + ' 门');
end