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

Sample table: rating

Sample table: movie

Sample Solution:

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.

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