﻿ SQL exercises on movie Database: Find movies that have been reviewed by a reviewer and received a rating - 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 Tree:

## Query Visualization:

Duration:

Rows:

Cost:

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

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

## 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