w3resource

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

SQL movie Database: Join Exercise-19 with Solution

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
    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: 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
    111 |    911 | Alvy Singer
    112 |    912 | San
    113 |    913 | Andy Dufresne
    114 |    914 | Lester Burnham
    115 |    915 | Rose DeWitt Bukater
    116 |    916 | Sean Maguire
    117 |    917 | Ed
    118 |    918 | Renton
    120 |    920 | Elizabeth Darko
    121 |    921 | Older Jamal
    122 |    922 | Ripley
    114 |    923 | Bobby Darin
    109 |    909 | J.J. Gittes
    119 |    919 | Alfred Borden
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
    110 | Mark                 | Wahlberg             | M
    111 | Woody                | Allen                | M
    112 | Claire               | Danes                | F
    113 | Tim                  | Robbins              | M
    114 | Kevin                | Spacey               | M
    115 | Kate                 | Winslet              | F
    116 | Robin                | Williams             | M
    117 | Jon                  | Voight               | M
    118 | Ewan                 | McGregor             | M
    119 | Christian            | Bale                 | M
    120 | Maggie               | Gyllenhaal           | F
    121 | Dev                  | Patel                | M
    122 | Sigourney            | Weaver               | F
    123 | David                | Aston                | M
    124 | Ali                  | Astin                | F

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.

Practice Online


Movie database model

Query Visualization:

Duration:

Query visualization of Find the cast list for the movie Chinatown - Duration

Rows:

Query visualization of Find the cast list for the movie Chinatown - Rows

Cost:

Query visualization of Find the cast list for the movie Chinatown - 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 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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.