w3resource

SQL exercises on movie Database: Find the titles of all movies which have been reviewed by anybody except by Paul Monks

SQL movie Database: Subquery Exercise-13 with Solution

13. From the following table, write a SQL query to find the movies that have not been reviewed by any reviewer body other than 'Paul Monks'. Return movie title.

Sample table: reviewer
 rev_id |            rev_name
--------+--------------------------------
   9001 | Righty Sock
   9002 | Jack Malvern
   9003 | Flagrant Baronessa
   9004 | Alec Shaw
   9005 |
   9006 | Victor Woeltjen
   9007 | Simon Wright
   9008 | Neal Wruck
   9009 | Paul Monks
   9010 | Mike Salvati
   9011 |
   9012 | Wesley S. Walker
   9013 | Sasha Goldshtein
   9014 | Josh Cates
   9015 | Krug Stillo
   9016 | Scott LeBrun
   9017 | Hannah Steele
   9018 | Vincent Cadena
   9019 | Brandt Sponseller
   9020 | Richard Adams
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 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 Solution:

-- Selecting movie titles
-- Using the 'movie' table
-- Filtering rows where movie.mov_id is in the result of a subquery
-- The subquery selects mov_id from the 'rating' table
--   where rev_id is not in the result of another subquery
--   The inner subquery selects rev_id from the 'reviewer' table
--   where rev_name is equal to 'Paul Monks'
SELECT movie.mov_title
FROM movie 
WHERE movie.mov_id IN (
  SELECT mov_id 
  FROM rating 
  WHERE rev_id NOT IN (
    SELECT rev_id 
    FROM reviewer 
    WHERE rev_name='Paul Monks'
  )
);

Sample Output:

                     mov_title
----------------------------------------------------
 Avatar
 Lawrence of Arabia
 Donnie Darko
 Aliens
 Vertigo
 The Innocents
 Slumdog Millionaire
 Annie Hall
 Good Will Hunting
 American Beauty
 Titanic
 Beyond the Sea
 Trainspotting
 Princess Mononoke
 The Usual Suspects
 Blade Runner
 Braveheart
 Chinatown
(18 rows)

Code Explanation:

The said query in SQL that retrieves the titles of all movies that have been reviewed by reviewers other than "Paul Monks".
1. The inner most subquery selects the ID of the reviewer with the name "Paul Monks".
2. The another subquery selects all the movie IDs from the rating table where the reviewer ID is not in another subquery of step 1.
3. The condition specifies that must be met for a row to be included in the results. That is for movies where their IDs are included in the subquery in step 2.

Alternative Solution:

Using INNER JOIN:


SELECT m.mov_title
FROM movie m
INNER JOIN rating r ON m.mov_id = r.mov_id
WHERE r.rev_id NOT IN (
    SELECT rev_id
    FROM reviewer
    WHERE rev_name = 'Paul Monks'
);

Explanation:

This query uses an INNER JOIN to combine the movie and rating tables based on mov_id. It then applies a WHERE clause to filter for reviews where the reviewer name is not 'Paul Monks'.

Practice Online


Movie database model

Query Visualization:

Duration:

Query visualization of Find the titles of all movies which have been reviewed by anybody except by Paul Monks - Duration

Rows:

Query visualization of Find the titles of all movies which have been reviewed by anybody except by Paul Monks - Rows

Cost:

Query visualization of Find the titles of all movies which have been reviewed by anybody except by Paul Monks - 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 all reviewers who rated the movie ‘American Beauty’. Return reviewer name.
Next: From the following tables, write a SQL query to find the lowest rated movies. Return reviewer name, movie title, and number of stars for those movies.

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