《数据库系统与应用》上机习题*************************************************************************** **********************第五部分、SQL高级应用要求掌握：熟练掌握T-SQL语言，了解异常处理的相关语句，学会用游标方式对数据库进行操作。
一、做书上第十章的例题二、写出书上198页练习题10中第7、8、9、11题的结果，并上机验证。
完成第12、13、14题7、数据库中没有stud表8、9、重复插入ID的值11、12、编写一个程序,采用游标的方式输出所有课程的平均分use schoolgoset nocount ondeclare @s_cj int,@s_name char(8)declare c_cursor cursor forselect score.课程号,AVG(score.分数)from scoregroup by score.课程号order by score.课程号open c_cursorfetch next from c_cursor into @s_cj,@s_namewhile@@FETCH_STATUS=0beginprint CAST(@s_cj as char(8))+@s_namefetch next from c_cursor into @s_cj,@s_nameendclose c_cursordeallocate c_cursorgo13、编写一个程序，使用游标的方式输出所有学号，课程号，成绩等级use schooldeclare @s_xh int,@c_name char(8),@s_cj float,@dj char(1)declare c_cursor cursor forselect student.学号,score.课程号,score.分数from score,studentwhere score.学号=student.学号group by student.学号,score.课程号,score.分数order by student.学号beginset @dj=CASEwhen @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'endopen cfetch next from c_cursor into @s_xh,@c_name,@s_cjprint'学号课程号等级'print'---------------------------'while@@FETCH_STATUS=0beginprint @s_xh+' '+@c_name+' '+@s_cjfetch next from c_cursor into @s_xh,@c_name,@s_cjendclose c_cursordeallocate c_cursor14、编写一个程序，输出各班各课程的平均分use schoolgoset nocount ondeclare @s_cj int,@s_name char(8),@s_bj char(8)declare c_cursor cursor forselect student.班级,score.课程号,AVG(score.分数)from score,studentgroup by score.课程号,student.班级order by score.课程号,student.班级open c_cursorfetch next from c_cursor into @s_cj,@s_name,@s_bjprint'学号班级成绩'print'-------------------'while@@FETCH_STATUS=0beginprint CAST(@s_cj as char(8))+@s_name+@s_bjfetch next from c_cursor into @s_cj,@s_name,@s_bjendclose c_cursordeallocate c_cursorgo三、完成书上394页上机实验题3（1）对各出版社的图书比例情况进行分析，即图书比例高于50%为“很高”，图书比例高于30%为“'较高”，图书比例高于10%为“一般”。
并按图书比例递增排列。
USE LibraryDECLARE @num intSELECT @num=(SELECT COUNT(*)FROM book)--图书总数SELECT a.出版社AS'出版社',CASEWHEN>THEN'很高'WHEN>THEN'较高'WHEN>THEN'一般'ELSE'较低'END AS'图书比例情况'FROM (SELECT出版社AS'出版社',CAST(ROUND(COUNT(*)*@num,1)AS decimal(5,1))AS'rate'FROM bookGROUP BY出版社) aORDER BY（2）对各系学生借书比例情况进行分析，即借书比例高于50%为“很高”，借书比例高于30%为“较高”，借书比例高于10%为“一般”。
并按借书比例递减排列。
USE LibraryDECLARE @num intSELECT @num=(SELECT COUNT(*)FROM borrow)--借书总数SELECT a.系名AS'系名',CASEWHEN>THEN'很高'WHEN>THEN'较高'WHEN>THEN'一般'ELSE'较低'END AS'借书情况'FROM (SELECT depart.系名,CAST(ROUND(COUNT(*)*@num,1)AS decimal(5,1))AS'rate'FROM borrow,student,departWHERE borrow.学号=student.学号AND student.班号=depart.班号GROUP BY depart.系名) aORDER BY DESC（3）采用游标方式对图书价格进行评价。
USE LibraryGODECLARE b_cur CURSORFOR SELECT DISTINCT(图书名),定价FROM bookDECLARE @bn char(20),@dj decimal(4,1),@pr char(10)OPEN b_curFETCH NEXT FROM b_cur INTO @bn,@djWHILE@@FETCH_STATUS= 0BEGINSET @pr=CASEWHEN @dj>=50 THEN'价格太高'WHEN @dj>=30 THEN'价格偏高'WHEN @dj>=20 THEN'价格适中'WHEN @dj>=10 THEN'价格偏低'ELSE'价格太低了'ENDPRINT @bn+CAST(@dj AS char(5))+@prFETCH NEXT FROM b_cur INTO @bn,@djENDCLOSE b_curDEALLOCATE b_cur（4）GO采用游标方式统计每个出版社图书的借出率。
USE LibraryGODECLARE A CURSORFORSELECT a.出版社,CAST(csum*bsum AS decimal(4,1))As'rate'FROM (SELECT出版社,COUNT(*)AS'bsum'FROM bookGROUP BY出版社) a,(SELECT b.出版社,COUNT(bor.图书编号)AS'csum'FROM book b,borrow borWHERE b.图书编号=bor.图书编号GROUP BY b.出版社) bWHERE a.出版社=b.出版社DECLARE @name char(16),@num decimal(4,1)OPEN AFETCH NEXT FROM A INTO @name,@numWHILE@@FETCH_STATUS= 0BEGINPRINT'"'+RTRIM(@name)+'"图书借出率为'+RTRIM(CAST(@num AS char(5)))+'%' FETCH NEXT FROM A INTO @name,@numENDCLOSE ADEALLOCATE AGO。