数据库系统概念第三章.ppt

上传人:b****1 文档编号:1726332 上传时间:2022-10-23 格式:PPT 页数:30 大小:521KB
下载 相关 举报
数据库系统概念第三章.ppt_第1页
第1页 / 共30页
数据库系统概念第三章.ppt_第2页
第2页 / 共30页
数据库系统概念第三章.ppt_第3页
第3页 / 共30页
数据库系统概念第三章.ppt_第4页
第4页 / 共30页
数据库系统概念第三章.ppt_第5页
第5页 / 共30页
点击查看更多>>
下载资源
资源描述

数据库系统概念第三章.ppt

《数据库系统概念第三章.ppt》由会员分享,可在线阅读,更多相关《数据库系统概念第三章.ppt(30页珍藏版)》请在冰豆网上搜索。

数据库系统概念第三章.ppt

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

展开阅读全文
相关资源
猜你喜欢
相关搜索

当前位置:首页 > 考试认证 > IT认证

copyright@ 2008-2022 冰豆网网站版权所有

经营许可证编号:鄂ICP备2022015515号-1