SQL exercises on movie Database: Find the name of all reviewers and movies together in a single list
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:
Relational Algebra Tree:
Go to:
PREV : From the following table, write a SQL query to find those movies, which was released before 1998. Return movie title.
NEXT : From the following tables, write a SQL query to find all reviewers who have rated 7 or more stars to their rating. Return reviewer name.
Practice Online
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.