w3resource

SQL exercises on movie Database: Find the reviewer name, movie title, and number of stars for those movies which rating is the lowest one

SQL movie Database: Subquery Exercise-14 with Solution

14. From the following table, write a SQL query to find the movies with the lowest ratings. Return reviewer name, movie title, and number of stars for those movies.

Sample table : reviewer


Sample table: rating


Sample table: movie


Sample Solution:

SELECT reviewer.rev_name, movie.mov_title, rating.rev_stars
FROM reviewer, movie, rating
WHERE rating.rev_stars = (
SELECT MIN(rating.rev_stars)
FROM rating
)
AND rating.rev_id = reviewer.rev_id
AND rating.mov_id = movie.mov_id;

Sample Output:

            rev_name            |                     mov_title                      | rev_stars
--------------------------------+----------------------------------------------------+-----------
 Paul Monks                     | Boogie Nights                                      |      3.00
(1 row)

Code Explanation:

The said query in SQL that retrieves the names of reviewers who gave the lowest rating, the titles of movies that received the lowest rating, and the lowest rating itself.
the names of reviewers, the titles of movies, and the lowest ratings given by any reviewer for any movie in a database.
The query joins the tables reviewer, movie, and rating.
The query retrieves the rev_name from the reviewer table, the mov_title from the movie table, and the rev_stars from the rating table. It uses a subquery to find the minimum value of rev_stars from the rating table. It then selects the rows from the three tables where the rev_stars equals the minimum value found in the subquery, and the rev_id and mov_id in the rating table match the corresponding values in the reviewer and movie tables.

Practice Online


Movie database model

Query Visualization:

Duration:

Query visualization of Find the reviewer name, movie title, and number of stars for those movies which rating is the lowest one - Duration

Rows:

Query visualization of Find the reviewer name, movie title, and number of stars for those movies which rating is the lowest one - Rows

Cost:

Query visualization of Find the reviewer name, movie title, and number of stars for those movies which rating is the lowest one - 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 the movies, which have reviewed by any reviewer body except by 'Paul Monks'. Return movie title.
Next: From the following tables, write a SQL query to find the movies directed by ‘James Cameron’. Return movie title.

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