w3resource

SQL exercises on movie Database: Find the highest-rated movie, and report its title, year, rating, and releasing country

SQL movie Database: Join Exercise-21 with Solution

21. From the following tables, write a SQL query to find the highest-rated movies. Return movie title, movie year, review stars and releasing country.

Sample table: movie
 mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
    901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
    902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
    903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
    904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
    905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
    906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
    907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
    908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
    909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
    910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
    911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
    912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
    913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
    914 | American Beauty                                    |     1999 |      122 | English         |            | UK
    915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
    916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
    917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
    918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
    919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
    920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
    921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
    922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
    923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
    924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
    926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
    927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
    928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
    925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
Sample table: rating
 mov_id | rev_id | rev_stars | num_o_ratings
--------+--------+-----------+---------------
    901 |   9001 |      8.40 |        263575
    902 |   9002 |      7.90 |         20207
    903 |   9003 |      8.30 |        202778
    906 |   9005 |      8.20 |        484746
    924 |   9006 |      7.30 |
    908 |   9007 |      8.60 |        779489
    909 |   9008 |           |        227235
    910 |   9009 |      3.00 |        195961
    911 |   9010 |      8.10 |        203875
    912 |   9011 |      8.40 |
    914 |   9013 |      7.00 |        862618
    915 |   9001 |      7.70 |        830095
    916 |   9014 |      4.00 |        642132
    925 |   9015 |      7.70 |         81328
    918 |   9016 |           |        580301
    920 |   9017 |      8.10 |        609451
    921 |   9018 |      8.00 |        667758
    922 |   9019 |      8.40 |        511613
    923 |   9020 |      6.70 |         13091

Sample Solution:

-- Selecting specific columns from the movie and rating tables
SELECT mov_title, mov_year, rev_stars, mov_rel_country
-- Performing a natural join between the movie and rating tables
FROM movie 
NATURAL JOIN rating
-- Filtering the result to include only records where rev_stars is equal to the maximum rev_stars in the rating table
WHERE rev_stars = (
  -- Subquery: Selecting the maximum rev_stars from the rating table
  SELECT MAX(rev_stars)
  FROM rating
);

Sample Output:

                     mov_title                      | mov_year | rev_stars | mov_rel_country
----------------------------------------------------+----------+-----------+-----------------
 The Usual Suspects                                 |     1995 |      8.60 | UK
(1 row)

Code Explanation :

The said query in SQL that selects the movie title, year, rating stars, and release country for the movie(s) with the highest rating.
The NATURAL JOIN clause joins the two tables on columns that have the same name in both tables, which in this case is the mov_id column.
The WHERE clause filters the results to only include rows where the rev_stars column in the rating table is equal to the maximum rev_stars value from the rating table, which is found using a subquery. This subquery selects the maximum rev_stars value from the rating table.
The result will include all movies that have the highest rating(s), which could be more than one movie if they share the same maximum rating.

Alternative Solutions:

Using EXISTS Clause:


SELECT m.mov_title, m.mov_year, r.rev_stars, m.mov_rel_country
FROM movie m
JOIN rating r ON m.mov_id = r.mov_id
WHERE r.rev_stars IS NOT NULL
ORDER BY r.rev_stars DESC
LIMIT 1;

Explanation:

This SQL query uses an ORDER BY clause to sort the result set by rev_stars in descending order. The LIMIT 1 ensures that only the highest rated movie is returned.

Using a JOIN and Subquery in ON Clause:


SELECT m.mov_title, m.mov_year, r.rev_stars, m.mov_rel_country
FROM movie m
JOIN rating r ON m.mov_id = r.mov_id
JOIN (
    SELECT MAX(rev_stars) as max_stars
    FROM rating
) max_rating ON r.rev_stars = max_rating.max_stars;

Explanation:

This SQL query first creates a subquery to find the maximum rev_stars. It then joins the movie and rating tables, and filters the results for movies with rev_stars equal to the maximum value found in the subquery.

Practice Online


Movie database model

Query Visualization:

Duration:

Query visualization of Find the highest-rated movie, and report its title, year, rating, and releasing country - Duration

Rows:

Query visualization of Find the highest-rated movie, and report its title, year, rating, and releasing country - Rows

Cost:

Query visualization of Find the highest-rated movie, and report its title, year, rating, and releasing country - 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 movies where actor’s first name is 'Harrison' and last name is 'Ford'. Return movie title.
Next: From the following tables, write a SQL query to find the highest-rated 'Mystery Movies'. Return the title, year, and rating.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.