﻿ SQL exercises on movie Database: Look for movies that have not been reviewed by any reviewer body other than 'Paul Monks' - 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

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

## Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

## SQL: Tips of the Day

What is the best way to paginate results in SQL Server?

```SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
FROM      Orders
WHERE     OrderDate >= '1980-01-01'
) AS RowConstrainedResult
WHERE   RowNum >= 1
AND RowNum < 20
ORDER BY RowNum
```

Database: SQL Server

Ref: https://bit.ly/3MGrNlk

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook