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

Last update on January 12 2026 12:50:02 (UTC/GMT +8 hours)

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
......
    925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK

View the table

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
....
   1013 | War

View the table

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
....
    904 |   1013

View the table

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
.....
    923 |   9020 |      6.70 |         13091

View the table

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.

Go to:

PREV : 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.

Practice Online