9.6(3) create trigger ScoreXk on Score for insert as if exists ( select * from Inserted, Course a, Student b where inserted.courseNo=a.courseNo and inserted.studentNo=b.studentNo and a.priorCourse is not null and a.priorCourse not in ( select c.courseNo from Inserted, Course c, Score d where inserted.studentNo=d.studentNo and c.courseNo=d.courseNo)) rollback 9.10(4)不允许使用聚集函数，统计每门课程的选课人数和平均分，按如下格式输 出 --定义变量 declare @cou_name varchar(30), @stu_no char(7), @stu_name varchar(20), @score numeric(5,1) declare @text char(300), @sum numeric(5,1), @avg numeric(5,2), @count tinyint, @i tinyint set @i=1 declare sc_cou cursor for select courseName from Course select @text='================学生选修课程情况================' print @text open sc_cou fetch sc_cou into @cou_name while (@@fetch_status=0) begin --判断是否提取成功 --打开 sc_cou 游标 --定义外层游标 

姓名 成绩' set @sum=0 set @count=0 select @text='课程名'+ convert(char(1),@i) +':'+@cou_name print @text select @text=' 学号 print @text declare sc_stu cursor for --定义内层游标 select b.studentNo, studentName, score from Course a,Student b, Score c where b.studentNo=c.studentNo and a.courseNo=c.courseNo and a.courseName=@cou_name --打开 sc_stu 游标 open sc_stu fetch sc_stu into @stu_no, @stu_name, @score while(@@fetch_status=0) begin select @text=@stu_no +' '+ @stu_name +' convert(char(10),@score) '+ --提取下一条记录 print @text set @count=@count+1 set @sum=@sum+@score if @count=0 select @avg=0 else select @avg=@sum/@count --选课人数总和 --总分数 --平均分 fetch sc_stu into @stu_no, @stu_name, @score end close sc_stu deallocate sc_stu set @i=@i+1 select @text='选课人数：'+convert(char(10),@count) print @text select @text='平均分：' +convert(char(10), @avg) print @text select @text='-----------------------------------------------' print @text fetch sc_cou into @cou_name --提取下一条记录 end close sc_cou deallocate sc_cou