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.
Solution:
Ans 1.
SELECT TITLE FROM MOVIE WHERE MID NOT IN (SELECT MOVIE.MID FROM MOVIE, RATING WHERE MOVIE.MID=RATING.MID )
Ans 2
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
Simple answer for 1st query
SELECT M.TITLE
FROM MOVIE AS M LEFT JOIN RATING AS R
ON R.MID=M.MID
WHERE R.MID IS NULL
select name, title ,ratingDate
from movie a
inner join (
select mID,rID,max(stars),ratingDate
from rating
group by rid
having count( mID ) = 2
) b on b.mID = a.mID
inner join reviewer c on b.rID = c.rid
Pingback: MySQL Query Set » Tech Blog