SQL exercises on movie Database: Find the titles of all movies that have no ratings
7. From the following table, write a SQL query to search for movies that do not have any ratings. Return movie title.
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 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 Solution:
-- Selecting distinct movie titles from the 'movie' table
-- Using a subquery to find mov_id from the 'movie' table based on a condition
-- Specifically, finding mov_id where it is not in the set of mov_id from the 'Rating' table
SELECT DISTINCT mov_title
FROM movie
WHERE mov_id IN (
-- Subquery to find mov_id not present in the 'Rating' table
SELECT mov_id
FROM movie
WHERE mov_id NOT IN (
SELECT mov_id
FROM Rating
)
);
Sample Output:
mov_title
----------------------------------------------------
Deliverance
Amadeus
Spirited Away
The Prestige
The Deer Hunter
Eyes Wide Shut
Back to the Future
The Shawshank Redemption
Seven Samurai
(9 rows)
Code Explanation:
The said query in SQL which selects distinct movie titles from the 'movie' table where the corresponding movie IDs are not present in the 'Rating' table. The results are ordered by movie title in ascending order.
The query uses a subquery to find the movie IDs that are not present in the 'Rating' table, and then selects the distinct movie titles corresponding to those IDs. The "DISTINCT" keyword ensures that only unique movie titles are returned.
Alternative Solutions:
Using LEFT JOIN and NULL Check:
SELECT DISTINCT m.mov_title
FROM movie m
LEFT JOIN rating r ON m.mov_id = r.mov_id
WHERE r.mov_id IS NULL;
Explanation:
This solution uses a LEFT JOIN to combine the movie and rating tables based on mov_id. It then filters the result to only include movies where there is no corresponding record in the rating table, effectively finding movies without ratings.
Using NOT EXISTS:
SELECT DISTINCT m.mov_title
FROM movie m
WHERE NOT EXISTS (
SELECT 1
FROM rating r
WHERE r.mov_id = m.mov_id
);
Explanation:
This solution employs the NOT EXISTS clause to check if there is no corresponding record in the rating table for each movie in the movie table. If there is no match, the movie's mov_title is included in the result set.
Using NOT IN Subquery:
SELECT DISTINCT mov_title
FROM movie
WHERE mov_id NOT IN (
SELECT mov_id
FROM rating
);
Explanation:
This solution uses a subquery with NOT IN to find movies whose mov_id is not present in the list of mov_id values from the rating table, effectively identifying movies without ratings.
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 those years, which produced at least one movie and that, received a rating of more than three stars. Sort the result-set in ascending order by movie year. Return movie year.
Next: From the following tables, write a SQL query to find those reviewers who have rated nothing for some movies. Return reviewer name.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.