SQL exercises on movie Database: Find the names of all reviewers who rated the movie American Beauty

SQL movie Database: Subquery Exercise-12 with Solution

12. From the following table, write a SQL query to find all reviewers who rated the movie ‘American Beauty’. Return reviewer name.

Sample table: reviewer

Sample table: rating

Sample table: movie

Sample Solution:

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".

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.