SQL Query Optimization: Finding Highest Movie Rating

This article addresses a common SQL challenge: finding the highest rating for each movie in a database. We'll analyze an incorrect query and provide a corrected and optimized solution. Let's dive into the problem and its solution.

Problem Statement

Given the following database schema:

• Movie ( mID, title, year, director): Represents movies with their ID, title, release year, and director.
• Reviewer ( rID, rname ): Stores information about reviewers with their ID and name.
• Rating ( rID, mID, ratingStars, ratingDate ): Records movie ratings given by reviewers, including the reviewer ID, movie ID, rating stars (1-5), and rating date.

The task is to find the highest number of stars received by each movie that has at least one rating. The output should display the movie title and its highest rating, sorted alphabetically by movie title.

Incorrect Query

The provided query attempts to solve the problem but contains errors. Here's the original query and why it's incorrect:

SELECT ratingStars FROM Rating1 WHERE NOT EXISTS(SELECT* FROM Rating2 WHERE Rating1.mID=Rating1.mID AND Rating1.ratingStars<Rating2.ratingStars) GROUP BY Movie.title HAVING COUNT(*) >= 1 ORDER BY Movie.title;

The following issues are present in the incorrect query:

• Missing Joins: The query lacks the necessary JOINs to connect the Rating table with the Movie table, which is crucial for retrieving the movie title.
• Incorrect Table Names: The query uses Rating1 and Rating2, which are not the actual table names. The correct table name is Rating.
• Logic Error: The logic for finding the highest rating is not well-defined. It uses a subquery with NOT EXISTS and AND, which doesn't directly find the maximum rating.

Corrected and Optimized Query

Here's the corrected and optimized query to solve the problem:

SELECT Movie.title, MAX(Rating.ratingStars) AS highestStars FROM Movie JOIN Rating ON Movie.mID = Rating.mID GROUP BY Movie.title ORDER BY Movie.title;

This query uses the following steps:

• JOIN: The JOIN clause connects the Movie and Rating tables on the common column mID.
• MAX Function: The MAX(Rating.ratingStars) function calculates the highest rating for each movie.
• GROUP BY: The GROUP BY Movie.title clause groups the results by movie title, allowing the MAX function to find the highest rating for each movie.
• ORDER BY: The ORDER BY Movie.title clause sorts the results alphabetically by movie title.

Explanation

The corrected query effectively retrieves the highest rating for each movie by joining the tables, using the MAX function, and grouping the results. It avoids the errors of the original query, ensuring a correct and efficient solution. Remember, understanding the database schema and applying the appropriate SQL commands are crucial for successful query optimization.