SQL exercises on movie Database: Find the cast list for the movie Chinatown
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: moviemov_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 | UKSample 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 BordenSample 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.
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

Query Visualization:
Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.