实用数据库系统开发 第八章 课后习题答案

第八章

教材jxsk

8.1.1.1

CREATE PROCEDURE Pro_Qsinf

@Sno_in char(8)=’S2’,@Sname_out char(8) output,@Age_out int output,@Dept_out char(10) output

As select @Sname_out=Sname,@Age_out=age,@Dept_out=dept

From Student where sno=@Sno_in

GO

8.1.1.2

USE jxsk

declare @Sno_in char(8),

@Sname_out nvarchar(255),

@Age_out int,

@Dept_out nvarchar(255)

exec Pro_Qsinf default,@Sname_out output,@Age_out output,@Dept_out output

print @Sname_out

print @Age_out

print @Dept_out

select @Sno_in='S4'

exec Pro_Qsinf @Sno_in,@Sname_out output,@Age_out output,@Dept_out output

print @Sname_out

print @Age_out

print @Dept_out

Go

8.1.2.1

USE jxsk

CREATE Procedure Pro_Qscore

@Sname_in nvarchar(255),@Cname_in nvarchar(255),@Score_out float output

As select @Score_out=Score from Student,Course,SC

where Student.Sno=SC.Sno and https://www.360docs.net/doc/eb2926355.html,o=https://www.360docs.net/doc/eb2926355.html,o and Sname=@Sname_in and Cname=@Cname_in

8.1.2.2

USE jxsk

declare @Sname_in nvarchar(255),

@Cname_in nvarchar(255),

@Score_out float

select @Sname_in='李思'

select @Cname_in='程序设计'

exec Pro_Qscore @Sname_in,@Cname_in,@Score_out output

print Rtrim(@Sname_in)+'='+Ltrim(str(@Score_out))

Go

8.2.1

CREATE PROCEDURE Pro_Qsinf

@Sno_in char(2)='S2',@Sname_out char(8) output,@Age_out tinyint output,@Dept_out char(10) output

As select @Sname_out=Sname,@Age_out=age,@Dept_out=dept

from Student where sno=@Sno_in

GO

8.2.2

Alter Procedure Pro_Qsinf

@Sno_in char(2)='S1',@Sname_out char(8) output,@Sex_out char(2) output,@Dept_out char(10) output

AS

Select @Sname_out=Sname,@Sex_out=Sex,@Dept_out=Dept

from Student where Sno=@Sno_in

Go

8.3.2

USE jxsk

Drop procedure Pro_Qscore

GO

习题

Jiaoxuedb

1.CREATE Procedure Pro_Qstuin

@Sname_in char(8),@Cname_out char(8),@Score_out float,@Tname_out char(8) output AS select @Cname_out=Cname,@Score_out=Score,@Tname_out=Tname

from Student,Course,SC,Teacher

where Student.Sno=SC.Sno and https://www.360docs.net/doc/eb2926355.html,o=https://www.360docs.net/doc/eb2926355.html,o

and Sname=@Sname_in

GO

2.Create Procedure Pro_Qexage

@Dept_in char(8),@Maxage_out float(8),@Minage_out float(8) output

AS select @Maxage_out=max(Age),@Minage_out=min(Age) from Student

where Dept=@Dept_in

GO

3.CREATE Procedure Pro_Qscore

@Sname_in char(8),@Cname_in char(8),@Score_out float output

As select @Score_out=Score from Student,Course,SC

where Student.Sno=SC.Sno and https://www.360docs.net/doc/eb2926355.html,o=https://www.360docs.net/doc/eb2926355.html,o

and Sname=@Sname_in and Cname=@Cname_in

GO

4.CREATE Procedure Pro_Qcount

@Prof_in char(8),@count_out tinyint output

As select @count_out=count(Tno) from Teacher

where Prof=@Prof_in

group by Prof

declare @Prof_in char(8),

@count_out tinyint

select @Prof_in='副教授'

exec Pro_Qcount @Prof_in,@count_out output

print Rtrim(@Prof_in)+'='+Ltrim(str(@count_out))

GO

5.CREATE Procedure Pro_Qtdata

@Prof_in char(8),@count_out tinyint,@Avgage_out int,@Avgsal_out float,@Maxsal_out float output

As select @count_out=count(Tno),@Avgage_out=avg(Age),@Avgsal_out=avg(Sal),@Maxsal_out=max( Sal) from Teacher

where Prof=@Prof_in

group by Prof

GO

6.CREATE Procedure Pro_Qdept

@dept_in char(16) ,@tcount_out int,@tavgage_out int,@scount_out int As select @tcount_out=count(Tno),@tavgage_out=avg(Teacher.Age),@scount_out=count(Sno) from Teacher,Student

where Teacher.Dept=@dept_in and Teacher.Dept=Student.Dept

group by Teacher.Dept

GO

7.CREATE Procedure Pro_Qsc

@Cname_in char(8),@Sname_out char(8),@dept_out char(16),@score_out float As select @Sname_out=Sname,@dept_out=Dept,@score_out=Score from Student,SC,Course

where https://www.360docs.net/doc/eb2926355.html,o=https://www.360docs.net/doc/eb2926355.html,o and SC.Sno=Student.Sno and Cname=@Cname_in GO

declare @Cname_in char(8),@Sname_out char(8),@dept_out char(16),@score_out float select @Cname_in='程序设计'

exec Pro_Qsc @Sname_out,@dept_out,@score_out

print Rtrim(@Cname_in)+' '+Ltrim(str(@Sname_out))+' '+Ltrim(str(@dept_out))+' '+Ltrim(str(@score_out))

GO

8．use jiaoxuedb

CREATE Procedure Pro_Qstc

@tname_in char(8),@cname_in char(8),@tcname_out char(8),@thour_out int,@ccount_out int,@avgscore_out int,@maxscore_out int

as select @cname_out =Cname,@thour_out=sum(Chour),@ccount_out=count(Sno),@avgscore_out=avg(Score),@m axscore=max(Score)

from Course,SC,TC

where Tname=@tname_in,Cname=@cname_in

group by Cno

GO

declare @tname_in char(8),@cname_in char(8),@tcname_out char(8),@thour_out int,@ccount_out int,@avgscore_out int,@maxscore_out int

select @tname_in='赵策'

select @cname_in='计算机网络'

exec Pro_Qstc @tname_in, @cname_in, @tcname_out, @thour_out, @ccount_out, @avgscore_out, @maxscore_out

print Rtrim(@tname_in)+' '+Ltrim(@cname_in)+' '+Ltrim(@tcname_out)+' '+Ltrim(@thour_out)+' '+Ltrim(@ccount_out)+' '+Ltrim(@avgscore_out)+'

'+Ltrim(@maxscore_out)

GO

8.Drop Procedure Pro_Qstuin

GO

9.Drop Procedure Pro_Qscore

Drop Procedure Pro_Qcount

GO