SQL exercises on movie Database: Find the first and last name of an actor with their role in the movie which was also directed by themselves

SQL movie Database: Join Exercise-18 with Solution

18. From the following tables, write a SQL query to find those actors who have directed their movies. Return actor first name, last name, movie title and role.

Sample table: movie

Sample table: movie_cast

Sample table: actor

Sample table: director

Sample table: movie_direction

Sample Solution:

SELECT act_fname, act_lname, mov_title, role
FROM actor
JOIN movie_cast 
  ON actor.act_id=movie_cast.act_id
JOIN movie_direction 
  ON movie_cast.mov_id=movie_direction.mov_id
JOIN director 
  ON movie_direction.dir_id=director.dir_id
JOIN movie 
  ON movie.mov_id=movie_direction.mov_id
WHERE act_fname=dir_fname 
  AND act_lname=dir_lname;

Sample Output:

      act_fname       |      act_lname       |                     mov_title                      |              role
----------------------+----------------------+----------------------------------------------------+--------------------------------
 Woody                | Allen                | Annie Hall                                         | Alvy Singer
 Kevin                | Spacey               | Beyond the Sea                                     | Bobby Darin
(2 rows)

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: 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.
Next: From the following tables, write a SQL query to find the cast list of the movie ‘Chinatown’. Return first name, last name.