SQL exercises on movie Database: Find the reviewer's name and the title of the movie for those reviewers who rated more than one movies
10. From the following table, write a SQL query to find movies that have been reviewed by a reviewer and received a rating. Group the result set on reviewer’s name, movie title. Return reviewer’s name, movie title.
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 table: movie
mov_id | mov_title | mov_year | mov_time | mov_lang | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo | 1958 | 128 | English | 1958-08-24 | UK
902 | The Innocents | 1961 | 100 | English | 1962-02-19 | SW
903 | Lawrence of Arabia | 1962 | 216 | English | 1962-12-11 | UK
904 | The Deer Hunter | 1978 | 183 | English | 1979-03-08 | UK
905 | Amadeus | 1984 | 160 | English | 1985-01-07 | UK
906 | Blade Runner | 1982 | 117 | English | 1982-09-09 | UK
907 | Eyes Wide Shut | 1999 | 159 | English | | UK
908 | The Usual Suspects | 1995 | 106 | English | 1995-08-25 | UK
909 | Chinatown | 1974 | 130 | English | 1974-08-09 | UK
910 | Boogie Nights | 1997 | 155 | English | 1998-02-16 | UK
911 | Annie Hall | 1977 | 93 | English | 1977-04-20 | USA
912 | Princess Mononoke | 1997 | 134 | Japanese | 2001-10-19 | UK
913 | The Shawshank Redemption | 1994 | 142 | English | 1995-02-17 | UK
914 | American Beauty | 1999 | 122 | English | | UK
915 | Titanic | 1997 | 194 | English | 1998-01-23 | UK
916 | Good Will Hunting | 1997 | 126 | English | 1998-06-03 | UK
917 | Deliverance | 1972 | 109 | English | 1982-10-05 | UK
918 | Trainspotting | 1996 | 94 | English | 1996-02-23 | UK
919 | The Prestige | 2006 | 130 | English | 2006-11-10 | UK
920 | Donnie Darko | 2001 | 113 | English | | UK
921 | Slumdog Millionaire | 2008 | 120 | English | 2009-01-09 | UK
922 | Aliens | 1986 | 137 | English | 1986-08-29 | UK
923 | Beyond the Sea | 2004 | 118 | English | 2004-11-26 | UK
924 | Avatar | 2009 | 162 | English | 2009-12-17 | UK
926 | Seven Samurai | 1954 | 207 | Japanese | 1954-04-26 | JP
927 | Spirited Away | 2001 | 125 | Japanese | 2003-09-12 | UK
928 | Back to the Future | 1985 | 116 | English | 1985-12-04 | UK
925 | Braveheart | 1995 | 178 | English | 1995-09-08 | UK
Sample Solution:
-- Selecting reviewer name and movie title
-- Using the 'reviewer', 'movie', 'rating', and 'rating r2' tables
-- Joining tables based on mov_id and rev_id
SELECT rev_name, mov_title
FROM reviewer, movie, rating, rating r2
WHERE rating.mov_id = movie.mov_id
AND reviewer.rev_id = rating.rev_ID
AND rating.rev_id = r2.rev_id
-- Grouping the result set by rev_name and mov_title
GROUP BY rev_name, mov_title
-- Filtering out groups where the count of entries is not greater than 1
HAVING count(*) > 1;
Sample Output:
rev_name | mov_title
--------------------------------+----------------------------------------------------
Righty Sock | Titanic
Righty Sock | Vertigo
(2 rows)
Code Explanation:
The given statement in SQL that selects the names of reviewers and the titles of movies that they have reviewed more than once, by joining the reviewer, movie, and rating tables, grouping the results by reviewer and movie, and filtering the results to only show combinations where the reviewer has reviewed the movie more than once.
The conditions specifies for joining that the mov_id column in the rating table and movie table must match, the rev_id column in the reviewer table and the rating table must match, and the rev_id column in the rating table must also match the rev_id column in the r2 table which is the alias of rating table.
The results are group by the rev_name and mov_title, and then applies a HAVING clause that filters the results to only show combinations where the count of rows in the group (i.e. the number of times the reviewer has reviewed the movie) is greater than 1.
Alternative Solution:
Using INNER JOIN:
SELECT r1.rev_name, m.mov_title
FROM reviewer r1
INNER JOIN rating ON r1.rev_id = rating.rev_id
INNER JOIN movie m ON rating.mov_id = m.mov_id
INNER JOIN rating r2 ON rating.rev_id = r2.rev_id
GROUP BY r1.rev_name, m.mov_title
HAVING COUNT(*) > 1;
Explanation:
This query uses INNER JOINs to combine the reviewer, rating, and movie tables based on their respective IDs. It then groups the result by rev_name and mov_title and applies a HAVING clause to filter for rows where the count is greater than 1.
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 those movies, which reviewed by a reviewer and got a rating. Sort the result-set in ascending order by reviewer name, movie title, review Stars. Return reviewer name, movie title, review Stars.
Next: From the following table, write a SQL query to find those movies, which have received highest number of stars. Group the result set on movie title and sorts the result-set in ascending order by movie title. Return movie title and maximum number of review stars.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.