SQL Server实验指导(第三版)习题8答案
- 格式：docx
- 大小：20.36 KB
- 文档页数：5
use jiaoxuedb
go
--page154实验
--1利用学生姓名查询该生选修的课程名，成绩，以及任课教师姓名
if object_id('pro_1') is not null
drop procedure pro_1
go
create procedure pro_1
(@sn char(8)
)
as
begin
select cn,score,tn from student,sc,course,tc,teacher where
student.sno=sc.sno and o=o and tc.tno=teacher.tno and
o=o and sn=@sn
end
--查询
declare @sn char(8)
exec pro_1 '张建国'
--2查询某系的学生的最大年龄和最小年龄
--select dept ,max(age) maxage,min(age) minage from student group by dept
if object_id('pro_2') is not null
drop procedure pro_2
go
create procedure pro_2
(@dept char(10),
@maxage tinyint output,
@minage tinyint output
)
as
begin
select @maxage=max(age),@minage=min(age) from student where dept=@dept
end
--查询
declare @dept char(10),
@maxage tinyint,
@minage tinyint
exec pro_2 '计算机',@maxage output,@minage output
print @maxage
print @minage
--3利用学生姓名和课程名检索该生该课程的成绩
if object_id('pro_3') is not null drop procedure pro_3
go
create procedure pro_3
(@sn char(8) ,
@cn char(10),
@score tinyint output
)
as
begin
select @score=score from student,sc,course where sn=@sn and cn=@cn
end
--查询
declare @sn char(8),
@cn char(10),
@score tinyint
exec pro_3 '张建国','数据库',@score output
print @score
--4根据职称查询人数，并给出“副教授”的人数
if object_id('pro_4') is not null
drop procedure pro_4
go
create procedure pro_4
(
@prof char(10),
@count smallint output
)
as
begin
select @count=count(*) from teacher where prof=@prof
end
--查询
declare @prof char(10),
@count smallint
exec pro_4 '副教授',@count output
print @count
--5统计某系某职称的人数，平均年龄，平均工资，最高工资
if object_id('pro_5') is not null
drop procedure pro_5
go
create procedure pro_5
(
@dept char(10), @prof char(10),
@count smallint output,
@avgage tinyint output,
@avgsal smallint output,
@maxsal smallint output
)
as
begin
select
@count=count(*),@avgage=avg(age),@avgsal=avg(sal),@maxsal=max(sal)
from teacher where dept=@dept and prof=@prof
end
--查询
declare @dept char(10),
@prof char(10),
@count smallint,
@avgage tinyint,
@avgsal smallint,
@maxsal smallint
exec pro_5 '计算机','讲师',@count output,@avgage output,@avgsal
output,@maxsal output
print @count
print @avgage
print @avgsal
print @maxsal
--6查询某系的教师人数，平均年龄和学生人数
--select teacher.dept,count(distinct tno) t,avg(teacher.age)
avgage,count(distinct sno) s from teacher,student where
teacher.dept=student.dept group by teacher.dept having teacher.dept='计算机'
if object_id('pro_6') is not null
drop procedure pro_6
go
create procedure pro_6
(
@dept char(10),
@tcount smallint output,
@avgage tinyint output,
@scount smallint output
)
as
begin select @tcount=count(distinct
tno),@avgage=avg(teacher.age) ,@scount=count(distinct sno) from
teacher,student where teacher.dept=student.dept group by teacher.dept
having teacher.dept=@dept
end
--查询
declare @dept char(10),
@tcount smallint,
@avgage tinyint,
@scount smallint
exec pro_6 '计算机',@tcount output,@avgage output,@scount output
print @tcount
print @avgage
print @scount
--7利用课程名查询选修该课程的学生姓名，系别，成绩，并给出“程序设计”该课程的查询信息
if object_id('pro_7') is not null
drop procedure pro_7
go
create procedure pro_7
(
@cn char(10)
)
as
begin
select sn,dept,score from student,sc,course where student.sno=sc.sno and
o=o and cn=@cn
end
--查询
declare @cn char(10)
exec pro_7 '程序设计'
--8利用教师姓名和课程名检索该教师该任课的课程名,课时数,选课人数,平均成绩,最高成绩,并查询教师"张雪"的"微机原理"课程的情况记录
select tn,o,cn,ct,count(sno) as 选课人数from sc,course,tc,teacher
where o=o and teacher.tno=tc.tno and
o=o group by o,cn,ct,tn
if object_id('pro_8') is not null
drop procedure pro_8
go
create procedure pro_8
( @tn char(8),
@cn char(10)
)
as
begin
select cn,ct,count(sno) as 选课人数,avg(score) as 平均成绩,max(score) as 最高成绩from sc,course,tc,teacher where o=o and
teacher.tno=tc.tno and o=o and tn=@tn
group by o,cn,ct having cn=@cn
end
--查询
declare @tn char(8),
@cn char(10)
exec pro_8 '张雪','微机原理'
--9删除实验题创建的存储过程。
drop procedure pro_1
--10删除实验题和创建的存储过程
drop procedure pro_3
drop procedure pro_4
