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
Query Visualization:
Duration:
Rows:
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.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/sql-exercises/movie-database-exercise/sql-exercise-movie-database-45.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics