w3resource

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.

Sample table: reviewer
 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
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 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 Expression: Find the name of all reviewers who have rated their ratings with a NULL value.

Relational Algebra Tree:

Relational Algebra Tree: Find the name of all reviewers who have rated their ratings with a NULL value.

Practice Online


Movie database model

Query Visualization:

Duration:

Query visualization of Find the name of all reviewers who have rated their ratings with a NULL value - Duration

Rows:

Query visualization of Find the name of all reviewers who have rated their ratings with a NULL value - Rows

Cost:

Query visualization of Find the name of all reviewers who have rated their ratings with a NULL value - 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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

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-8.php