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

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