数据库 sql 习题及答案
- 格式：doc
- 大小：42.00 KB
- 文档页数：5
/*
Question 1:Find the titles of all movies directed by Steven Spielberg.
select title from movie where director='Steven Spielberg'
Question 2:Find all years that have a movie that received a rating
of 4 or 5, and sort them in increasing order.
select distinct year from movie,rating
where movie.mid=rating.mid
and stars in (4,5)
order by year
Question 3:Find the titles of all movies that have no ratings
select title from movie
where mID in
(
select mid from Movie
except
select mid from rating
)
select title from movie
except
select title from movie,rating
where movie.mid=rating.mid
Question 4:Some reviewers didn't provide a date with
their rating. Find the names of all reviewers who have
ratings with a NULL value for the date.
select name from reviewer,rating
where reviewer.rid=rating.rid
and ratingdate is null
Question 5:Write a query to return the ratings data in a more
readable format: reviewer name, movie title, stars, and ratingDate.
Also, sort the data, first by reviewer name, then by movie title,
and lastly by number of stars.
select name,title,stars,ratingdate
from movie,rating,reviewer where movie.mid=rating.mid
and reviewer.rid=rating.rid
order by name,title,stars
Question 6:For all cases where the same reviewer rated the same
movie twice and gave it a higher rating the second time, return
the reviewer's name and the title of the movie.
select name,title
from rating as F,Rating as S,Movie,Reviewer
where F.rID=S.rID
and F.mID=S.mID
and F.stars
and F.ratingDate
and F.mID=Movie.mID
and F.rID=Reviewer.rID
select name,title
from movie,reviewer,
( select r1.rid,r1.mid from rating as r1,rating as r2
where r1.rid=r2.rid and r1.mid=r2.mid
and r1.ratingdate>r2.ratingdate
and r1.stars>r2.stars) as r
where movie.mid=r.mid and reviewer.rid=r.rid
Question 7:For each movie, find the highest number of stars
that movie received as a rating. Return the movie title and
number of stars. Sort by movie title.
select title,MAX(stars) as max_star
from rating,Movie
where Rating.mID=Movie.mID
group by title
order by title
select title,maxrating
from movie,
(select mid,max(stars) as maxrating from rating
group by mid) as r
where movie.mid=r.mid
order by title
Question 8:For each movie, return the title and the
‘rating spread(范围)', that is, the difference between highest and lowest ratings given to that movie. Sort by
rating spread from highest to lowest, then by movie title.
select title,MAX(stars)-MIN(stars) as rating_spread
from rating,Movie
where Rating.mID=Movie.mID
group by title
order by rating_spread desc,title
select title,spread as "rating spread"
from movie, (select mid,max(stars)-min(stars) as
spread from rating group by mid) as r
where movie.mid=r.mid
order by spread desc,title
Question 9:Find the difference between the average rating
of movies released before 1980 and the average rating of
movies released after 1980.
(Make sure to calculate the average rating for each movie,
then the average of those averages for movies before 1980
and movies after. Don't just calculate the overall average
rating before and after 1980.)
select F.avg_star - S.avg_star
from
(
select AVG(avg_star) as avg_star from
(
select Rating.mID,AVG(stars) as avg_star
from Rating left join Movie on Rating.mID=Movie.mID
where year<1980
group by Rating.mID
) as F
) as F
,
(
select AVG(avg_star) as avg_star from
(
select Rating.mID,AVG(stars) as avg_star
from Rating left join Movie on Rating.mID=Movie.mID
where year>=1980
group by Rating.mID
) as S
) as S
select r1979.avgm-r1980.avgm
from
(select sum(avgrating)/count(*) as avgM
from
(select avg(stars) as avgrating from rating,movie
where movie.mid=rating.mid and year<1980
group by movie.mid)
as r)
as r1979
,
(select sum(avgrating)/count(*) as avgM
from
(select avg(stars) as avgrating from rating,movie
where movie.mid=rating.mid and year>=1980
group by movie.mid)
as r)
as r1980
Question 10:Add the reviewer Roger Ebert to your database,
with an rID of 209.
insert into Reviewer
values (209,'Roger Ebert')
Question 11:
Insert 5-star ratings by James Cameron for all movies
in the database. Leave the review date as NULL.
insert into Rating(rID,mID,stars)
select rid,mID,5 as stras
from movie,
(select rid from Reviewer where name='James Cameron') as T
insert into rating(rid,mid,stars)
select rid,mid,5
from reviewer,movie
where name='James Cameron'
Question 12:For all movies that have an average rating of 4 stars
or higher, add 25 to the release year.
(Update the existing tuples; don't insert new tuples.)
update Movie set year=year+25
