I am taking Stanford's Introduction to Databases Self-Paced online course. I have gone through the videos in the SQL mini-course, and I am having trouble completing the exercises.
The following is the question from the SQL Movie-Rating Query Exercises, Question 7:
For each movie that has at least one rating, find the highest number of stars that movie received. Return the movie title and number of stars. Sort by movie title.
The database can be found here.
My answer to this question is as follows:
SELECT distinct Movie.title, Rate.stars FROM Movie, Rating, (SELECT * FROM Rating R1 WHERE not exists (SELECT mID FROM Rating R2 WHERE R1.stars < R2.stars and R1.mID = R2.mID)) as Rate WHERE Movie.mID = Rate.mID and Rate.stars = Rating.stars order by Movie.title;
This seems like a very tortured query, and it seems to me like I am missing some important concepts. Can someone help me refactor this query?