w3resource
SQL exercises

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


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: Write a query in SQL to find the director's first and last name together with the title of the movie(s) they directed and received the rating.
Next: Write a query in SQL to find the first and last name of a director and the movie he or she directed, and the actress appeared which first name was Claire and last name was Danes along with her role in that movie.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming