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: reviewerrev_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 AdamsSample 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 | 13091Sample 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
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics