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
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'));
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)
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.
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.
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
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
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