Finding max rating for a movie

Question

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?

Answer 1

SELECT distinct Movie.title, Rate.stars 

You should rarely use the DISTINCT keyword. In this particular case it's unnecessary and may do the wrong thing. What you want to do is to return the highest star rating for a given movie title:

SELECT Movie.title, MAX(Rating.stars)

There's the title and we use the MAX keyword to make sure that it's the highest stars. More on when we can use MAX later.

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

We can make this simpler:

FROM Movie, Rating
WHERE Movie.mID = Rating.mID

No subselects required.

GROUP BY Movie.title
ORDER BY Movie.title;

The GROUP BY will allow us to use MAX. It says to return only one row per Movie.title value. The other columns need to be aggregated with grouping functions, like MAX. You already had the ORDER BY clause and presumably it's correct.

• Steven L.: Is it possible to use an aggregation in the SELECT Clause without using the GROUP BY function? How would that differ?
• Alfred Armstrong: If you don't GROUP BY, the aggregation is across the entire result set. So MAX(stars) would be the maximum rating for any movie.
• Steven L.: Thanks that's obviously a huge caveat. I thought I understood this going through the videos, but I underestimated the complexity of it all.

Answer 2

Style Points

You use a mixture of both lowercase and UPPERCASE for your keywords, sticking to one or the other makes a query much easier to read at a glance.

Old-style Joins

It's generally agreed by the SQL community that old style joins are more difficult to read and use. They also have the nasty side effect of becoming Cross Joins (also known as the Cartesian Product) if you forget to add the correct code to the where clause

Your query would be much nicer to read with an explicit INNER JOIN.

Putting it all together

Note that I prefer my keywords in UPPERCASE, so that is what I used when rewriting your query.

SELECT Movie.title, MAX(Rate.stars) 
FROM Movie
INNER JOIN Rating
 ON Movie.Mid = Rating.Mid
GROUP BY Movie.title
ORDER BY Movie.title; 

• Steven L.: This is an awesome suggestion and the code looks incredible compared to what I wrote. I'm having a much harder time than I thought I would with SQL.
• PenutReaper: @user245185 Well, what you had was a pretty good start. I'd recommend looking around this site at the questions and answers and seeing what mistakes or slip ups people regularly make so you can avoid them. I'm sure you'll get it in no time at all. The DBA StackExchange might be helpful too, though probably only once you get a little more familiar with SQL.
• Steven L.: Thanks. I'm going to take your suggestion and start looking at questions / answers tagged SQL. I hope this does not go against site rules but do you have a recommendation as to an SQL resource to learn how to query the database well?
• PenutReaper: This is a pretty good for SQL training. The MySQL Documentation provides a bunch of examples, just use the handy little search box and you'll find explanations and examples that should be helpful.