创建一个函数，要求；根据教师姓名查询该教师所教课程名，学生人数，平均成绩，最高成绩，最低成绩。

Question

create table student (sno char(6),
sname char(10),
sex char(2) constraint default_sex default '男' constraint check_sex check(sex='男' or sex='女'),
age int constraint check_age check(age>0 and age<100),
Dept char(10),
constraint pk_student primary key(sno)
)
insert into student values('001101','宋大方','男','19','计算机')
insert into student values('002102','李王','男','20','信息')
insert into student values('991101','张彬','男','18','计算机')
insert into student values('991102','王蕾','女','19','计算机')
insert into student values('991103','张建国','男','18','计算机')
insert into student values('991104','李平方','男','18','计算机')
insert into student values('991201','陈东辉','男','19','计算机')
insert into student values('991202','葛鹏','男','21','计算机')
insert into student values('991203','潘桃芝','女','19','计算机')
insert into student values('991204','姚一峰','男','18','计算机')
insert into student values('001102','许辉','女','22','计算机')
insert into student values('001201','王一山','男','20','计算机')
insert into student values('001202','牛莉','女','19','计算机')
insert into student values('002101','李丽丽','女','19','信息')
create table teacher (tno char(6),
tname char(10),
sex char(2) constraint default_t_sex default '男' constraint check_t_sex check(sex='男'or sex='女'),
age int constraint check_t_age check(age>0 and age<100),
prof char(10),
sal int constraint check_t_sal check(sal>0),
comm int constraint check_t_comm check(comm>0),
dept char(10),
constraint pk_teacher primary key(tno)
)
insert into teacher values('000006','许红霞','女','39','讲师','1100','1200','计算机')
insert into teacher values('000007','许永军','男','57','教授','2000','3000','计算机')
insert into teacher values('000008','李桂青','女','65','教授','2000','3000','计算机')
insert into teacher values('000009','王一凡','女','43','讲师','1200','1200','计算机')
insert into teacher values('000010','田峰','男','33','助教','500','800','信息')
insert into teacher values('000001','李英','女','39','副教授','1500','2000','信息')
insert into teacher values('000002','张雪','女','51','教授','1900','3000','信息')
insert into teacher values('000003','张朋','男','30','讲师','1000','1200','计算机')
insert into teacher values('000004','王平','女','28','讲师','850','1200','信息')
insert into teacher values('000005','李力','男','47','教授','1800','3000','计算机')
create table course (cno char(5),
cname char(12),
chout int ,
ccredit int,
constraint pk_course primary key(cno)
)insert into course values('01001','计算机基础','60','3')
insert into course values('01002','程序设计','80','5')
insert into course values('02003','数据结构','60','6')
insert into course values('02001','数据库','80','6')
insert into course values('02002','计算机网络','60','6')
insert into course values('01003','微机原理','60','8')
insert into course values('02004','操作系统','60','6')
insert into course values('03001','软件工程','60','3')
insert into course values('03002','大型数据库','48','2')
insert into course values('03003','图像处理','48','2')
create table sc ( sno char(6),
cno char(5),
score int constraint check_s_score check(score>=0 and score<=100),
constraint pk_sc primary key(sno,cno),
constraint fk_s_sc foreign key(sno) references student(sno),
constraint fk_c_sc foreign key(cno) references course(cno)
)
insert into sc values('991101','01001','88')
insert into sc values('991102','01001','93')
insert into sc values('991103','01001','90')
insert into sc values('991101','01002','90')
insert into sc values('991102','01002','98')
insert into sc values('991103','01002','74')
insert into sc values('991104','01002','85')
insert into sc values('001201','01002','64')
insert into sc values('991104','02001','33')
insert into sc values('991104','01001','35')
insert into sc values('991201','01001','76')create table tc (tno char(6),
cno char(5),
constraint pk_tc primary key(tno,cno),
constraint fk_t_tc foreign key(tno) references teacher(tno),
constraint fk_c_tc foreign key(cno) references course(cno)
)
insert into tc values('000001','02001')
insert into tc values('000008','02002')
insert into tc values('000003','02001')
insert into tc values('000010','02003')
insert into tc values('000001','01001')
insert into tc values('000002','01002')
insert into tc values('000002','01003')
insert into tc values('000004','02002')
insert into tc values('000005','01001')
insert into tc values('000006','01002')
insert into tc values('000003','01003')
创建一个函数，要求；根据教师姓名查询该教师所教课程名，学生人数，平均成绩，最高成绩，最低成绩。

Answer 1

create proc p_query(@tname varchar(20))
as
declare @tno char(6),@cno char(6),@cname char(20),@scount int,@avg int,@maxscore int,@minscore int
select cno,cname from course where cno in (select  cno from tc where tno in (select tno from teacher where tname=@tname))
select cno,count(1) scount,avg(score) avg,max(score) maxscore,min(score) minscore from sc 
where cno in (select cno from course where cno in (select  cno from tc where tno in (select tno from teacher where tname=@tname)))
group by cno
go
exec p_query '李英'
go
cno   cname        
----- ------------ 
01001 计算机基础  
02001 数据库      （所影响的行数为 2 行）cno   scount      avg         maxscore    minscore    
----- ----------- ----------- ----------- ----------- 
01001 5           76          93          35
02001 1           33          33          33（所影响的行数为 2 行）

Answer 2

create function fun_query(@tname varchar(20))
returns table
as
return select @tname tname,b.cname,count(1) scount,avg(score) avg,max(score) maxscore,min(score) minscore from sc a join course b on a.cno=b.cno
where a.cno in (select cno from course where cno in (select  cno from tc where tno in (select tno from teacher where tname=@tname)))
group by b.cname
go
select * from fun_query('张雪')tname                cname        scount      avg         maxscore    minscore    
-------------------- ------------ ----------- ----------- ----------- ----------- 
张雪                   程序设计         5           82          98          64（所影响的行数为 1 行）