SQL exercises on movie Database: Find the 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

SQL movie Database: Join Exercise-17 with Solution

17. From the following table, 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.

Sample table: movie

Sample table: movie_cast

Sample table: actor

Sample table: director

Sample table: movie_direction

Sample Solution:

SELECT dir_fname, dir_lname, mov_title, act_fname, act_lname, 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='Claire' 
  AND act_lname='Danes';

Sample Output:

      dir_fname       |      dir_lname       |                     mov_title                      |      act_fname       |      act_lname       |              role
----------------------+----------------------+----------------------------------------------------+----------------------+----------------------+--------------------------------
 Hayao                | Miyazaki             | Princess Mononoke                                  | Claire               | Danes                | San
(1 row)

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 movie title, actor first and last name, and the role for those movies where one or more actors acted in two or more movies.
Next: 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.