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

go 10、查询每个同学各门课程的平均成绩和最高成绩，按降序排列输出姓名、平均成绩、最高成绩。 use jiaoxuedb select sname as 姓名,avg(score) as 平均,max(score) as 最高 from student,sc where student.sno=sc.sno group by sname order by sname desc go

11、查询所有学生都选修了的课程号和课程名。 use jiaoxuedb select cname as 课程号,cno as 课程名 from course where not exists go

12、查询选修了991102号学生选修了的课程的学生学号和姓名。 use jiaoxuedb select distinct sno from sc s1 where not exists(select * from sc s2 where sno='991101' and not exists(select * from sc s3 where s1.sno=s3.sno and s2.cno=s3.cno)) go

实验8：习题

基于jiaoxuedb实验：

1、利用学生名查询该生选修的课程名、成绩、任课教师名。 --创建存储过程 if exists (select name from sysobjects where name='pro_sn' and type='p') drop procedure pro_sn go create procedure pro_sn @sname_in char(10) (select * from student where not exists (select * from sc where student.sno=sc.sno and sc.cno=course.cno)); from sc s1 where sno not in (select s2.sno from sc s2 where cno='01001')) - 12 -

as select student.sname 学生,cname 选修课程,score 分数,tname 教师 from student,sc,teacher,course,tc where student.sname=@sname_in go --执行存储过程 use jiaoxuedb declare @sname_in char(10) and sc.sno=student.sno and sc.cno=course.cno and sc.cno=tc.cno and tc.cno=course.cno and tc.tno=teacher.tno select @sname_in='王蕾' exec pro_sn @sname_in

2、查询某系的学生的最大年龄和最小年龄。 --创建存储过程 if exists (select name from sysobjects where name='pro_age_max_min' and type='p') drop procedure pro_age_max_min go create procedure pro_age_max_min @sdept_in char(10) as select dept 系别,max(age) 最大年龄,min(age) 最小年龄 from student where dept=@sdept_in group by dept go --执行存储过程 use jiaoxuedb declare @sdept_in char(10) select @sdept_in='计算机' exec pro_age_max_min @sdept_in

3、利用学生姓名和课程名检索该生课程的成绩。 --创建存储过程 if exists (select name from sysobjects where name='pro_qscore' and type='p') drop procedure pro_qscore go create procedure pro_qscore @sname_in char(10), @scname_in char(10) as select sname 学生姓名,cname 课程名,score 分数 - 13 -

from student,sc,course where student.sname=@sname_in go --执行存储过程 use jiaoxuedb declare @sname_in char(10), @scname_in char(10) and course.cname=@scname_in and sc.sno=student.sno and sc.cno=course.cno select @sname_in='王蕾' select @scname_in='程序设计' exec pro_qscore @sname_in,@scname_in 4、根据职称查询人数，并给出“副教授”的人数。 --创建存储过程 if exists (select name from sysobjects where name='pro_prof_count' and type='p') drop procedure pro_prof_count go create procedure pro_prof_count @prof_in char(10) as select prof 职称,count(prof) 人数 from teacher where prof=@prof_in group by prof go --执行存储过程 use jiaoxuedb declare @prof_in char(10) select @prof_in='副教授' exec pro_prof_count @prof_in 副教授 1

5、统计某系某职称的人数平均年龄、平均工资最高工资。 --创建存储过程 if exists (select name from sysobjects where name='pro_dept_prof' and type='p') drop procedure pro_dept_prof go create procedure pro_dept_prof @dept_in char(10), @prof_in char(10) as select dept 系别,prof 职称, - 14 -

count(*) 人数,avg(age) 平均年龄, avg(sal) 平均工资,max(sal) 最高工资 from teacher where dept=@dept_in and prof=@prof_in group by dept,prof go --执行存储过程 use jiaoxuedb declare @dept_in char(10), @prof_in char(10) select @dept_in='计算机' select @prof_in='教授' exec pro_dept_prof @dept_in,@prof_in 6、查询某系的教师人数、平均年龄和学生人数。 --创建存储过程

if exists (select name from sysobjects where name='pro_dept_count' and type='p') drop procedure pro_dept_count go

create procedure pro_dept_count

@dept_in char(10)

as select dept,count(teacher.dept) 教师人数,avg(age) 教师平均年龄 into #a from teacher

where teacher.dept=@dept_in group by dept

select dept,count(student.dept) 学生人数 into #b from student

where dept=@dept_in group by dept

select #a.dept 系别,教师人数,教师平均年龄,学生人数 from #a,#b

where #a.dept=#b.dept go

--执行存储过程 use jiaoxuedb declare

@dept_in char(10)

- 15 -