w3resource

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


Movie database model

Query Visualization:

Duration:

Query visualization of Find the movie title, actor name, and the role for those movies where one or more actors acted in two or more movies - Duration

Rows:

Query visualization of Find the movie title, actor name, and the role for those movies where one or more actors acted in two or more movies - Rows

Cost:

Query visualization of Find the movie title, actor name, and the role for those movies where one or more actors acted in two or more movies - 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.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

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-42.php