w3resource

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


Movie database model

Query Visualization:

Duration:

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

Rows:

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

Cost:

Query visualization of Find the highest-rated Mystery movie, and report the title, year, and rating - 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.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

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-48.php