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. Write a query in SQL to find the movie title, actor first and last name, and the role for those movies where one or more actors acted in two or more movies.

Sample table: movie

Sample table: movie_cast

Sample table: actor

Sample Solution:

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 actor.act_id IN (
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)

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.