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:
Go to:
PREV : 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.
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.