SQL exercises on movie Database: Find the name of those movies where one or more actors acted in two or more movies
SQL movie Database: Subquery Exercise-16 with Solution
16. Write a query in SQL to find the movies in which one or more actors appeared in more than one film.
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 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_cast' table
WHERE mov_id IN (
-- Further filtered by act_id from the result of another subquery
SELECT mov_id
FROM movie_cast
-- The innermost subquery selects act_id from the 'actor' table
-- Filtering actors whose act_id is in the result of another subquery
WHERE act_id IN (
SELECT act_id
FROM movie_cast
-- Grouping by act_id and filtering those with count greater than 1
GROUP BY act_id
HAVING COUNT(act_id) > 1
)
);
Sample Output:
mov_title
----------------------------------------------------
Beyond the Sea
American Beauty
(2 rows)
Code Explanation:
The above query in SQL which retrieves the titles of all movies in which an actor has acted more than one role in the same movie.
The subquery uses to find all actor IDs for actors who have acted in more than one role. The main query then uses another subquery to find all movies in which any of those actors have acted based on the actor IDs found in the subquery. Finally, the main query retrieves the titles of all those movies from the movie table.
Alternative Solutions:
Using INNER JOIN:
SELECT m.mov_title
FROM movie m
JOIN movie_cast mc ON m.mov_id = mc.mov_id
JOIN actor a ON a.act_id = mc.act_id
WHERE a.act_id IN (
SELECT act_id
FROM movie_cast
GROUP BY act_id
HAVING COUNT(act_id) > 1
);
Explanation:
This query uses INNER JOINs to combine the movie, movie_cast, and actor tables based on their respective IDs. It then applies a WHERE clause to filter for movies with actors who have appeared in more than one movie.
Using JOINs with ON Clause:
SELECT m.mov_title
FROM movie m
JOIN movie_cast mc ON m.mov_id = mc.mov_id
JOIN actor a ON a.act_id = mc.act_id
WHERE a.act_id IN (
SELECT act_id
FROM movie_cast
GROUP BY act_id
HAVING COUNT(act_id) > 1
);
Explanation:
This query explicitly specifies the join conditions using the ON clause. It then applies a WHERE clause to filter for movies with actors who have appeared in more than one movie.
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 the movies directed by 'James Cameron'. Return movie title.
Next: SQL JOINS Exercises on movie Database
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics