Stanford--Introduce to Databases(2)

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 natural join Reviewer) natural join  Rating 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 Reviewer natural join Movie where rID in (select rID from ((select * from Rating R1 where rID in (select rID from Rating R2 group by rID, mID having count(stars)=2) and exists (select * from Rating R3 where R3.rID=R1.rID and R3.mID=R1.mID and R3.ratingDate>R1.ratingDate and R3.stars>R1.stars)) as Rs)) and mID in (select mID from ((select * from Rating R1 where rID in (select rID from Rating R2 group by rID, mID having count(stars)=2) and exists (select * from Rating R3 where R3.rID=R1.rID and R3.mID=R1.mID and R3.ratingDate>R1.ratingDate and R3.stars>R1.stars)) as Rs))

Question 7

For each movie that has at least one rating, find the highest number of stars that movie received. Return

the movie title and number of stars. Sort by movie title.

 

select title, Ep.stars from Movie,  (select mID, max(stars) as stars from Rating group by mID) Ep where Movie.mID=Ep.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, Ep.diff from Movie M, (select mID, max(stars)-min(stars) as diff from Rating group by mID) Ep where M.mID=Ep.mID order by Ep.diff 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 distinct ((select avg(Ep.avgstar) from (select mID, avg(stars) avgstar from Rating join Movie using(mID) group by mID having year<1980) Ep)-(select avg(Ep.avgstar) from (select mID, avg(stars) avgstar from Rating join Movie using(mID) group by mID having year>1980) Ep)) from Rating