﻿ SQL exercises on movie Database: Find the reviewer name, movie title, and number of stars for those movies which rating is the lowest one - 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
``` rev_id |            rev_name
--------+--------------------------------
9001 | Righty Sock
9002 | Jack Malvern
9003 | Flagrant Baronessa
9004 | Alec Shaw
9005 |
9006 | Victor Woeltjen
9007 | Simon Wright
9008 | Neal Wruck
9009 | Paul Monks
9010 | Mike Salvati
9011 |
9012 | Wesley S. Walker
9013 | Sasha Goldshtein
9014 | Josh Cates
9015 | Krug Stillo
9016 | Scott LeBrun
9017 | Hannah Steele
9019 | Brandt Sponseller
```
Sample table: rating
``` mov_id | rev_id | rev_stars | num_o_ratings
--------+--------+-----------+---------------
901 |   9001 |      8.40 |        263575
902 |   9002 |      7.90 |         20207
903 |   9003 |      8.30 |        202778
906 |   9005 |      8.20 |        484746
924 |   9006 |      7.30 |
908 |   9007 |      8.60 |        779489
909 |   9008 |           |        227235
910 |   9009 |      3.00 |        195961
911 |   9010 |      8.10 |        203875
912 |   9011 |      8.40 |
914 |   9013 |      7.00 |        862618
915 |   9001 |      7.70 |        830095
916 |   9014 |      4.00 |        642132
925 |   9015 |      7.70 |         81328
918 |   9016 |           |        580301
920 |   9017 |      8.10 |        609451
921 |   9018 |      8.00 |        667758
922 |   9019 |      8.40 |        511613
923 |   9020 |      6.70 |         13091
```
Sample table: movie
``` mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
914 | American Beauty                                    |     1999 |      122 | English         |            | UK
915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
```

Sample Solution:

``````-- Selecting reviewer names, movie titles, and review stars
-- Using the 'reviewer', 'movie', and 'rating' tables
-- Filtering rows where rating.rev_stars is equal to the minimum rev_stars
-- The subquery selects the minimum rev_stars from the 'rating' table
-- Joining tables based on rev_id and mov_id to get reviewer names and movie titles
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.

Alternative Solutions:

Using JOIN and Subquery:

``````
SELECT r.rev_name, m.mov_title, ra.rev_stars
FROM reviewer r
JOIN rating ra ON r.rev_id = ra.rev_id
JOIN movie m ON m.mov_id = ra.mov_id
WHERE ra.rev_stars = (
SELECT MIN(rev_stars)
FROM rating
);
``````

Explanation:

This query uses JOINs to combine the reviewer, rating, and movie tables based on their respective IDs. It then applies a WHERE clause to filter for ratings that have the minimum rev_stars using a subquery.

Using INNER JOIN and Subquery:

``````
SELECT r.rev_name, m.mov_title, ra.rev_stars
FROM reviewer r
INNER JOIN rating ra ON r.rev_id = ra.rev_id
INNER JOIN movie m ON m.mov_id = ra.mov_id
WHERE ra.rev_stars = (
SELECT MIN(rev_stars)
FROM rating
);
``````

Explanation:

This query also uses INNER JOINs to combine the tables and applies a WHERE clause to filter for ratings that have the minimum rev_stars using a subquery.

Using JOINs with ON Clause:

``````
SELECT r.rev_name, m.mov_title, ra.rev_stars
FROM reviewer r
JOIN rating ra ON r.rev_id = ra.rev_id
JOIN movie m ON m.mov_id = ra.mov_id
WHERE ra.rev_stars = (
SELECT MIN(rev_stars)
FROM rating
);
``````

Explanation:

This query explicitly specifies the join conditions using the ON clause. It then applies a WHERE clause to filter for ratings that have the minimum rev_stars using a subquery.

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

﻿