SQL exercises on movie Database: Find the titles of all movies directed by James Cameron

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

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