上海大学-数据库选修-上机练习5

数据库系统与应用上机习题* 第五部分、SQL高级应用要求掌握：熟练掌握T-SQL语言，了解异常处理的相关语句，学会用游标方式对数据库进行操作。一、 做书上第十章的例题二、 写出书上198页练习题10中第7、8、9、11题的结果，并上机验证。完成第12、13、14题7、数据库中没有stud表8、9、重复插入ID的值11、12、编写一个程序,采用游标的方式输出所有课程的平均分use schoolgo set 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=0begin print 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.学号begin set dj=CASE when s_cj=90 then Awhen s_cj=80 then B when s_cj=70 then Cwhen s_cj=60 then Delse 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 schoolgo set 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=0begin print 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 Library DECLARE num int SELECT num=(SELECT COUNT(*) FROM book) -图书总数 SELECT a.出版社 AS 出版社, CASE WHEN a.rate50.0 THEN 很高 WHEN a.rate30.0 THEN 较高 WHEN a.rate10.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 出版社) aORDER BY a.rate （2） 对各系学生借书比例情况进行分析，即借书比例高于50%为“很高”，借书比例高于30%为“较高”，借书比例高于10%为“一般”。并按借书比例递减排列。USE Library DECLARE num int SELECT num=(SELECT COUNT(*) FROM borrow) -借书总数 SELECT a.系名 AS 系名, CASE WHEN a.rate50.0 THEN 很高 WHEN a.rate30.0 THEN 较高 WHEN a.rate10.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 bookDECLARE 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,djEND 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 FETC