We have 3 tables Movie, Reviewer, Rating as shown below:
Movie ( mID, title, year, director )
There is a movie with ID number mID, a title, a release year, and a director.
Reviewer ( rID, name )
The reviewer with ID number rID has a certain name.
Rating ( rID, mID, stars, ratingDate )
The reviewer rID gave the movie mIDa number of stars rating (1-5) on a certain ratingDate.
Q1. Find the titles of all movies that have no ratings.
Q2. 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.
Ans 1) select title from movie where mid not in (select distinct mid from rating)
SELECT NAME,TITLE FROM RATING AS R1,RATING AS R2,REVIEWER,MOVIE
WHERE MOVIE.MID=R1.MID AND REVIEWER.RID=R1.RID
AND R1.MID=R2.MID AND R1.RID = R2.RID
AND R1.STARS < R2.STARS
AND R1.RATINGDATE < R2.RATINGDATE
ORDER BY R1.RATINGDATE ASC