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

SQL movie Database: Join Exercise-1 with Solution

1. From the following table, write a SQL query to find all reviewers whose ratings contain a NULL value. 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 reviewer names
-- Using the 'reviewer' table
SELECT rev_name
FROM reviewer
-- Performing an inner join with the 'rating' table on rev_id
INNER JOIN rating USING(rev_id)
-- Filtering rows where rev_stars is NULL
WHERE rev_stars IS NULL;

Sample Output:

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

Code Explanation:

The above query in SQL which selects the "rev_name" column from the 'reviewer' table for reviewers who have not yet provided a rating by joining the "rating" table using the "rev_id" column.
The "INNER JOIN" keyword combined the two tables based on the "rev_id" column.
The "USING" keyword specifies which column to join on.
The "WHERE" clause filters the results to only include rows where "rev_stars" is null.

Alternative Solution:

JOIN with WHERE Clause:

SELECT rev_name
FROM reviewer, rating
WHERE reviewer.rev_id = rating.rev_id
AND rating.rev_stars IS NULL;

Explanation:

This query uses the older comma-separated syntax for joining tables and specifies the join conditions in the WHERE clause. It then applies a WHERE clause to filter for reviews where the rev_stars is NULL.

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous: SQL JOINS Exercises on movie Database
Next: From the following tables, write a SQL query to find the actors who were cast in the movie 'Annie Hall'. Return actor first name, last name and role.