《数据库系统与应用》上机习题*************************************************************************************************
第五部分、SQL高级应用
要求掌握:熟练掌握T-SQL语言,了解异常处理的相关语句,学会用游标方式对数据库
进行操作。
一、做书上第十章的例题
二、写出书上198页练习题10中第7、8、9、11题的结果,并上机验证。完成第12、13、
14题
7、数据库中没有stud表
8、
9、重复插入ID的值11、
12、编写一个程序,采用游标的方式输出所有课程的平均分
use school
go
set nocount on
declare @s_cj int,@s_name char(8)
declare c_cursor cursor for
select score、课程号,AVG(score、分数)
from score
group by score、课程号
order by score、课程号
open c_cursor
fetch next from c_cursor into @s_cj,@s_name
while@@FETCH_STATUS=0
begin
print CAST(@s_cj as char(8))+@s_name
fetch next from c_cursor into @s_cj,@s_name
end
close c_cursor
deallocate c_cursor
go
13、编写一个程序,使用游标的方式输出所有学号,课程号,成绩等级use school
declare @s_xh int,@c_name char(8),@s_cj float,@dj char(1) declare c_cursor cursor for
select student、学号,score、课程号,score、分数
from score,student
where score、学号=student、学号
group by student、学号,score、课程号,score、分数
order by student、学号
begin
set @dj=CASE
when @s_cj>=90 then'A'
when @s_cj>=80 then'B'
when @s_cj>=70 then'C'
when @s_cj>=60 then'D'
else'E'
end
open c
fetch next from c_cursor into @s_xh,@c_name,@s_cj
print'学号课程号等级'
print'---------------------------'
while@@FETCH_STATUS=0
begin
print @s_xh+' '+@c_name+' '+@s_cj
fetch next from c_cursor into @s_xh,@c_name,@s_cj
end
close c_cursor
deallocate c_cursor
14、编写一个程序,输出各班各课程的平均分
use school
go
set nocount on
declare @s_cj int,@s_name char(8),@s_bj char(8)
declare c_cursor cursor for
select student、班级,score、课程号,AVG(score、分数)
from score,student
group by score、课程号,student、班级
order by score、课程号,student、班级
open c_cursor
fetch next from c_cursor into @s_cj,@s_name,@s_bj
print'学号班级成绩'
print'-------------------'
while@@FETCH_STATUS=0
begin
print CAST(@s_cj as char(8))+@s_name+@s_bj
fetch next from c_cursor into @s_cj,@s_name,@s_bj
end
close c_cursor
deallocate c_cursor
go
三、完成书上394页上机实验题3
（1）对各出版社的图书比例情况进行分析,即图书比例高于50%为“很高”,图书比例高于30%为“'较高”,图书比例高于10%为“一般”。并按图书比例递增排列。USE Library
DECLARE @num int
SELECT @num=(SELECT COUNT(*)FROM book)--图书总数
SELECT a、出版社AS'出版社',
CASE
WHEN a、rate>50、0 THEN'很高'
WHEN a、rate>30、0 THEN'较高'
WHEN a、rate>10、0 THEN'一般'
ELSE'较低'
END AS'图书比例情况'
FROM (SELECT出版社AS'出版社',
CAST(ROUND(COUNT(*)*100、0/@num,1)AS decimal(5,1))AS'rate'
FROM book
GROUP BY出版社) a
ORDER BY a、rate
（2）对各系学生借书比例情况进行分析,即借书比例高于50%为“很高”,借书比例高于30%为“较高”,借书比例高于10%为“一般”。并按借书比例递减排列。USE Library
DECLARE @num int
SELECT @num=(SELECT COUNT(*)FROM borrow)--借书总数
SELECT a、系名AS'系名',
CASE
WHEN a、rate>50、0 THEN'很高'
WHEN a、rate>30、0 THEN'较高'
WHEN a、rate>10、0 THEN'一般'
ELSE'较低'
END AS'借书情况'
FROM (SELECT depart、系名,CAST(ROUND(COUNT(*)*100、0/@num,1)AS decimal(5,1))AS'rate' FROM borrow,student,depart
WHERE borrow、学号=student、学号AND student、班号=depart、班号
GROUP BY depart、系名) a
ORDER BY a、rate DESC
（3）采用游标方式对图书价格进行评价。
USE Library
GO
DECLARE b_cur CURSOR
FOR SELECT DISTINCT(图书名),定价FROM book
DECLARE @bn char(20),@dj decimal(4,1),@pr char(10)
OPEN b_cur
FETCH NEXT FROM b_cur INTO @bn,@dj
WHILE@@FETCH_STATUS= 0
BEGIN
SET @pr=CASE
WHEN @dj>=50 THEN'价格太高'
WHEN @dj>=30 THEN'价格偏高'
WHEN @dj>=20 THEN'价格适中'
WHEN @dj>=10 THEN'价格偏低'
ELSE'价格太低了'
END
PRINT @bn+CAST(@dj AS char(5))+@pr
FETCH NEXT FROM b_cur INTO @bn,@dj
END
CLOSE b_cur
DEALLOCATE b_cur
（4）GO采用游标方式统计每个出版社图书的借出率。
USE Library
GO
DECLARE A CURSOR
FOR
SELECT a、出版社,CAST(csum*100、0/bsum AS decimal(4,1))As'rate'
FROM (SELECT出版社,COUNT(*)AS'bsum'
FROM book
GROUP BY出版社) a,
(SELECT b、出版社,COUNT(bor、图书编号)AS'csum'
FROM book b,borrow bor
WHERE b、图书编号=bor、图书编号
GROUP BY b、出版社) b
WHERE a、出版社=b、出版社
DECLARE @name char(16),@num decimal(4,1)
OPEN A
FETCH NEXT FROM A INTO @name,@num
WHILE@@FETCH_STATUS= 0
BEGIN
PRINT'"'+RTRIM(@name)+'"图书借出率为'+RTRIM(CAST(@num AS char(5)))+'%'
FETCH NEXT FROM A INTO @name,@num
END
CLOSE A
DEALLOCATE A GO