w3resource
SQL exercises

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. Write a query in SQL to find the reviewer's name and the title of the movie for those reviewers who rated more than one movies.

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)

Practice Online


Movie database model

Query Visualization:

Duration:

Query visualization of Find the reviewer's name and the title of the movie for those reviewers who rated more than one movies - Duration

Rows:

Query visualization of Find the reviewer's name and the title of the movie for those reviewers who rated more than one movies - Rows

Cost:

Query visualization of Find the reviewer's name and the title of the movie for those reviewers who rated more than one movies - Cost

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query in SQL to return the reviewer name, movie title, and stars for those movies which reviewed by a reviewer and must be rated. Sort the result by reviewer name, movie title, and number of stars.
Next: Write a query in SQL to find the movie title, and the highest number of stars that movie received and arranged the result according to the group of a movie and the movie title appear alphabetically in ascending order.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming