《数据库系统与应用》上机习题*************************************************************************************************第五部分、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 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 bookGROUP BY出版社) aORDER BY a、rate（2）对各系学生借书比例情况进行分析,即借书比例高于50%为“很高”,借书比例高于30%为“较高”,借书比例高于10%为“一般”。
并按借书比例递减排列。
USE LibraryDECLARE @num intSELECT @num=(SELECT COUNT(*)FROM borrow)--借书总数SELECT a、系名AS'系名',CASEWHEN 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,departWHERE borrow、学号=student、学号AND student、班号=depart、班号GROUP BY depart、系名) aORDER BY a、rate 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*100、0/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 A GO。