数据库sql习题与答案.doc

《数据库sql习题与答案.doc》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、.专业.专注./*Question1:FindthetitlesofallmoviesdirectedbyStevenSpielberg.selecttitlefrommoviewheredirector='StevenSpielberg'Question2:Findallyearsthathaveamoviethatreceivedaratingof4or5,andsorttheminincreasingorder.selectdistinctyearfrommovie,ratingwheremovie.mid=rating.midandstarsin(4,5)orderbyy

2、earQuestion3:FindthetitlesofallmoviesthathavenoratingsselecttitlefrommoviewheremIDin(selectmidfromMovieexceptselectmidfromrating.word可编辑..专业.专注.)selecttitlefrommovieexceptselecttitlefrommovie,ratingwheremovie.mid=rating.midQuestion4:Somereviewersdidn'tprovideadatewiththeirrating.Findthenameso

3、fallreviewerswhohaveratingswithaNULLvalueforthedate.selectnamefromreviewer,ratingwherereviewer.rid=rating.ridandratingdateisnullQuestion5:Writeaquerytoreturntheratingsdatainamorereadableformat:reviewername,movietitle,stars,andratingDate.Also,sortthedata,firstbyreviewername,thenbymovietitle,an

4、dlastlybynumberofstars.selectname,title,stars,ratingdatefrommovie,rating,reviewer.word可编辑..专业.专注.wheremovie.mid=rating.midandreviewer.rid=rating.ridorderbyname,title,starsQuestion6:Forallcaseswherethesamereviewerratedthesamemovietwiceandgaveitahigherratingthesecondtime,returnthereviewer'sname

5、andthetitleofthemovie.selectname,titlefromratingasF,RatingasS,Movie,ReviewerwhereF.rID=S.rIDandF.mID=S.mIDandF.stars

6、1.mid=r2.midandr1.ratingdate>r2.ratingdate.word可编辑..专业.专注.andr1.stars>r2.stars)asrwheremovie.mid=r.midandreviewer.rid=r.ridQuestion7:Foreachmovie,findthehighestnumberofstarsthatmoviereceivedasarating.Returnthemovietitleandnumberofstars.Sortbymovietitle.selecttitle,MAX(stars)asmax_starfromrati

7、ng,MoviewhereRating.mID=Movie.mIDgroupbytitleorderbytitleselecttitle,maxratingfrommovie,(selectmid,max(stars)asmaxratingfromratinggroupbymid)asrwheremovie.mid=r.midorderbytitleQuestion8:Foreachmovie,returnthetitleandthe‘ratingspread(范围)',that