w3resource
SQL exercises

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. Write a query in SQL to find the movie title, and the highest number of stars that movie received and arranged the result according to the group of a movie and the movie title appear alphabetically in ascending order.

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)

Practice Online


Movie database model

Query Visualization:

Duration:

Query visualization of Find the movie title, and the highest number of stars that movie received - Duration

Rows:

Query visualization of Find the movie title, and the highest number of stars that movie received - Rows

Cost:

Query visualization of Find the movie title, and 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 find the reviewer's name and the title of the movie for those reviewers who rated more than one movies.
Next: Write a query in SQL to find the names of all reviewers who rated the movie American Beauty.

What is the difficulty level of this exercise?



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