SQL exercises on movie Database: Find the movie title, actor name, and the role for those movies where one or more actors acted in two or more movies
SQL movie Database: Join Exercise-16 with Solution
16. From the following table, write a SQL query to find movies in which one or more actors have acted in more than one film. Return movie title, actor first and last name, and the role.
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 specific columns from the movie, movie_cast, and actor tables
SELECT mov_title, act_fname, act_lname, role
-- Joining the movie and movie_cast tables using the mov_id column
FROM movie
JOIN movie_cast
ON movie_cast.mov_id = movie.mov_id
-- Joining the result with the actor table using the act_id column
JOIN actor
ON movie_cast.act_id = actor.act_id
-- Filtering the result to include only records where actor.act_id is in the subquery result
WHERE actor.act_id IN (
-- Subquery: Selecting act_id from movie_cast and counting occurrences, then filtering by count >= 2
SELECT act_id
FROM movie_cast
GROUP BY act_id HAVING COUNT(*) >= 2
);
Sample Output:
mov_title | act_fname | act_lname | role
----------------------------------------------------+----------------------+----------------------+--------------------------------
American Beauty | Kevin | Spacey | Lester Burnham
Beyond the Sea | Kevin | Spacey | Bobby Darin
(2 rows)
Code Explanation :
The said query in SQL which selects the title of a movie, the first name and last name of an actor, and the role they played in the movie. It only includes actors who have appeared in at least two movies.
The JOIN clause joins the movie table with the movie_cast table using the mov_id column, and then joins the actor table using the act_id column from the movie_cast table.
The WHERE clause filters the results to only include actors who have appeared in at least two movies. Using a subquery, it finds all the act_ids that appear in the movie_cast table at least twice, and then only includes the results in which the actor's act_id appears in the resulting list if that actor's act_id is in that list.
Alternative Solutions:
Using EXISTS Clause:
SELECT mov_title, act_fname, act_lname, role
FROM movie
JOIN movie_cast ON movie_cast.mov_id = movie.mov_id
JOIN actor ON movie_cast.act_id = actor.act_id
WHERE EXISTS (
SELECT 1
FROM movie_cast mc
WHERE mc.act_id = actor.act_id
GROUP BY mc.act_id
HAVING COUNT(*) >= 2
);
Explanation:
This SQL query uses the EXISTS clause with a subquery to check if there exists another row in the movie_cast table with the same act_id. If such rows exist, the main query includes the corresponding movie and actor.
Using a Self-Join with GROUP BY:
SELECT mov_title, act_fname, act_lname, role
FROM movie
JOIN movie_cast ON movie_cast.mov_id = movie.mov_id
JOIN actor ON movie_cast.act_id = actor.act_id
JOIN (
SELECT act_id
FROM movie_cast
GROUP BY act_id
HAVING COUNT(*) >= 2
) AS multiple_casts ON actor.act_id = multiple_casts.act_id;
Explanation:
This SQL query first creates a subquery that identifies act_id values with multiple casts. It then joins this subquery with the main query, allowing the selection of movies and actors with multiple appearances.
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 table, write a SQL query to find those movies, which have received ratings. Return movie title, director first name, director last name and review stars.
Next: From the following tables, write a SQL query to find the actor whose first name is 'Claire' and last name is 'Danes'. Return director first name, last name, movie title, actor first name and last name, role.
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