I have been trying to work on a DB query but it doesnt give me proper result.
The statement for which I am trying to create a query is
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.
The schema of the tables are as follows
Movie ( mID, title, year, director )
English: There is a movie with ID number mID, a title, a release year, and a director.
Reviewer ( rID, name )
English: The reviewer with ID number rID has a certain name.
Rating ( rID, mID, stars, ratingDate )
English: The reviewer rID gave the movie mID a number of stars rating (1-5) on a certain ratingDate.
I tried the following query
select name,title from (reviewer natural join rating natural join movie) group by name,title having count(*)=2;
But it gave me incorrect results as I failed to implement this condition "gave it a higher rating the second time".
Though I understand that the check could be done by using the ratingDate and stars, I still fail to understand how to do that.
Could you please help me in this regard. Your help is highly appreciated.