SQL exercises on movie Database: Find the cast list for the movie Chinatown

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

19. From the following tables, write a SQL query to find the cast list of the movie ‘Chinatown’. Return first name, last name.

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

 act_id | mov_id |              role
--------+--------+--------------------------------
    101 |    901 | John Scottie Ferguson
    102 |    902 | Miss Giddens
    103 |    903 | T.E. Lawrence
    104 |    904 | Michael
    105 |    905 | Antonio Salieri
    106 |    906 | Rick Deckard
    107 |    907 | Alice Harford
    108 |    908 | McManus
    110 |    910 | Eddie Adams
.....
    119 |    919 | Alfred Borden

View the table

Sample table: actor

 act_id |      act_fname       |      act_lname       | act_gender
--------+----------------------+----------------------+------------
    101 | James                | Stewart              | M
    102 | Deborah              | Kerr                 | F
    103 | Peter                | OToole               | M
    104 | Robert               | De Niro              | M
    105 | F. Murray            | Abraham              | M
    106 | Harrison             | Ford                 | M
    107 | Nicole               | Kidman               | F
    108 | Stephen              | Baldwin              | M
    109 | Jack                 | Nicholson            | M
.....
    124 | Ali                  | Astin                | F

View the table

Sample Solution:

-- Selecting actor first name (act_fname) and last name (act_lname)
FROM
-- Joining the movie_cast and actor tables using the act_id column
movie_cast c
JOIN actor a ON
c.act_id = a.act_id
-- Filtering the result to include only records where mov_id is equal to the subquery result
Where mov_id = (
  -- Subquery: Selecting mov_id from the movie table where mov_title is 'Chinatown'
  SELECT mov_id
  FROM movie
  Where mov_title = 'Chinatown'
);

Sample Output:

      act_fname       |      act_lname
----------------------+----------------------
 Jack                 | Nicholson
(1 row)

Code Explanation :

The said query in SQL that selects the first name and last name of actors who appeared in a specific movie, 'Chinatown'.
The query joins the movie_cast table with the actor table based on the act_id column.
The WHERE clause filters the results to include those actors who appeared in the movie 'Chinatown'. It happens by checking the mov_id column in the movie_cast table against the mov_id of the 'Chinatown' movie. The mov_id is obtained by running a subquery that selects the mov_id from the movie table where the mov_title is 'Chinatown'.

Alternative Solutions:

Using a JOIN and Subquery in ON Clause:


SELECT a.act_fname, a.act_lname
FROM movie_cast c
JOIN actor a ON c.act_id = a.act_id
JOIN movie m ON c.mov_id = m.mov_id AND m.mov_title = 'Chinatown';

Explanation:

This SQL query uses a JOIN to connect the movie_cast, actor, and movie tables. The condition for the JOIN includes both the regular condition and a condition involving a subquery in the ON clause, which filters for the movie with the title 'Chinatown'.

Using EXISTS Clause:


SELECT a.act_fname, a.act_lname
FROM movie_cast c
JOIN actor a ON c.act_id = a.act_id
WHERE EXISTS (
    SELECT 1
    FROM movie m
    WHERE m.mov_id = c.mov_id AND m.mov_title = 'Chinatown'
);

Explanation:

This SQL query uses the EXISTS clause with a subquery to check if there exists a movie with the title 'Chinatown' that matches the mov_id in the movie_cast table. If such a movie exists, the main query includes the corresponding actor.

Using Subquery with IN Clause:


SELECT a.act_fname, a.act_lname
FROM movie_cast c
JOIN actor a ON c.act_id = a.act_id
WHERE c.mov_id IN (SELECT mov_id FROM movie WHERE mov_title = 'Chinatown');

Explanation:

This SQL query uses a subquery with an IN clause to find the mov_id of the movie titled 'Chinatown'. It then joins the movie_cast and actor tables and filters the results for actors associated with any movie that matches the mov_id found in the subquery.

Go to:

PREV : From the following tables, write a SQL query to find those actors who have directed their movies. Return actor first name, last name, movie title and role.
NEXT : From the following tables, write a SQL query to find those movies where actor’s first name is 'Harrison' and last name is 'Ford'. Return movie title.

Practice Online