SQL函数 例题

创建一个自定义函数F1，能够通过输入学号返回学生的平均成绩

create function F1 (@sno char(12))
returns char(12)
as
begin
	declare @grade numeric(5,1)
	select @grade=avg(Grade) from Grade where StudentID=@sno
	return @grade
end

创建一个自定义函数F2：完成通过系部名称查询系部学生人数的功能

create function F2(@dname varchar(20))
returns int
as
begin
declare @num int
select @num=count(StudentID)from Department join Class 
on Class.DepartmentID=Department.DepartmentID join Student
on Student.ClassID=Class.ClassID where DepartmentName=@dname
return @num
end

创建一个自定义函数F3：能够通过输入的学生学号能够返回该学生的的所属班级名称

create function F3(@sno char(12))
returns varchar(20)
as
begin
declare @cname varchar(20)
select @cname=ClassName from Class join Student
on Student.ClassID=Class.ClassID where StudentID=@sno
return @cname
end

创建一个自定义函数F4：能够通过输入的学生姓名返回这个学生姓名、选修课程名称和对应课程的成绩(提醒：表值函数)

create function F4(@sname char(8))
returns @result table
(
	StudentName char(8),
	CourseName varchar(20),
	Grade numeric(5,1)
)
as
begin
insert into @result
select StudentName,CourseName,Grade from Student join Grade
on Grade.StudentID=Student.StudentID join Course
on Course.CourseID=Grade.CourseID where StudentName=@sname
return
end

创建一个自定义函数F5：完成通过教师号查询教师姓名的功能

create function F5(@tno char(8))
returns char(12)
as
begin 
	declare @tname char(12)
	select @tname=Teachername from Teacher where TeacherID=@tno
	return @tname
end

 创建一个自定义函数F6，能够通过输入班级号返回该班的男生人数

create function F6(@cno char(8))
returns int
as
begin
	declare @num int
	select @num=count(*) from Student where Sex='男'
	return @num
end

创建一个自定义函数F7，能够通过输入课程号返回该课程的平均成绩

create function F7(@courseno char(8))
returns numeric(5,1)
as
begin
	declare @avg numeric(5,1)
	select @avg=avg(Grade) from Grade where CourseID=@courseno
	return @avg
end