SQL exercises on movie Database: Find the names of all reviewers who rated the movie American Beauty
12. From the following table, write a SQL query to find all reviewers who rated the movie ‘American Beauty’. 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 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 distinct reviewer names
-- Using the 'reviewer', 'rating', and 'movie' tables
-- Joining tables based on rev_id and mov_id
-- Filtering rows where reviewer.rev_id is equal to rating.rev_id
-- and movie.mov_id is equal to rating.mov_id
-- and movie.mov_title is equal to 'American Beauty'
SELECT DISTINCT reviewer.rev_name
FROM reviewer, rating, movie
WHERE reviewer.rev_id = rating.rev_id
AND movie.mov_id = rating.mov_id
AND movie.mov_title = 'American Beauty';
Sample Output:
rev_name
--------------------------------
Sasha Goldshtein
(1 row)
Code Explanation:
The said query in SQL that retrieves the names of all reviewers who have rated the movie "American Beauty":
The DISTINCT keyword ensures that only get each reviewer's name once .
The condition joins the reviewer, rating, and movie tables on their respective IDs (rev_id and mov_id), and only selecting rows where the movie title is "American Beauty".
Alternative Solutions:
Using INNER JOIN and MAX() Aggregate Function:
SELECT DISTINCT r.rev_name
FROM reviewer r
INNER JOIN rating ra ON r.rev_id = ra.rev_id
INNER JOIN movie m ON m.mov_id = ra.mov_id
WHERE m.mov_title = 'American Beauty';
Explanation:
This query uses INNER JOINs to combine the reviewer, rating, and movie tables based on their respective IDs. It then applies a WHERE clause to filter for movies with the title 'American Beauty' and selects the distinct reviewer names.
Using INNER JOIN with ON Clause:
SELECT DISTINCT r.rev_name
FROM reviewer r
INNER JOIN rating ra ON r.rev_id = ra.rev_id
INNER JOIN movie m ON m.mov_id = ra.mov_id
WHERE m.mov_title = 'American Beauty';
Explanation:
This query also uses INNER JOINs, but explicitly specifies the join conditions using the ON clause. It then applies a WHERE clause to filter for movies with the title 'American Beauty' and selects the distinct reviewer names.
Using Subquery:
SELECT DISTINCT r.rev_name
FROM reviewer r
WHERE r.rev_id IN (
SELECT ra.rev_id
FROM rating ra
JOIN movie m ON m.mov_id = ra.mov_id
WHERE m.mov_title = 'American Beauty'
);
Explanation:
This query uses a subquery to first find the rev_id values for reviews associated with the movie 'American Beauty'. It then selects the distinct reviewer names based on these rev_id values.
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 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.
Next: From the following tables, write a SQL query to find the movies, which have reviewed by any reviewer body except by 'Paul Monks'. Return movie title.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.