数据库系统概念第三章PPT格式课件下载.ppt

数据库系统概念第三章PPT格式课件下载.ppt

《数据库系统概念第三章PPT格式课件下载.ppt》由会员分享，可在线阅读，更多相关《数据库系统概念第三章PPT格式课件下载.ppt（30页珍藏版）》请在冰豆网上搜索。

PrinciplesofDatabaseSystems,SQLExercise1,SQLExercise1,Youvestartedanewmovie-ratingwebsite,andyouvebeencollectingdataonreviewersratingsofvariousmovies.Theresnotmuchdatayet,butyoucanstilltryoutsomeinterestingqueries.Herestheschema:

@#@,SSDUT-SoftwareSchoolofDUT,SQLExercise1,Movie（mID,title,year,director）English:

@#@ThereisamoviewithIDnumbermID,atitle,areleaseyear,andadirector.Reviewer（rID,name）English:

@#@ThereviewerwithIDnumberrIDhasacertainname.Rating（rID,mID,stars,ratingDate）English:

@#@ThereviewerrIDgavethemoviemIDanumberofstarsrating（1-5）onacertainratingDate.,SSDUT-SoftwareSchoolofDUT,SQLExercise1,Movie（mID,title,year,director）Reviewer（rID,name）Rating（rID,mID,stars,ratingDate）Question1FindthetitlesofallmoviesdirectedbyStevenSpielberg.,SSDUT-SoftwareSchoolofDUT,SQLExercise1,Movie（mID,title,year,director）Reviewer（rID,name）Rating（rID,mID,stars,ratingDate）Question2Findallyearsthathaveamoviethatreceivedaratingof4or5,andsorttheminincreasingorder.,SSDUT-SoftwareSchoolofDUT,SQLExercise1,Movie（mID,title,year,director）Reviewer（rID,name）Rating（rID,mID,stars,ratingDate）Question3Findthetitlesofallmoviesthathavenoratings,SSDUT-SoftwareSchoolofDUT,SQLExercise1,Movie（mID,title,year,director）Reviewer（rID,name）Rating（rID,mID,stars,ratingDate）Question4Somereviewersdidntprovideadatewiththeirrating.FindthenamesofallreviewerswhohaveratingswithaNULLvalueforthedate.,SSDUT-SoftwareSchoolofDUT,SQLExercise1,Movie（mID,title,year,director）Reviewer（rID,name）Rating（rID,mID,stars,ratingDate）Question5Writeaquerytoreturntheratingsdatainamorereadableformat:

@#@reviewername,movietitle,stars,andratingDate.Also,sortthedata,firstbyreviewername,thenbymovietitle,andlastlybynumberofstars.,SSDUT-SoftwareSchoolofDUT,SQLExercise1,Movie（mID,title,year,director）Reviewer（rID,name）Rating（rID,mID,stars,ratingDate）Question6Forallcaseswherethesamereviewerratedthesamemovietwiceandgaveitahigherratingthesecondtime,returnthereviewersnameandthetitleofthemovie.,SSDUT-SoftwareSchoolofDUT,SQLExercise1,Movie（mID,title,year,director）Reviewer（rID,name）Rating（rID,mID,stars,ratingDate）Question7Foreachmovie,findthehighestnumberofstarsthatmoviereceivedasarating.Returnthemovietitleandnumberofstars.Sortbymovietitle.,SSDUT-SoftwareSchoolofDUT,SQLExercise1,Movie（mID,title,year,director）Reviewer（rID,name）Rating（rID,mID,stars,ratingDate）Question8Foreachmovie,returnthetitleandtheratingspread,thatis,thedifferencebetweenhighestandlowestratingsgiventothatmovie.Sortbyratingspreadfromhighesttolowest,thenbymovietitle.,SSDUT-SoftwareSchoolofDUT,SQLExercise1,Movie（mID,title,year,director）Reviewer（rID,name）Rating（rID,mID,stars,ratingDate）Question9Findthedifferencebetweentheaverageratingofmoviesreleasedbefore1980andtheaverageratingofmoviesreleasedafter1980.（Makesuretocalculatetheaverageratingforeachmovie,thentheaverageofthoseaveragesformoviesbefore1980andmoviesafter.Dontjustcalculatetheoverallaverageratingbeforeandafter1980.）,SSDUT-SoftwareSchoolofDUT,SQLExercise1,Movie（mID,title,year,director）Reviewer（rID,name）Rating（rID,mID,stars,ratingDate）Question10AddthereviewerRogerEberttoyourdatabase,withanrIDof209.,SSDUT-SoftwareSchoolofDUT,SQLExercise1,Movie（mID,title,year,director）Reviewer（rID,name）Rating（rID,mID,stars,ratingDate）Question11Insert5-starratingsbyJamesCameronforallmoviesinthedatabase.LeavethereviewdateasNULL.,SSDUT-SoftwareSchoolofDUT,SQLExercise1,Movie（mID,title,year,director）Reviewer（rID,name）Rating（rID,mID,stars,ratingDate）Question12Forallmoviesthathaveanaverageratingof4starsorhigher,add25tothereleaseyear.（Updatetheexistingtuples;@#@dontinsertnewtuples.）,SSDUT-SoftwareSchoolofDUT,Solutions,SSDUT-SoftwareSchoolofDUT,SolutionsforSQLExercise1,Movie（mID,title,year,director）Reviewer（rID,name）Rating（rID,mID,stars,ratingDate）Question1FindthetitlesofallmoviesdirectedbyStevenSpielberg.selecttitlefrommoviewheredirector=StevenSpielberg,SSDUT-SoftwareSchoolofDUT,SolutionsforSQLExercise1,Movie（mID,title,year,director）Reviewer（rID,name）Rating（rID,mID,stars,ratingDate）Question2Findallyearsthathaveamoviethatreceivedaratingof4or5,andsorttheminincreasingorder.selectdistinctyearfrommovie,ratingwheremovie.mid=rating.midandstarsin（4,5）orderbyyear,SSDUT-SoftwareSchoolofDUT,SolutionsforSQLExercise1,Movie（mID,title,year,director）Reviewer（rID,name）Rating（rID,mID,stars,ratingDate）Question3Findthetitlesofallmoviesthathavenoratings.selecttitlefrommovieexceptselecttitlefrommovie,ratingwheremovie.mid=rating.mid,SSDUT-SoftwareSchoolofDUT,SolutionsforSQLExercise1,Movie（mID,title,year,director）Reviewer（rID,name）Rating（rID,mID