数据库sql习题及答案.pdf

《数据库sql习题及答案.pdf》由会员分享，可在线阅读，更多相关《数据库sql习题及答案.pdf（5页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、文档/*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=rati

2、ng.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 revi

3、ewers didnt 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 na

4、me,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 w

5、here the same reviewer rated the same movie twice and gave it a higher rating the second time,return the reviewers 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.starsS.stars and F.ratingDater2.ratingdate and r1.s

6、tarsr2.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 gro

7、up 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 t

8、o 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(st

9、ars)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 th

10、e average of those averages for movies before 1980 and movies after.Dont 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.m

11、ID 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 r1980 Question 10:Add the reviewer Roger Ebert to y

12、our 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

13、 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;dont insert new tuples.)update Movie set year=year+25 文档 where mID in(select mID from Rating group by mID having AVG(stars)=4)*/