SQL exercises on movie Database: Find the titles of all movies directed by the director whose first and last name are Woody Allen
SQL movie Database: Subquery Exercise-5 with Solution
5. From the following tables, write a SQL query to find those movies directed by the director whose first name is Woddy and last name is Allen. Return movie title.
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: 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 the movie title from the 'movie' table
-- Using a subquery to find the mov_id from the 'movie_direction' table
-- Using another subquery to find the dir_id from the 'director' table
-- Specifying conditions to identify the director with the first name 'Woody' and last name 'Allen'
SELECT mov_title
FROM movie
WHERE mov_id=(
-- Subquery to find the mov_id based on the dir_id
SELECT mov_id
FROM movie_direction
WHERE dir_id=(
-- Subquery to find the dir_id based on the director's first name and last name
SELECT dir_id
FROM director
WHERE dir_fname='Woody' AND dir_lname='Allen'
)
);
Sample Output:
mov_title
----------------------------------------------------
Annie Hall
(1 row)
Code Explanation:
The said query in SQL which selects the movie titles from the 'movie' table that were directed by a specific director, Woody Allen.
The condition is based on subqueries:
The innermost subquery selects the "dir_id" from the 'director' table where the first name is "Woody" and the last name is "Allen".
The middle subquery selects the "mov_id" from the 'movie_direction' table where the "dir_id" matches the result of the inner subquery.
The outermost query selects the movie title from the 'movie' table where the "mov_id" matches the result of the middle subquery.
Alternative Solutions:
Using JOIN:
SELECT m.mov_title
FROM movie m
JOIN movie_direction md ON m.mov_id = md.mov_id
JOIN director d ON md.dir_id = d.dir_id
WHERE d.dir_fname = 'Woody' AND d.dir_lname = 'Allen';
Explanation:
This solution uses JOIN statements to connect the movie, movie_direction, and director tables based on their respective IDs. It then applies the condition to filter for movies directed by Woody Allen.
Using EXISTS:
SELECT mov_title
FROM movie m
WHERE EXISTS (
SELECT 1
FROM movie_direction md
JOIN director d ON md.dir_id = d.dir_id
WHERE md.mov_id = m.mov_id
AND d.dir_fname = 'Woody' AND d.dir_lname = 'Allen'
);
Explanation:
This solution uses the EXISTS clause to check if there exists a record in the subquery that connects the movie_direction and director tables and satisfies the condition for Woody Allen.
Using IN Operator:
SELECT mov_title
FROM movie
WHERE mov_id IN (
SELECT mov_id
FROM movie_direction
WHERE dir_id = (
SELECT dir_id
FROM director
WHERE dir_fname = 'Woody' AND dir_lname = 'Allen'
)
);
Explanation:
This solution uses the IN operator to compare the mov_id with a list of IDs retrieved from the subquery, which identifies the director with the name Woody Allen.
Using Scalar Subquery:
SELECT mov_title
FROM movie
WHERE mov_id = (
SELECT md.mov_id
FROM movie_direction md
JOIN director d ON md.dir_id = d.dir_id
WHERE d.dir_fname = 'Woody' AND d.dir_lname = 'Allen'
);
Explanation:
This solution utilizes a scalar subquery to directly retrieve the mov_id for movies directed by Woody Allen and then selects the corresponding mov_title from the main query.
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 where reviewer is unknown. Return movie title, year, release date, director first name, last name, actor first name, last name.
Next: From the following tables, write a SQL query to find those years, which produced at least one movie and that, received a rating of more than three stars. Sort the result-set in ascending order by movie year. Return movie year.
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