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 those movies, which reviewed by a reviewer and got 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

Sample table : rating

Sample table : movie

Sample Solution:

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 
     AND reviewer.rev_name IS NOT NULL 
       AND rating.rev_stars IS NOT NULL
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)

Relational Algebra Expression:

Relational Algebra Tree:

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