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.
SELECT TITLE FROM MOVIE WHERE MID NOT IN (SELECT MOVIE.MID FROM MOVIE, RATING WHERE MOVIE.MID=RATING.MID )
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
References: Online course on database