w3resource
SQL exercises

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. 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.

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)

Practice Online


Movie database model

Query Visualization:

Duration:

Query visualization of 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 - Duration

Rows:

Query visualization of 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 - Rows

Cost:

Query visualization of 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 - 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: 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.

What is the difficulty level of this exercise?