w3resource
SQL exercises

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 themselve

SQL movie Database: Join Exercise-18 with Solution

18. Write a query in SQL to find the first and last name of an actor with their role in the movie which was also directed by themselve.

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)

Practice Online


Movie database model

Query Visualization:

Duration:

Query visualization of Find the first and last name of an actor with their role in the movie which was also directed by themselve - Duration

Rows:

Query visualization of Find the first and last name of an actor with their role in the movie which was also directed by themselve - Rows

Cost:

Query visualization of Find the first and last name of an actor with their role in the movie which was also directed by themselve - 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: Write a query in SQL to find the cast list for the movie Chinatown.

What is the difficulty level of this exercise?



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