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

SQL movie Database: Subquery Exercise-10 with Solution

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: reviewer

Sample table: rating

Sample table: movie

Sample Solution:

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 
GROUP BY rev_name, mov_title 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.

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.