SQL exercises on movie Database: Find the titles of all movies directed by the director whose first and last name are Woody Allen

Last update on January 12 2026 12:49:17 (UTC/GMT +8 hours)

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
......
    925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK

View the table

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
....
    223 | Peter                | Jackson

View the table

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
...
    221 |    923

View the table

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.

Go to:

PREV : 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.

Practice Online