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

Sample table: movie_cast

Sample table: actor

Sample Solution:

SELECT mov_title 
FROM movie 
WHERE mov_id IN (
SELECT mov_id 
FROM movie_cast 
WHERE act_id IN (
SELECT act_id 
FROM actor 
WHERE act_id IN (
SELECT act_id 
FROM movie_cast 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.

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