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
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;
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)
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.
What is the difficulty level of this exercise?