SQL exercises on movie Database: Find the director's first and last name together with the title of the movie(s) they directed and received the rating
SQL movie Database: Join Exercise-15 with Solution
15. From the following table, write a SQL query to find out which movies have received ratings. Return movie title, director first name, director last name and review 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: movie_direction
dir_id | mov_id
--------+--------
201 | 901
202 | 902
203 | 903
204 | 904
205 | 905
206 | 906
207 | 907
208 | 908
209 | 909
210 | 910
211 | 911
212 | 912
213 | 913
214 | 914
215 | 915
216 | 916
217 | 917
218 | 918
219 | 919
220 | 920
218 | 921
215 | 922
221 | 923
Sample table: director
dir_id | dir_fname | dir_lname
--------+----------------------+----------------------
201 | Alfred | Hitchcock
202 | Jack | Clayton
203 | David | Lean
204 | Michael | Cimino
205 | Milos | Forman
206 | Ridley | Scott
207 | Stanley | Kubrick
208 | Bryan | Singer
209 | Roman | Polanski
210 | Paul | Thomas Anderson
211 | Woody | Allen
212 | Hayao | Miyazaki
213 | Frank | Darabont
214 | Sam | Mendes
215 | James | Cameron
216 | Gus | Van Sant
217 | John | Boorman
218 | Danny | Boyle
219 | Christopher | Nolan
220 | Richard | Kelly
221 | Kevin | Spacey
222 | Andrei | Tarkovsky
223 | Peter | Jackson
Sample Solution:
-- Selecting specific columns from the Movie table, movie_direction table, director table, and rating table
SELECT mov_title, dir_fname, dir_lname, rev_stars
-- Joining the Movie and movie_direction tables using the mov_id column
FROM Movie
JOIN movie_direction USING(mov_id)
-- Joining the result with the director table using the dir_id column
JOIN director USING (dir_id)
-- Performing a left join with the rating table using the mov_id column
LEFT JOIN rating USING(mov_id)
-- Filtering the result to include only records where rev_stars is not null
WHERE rev_stars IS NOT NULL;
Sample Output:
mov_title | dir_fname | dir_lname | rev_stars
----------------------------------------------------+----------------------+----------------------+-----------
Vertigo | Alfred | Hitchcock | 8.40
The Innocents | Jack | Clayton | 7.90
Lawrence of Arabia | David | Lean | 8.30
Blade Runner | Ridley | Scott | 8.20
The Usual Suspects | Bryan | Singer | 8.60
Boogie Nights | Paul | Thomas Anderson | 3.00
Annie Hall | Woody | Allen | 8.10
Princess Mononoke | Hayao | Miyazaki | 8.40
American Beauty | Sam | Mendes | 7.00
Titanic | James | Cameron | 7.70
Good Will Hunting | Gus | Van Sant | 4.00
Donnie Darko | Richard | Kelly | 8.10
Slumdog Millionaire | Danny | Boyle | 8.00
Aliens | James | Cameron | 8.40
Beyond the Sea | Kevin | Spacey | 6.70
(15 rows)
Code Explanation :
The said query in SQL that retrieves data from the tables Movie, movie_direction, and director, and a left join to rating table to retrieve movies that have received ratings.
The FROM clause specifies the main table Movie and two other tables movie_direction and director that are joined using the JOIN keyword with mov_id as the common column.
The left join keyword is used to retrieve movies that have received ratings from the rating table.
The where clause is used to filter the result set to only include movies that have received ratings.
Alternative Solutions:
Using EXISTS Clause:
SELECT mov_title, dir_fname, dir_lname, rev_stars
FROM Movie
JOIN movie_direction USING(mov_id)
JOIN director USING(dir_id)
LEFT JOIN rating USING(mov_id)
WHERE EXISTS (SELECT 1 FROM rating WHERE rating.mov_id = Movie.mov_id AND rev_stars IS NOT NULL);
Explanation:
This SQL query uses the EXISTS clause with a subquery to check if there exists a rating for a particular movie. If a rating exists, the row is included in the result set.
Using a Subquery with IN Clause:
SELECT mov_title, dir_fname, dir_lname, rev_stars
FROM Movie
JOIN movie_direction USING(mov_id)
JOIN director USING(dir_id)
LEFT JOIN rating USING(mov_id)
WHERE mov_id IN (SELECT mov_id FROM rating WHERE rev_stars IS NOT NULL);
Explanation:
This SQL query uses a subquery in the WHERE clause with an IN clause to filter out movies without ratings. It selects mov_id values from the rating table where rev_stars is not null, and then includes only those movies in the result set.
Using a Subquery with JOIN:
SELECT mov_title, dir_fname, dir_lname, rev_stars
FROM Movie
JOIN movie_direction USING(mov_id)
JOIN director USING(dir_id)
LEFT JOIN (
SELECT mov_id, rev_stars
FROM rating
WHERE rev_stars IS NOT NULL
) AS rated_movies USING(mov_id)
WHERE rev_stars IS NOT NULL;
Explanation:
This SQL query uses a subquery that selects mov_id and rev_stars from the rating table where rev_stars is not null. This subquery is then joined with the main query using a LEFT JOIN, allowing for movies without ratings to still appear in the result set.
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 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.
Next: Write a query in SQL to find the movie title, actor first and last name, and the role for those movies where one or more actors acted in two or more movies.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics