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

1、数据库系统与应用上机习题* 第五部分、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.

2、分数)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,

3、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 'A'when s_cj>=80 then 'B' when s_cj>=70

4、 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_cursorde

5、allocate 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_c

6、j,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%为“一般”。并按图书比例递增排

7、列。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 '出版社'

8、, 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

9、 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 st

10、udent.班号=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>=

11、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

12、 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