发布时间 : 星期二 文章《SQL - Server - 2000 - 实验指导》课后作业更新完毕开始阅读2761690279563c1ec5da71e6
select @dept_in='信息' exec pro_dept_count @dept_in
7、利用课程名查询选修该课程的学生姓名、系别、成绩，并给出“程序设计”课程的该查询信息。 --创建存储过程 if exists (select name from sysobjects where name='pro_cname' and type='p') drop procedure pro_cname go create procedure pro_cname @cname char(10) as select sname,sex,dept,score from student,sc,course where cname=@cname go --执行存储过程 use jiaoxuedb declare @cname char(10) and sc.cno=course.cno and sc.sno=student.sno select @cname='程序设计' exec pro_cname @cname 张彬 王蕾 张建国 李平方 王一山
8、利用教师姓名和课程名检索该教师该任课的课程名、课时数、选课人数、平均成绩、最高成绩，并查询教师“张雪”的“微机原理”课程的情况记录。 --创建存储过程 if exists (select name from sysobjects where name='pro_tname_cname' and type='p') drop procedure pro_tname_cname go create procedure pro_tname_cname @tname char(10), @cname char(10) 男 计算机 女 计算机 男 计算机 男 计算机 男 计算机 90 98 74 85 64 as select tname,cname,sum(chour) 课时数,count(sno) 选课人数,avg(score) 平均成绩 from teacher,course,sc,tc where tname=@tname and cname=@cname and tc.tno=teacher.tno and tc.cno=course.cno - 16 -
go --执行存储过程 use jiaoxuedb declare @tname char(10), @cname char(10) and sc.cno=course.cno group by tname,cname select @tname='张雪' select @cname='微机原理' exec pro_tname_cname @tname,@cname 没有学生选“微机原理”这门课程，故课时数，选课人数，平均成绩，统统为零。
9、删除上面第1题创建的存储过程。 use jiaoxuedb drop procedure pro_sn
10、删除上面第3、4题创建的存储过程。 use jiaoxuedb drop procedure pro_qscore drop procedure pro_prof_count
实验9：习题 基于jxsk实验：
1、为表sc创建一触发器：当插入或修改一个记录时，确保此记录的成绩在0~100之间。 use jxsk go if exists (select name from sysobjects where name='score_sc_tri' and type='tr') drop trigger score_sc_tri go create trigger score_sc_tri on sc for insert,update as declare @score_read tinyint select @score_read=score from inserted if @score_read < 0 or @score_read > 100 begin go
2、为教师表t创建一触发器：男职工年龄不能超过60周岁，女职工职称是“教授”的年龄不能超过60
- 17 -
print '成绩超出0~100！请重新输入。' rollback transaction end
岁，其他女职工年龄不能超过55岁。 use jxsk go
if exists (select name from sysobjects where name='age_t_tri' and type='tr') drop trigger age_t_tri go
create trigger age_t_tri on t for insert,update as
declare @sex_read char(2) declare @age_read tinyint declare @prof_read char(10)
select @sex_read=sex from inserted select @age_read=age from inserted select @prof_read=prof from inserted
if ( @sex_read = '男' and @age_read > 60 ) go
3、为表c、表tc和表sc创建参照完整性：级联删除和级联修改触发器。 --级联删除 use jxsk go if exists (select name from sysobjects where name='dc_sc_tc' and type='tr') drop trigger dc_sc_tc go create trigger dc_sc_tc on c for delete as declare @cno_del char(2) select @cno_del=cno from deleted delete from sc where cno=@cno_del delete from tc where cno=@cno_del go --级联更新 use jxsk go or (@sex_read = '女' and @prof_read = '教授' and @age_read > 60) or (@sex_read = '女' and @prof_read <> '教授' and @age_read > 60) print '数据不合法。' rollback transaction
begin
end
- 18 -
if exists (select name from sysobjects where name='uc_sc_tc' and type='tr') drop trigger uc_sc_tc go create trigger uc_sc_tc on c for update as declare @cno_old char(2) declare @cno_new char(2) select @cno_old=cno from deleted select @cno_new=cno from inserted update sc set cno=@cno_new where cno=@cno_old update tc set cno=@cno_new where cno=@cno_old go --验证级联删除 use jxsk delete from c where cno='c2' go --验证级联更新 use jxsk update c set cno='c0' where cno='c1' go
4、为表t创建一个触发器：当职称从“讲师”晋升为“副教授”时，岗位津贴自动增加500元；从“副教授”晋升为“教授”时，岗位津贴自动增加900元。 use jxsk go
if exists (select name from sysobjects where name='ut' and type='tr') drop trigger ut go
create trigger ut on t for update as
declare @prof_old char(10) declare @prof_new char(10) declare @tno char(2)
select @prof_old=prof from deleted select @prof_new=prof from inserted select @tno=tno from deleted
if @prof_old='讲师' and @prof_new='副教授' begin
update t set comm=comm+500 where tno=@tno end
- 19 -