资源描述:
《数据库系统概念第三章》由会员上传分享,免费在线阅读,更多相关内容在学术论文-天天文库。
1、PrinciplesofDatabaseSystemsSQLExercise1SQLExercise1You'vestartedanewmovie-ratingwebsite,andyou'vebeencollectingdataonreviewers'ratingsofvariousmovies.There'snotmuchdatayet,butyoucanstilltryoutsomeinterestingqueries.Here'stheschema:SSDUT-SoftwareSchoolofDUTSQLExercise1Movie(
2、mID,title,year,director)English:ThereisamoviewithIDnumbermID,atitle,areleaseyear,andadirector.Reviewer(rID,name)English:ThereviewerwithIDnumberrIDhasacertainname.Rating(rID,mID,stars,ratingDate)English:ThereviewerrIDgavethemoviemIDanumberofstarsrating(1-5)onacertainratingDa
3、te.SSDUT-SoftwareSchoolofDUTSQLExercise1Movie(mID,title,year,director)Reviewer(rID,name)Rating(rID,mID,stars,ratingDate)Question1FindthetitlesofallmoviesdirectedbyStevenSpielberg.SSDUT-SoftwareSchoolofDUTSQLExercise1Movie(mID,title,year,director)Reviewer(rID,name)Rating(rI
4、D,mID,stars,ratingDate)Question2Findallyearsthathaveamoviethatreceivedaratingof4or5,andsorttheminincreasingorder.SSDUT-SoftwareSchoolofDUTSQLExercise1Movie(mID,title,year,director)Reviewer(rID,name)Rating(rID,mID,stars,ratingDate)Question3Findthetitlesofallmoviesthathaven
5、oratingsSSDUT-SoftwareSchoolofDUTSQLExercise1Movie(mID,title,year,director)Reviewer(rID,name)Rating(rID,mID,stars,ratingDate)Question4Somereviewersdidn'tprovideadatewiththeirrating.FindthenamesofallreviewerswhohaveratingswithaNULLvalueforthedate.SSDUT-SoftwareSchoolofDUTSQ
6、LExercise1Movie(mID,title,year,director)Reviewer(rID,name)Rating(rID,mID,stars,ratingDate)Question5Writeaquerytoreturntheratingsdatainamorereadableformat:reviewername,movietitle,stars,andratingDate.Also,sortthedata,firstbyreviewername,thenbymovietitle,andlastlybynumberofst
7、ars.SSDUT-SoftwareSchoolofDUTSQLExercise1Movie(mID,title,year,director)Reviewer(rID,name)Rating(rID,mID,stars,ratingDate)Question6Forallcaseswherethesamereviewerratedthesamemovietwiceandgaveitahigherratingthesecondtime,returnthereviewer'snameandthetitleofthemovie.SSDUT-Sof
8、twareSchoolofDUTSQLExercise1Movie(mID,title,year,director)Reviewer(rID,name)Rating