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

(