SQL语句之函数

1. 例题一
   查询指定学生已经得到的修课总学分（考试及格的课程才能拿到学分），学号为输入参数，总学分为函数返回结果。

   create function fun1(@sno char(10)) returns int 
   as
   begin
   	declare @res int 
   	select @res = sum(credit) from student s
   	inner join sc on sc.sno = sc.sno
   	inner join course c on c.cno = sc.cno
   	where s.sno = @sno and grade > 60
   	return @res
   end
   

2. 例题二
   查询指定系在指定课程（课程号）的考试平均成绩。

   create function fun2(@sdept char(10),@cno char(10)) returns int 
   as
   begin
   	declare @res int
   	select @res=avg(grade) from student s
   	inner join sc on sc.sno = s.sno
   	group by sdept,cno
   	having sdept = @sdept and cno = @cno
   	return @res
   end
   

3. 查询指定系的男生中选课门数超过指定门数的学生人数。

   create function fun3(@sdept char(10),@num int) returns int
   as
   begin
   	declare @res int
   	select @res = count(*) from student s
   	inner join sc on sc.sno = s.sno
   	where ssex = '男' and sdept = @sdept
   	group by s.sno
   	having count(*) > @num
   return @res
   end