SQL exercises on movie Database: Find the name of all reviewers and movies together in a single list

Last update on June 05 2024 13:34:17 (UTC/GMT +8 hours)

SQL movie Database: Basic Exercise-5 with Solution

5. From the following tables, write a SQL query to find the name of all reviewers and movies together in a single list.

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: 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 the 'rev_name' column from the 'reviewer' table
-- Combining the result with the 'mov_title' column from the 'movie' table using UNION
SELECT reviewer.rev_name
FROM reviewer
UNION
SELECT movie.mov_title
FROM movie;

Sample Output:

                      rev_name
----------------------------------------------------

 Hannah Steele
 Annie Hall
 The Usual Suspects
 Aliens
 Wesley S. Walker
 Josh Cates
 Trainspotting
 Brandt Sponseller
 Eyes Wide Shut
 American Beauty
 Blade Runner
 The Shawshank Redemption
 Victor Woeltjen
 Simon Wright
 Righty Sock
 Princess Mononoke
 Jack Malvern
 Paul Monks
 Scott LeBrun
 Vertigo
 Beyond the Sea
 Chinatown
 The Prestige
 The Deer Hunter
 Sasha Goldshtein
 Back to the Future
 Good Will Hunting
 Seven Samurai
 Alec Shaw
 Boogie Nights
 Mike Salvati
 Krug Stillo
 Deliverance
 Braveheart
 The Innocents
 Spirited Away
 Lawrence of Arabia
 Vincent Cadena
 Donnie Darko
 Avatar
 Neal Wruck
 Slumdog Millionaire
 Amadeus
 Flagrant Baronessa
 Richard Adams
 Titanic
(47 rows)

Code Explanation:

The given query in SQL that selects the rev_name column from the 'reviewer' table and combines it with the mov_title column from the movie table using the UNION operator.
Using the UNION operator, the results of two SELECT statements combined into one result set with distinct rows that contains all of the results of both queries.
In this case, it would return a list of all unique reviewer names from the reviewer table and all unique movie titles from the movie table, with no duplicates.

Relational Algebra Expression: