w3resource
SQL exercises

SQL exercises on movie Database: Find the name of movie and director who directed a movie that casted a role as Sean Maguire

SQL movie Database: Join Exercise-4 with Solution

4. Write a query in SQL to find the name of movie and director (first and last names) who directed a movie that casted a role as Sean Maguire.

Sample table: director


Sample table: movie_direction


Sample table: movie_cast


Sample table: movie


Sample Solution:

SELECT dir_fname, dir_lname, mov_title
FROM  director 
JOIN movie_direction 
  ON director.dir_id=movie_direction.dir_id
JOIN movie 
  ON movie_direction.mov_id=movie.mov_id
JOIN movie_cast 
  ON movie_cast.mov_id=movie.mov_id
  WHERE role='Sean Maguire';

OR

SELECT dir_fname, dir_lname, mov_title
FROM  director, movie_direction, movie, movie_cast
WHERE director.dir_id=movie_direction.dir_id
AND movie_direction.mov_id=movie.mov_id
AND movie.mov_id=movie_cast.mov_id
AND movie_cast.role='Sean Maguire';

Sample Output:

      dir_fname       |      dir_lname       |                     mov_title
----------------------+----------------------+----------------------------------------------------
 Gus                  | Van Sant             | Good Will Hunting
(1 row)

Practice Online


Movie database model

Query Visualization for Sample Solution:

Duration:

Query visualization of Find the name of movie and director who directed a movie that casted a role as Sean Maguire - Duration

Rows:

Query visualization of Find the name of movie and director who directed a movie that casted a role as Sean Maguire - Rows

Cost:

Query visualization of Find the name of movie and director who directed a movie that casted a role as Sean Maguire - Cost

Query Visualization for alternate Sample Solution:

Duration:

Query visualization of Find the name of movie and director who directed a movie that casted a role as Sean Maguire - Duration

Rows:

Query visualization of Find the name of movie and director who directed a movie that casted a role as Sean Maguire - Rows

Cost:

Query visualization of Find the name of movie and director who directed a movie that casted a role as Sean Maguire - Cost

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

Previous: Write a query in SQL to find the name of movie and director (first and last names) who directed a movie that casted a role for 'Eyes Wide Shut'.
Next: Write a query in SQL to list all the actors who acted in a movie before 1990 and also in a movie after 2000.

What is the difficulty level of this exercise?



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