SQL exercises on movie Database: Find the name of all reviewers who have rated their ratings with a NULL value
1. From the following table, write a SQL query to find all reviewers whose ratings contain a NULL value. Return reviewer name.
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 | 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:

Go to:
PREV : 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.
Practice Online

Query Visualization:
Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.