SQL函数例题

例题"/>

SQL函数例题

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

create function F2(@dname varchar(20))returns intasbegindeclare @num intselect @num=count(StudentID)from Department join Classon Class.DepartmentID=Department.DepartmentID join Studenton Student.ClassID=Class.ClassID where DepartmentName=@dnamereturn @numendselect dbo.F2('计算机系')

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

create function F3(@sno char(12))returns varchar(20)asbegindeclare @cname varchar(20)select @cname=ClassName from Class join Studenton Student.ClassID=Class.ClassID where StudentID=@snoreturn @cnameendselect dbo.F3('St0109010002')

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

create function F4(@sname char(8))returns @result table(StudentName char(8),CourseName varchar(20),Grade numeric(5,1))asbegininsert into @resultselect StudentName,CourseName,Grade from Student join Gradeon Grade.StudentID=Student.StudentID join Courseon Course.CourseID=Grade.CourseID where StudentName=@snamereturnendselect *from dbo.F4('姜明凡')

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

create function F5(@tno char(8))returns char(12)asbegindeclare @tname char(12)select @tname=Teachername from Teacher where TeacherID=@tnoreturn @tnameend

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

create function F6(@cno char(8))returns intasbegindeclare @num intselect @num=count(*) from Student where Sex='男'return @numend

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

create function F7(@courseno char(8))returns numeric(5,1)asbegindeclare @avg numeric(5,1)select @avg=avg(Grade) from Grade where CourseID=@coursenoreturn @avgend