SQL exercises on movie Database: Find the name of all reviewers who have rated 7 or more stars to their rating
SQL movie Database: Basic Exercise-6 with Solution
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: 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 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:
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: From the following tables, write a SQL query to find the name of all reviewers and movies together in a single list.
Next: From the following tables, write a SQL query to find the movies without any rating. Return movie title.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
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-6.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics