SQL exercises on movie Database: Find the titles of all movies directed by James Cameron
15. From the following tables, write a SQL query to find the movies directed by 'James Cameron'. Return movie title.
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 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 Solution:
-- Selecting movie titles
-- Using the 'movie' table
SELECT mov_title
FROM movie
-- Filtering rows where mov_id is in the result of a subquery
-- The subquery selects mov_id from the 'movie_direction' table
WHERE mov_id IN (
-- Further filtered by dir_id from the result of another subquery
SELECT mov_id
FROM movie_direction
-- The innermost subquery selects dir_id from the 'director' table
-- Filtering directors with the first name 'James' and last name 'Cameron'
WHERE dir_id IN (
SELECT dir_id
FROM director
WHERE dir_fname = 'James' AND dir_lname='Cameron'
)
);
Sample Output:
mov_title
----------------------------------------------------
Titanic
Aliens
(2 rows)
Code Explanation:
The said query in SQL that selects the dir_id of the director named James Cameron from the director table, and then selects the mov_id of all movies directed by James Cameron from the movie_direction table. Finally, the query selects the title of each of those movies from the movie table.
The subquery is used to find the director ID for James Cameron, and then uses another subquery to find all movies directed by him based on the director ID. Then the outer most query retrieves the titles of all those movies from the movie table.
Alternative Solutions:
Using INNER JOIN:
SELECT mov_title FROM movie
JOIN movie_direction
ON movie.mov_id=movie_direction.mov_id
JOIN director
ON movie_direction.dir_id=director.dir_id
WHERE dir_fname = 'James' AND dir_lname='Cameron';
Explanation:
This query uses INNER JOINs to combine the movie, movie_direction, and director tables based on their respective IDs. It then applies a WHERE clause to filter for movies directed by James Cameron.
Using EXISTS:
SELECT m.mov_title
FROM movie m
WHERE EXISTS (
SELECT 1
FROM movie_direction md
JOIN director d ON d.dir_id = md.dir_id
WHERE md.mov_id = m.mov_id
AND d.dir_fname = 'James' AND d.dir_lname = 'Cameron'
);
Explanation:
This query uses the EXISTS clause to check if there exists a record in the movie_direction and director tables for each movie where the director's name is James Cameron.
Using IN Subquery:
SELECT mov_title
FROM movie
WHERE mov_id IN (
SELECT md.mov_id
FROM movie_direction md
JOIN director d ON d.dir_id = md.dir_id
WHERE d.dir_fname = 'James' AND d.dir_lname = 'Cameron'
);
Explanation:
This query uses an IN subquery to find movies whose mov_id is present in the list of mov_id values from the subquery, which identifies movies directed by James Cameron.
Relational Algebra Expression:
Relational Algebra Tree:
Practice Online
Query Visualization for Sample Solution:
Duration:
Rows:
Cost:
Query Visualization for alternate Sample Solution:
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 the lowest rated movies. Return reviewer name, movie title, and number of stars for those movies.
Next: Write a query in SQL to find the name of 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.