《SQL - Server - 2000 - 实验指导》课后作业

select distinct tn as 姓名, prof as 职称,cn as 课程,ct as 课时 from t,c,tc where t.tno=tc.tno and tc.cno=c.cno order by tn,ct desc compute sum(ct) by tn compute sum(ct) go

实验11：习题 基于jiaoxuedb实验

1、用sql语句创建一用户定义的数据类型agetype。要求：系统数据类型为tinyint，可为空，取值范围是0~100，用于学生表和教师表中的年龄字段和选课表中成绩字段的数据类型。 use jiaoxuedb go exec sp_addtype agetype,'tinyint','null' go create rule ru_agetype as @agetype between 0 and 100 go exec sp_bindrule 'ru_agetype','agetype' go

2、用企业管理器创建数据类型nametype。要求：系统数据类型为varchar，长度10个字节，不能为空，用于教师名，学生名，职称字段的数据类型。

--tsql use jiaoxuedb go - 24 -

exec sp_addtype nametype,'varchar (10)','not null' go

3、创建一个函数，要求：根据学生姓名和课程名查询该生该课程的成绩。 --创建函数 use jiaoxuedb go create function score_fun (@sname_in char(10), @cname_in char(10)) returns tinyint as begin go --执行函数 use jiaoxuedb declare @s_score tinyint exec @s_score=score_fun '张建国','程序设计' print '张建国 的 程序设计 成绩是：'+str(@s_score) go

4、创建一个函数，要求：根据教师姓名查询该教师所教课程名、学生人数、平均成绩、最高成绩、最低成绩。 --创建函数 use jiaoxuedb go use jiaoxuedb go create function t_sc_fun (@tname_in char(10)) returns table as go return (select cname as 课程名,count(sno) as 学生人数,avg(score) as 平均成 from teacher,tc,course,sc where @tname_in=teacher.tname and teacher.tno=tc.tno and course.cno=tc.cno and sc.cno=tc.cno group by cname) declare @score_out tinyint select @score_out=score from sc,student,course where student.sname=@sname_in and course.cname=@cname_in and student.sno=sc.sno and course.cno=sc.cno return (@score_out) end 绩,max(score) as 最高成绩,min(score) as 最低成绩 - 25 -

--执行函数 use jiaoxuedb declare @tname_in char(10) select * from t_sc_fun ('李英') go

5、创建一个函数，要求：统计各系个支撑的总人数、男女人数、平均年龄。 --创建函数 use jiaoxuedb go

create function t_prof_fun (@prof_in char(10))

returns @t_prof_table table (职称 char(10) ,人数 tinyint, as begin

insert @t_prof_table select prof,count(tno),

man=(select count(*) as man from teacher where @prof_in=prof and sex=' female=(select count(*) as female from teacher where @prof_in=prof and declare @man tinyint declare @female tinyint

男性人数 tinyint,女士人数 tinyint,均年龄 tinyint)

男' group by prof), sex='女' group by prof), go

--执行函数 use jiaoxuedb

select * from t_prof_fun ('讲师') go

实验12：习题

1、创建一个windows认证的登陆账号newuser，只允许该用户对数据库jiaoxuedb查询。

avg(age) as 平均年龄 from teacher

where @prof_in=teacher.prof group by prof return

end

- 26 -

- 27 -