SQL exercises on movie Database: Find the highest-rated Mystery movie, and report the title, year, and rating
SQL movie Database: Join Exercise-22 with Solution
22. From the following tables, write a SQL query to find the highest-rated ‘Mystery Movies’. Return the title, year, and rating.
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: genres
gen_id | gen_title --------+---------------------- 1001 | Action 1002 | Adventure 1003 | Animation 1004 | Biography 1005 | Comedy 1006 | Crime 1007 | Drama 1008 | Horror 1009 | Music 1010 | Mystery 1011 | Romance 1012 | Thriller 1013 | War
Sample table: movie_genres
mov_id | gen_id
--------+--------
922 | 1001
917 | 1002
903 | 1002
912 | 1003
911 | 1005
908 | 1006
913 | 1006
926 | 1007
928 | 1007
918 | 1007
921 | 1007
902 | 1008
923 | 1009
907 | 1010
927 | 1010
901 | 1010
914 | 1011
906 | 1012
904 | 1013
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, movie_genres, genres, and rating tables
SELECT mov_title, mov_year, rev_stars
-- Performing natural joins between the movie, movie_genres, genres, and rating tables
FROM movie
NATURAL JOIN movie_genres
NATURAL JOIN genres
NATURAL JOIN rating
-- Filtering the result to include only records where gen_title is 'Mystery' and rev_stars is greater than or equal to ALL rev_stars in the subquery
WHERE gen_title = 'Mystery' AND rev_stars >= ALL (
-- Subquery: Selecting rev_stars from the rating, movie_genres, and genres tables, where gen_title is 'Mystery'
SELECT rev_stars
FROM rating
NATURAL JOIN movie_genres
NATURAL JOIN genres
WHERE gen_title = 'Mystery'
);
Sample Output:
mov_title | mov_year | rev_stars
----------------------------------------------------+----------+-----------
Vertigo | 1958 | 8.40
(1 row)
Code Explanation :
The said query in SQL that selects the movie title, year, and rating stars for the movie(s) in the "Mystery" genre with the highest rating.
The NATURAL JOIN clause joins the three tables on columns that have the same name in all three tables, which is the mov_id and gen_id columns.
The NATURAL JOIN clause is also used to join the rating table to the existing join. This joins the rating table on the mov_id column, which is shared with the movie_genres and genres tables.
The WHERE clause filters the results to only include rows where the gen_title column in the genres table is equal to "Mystery" and the rev_stars column in the rating table is greater than or equal to all of the rev_stars values for movies in the "Mystery" genre, which is found using a subquery.
The subquery selects the rev_stars column from the rating table and then uses NATURAL JOIN to join the movie_genres and genres tables. This filters the results to only include movies in the "Mystery" genre. The ALL operator is used to select only the maximum rev_stars value from the subquery results. The main query then selects all movies that have a rating of the maximum rev_stars value found by the subquery.
Alternative Solution:
Using Subquery with JOIN:
SELECT mov_title, mov_year, rev_stars
FROM movie
JOIN movie_genres USING(mov_id)
JOIN genres USING(gen_id)
JOIN rating USING(mov_id)
WHERE gen_title = 'Mystery'
AND rev_stars >= ALL (
SELECT rev_stars
FROM rating
JOIN movie_genres USING(mov_id)
JOIN genres USING(gen_id)
WHERE gen_title = 'Mystery'
);
Explanation:
This query uses a subquery with a JOIN to find the maximum rev_stars for movies with the genre 'Mystery'. The main query then selects movies with the same genre and a rating greater than or equal to the maximum 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 the highest-rated movies. Return movie title, movie year, review stars and releasing country.
Next: From the following tables, write a SQL query to find the years when most of the ‘Mystery Movies’ produced. Count the number of generic title and compute their average rating. Group the result set on movie release year, generic title. Return movie year, generic title, number of generic title and average rating.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics