w3resource

SQL exercises on movie Database: Find the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars

SQL movie Database: Join Exercise-13 with Solution

13. From the following tables, write a SQL query to get the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars.

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 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: 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 Solution:

-- Selecting specific columns from the movie, rating, and reviewer tables
SELECT rev_name, mov_title, rev_stars
-- Joining the movie and rating tables based on the mov_id column
FROM movie, rating, reviewer
   WHERE movie.mov_id = rating.mov_id 
-- Joining the result with the reviewer table based on the rev_id column
   AND reviewer.rev_id = rating.rev_id 
-- Filtering the result to include only records where rev_name is not NULL
   AND rev_name IS NOT NULL
-- Ordering the result by rev_name, mov_title, and rev_stars in ascending order
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
 Neal Wruck                     | Chinatown                                          |
 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
 Scott LeBrun                   | Trainspotting                                      |
 Simon Wright                   | The Usual Suspects                                 |      8.60
 Victor Woeltjen                | Avatar                                             |      7.30
 Vincent Cadena                 | Slumdog Millionaire                                |      8.00
(17 rows)

Code Explanation :

The said query in SQL that retrieves "rev_name", "mov_title", and "rev_stars" from the tables 'movie', 'rating', and 'reviewer'.
The query joins the 'movie' and 'rating' tables based on mov_id column, and the 'reviewer' and 'rating' tables based on rev_id column. The "IS NOT NULL" condition is used to filter out any rows where the "rev_name" column is empty or null.
The result set will be ordered by "rev_name" in ascending order, then "mov_title" in ascending order, and finally "rev_stars" in ascending order.

Alternative Solutions:

Using INNER JOIN and WHERE Clause:

SELECT rev_name, mov_title, rev_stars
FROM movie
INNER JOIN rating ON movie.mov_id = rating.mov_id
INNER JOIN reviewer ON reviewer.rev_id = rating.rev_id
WHERE rev_name IS NOT NULL
ORDER BY rev_name, mov_title, rev_stars;

Explanation:

This SQL query retrieves the names of reviewers, along with the corresponding movie titles and their ratings. It uses INNER JOINs to combine the movie, rating, and reviewer tables based on their respective IDs. The WHERE clause filters out any null reviewer names. The results are then ordered by reviewer name, movie title, and rating.

Using INNER JOIN with USING Clause:

SELECT rev_name, mov_title, rev_stars
FROM movie
INNER JOIN rating USING(mov_id)
INNER JOIN Reviewer USING(rev_id)
WHERE rev_name IS NOT NULL
ORDER BY rev_name, mov_title, rev_stars;

Explanation:

This SQL query retrieves the names of reviewers, along with the corresponding movie titles and their ratings. It uses INNER JOINs with the USING clause to combine the movie, rating, and reviewer tables based on their respective IDs. The WHERE clause filters out any null reviewer names. The results are then ordered by reviewer name, movie title, and rating.

Using NATURAL JOIN and WHERE Clause:

SELECT rev_name, mov_title, rev_stars
FROM movie 
NATURAL JOIN rating 
NATURAL JOIN reviewer
WHERE rev_name IS NOT NULL
ORDER BY rev_name, mov_title, rev_stars;

Explanation:

This SQL query retrieves the names of reviewers, along with the corresponding movie titles and their ratings. It uses NATURAL JOINs to automatically combine the movie, rating, and reviewer tables based on their common columns. The WHERE clause filters out any null reviewer names. The results are then ordered by reviewer name, movie title, and rating.

Relational Algebra Expression:

Relational Algebra Expression: Find the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars .

Relational Algebra Tree:

Relational Algebra Tree: Find the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars .

Relational Algebra Expression:

Relational Algebra Expression: Find the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars .

Relational Algebra Tree:

Relational Algebra Tree: Find the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars .

Relational Algebra Expression:

Relational Algebra Expression: Find the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars .

Relational Algebra Tree:

Relational Algebra Tree: Find the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars .

Relational Algebra Expression:

Relational Algebra Expression: Find the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars .

Relational Algebra Tree:

Relational Algebra Tree: Find the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars .

Practice Online


Movie database model

Query Visualization for Sample Solution:

Duration:

Query visualization of Find the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars - Duration

Rows:

Query visualization of Find the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars - Rows

Cost:

Query visualization of Find the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars - Cost

Query Visualization for alternate Sample Solution:

Duration:

Query visualization of Find the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars - Duration

Rows:

Query visualization of Find the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars - Rows

Cost:

Query visualization of Find the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars - Cost

Query Visualization for second alternate Sample Solution:

Duration:

Query visualization of Find the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars - Duration

Rows:

Query visualization of Find the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars - Rows

Cost:

Query visualization of Find the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars - Cost

Query Visualization for third alternate Sample Solution:

Duration:

Query visualization of Find the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars - Duration

Rows:

Query visualization of Find the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars - Rows

Cost:

Query visualization of Find the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars - 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 years when a movie received a rating of 3 or 4. Sort the result in increasing order on movie year. Return move year.
Next: From the following tables, write a SQL query to find those movies that have at least one rating and received highest number of stars. Sort the result-set on movie title. Return movie title and maximum review stars.

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://w3resource.com/sql-exercises/movie-database-exercise/sql-exercise-movie-database-38.php