w3resource
SQL exercises

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


Movie database model

Query Visualization:

Duration:

Query visualization of 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 - Duration

Rows:

Query visualization of 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 - Rows

Cost:

Query visualization of 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 - 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.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming