Mysql Query – Set 5

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

3 Thoughts on “Mysql Query – Set 5

  1. Gowri Shankar on August 26, 2014 at 3:13 pm said:

    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

  2. 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

  3. Pingback: MySQL Query Set » Tech Blog

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Post Navigation