发布时间 : 星期六 文章《SQL - Server - 2000 - 实验指导》课后作业更新完毕开始阅读2761690279563c1ec5da71e6
if @prof_old='副教授' and @prof_new='教授' begin go 5、 为表sc创建一触发器，将成绩按下列对应关系由分数转换成等级；
（×题目有错：原sc表score列数据类型定义为tinyint无法转换成“不及格”，“及格”，“中”??等char型的数据） 小于60：不及格 60~70：及格 70~80：中 80~90：良 90~100：优 use jxsk go
create trigger usc on sc for update,insert as
declare @sno char(2) declare @score tinyint
select @sno=sno from sc select @score=score from sc
if @score < 60 begin
if @score > 60 and @score < 70 begin
if @score > 70 and @score < 80 begin
if @score > 80 and @score < 90 begin
update sc set score='良' where sno=@sno update sc set score='中' where sno=@sno end
update sc set score='及格' where sno=@sno end
update sc set score='不及格' where sno=@sno end
update t set comm=comm+900 where tno=@tno end - 20 -
end if @score > 90 and @score < 100 begin go
实验10：习题 基于jxsk实验 1、求
update sc set score='优' where sno=@sno end ?i
i?1100declare @sum smallint,@i smallint set @sum=0 set @i=1 while (@i <= 100) begin set @sum = @sum + @i set @i = @i + 1 end print '总和是：'+str(@sum) 2、求 10!
declare @n int,@i smallint set @n=1 set @i=2 while (@i <= 10) begin set @n = @n * @i set @i = @i + 1 end print '10! ='+str(@n)
3、查询各系的教师人数、学生人数，并按学生人数和教师人数升序排列。 use jxsk select s.dept,count(distinct tno) \教师数\学生数\from t full join s on s.dept=t.dept group by s.dept order by \教师数\学生数\go - 21 -
4、查询姓王学生的总人数及在各系的人数分布以及每个人的姓名、姓名、年龄。 use jxsk select dept as 系别,sn as 姓名,sex as 性别,age as 年龄 from s where sn like '王%' order by dept,sn,sex,age compute count(sn) by dept compute count(sn) go
5、查询学生中重名的名字，人数和分布在几个系中，并按重名人数降序排列。 use jxsk select s1.sn as 姓名,s1.dept as 系别 from s s1,s s2 where s1.sn=s2.sn and s1.sno <> s2.sno order by s1.dept,s1.sn compute count(s1.sn) by s1.dept compute count(s1.sn) go
6、求出每个职称中工资最高、最低教师的姓名、性别、年龄、工资，并给出各职称的平均工资。职称有：助教、讲师（包括工程师）、副教授（包括高级工程师）、教授（包括研究员）。 use jxsk select prof,tn as 姓名,sex,age,sal+comm as 工资 from t order by prof compute max(sal+comm) by prof compute min(sal+comm) by prof compute avg(sal+comm) by prof go 7、按分数段查询课程名、各分数段人数。分数段划分：60分以下、60~70、70~80、80~90、90~100。 use jxsk select cn as 课程名,count(*) as '60以下人数' from c,sc where sc.cno = c.cno and score < 60 group by cn select cn as 课程名,count(*) as '60~70人数' from c,sc where sc.cno = c.cno and score > 60 and score < 70 group by cn - 22 -
select cn as 课程名,count(*) as '70~80人数' from c,sc where sc.cno = c.cno and score > 70 and score < 80 group by cn select cn as 课程名,count(*) as '80~90人数' from c,sc where sc.cno = c.cno and score > 80 and score < 90 group by cn select cn as 课程名,count(*) as '90~100人数' from c,sc where sc.cno = c.cno and score > 90 and score < 100 group by cn go
8、教师“刘伟”已调离该校，要求删除骄傲学数据库jxsk中与刘伟有关的信息，并列出这些被删除的信息。 use jxsk select tno,tn,sex,age,prof,sal,comm,dept from t where tn='刘伟' delete from t where tn='刘伟' go
9、查询比教师“张雪”工资低的教师的姓名、性别、出生日期、职称、工资。要求先列出“张雪”的姓名、性别、出生日期、职称和工资。然后依次列出所有查询得到的教师的信息。 use jxsk (select tn as 姓名,sex as 性别,2008-age as 出生日期, prof as 职称, sal+comm as 工资 from t where tn = '张雪') union (select t1.tn as 姓名,t1.sex as 性别,2008-t1.age as 出生日期, t1.prof as 职称, t1.sal+t1.comm as 工资 from t t1,t t2 where t2.tn = '张雪' and t1.tn <> '张雪'and (t1.sal+t1.comm) < (t2.sal+t2.comm)) order by (sal+comm) desc go 10、查询每位教师的姓名、职称、课程数、总课时数，并按课时数降序排列。 use jxsk - 23 -