版權(quán)說(shuō)明：本文檔由用戶(hù)提供并上傳，收益歸屬內(nèi)容提供方，若內(nèi)容存在侵權(quán)，請(qǐng)進(jìn)行舉報(bào)或認(rèn)領(lǐng)
文檔簡(jiǎn)介
Principles
of
Database
SystemSQL
Exercise
1SQL
Exercise
1SSDUT-SoftwareSchoolof
DUTYou"ve
started
a
new
movie-rating
website,and
you"ve
been
collecting
data
on
reviewers"ratings
of
various
movies.
There"s
not
muchdata
yet,
but
you
can
still
try
out
someinteresting
queries.
Here"s
the
schema:SQL
Exercise
1SSDUT-SoftwareSchoolof
DUTMovie
(
mID,
title,
year,
director
)English:
There
is
a
movie
with
ID
number
mID,a
title,
a
release
year,
and
a
director.Reviewer
(
rID,
name
)English:
The
reviewer
with
ID
number
rID
has
acertain
name.Rating
(
rID,
mID,
stars,
ratingDate
)English:
The
reviewer
rID
gave
the
movie
mID
anumber
of
stars
rating
(1-5)
on
a
certainratingDate.SQL
Exercise
1SSDUT-SoftwareSchoolof
DUTMovie
(
mID,
title,
year,
director
)Reviewer
(
rID,
name
)Rating
(
rID,
mID,
stars,
ratingDate
)Question
1Find
the
titles
of
all
movies
directedbySteven
Spielberg.SQL
Exercise
1SSDUT-SoftwareSchoolof
DUTMovie
(
mID,
title,
year,
director
)Reviewer
(
rID,
name
)Rating
(
rID,
mID,
stars,
ratingDate
)Question
2Find
all
years
that
have
a
movie
thatreceived
a
rating
of
4
or
5,
and
sort
them
inincreasing
order.SQL
Exercise
1SSDUT-SoftwareSchoolof
DUTMovie
(
mID,
title,
year,
director
)Reviewer
(
rID,
name
)Rating
(
rID,
mID,
stars,
ratingDate
)Question
3Find
the
titles
of
all
movies
that
have
noratingsSQL
Exercise
1SSDUT-SoftwareSchoolof
DUTMovie
(
mID,
title,
year,
director
)Reviewer
(
rID,
name
)Rating
(
rID,
mID,
stars,
ratingDate
)Question
4Some
reviewers
didn"t
provide
a
date
withtheir
rating.
Find
the
names
of
all
reviewerswho
have
ratings
with
a
NULL
value
for
thedate.SQL
Exercise
1SSDUT-SoftwareSchoolof
DUTMovie
(
mID,
title,
year,
director
)Reviewer
(
rID,
name
)Rating
(
rID,
mID,
stars,
ratingDate
)Question
5Write
a
query
to
return
the
ratings
data
in
amore
readable
format:
reviewer
name,
movietitle,
stars,
and
ratingDate.
Also,
sort
thedata,
first
by
reviewer
name,
then
by
movietitle,
and
lastly
by
number
of
stars.SQL
Exercise
1SSDUT-SoftwareSchoolof
DUTMovie
(
mID,
title,
year,
director
)Reviewer
(
rID,
name
)Rating
(
rID,
mID,
stars,
ratingDate
)Question
6For
all
cases
where
the
same
reviewer
ratedthe
same
movie
twice
and
gave
it
a
higherrating
the
second
time,
return
the
reviewer"sname
and
the
title
of
the
movie.SQL
Exercise
1SSDUT-SoftwareSchoolof
DUTMovie
(
mID,
title,
year,
director
)Reviewer
(
rID,
name
)Rating
(
rID,
mID,
stars,
ratingDate
)Question
7For
each
movie,
find
the
highest
number
ofstars
that
movie
received
as
a
rating.
Returnthe
movie
title
and
number
of
stars.
Sort
bymovie
title.SQL
Exercise
1SSDUT-SoftwareSchoolof
DUTMovie
(
mID,
title,
year,
director
)Reviewer
(
rID,
name
)Rating
(
rID,
mID,
stars,
ratingDate
)Question
8For
each
movie,
return
the
title
and
the"rating
spread",
that
is,
the
differencebetween
highest
and
lowest
ratings
given
tothat
movie.
Sort
by
rating
spread
fromhighest
to
lowest,
then
by
movie
title.SQL
Exercise
1SSDUT-SoftwareSchoolof
DUTMovie
(
mID,
title,
year,
director
)Reviewer
(
rID,
name
)Rating
(
rID,
mID,
stars,
ratingDate
)Question
9Find
the
difference
between
the
average
ratingof
movies
released
before
1980
and
the
averagerating
of
movies
released
after
1980.
(Make
sureto
calculate
the
average
rating
for
each
movie,then
the
average
of
those
averages
for
moviesbefore
1980
and
movies
after.
Don"t
justcalculate
the
overall
average
rating
before
andafter
1980.)SQL
Exercise
1SSDUT-SoftwareSchoolof
DUTMovie
(
mID,
title,
year,
director
)Reviewer
(
rID,
name
)Rating
(
rID,
mID,
stars,
ratingDate
)Question
10Add
the
reviewer
Roger
Ebert
to
yourdatabase,
with
an
rID
of
209.SQL
Exercise
1SSDUT-SoftwareSchoolof
DUTMovie
(
mID,
title,
year,
director
)Reviewer
(
rID,
name
)Rating
(
rID,
mID,
stars,
ratingDate
)Question
11Insert
5-star
ratings
by
James
Cameron
forall
movies
in
the
database.
Leave
the
reviewdate
as
NULL.SQL
Exercise
1SSDUT-SoftwareSchoolof
DUTMovie
(
mID,
title,
year,
director
)Reviewer
(
rID,
name
)Rating
(
rID,
mID,
stars,
ratingDate
)Question
12For
all
movies
that
have
an
average
rating
of4
stars
or
higher,
add
25
to
the
release
year.(Update
the
existing
tuples;
don"t
insert
newtuples.)SolutionsSSDUT-SoftwareSchoolof
DUTSolutions
for
SQL
Exercise
1SSDUT-SoftwareSchoolof
DUTMovie
(
mID,
title,
year,
director
)Reviewer
(
rID,
name
)Rating
(
rID,
mID,
stars,
ratingDate
)Question
1Find
the
titles
of
all
movies
directedbySteven
Spielberg.select
title
from
movie
wheredirector="Steven
Spielberg"Solutions
for
SQL
Exercise
1SSDUT-SoftwareSchoolof
DUTMovie
(
mID,
title,
year,
director
)Reviewer
(
rID,
name
)Rating
(
rID,
mID,
stars,
ratingDate
)Question
2Find
all
years
that
have
a
movie
that
received
arating
of
4
or
5,
and
sort
them
in
increasingorder.select
distinct
year
from
movie,rating
wheremovie.mid=rating.mid
and
stars
in
(4,5)
orderby
yearSolutions
for
SQL
Exercise
1SSDUT-SoftwareSchoolof
DUTMovie
(
mID,
title,
year,
director
)Reviewer
(
rID,
name
)Rating
(
rID,
mID,
stars,
ratingDate
)Question
3Find
the
titles
of
all
movies
that
have
noratings.select
title
from
movie
except
select
title
frommovie,rating
where
movie.mid=rating.midSolutions
for
SQL
Exercise
1SSDUT-SoftwareSchoolof
DUTMovie
(
mID,
title,
year,
director
)Reviewer
(
rID,
name
)Rating
(
rID,
mID,
stars,
ratingDate
)Question
4Some
reviewers
didn"t
provide
a
date
with
theirrating.
Find
the
names
of
all
reviewers
who
haveratings
with
a
NULL
value
for
the
date.select
name
from
reviewer,rating
wherereviewer.rid=rating.rid
and
ratingdate
is
nullSolutions
for
SQL
Exercise
1SSDUT-SoftwareSchoolof
DUTMovie
(
mID,
title,
year,
director
)Reviewer
(
rID,
name
)Rating
(
rID,
mID,
stars,
ratingDate
)Question
5Write
a
query
to
return
the
ratings
data
in
a
morereadable
format:
reviewer
name,
movie
title,
stars,and
ratingDate.
Also,
sort
the
data,
firstby
reviewername,
then
by
movie
title,
and
lastly
by
number
ofstars.select
name,title,stars,ratingdate
frommovie,rating,reviewer
where
movie.mid=rating.mid
and
reviewer.rid=rating.rid
order
by
name,title,starsSolutions
for
SQL
Exercise
1SSDUT-SoftwareSchoolof
DUTMovie
(
mID,
title,
year,
director
)Reviewer
(
rID,
name
)Rating
(
rID,
mID,
stars,
ratingDate
)Question
6For
all
cases
where
the
same
reviewer
rated
thesame
movie
twice
and
gave
it
a
higher
rating
thesecond
time,
return
the
reviewer"s
name
and
thetitle
of
the
movie.select
name,title
from
movie,reviewer,(
selectr1.rid,r1.mid
from
rating
as
r1,rating
as
r2
wherer1.rid=r2.rid
and
r1.mid=r2.mid
andr1.ratingdate>r2.ratingdate
and
r1.stars>r2.stars)as
r
where
movie.mid=r.mid
and
reviewer.rid=r.ridSolutions
for
SQL
Exercise
1SSDUT-SoftwareSchoolof
DUTMovie
(
mID,
title,
year,
director
)Reviewer
(
rID,
name
)Rating
(
rID,
mID,
stars,
ratingDate
)Question
7For
each
movie,
find
the
highest
number
ofstars
that
movie
received
as
a
rating.
Return
themovie
title
and
number
of
stars.
Sort
by
movietitle.select
title,maxrating
from
movie,
(selectmid,max(stars)
as
maxrating
from
rating
groupby
mid)
as
r
where
movie.mid=r.mid
order
bytitleSolutions
for
SQL
Exercise
1SSDUT-SoftwareSchoolof
DUTMovie
(
mID,
title,
year,
director
)Reviewer
(
rID,
name
)Rating
(
rID,
mID,
stars,
ratingDate
)Question
8For
each
movie,
return
the
title
and
the
"ratingspread",
that
is,
the
difference
between
highest
andlowest
ratings
given
to
that
movie.
Sortbyratingspread
from
highest
to
lowest,
then
by
movie
title.select
title,spread
as
"rating
spread"
from
movie,(select
mid,max(stars)-min(stars)
as
spread
fromrating
groupbymid)
as
r
where
movie.mid=r.midorder
by
spread
desc,titleSolutions
for
SQL
Exercise
1SSDUT-SoftwareSchoolof
DUTMovie
(
mID,
title,
year,
director
)Reviewer
(
rID,
name
)Rating
(
rID,
mID,
stars,
ratingDate
)Question
9Find
the
difference
between
the
average
ratingof
movies
released
before
1980
and
the
averagerating
of
movies
released
after
1980.
(Make
sureto
calculate
the
average
rating
for
each
movie,then
the
average
of
those
averages
for
moviesbefore
1980
and
movies
after.
Don"t
justcalculate
the
overall
average
rating
before
andafter
1980.)Solutions
for
SQL
Exercise
1SSDUT-SoftwareSchoolof
DUTMovie
(
mID,
title,
year,
director
)Reviewer
(
rID,
name
)Rating
(
rID,
mID,
stars,
ratingDate
)Question
9select
r1979.avgm-r1980.avgmfrom(selectsum(avgrating)/count(*)
as
avgM
from
(selectavg(stars)
as
avgrating
from
rating,movie
wheremovie.mid=rating.mid
and
year<1980
group
bymovie.mid)
as
r)
as
r1979
,
(selectsum(avgrating)/count(*)
as
avgM
from
(selectavg(stars)
as
avgrating
from
rating,movie
wheremovie.mid=rating.mid
and
year>=1980
groupby
movie.mid)
as
r)
as
r1980Solutions
for
SQL
Exercise
1SSDUT-SoftwareSchoolof
DUTMovie
(
mID,
title,
year,
director
)Reviewer
(
溫馨提示
- 1. 本站所有資源如無(wú)特殊說(shuō)明，都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請(qǐng)下載最新的WinRAR軟件解壓。
- 2. 本站的文檔不包含任何第三方提供的附件圖紙等，如果需要附件，請(qǐng)聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶(hù)所有。
- 3. 本站RAR壓縮包中若帶圖紙，網(wǎng)頁(yè)內(nèi)容里面會(huì)有圖紙預(yù)覽，若沒(méi)有圖紙預(yù)覽就沒(méi)有圖紙。
- 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
- 5. 人人文庫(kù)網(wǎng)僅提供信息存儲(chǔ)空間，僅對(duì)用戶(hù)上傳內(nèi)容的表現(xiàn)方式做保護(hù)處理，對(duì)用戶(hù)上傳分享的文檔內(nèi)容本身不做任何修改或編輯，并不能對(duì)任何下載內(nèi)容負(fù)責(zé)。
- 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容，請(qǐng)與我們聯(lián)系，我們立即糾正。
- 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時(shí)也不承擔(dān)用戶(hù)因使用這些下載資源對(duì)自己和他人造成任何形式的傷害或損失。
最新文檔
- 2025建筑工程防蟻保障合同
- 2025年自建房租賃合同模板
- 2025工程合同范本2
- 《2025物業(yè)管理服務(wù)保函示范合同》
- 裁判員在不同文化背景下的執(zhí)法方式探討試題及答案
- 2025數(shù)碼產(chǎn)品分銷(xiāo)商合同范文
- 2025租房合同漫畫(huà)范文
- 豬場(chǎng)股份制合同協(xié)議
- 電影股份代持協(xié)議合同
- 豬舍施工合同補(bǔ)充協(xié)議
- 遼寧協(xié)作校2024-2025學(xué)年度下學(xué)期高三第二次模擬考試語(yǔ)文試卷（含答案解析）
- 2025-2030汽車(chē)揚(yáng)聲器市場(chǎng)發(fā)展現(xiàn)狀分析及行業(yè)投資戰(zhàn)略研究報(bào)告
- 期中考試考后分析總結(jié)主題班會(huì)《全員出動(dòng)尋找消失的分?jǐn)?shù)》
- 2025年廣東省廣州市廣大附中等校聯(lián)考中考語(yǔ)文模擬試卷（4月份）
- 成都樹(shù)德中學(xué)2025年高三第四次聯(lián)考物理試題文試卷
- 民法典課程大綱
- 2025-2030中國(guó)數(shù)據(jù)安全服務(wù)行業(yè)市場(chǎng)深度分析及前景趨勢(shì)與投資研究報(bào)告
- 醫(yī)療AI輔助康復(fù)管理
- 山東省天一大聯(lián)考·齊魯名校教研體2024-2025學(xué)年（下）高三年級(jí)第六次聯(lián)考（物理試題及答案）
- 房地產(chǎn)市場(chǎng)報(bào)告 -2025年第一季度青島寫(xiě)字樓和零售物業(yè)市場(chǎng)概況報(bào)告
- 2025年03月人力資源社會(huì)保障部所屬單位筆試歷年典型考題（歷年真題考點(diǎn)）解題思路附帶答案詳解
評(píng)論
0/150
提交評(píng)論