w3resource

SQL exercises on movie Database: Find the reviewer name, movie title, and stars for those movies which reviewed by a reviewer and must be rated

SQL movie Database: Subquery Exercise-9 with Solution

9. From the following tables, write a SQL query to find movies that have been reviewed by a reviewer and received a rating. Sort the result-set in ascending order by reviewer name, movie title, review Stars. Return reviewer name, movie title, review Stars.

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
   9018 | Vincent Cadena
   9019 | Brandt Sponseller
   9020 | Richard Adams
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 name, movie title, and review stars
-- Using the 'reviewer', 'rating', and 'movie' tables
-- Joining tables based on rev_id and mov_id
SELECT rev_name, mov_title, rev_stars 
FROM reviewer, rating, movie 
WHERE reviewer.rev_id = rating.rev_id 
  AND movie.mov_id = rating.mov_id 
  -- Filtering out NULL values for rev_name and rev_stars
  AND reviewer.rev_name IS NOT NULL 
  AND rating.rev_stars IS NOT NULL
-- Ordering the result set by rev_name, mov_title, and rev_stars
ORDER BY rev_name, mov_title, rev_stars;

Sample Output:

            rev_name            |                     mov_title                      | rev_stars
--------------------------------+----------------------------------------------------+-----------
 Brandt Sponseller              | Aliens                                             |      8.40
 Flagrant Baronessa             | Lawrence of Arabia                                 |      8.30
 Hannah Steele                  | Donnie Darko                                       |      8.10
 Jack Malvern                   | The Innocents                                      |      7.90
 Josh Cates                     | Good Will Hunting                                  |      4.00
 Krug Stillo                    | Braveheart                                         |      7.70
 Mike Salvati                   | Annie Hall                                         |      8.10
 Paul Monks                     | Boogie Nights                                      |      3.00
 Richard Adams                  | Beyond the Sea                                     |      6.70
 Righty Sock                    | Titanic                                            |      7.70
 Righty Sock                    | Vertigo                                            |      8.40
 Sasha Goldshtein               | American Beauty                                    |      7.00
 Simon Wright                   | The Usual Suspects                                 |      8.60
 Victor Woeltjen                | Avatar                                             |      7.30
 Vincent Cadena                 | Slumdog Millionaire                                |      8.00
(15 rows)

Code Explanation:

The said query in SQL that selects data from the tables reviewer, rating, and movie. As a result, it will retrieve the name of the reviewer, the title of the movie, as well as the number of stars that the reviewer has given in their rating.
The condition is specifies that the reviewer ID in the reviewer table must match the reviewer ID in the rating table, and the movie ID in the movie table must match the movie ID in the rating table. It also specifies that the reviewer name and rating stars must not be null.
The results are sorted in ascending order by reviewer name, movie title, and rating stars.

Alternative Solutions:

Using INNER JOIN:


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 r.rev_name IS NOT NULL AND ra.rev_stars IS NOT NULL
ORDER BY r.rev_name, m.mov_title, ra.rev_stars;

Explanation:

This solution uses the modern INNER JOIN syntax to combine the reviewer, rating, and movie tables based on their respective IDs. It includes conditions to ensure that rev_name and rev_stars are not NULL. The result set is ordered accordingly.

Using INNER JOIN with ON Clause:


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 r.rev_name IS NOT NULL AND ra.rev_stars IS NOT NULL
ORDER BY r.rev_name, m.mov_title, ra.rev_stars;

Explanation:

This query also uses the INNER JOIN syntax but explicitly specifies the join conditions using the ON clause. It achieves the same result as the first solution.

Using INNER JOIN with Subqueries:


SELECT r.rev_name, m.mov_title, ra.rev_stars 
FROM (
    SELECT * 
    FROM reviewer 
    WHERE rev_name IS NOT NULL
) r
JOIN (
    SELECT * 
    FROM rating 
    WHERE rev_stars IS NOT NULL
) ra ON r.rev_id = ra.rev_id
JOIN movie m ON m.mov_id = ra.mov_id
ORDER BY r.rev_name, m.mov_title, ra.rev_stars;

Explanation:

This query uses subqueries to filter the reviewer and rating tables for non-null values before performing the joins. The result is then joined with the movie table, and the final result set is ordered accordingly.

Relational Algebra Expression:

Relational Algebra Expression: Find the reviewer name, movie title, and stars for those movies which reviewed by a reviewer and must be rated.

Relational Algebra Tree:

Relational Algebra Tree: Find the reviewer name, movie title, and stars for those movies which reviewed by a reviewer and must be rated.

Practice Online


Movie database model

Query Visualization:

Duration:

Query visualization of Find the reviewer name, movie title, and stars for those movies which reviewed by a reviewer and must be rated - Duration

Rows:

Query visualization of Find the reviewer name, movie title, and stars for those movies which reviewed by a reviewer and must be rated - Rows

Cost:

Query visualization of Find the reviewer name, movie title, and stars for those movies which reviewed by a reviewer and must be rated - 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 those reviewers who have rated nothing for some movies. Return reviewer name.
Next: From the following table, write a SQL query to find those reviewers who rated more than one movie. Group the result set on reviewer’s name, movie title. Return reviewer’s name, movie title.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql-exercises/movie-database-exercise/sql-exercise-movie-database-30.php