SQL exercises on movie Database: Find the movie title, and the highest number of stars that movie received

SQL movie Database: Subquery Exercise-11 with Solution

11. From the following tables, write a SQL query to find those movies, which have received highest number of stars. Group the result set on movie title and sorts the result-set in ascending order by movie title. Return movie title and maximum number of review stars.

Sample table: rating

Sample table: movie

Sample Solution:

SELECT mov_title, MAX(rev_stars)
FROM movie, rating 
WHERE movie.mov_id=rating.mov_id 
AND rating.rev_stars IS NOT NULL
GROUP BY  mov_title
ORDER BY mov_title;

Sample Output:

                     mov_title                      | max
----------------------------------------------------+------
 Aliens                                             | 8.40
 American Beauty                                    | 7.00
 Annie Hall                                         | 8.10
 Avatar                                             | 7.30
 Beyond the Sea                                     | 6.70
 Blade Runner                                       | 8.20
 Boogie Nights                                      | 3.00
 Braveheart                                         | 7.70
 Donnie Darko                                       | 8.10
 Good Will Hunting                                  | 4.00
 Lawrence of Arabia                                 | 8.30
 Princess Mononoke                                  | 8.40
 Slumdog Millionaire                                | 8.00
 The Innocents                                      | 7.90
 The Usual Suspects                                 | 8.60
 Titanic                                            | 7.70
 Vertigo                                            | 8.40
(17 rows)

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following tables, write a SQL query to find those reviewers who rated more than one movie. Group the result set on reviewer’s name, movie title. Return reviewer’s name, movie title.
Next: From the following tables, write a SQL query to find all reviewers who rated the movie ‘American Beauty’. Return reviewer name.