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 tables, write a SQL query to find those reviewers who rated more than one movie. 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)

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.