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
Query Visualization:
Duration:
Rows:
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.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/sql-exercises/movie-database-exercise/sql-exercise-movie-database-47.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics