SQL exercises on movie Database: Find all the movies with year, genres, and name of the director
8. From the following tables, write a SQL query to find all the movies with year, genres, and name of the director.
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: genres
gen_id | gen_title --------+---------------------- 1001 | Action 1002 | Adventure 1003 | Animation 1004 | Biography 1005 | Comedy 1006 | Crime 1007 | Drama 1008 | Horror 1009 | Music 1010 | Mystery 1011 | Romance 1012 | Thriller 1013 | War
Sample table: movie_genres
mov_id | gen_id
--------+--------
922 | 1001
917 | 1002
903 | 1002
912 | 1003
911 | 1005
908 | 1006
913 | 1006
926 | 1007
928 | 1007
918 | 1007
921 | 1007
902 | 1008
923 | 1009
907 | 1010
927 | 1010
901 | 1010
914 | 1011
906 | 1012
904 | 1013
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 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 Solution:
-- Selecting specific columns from the tables movie, movie_genres, genres, movie_direction, and director
SELECT mov_title, mov_year, gen_title, dir_fname, dir_lname
-- Performing a natural join between the movie and movie_genres tables
FROM movie
NATURAL JOIN movie_genres
-- Performing a natural join between the result and the genres table
NATURAL JOIN genres
-- Performing a natural join between the result and the movie_direction table
NATURAL JOIN movie_direction
-- Performing a natural join between the final result and the director table
NATURAL JOIN director;
Sample Output:
mov_title | mov_year | gen_title | dir_fname | dir_
----------------------------------------------------+----------+----------------------+----------------------+----------
Vertigo | 1958 | Mystery | Alfred | Hitchcock
The Innocents | 1961 | Horror | Jack | Clayton
Lawrence of Arabia | 1962 | Adventure | David | Lean
The Deer Hunter | 1978 | War | Michael | Cimino
-- More --
Code Explanation :
The said query in SQL that joins the tables movie, movie_genres, genres, movie_direction, and director, and retrieves the title and year of the movies, the genre titles for each movie, and the first name and last name of the directors who directed each movie.
The NATURAL JOIN keyword, which joins the movie table with the movie_genres table to get the genre for each movie. Then joins the resulting table with the genres table to get the genre title for each genre ID. It then joins the resulting table with the movie_direction table to get the directors for each movie and finally, it joins the resulting table with the director table to get the first name and last name of the directors.
Alternative Solutions:
Using INNER JOIN:
SELECT a.act_fname, a.act_lname, c.mov_title, c.mov_year
SELECT m.mov_title, m.mov_year, g.gen_title, d.dir_fname, d.dir_lname
FROM movie m
JOIN movie_genres mg ON m.mov_id = mg.mov_id
JOIN genres g ON mg.gen_id = g.gen_id
JOIN movie_direction md ON m.mov_id = md.mov_id
JOIN director d ON md.dir_id = d.dir_id;
Explanation:
This query uses INNER JOINs to combine the movie, movie_genres, genres, movie_direction, and director tables based on their respective IDs. It retrieves the movie title (mov_title), year (mov_year), genre title (gen_title), director's first name (dir_fname), and last name (dir_lname) from the joined tables.
Using WHERE Clause with Table Aliases:
SELECT a.act_fname, a.act_lname, c.mov_title, c.mov_year
SELECT m.mov_title, m.mov_year, g.gen_title, d.dir_fname, d.dir_lname
FROM movie m, movie_genres mg, genres g, movie_direction md, director d
WHERE m.mov_id = mg.mov_id
AND mg.gen_id = g.gen_id
AND m.mov_id = md.mov_id
AND md.dir_id = d.dir_id;
Explanation:
This query uses the older comma-separated syntax for joining tables and specifies the join conditions in the WHERE clause. It retrieves the movie title (mov_title), year (mov_year), genre title (gen_title), director's first name (dir_fname), and last name (dir_lname) from the joined tables.
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 table, write a SQL query to find the movies with year and genres. Return movie title, movie year and generic title.
Next: From the following tables, write a SQL query to find the movies released before 1st January 1989. Sort the result-set in descending order by date of release. Return movie title, release year, date of release, duration, and first and last name of the director.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.