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

Sample table: director

Sample table: movie_direction

Sample Solution:

SELECT mov_title 
FROM movie 
WHERE mov_id=(
SELECT mov_id 
FROM movie_direction 
WHERE dir_id=(
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.

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.