SQL exercises on movie Database: Find movie title and number of stars for each movie that has at least one rating and find the highest number of stars that movie received

SQL movie Database: Join Exercise-14 with Solution

14. Write a query in SQL to find movie title and number of stars for each movie that has at least one rating and find the highest number of stars that movie received and sort the result by movie title.

Sample table: movie

Sample table: rating

Sample Solution:

SELECT mov_title, MAX(rev_stars)
FROM movie
INNER JOIN rating USING(mov_id)
GROUP BY mov_title 
HAVING MAX(rev_stars)>0
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)

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous: Write a query in SQL to return the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars.
Next: Write a query in SQL to find the director's first and last name together with the title of the movie(s) they directed and received the rating.