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.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/sql-exercises/movie-database-exercise/sql-exercise-movie-database-26.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics