SQL exercises on movie Database: Find the name of all reviewers who have rated 7 or more stars to their rating

Last update on December 20 2024 12:02:12 (UTC/GMT +8 hours)

6. From the following table, write a SQL query to find all reviewers who have rated seven or more stars to their rating. 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 the 'rev_name' column from the 'reviewer' table
-- Joining the 'rating' table based on the 'rev_id' column
-- Filtering results where 'rev_stars' is greater than or equal to 7 and 'rev_name' is not NULL
SELECT reviewer.rev_name
FROM reviewer, rating
WHERE rating.rev_id = reviewer.rev_id
AND rating.rev_stars >= 7 
AND reviewer.rev_name IS NOT NULL;

Sample Output:

            rev_name
--------------------------------
Righty Sock
Jack Malvern
Flagrant Baronessa
Victor Woeltjen
Simon Wright
Mike Salvati
Sasha Goldshtein
Righty Sock
Krug Stillo
Hannah Steele
Vincent Cadena
Brandt Sponseller

Code Explanation:

The said query in SQL that selects the names of reviewers who have given ratings of 7 or higher.
The specified conditions that must be met in order for a reviewer's name to be selected. Selects only those reviewers who have given ratings of 7 or higher and whose names are not null and the reviewers who have actually given ratings.

Relational Algebra Expression: