SQL exercises on movie Database: Find the names of all reviewers who have ratings with a NULL value

Last update on December 20 2024 11:42:38 (UTC/GMT +8 hours)

8. From the following table, write a SQL query to find those reviewers who have not given a rating to certain films. Return reviewer name.

 rev_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 Adams

 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 reviewer names from the 'reviewer' table
-- Using a subquery to find rev_id from the 'rating' table based on a condition
-- Specifically, finding rev_id where rev_stars is NULL
SELECT DISTINCT rev_name 
FROM reviewer 
WHERE rev_id IN (
  -- Subquery to find rev_id with rev_stars being NULL
  SELECT rev_id 
  FROM rating 
  WHERE rev_stars IS NULL
);

Sample Output:

            rev_name
--------------------------------
 Neal Wruck
 Scott LeBrun
(2 rows)

Code Explanation:

The said query in SQL which selects distinct reviewer names from the 'reviewer' table where the corresponding reviewer IDs have at least one review with a null rating in the 'Rating' table. The results are ordered by reviewer name in ascending order.
The query uses a subquery to find the reviewer IDs with null ratings, and then selects the distinct reviewer names corresponding to those IDs. The "DISTINCT" keyword ensures that only unique reviewer names are returned.

Alternative Solutions:

Using INNER JOIN:

SELECT DISTINCT r.rev_name
FROM reviewer r
JOIN rating ra ON r.rev_id = ra.rev_id
WHERE ra.rev_stars IS NULL;

Explanation:

This solution uses an INNER JOIN to combine the reviewer and rating tables based on rev_id. It then filters the result to only include reviewers whose rev_stars are NULL.

Using EXISTS:

SELECT DISTINCT r.rev_name
FROM reviewer r
WHERE EXISTS (
    SELECT 1
    FROM rating ra
    WHERE ra.rev_id = r.rev_id AND ra.rev_stars IS NULL
);

Explanation:

This query utilizes the EXISTS clause to check if there exists a record in the rating table with a NULL rev_stars for each reviewer in the reviewer table. If such a record exists, the reviewer's rev_name is included in the result set.

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 table, write a SQL query to find those movies, which have no ratings. Return movie title.
Next: From the following tables, write a SQL query to find those movies, which reviewed by a reviewer and got a rating. Sort the result-set in ascending order by reviewer name, movie title, review Stars. Return reviewer name, movie title, review Stars.