w3resource

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 Expression: Find the reviewer's name and the title of the movie for those reviewers who rated more than one movies.

Relational Algebra Tree:

Relational Algebra Tree: Find the reviewer's name and the title of the movie for those reviewers who rated more than one movies.

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

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

What is the best way to paginate results in SQL Server?

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

Database: SQL Server

Ref: https://bit.ly/3MGrNlk

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook