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 tables, write a SQL query to find the lowest rated movies. 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)

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.