资源描述
数 据 库 系 统 与 应 用 上 机 习 题*第五部分、SQL高级应用要求掌握：熟练掌握T-SQ语言，了解异常处理的相关语句，学会用游标方式对数据 库进行操作。一、做书上第十章的例题二、写出书上198页练习题10中第7、8、9、11题的结果，并上机验证。完成第12、13、 14题7、数据库中没有stud表SQLQuery Lsql-J 8D6Boble(53)r xH USE schoolBIF GBJECT.IDf stud / u ) IS NULLPRJXT中汗有ELSESELECT * FROM 3tud舛100 % -; 2 030-OJ-12ri 71064 &34+OB; 00MJLQuery I sqll -.8060tle (Sa)* 七 X |-USE sctioolSELEC studFri 半号 5tudFTitscore 谍祎号CASETON 分裁TO TffiN *PASSPTfiEX 脊败-BO TUES 、；0 PASSEND AS 成鱸FHOM student- score%：ERE student 学号 score学号 丄一 肌就曲i.现级 1U0.| order b：-学引100轴 斗蛰果備滔电7：号叶杆沈和弓诫證I ； 1001 小州 000】 PASS1003 小诡 000】 NO PASS9、重复插入ID的值SQLQwryZ.刘-r,F8D6Bnoble(595)*USE tst-O xLAPTOP 15QFSMB.,. dtxkBil TableFIF 00JBCT_ID： tbT . rU*) IS Wt NULL DROP TABLE TbiGOFCREATE TABLE tbl:ID iut PHJMABY EEV)I- EEGIX TEtIX5ERT EMO ttl H) - VALLJ3 1： INSERT IXTO tbl ID：VALUBS 2 I-： tbl 5ET ID 1 TEEHE ID 2 END TYBEGIN CATCHPMtCT 在CATCH中:THRQT100 %j酵堀刖U”談蛊1. IQ EBJpT PH：Kfi=? F旳盼FK 匸北lYGEHb応二V.祁晤別软紙6 thl中蒜人車目乱宰京谥1胃汩（1 . 语句已燈止*吕：2020-03-07T2G; 4:43.15丁+腮:00USE schoolE- declare fs intdeclare nl im. &n2 intset 嗒nl = 0set Sn2 = 0-declare fs_cursor cursorfor select 分数 from score where 分数 is rot nullop&n fg_eurtrfttch nejst from fs.cursor into $fsHvhileetches t at us -0F： beginH if flfs 80 set 如l=tnl+lelse set n2&n2+lfetch nest from fs_cursor intos嗣close fs_cLirsordeallocate fs_d.irsorprint SOfr以上人次* castnl as chai(31)print 茸他人* _cast n2 as charSJ)go100% 4卜阴分JiLt人次：1其他人次212、编写一个程序，采用游标的方式输出所有课程的平均分 use schoolgoset nocount ondeclare s_cji nt,s_ namechar(8)declare c_cursor cursor forselect score课程号,AVG (score.分数)from scoregroup by score.课程号order by score.课程号ope n c_cursorfetch next from c_cursor into s_cjs_namewhile FETCH_STATU=S -beginprint CASTs_cj as char(8)+s_namefetch next from c_cursor into s_cjs_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 学号=stude nt.学号group by student.学号,score.课程号,score.分数 order by student.学号beginset dj=CASEwhen s_cj=90 then Awhen s_cj=80 then Bwhen s_cj=70 then Cwhen s_cj=60 then DelseEendopen cfetch nextfrom c_cursor into s_xh,c_name,s_cjprint 学号课程号等级 print while FETCH_STATU=S0beginprint s_xh+ +c_name+ +s_cjfetch nextfrom c_cursor into s_xh,c_name,s_cjendclose c_cursordeallocate c_cursor14、编写一个程序，输出各班各课程的平均分use schoolgosetnocount ondeclare s_cjint,s_namechar(8),s_bj char(8) declare c_cursor cursorforselect student.班级,score.课程号,AVG(score.分数) from score,studentgroup by score.课程号,student.班级order by score.课程号,student.班级 open c_cursorfetch nextfrom c_cursor into s_cj, s_name,s_bj print 学号班级 成绩 print while FETCH_STATU=S0beginprint CAST(s_cj as char(8)+s_name+s_bjfetch nextfrom c_cursor into s_cj, s_name,s_bj endclose c_cursordeallocate c_cursor go完成书上 394 页上机实验题 3( 1) 对各出版社的图书比例情况进行分析，即图书比例高于50%为“很高”，图书比例高于 30%为“ 较高”，图书比例高于 10%为“一般”。并按图书比例递增排列。USELibraryDECLAREnum intSELECTnum=(SELECTCOUNT(*) FROMbook)-图书总数SELECT岀版社AS岀版社,CASEWHENTHEN很高WHENTHEN较高WHENTHEN般ELSE较低E NDAS 图书比例情况 FROM(SELECT岀版社AS岀版社, CAST(ROUND(COUNT(*)* num,1)ASdecimal(5,1)ASrate FROM bookGROUPBY 岀版社 ) aORDERBY( 2) 对各系学生借书比例情况进行分析，即借书比例高于50%为“很高”，借书比例高于 30%为“较高”，借书比例高于 10%为“一般”。并按借书比例递减排列。USELibraryDECLAREnum intSELECTnum=(SELECTCOUNT(*) FROMborrow) -借书总数SELECT.系名 AS系名,CASEWHEN THEN很高WHEN THEN较高WHEN THEN一般ELSE较低ENDAS借书情况FROM(SELECTepart.系名,CASTROUND(COUN1(*)* num,1) ASdecimal(5,1) ASrateFROM borrow ,stude nt ,departWHEREborrow .学号=student学号 AND student.班号=depart.班号GROUPBYdepart.系名)aORDERBY DESC( 3) 采用游标方式对图书价格进行评价。USELibraryGODECLAREb_cur CURSORFORSELECDSTINCT图书名),定价 FROM book DECLAREbn char(20),dj decimal(4,1),pr char(10) OPENb_curFETCHNEXTFROMb_cur INTO bn,djWHILEFETCH_STATU=S0BEGINSETpr=CASEWHEN dj=50 THEN价格太高WHEN dj=30 THEN价格偏高WHEN dj=20 THEN价格适中WHEN dj=10 THEN价格偏低ELSE价格太低了 ENDPRINTbn+CAST(dj ASchar(5)+prFETCHNEXTFROMb_cur INTO bn,djENDCLOSEb_curDEALLOCATEb_cur( 4) GO 采用游标方式统计每个出版社图书的借出率。 USELibrary GODECLAREA CURSORFORSELECT.岀版社，CASTcsum*bsum ASdecimal(4,1) AsrateFROM (SELECT出版社,COUNT(*) ASbsumFROM bookGROUPBY 岀版社)a,(SELECT).岀版社,COUNT(bor.图书编号)AScsumFROM book b,borrow borWHEREb.图书编号=bor.图书编号GROUPBYb.岀版社)bWHEREa.岀版社=b.岀版社DECLAREname char(16),num decimal(4,1)OPENAFETCHNEXTFROMA INTO name,numWHILEFETCH_STATU=S0BEGINPRINT +RTRIM：name)+图书借岀率为+RTRIM(CASTnum ASchar(5)+%FETCHNEXTFROMA INTO name,numENDCLOSEADEALLOCATEAGO
展开阅读全文