w3resource

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 table, write a SQL query to find for actors whose films have been directed by them. Return actor first name, last name, movie title and role.

Sample table: movie
 mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
    901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
    902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
    903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
    904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
    905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
    906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
    907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
    908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
    909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
    910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
    911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
    912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
    913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
    914 | American Beauty                                    |     1999 |      122 | English         |            | UK
    915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
    916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
    917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
    918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
    919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
    920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
    921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
    922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
    923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
    924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
    926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
    927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
    928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
    925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
Sample table: movie_cast
 act_id | mov_id |              role
--------+--------+--------------------------------
    101 |    901 | John Scottie Ferguson
    102 |    902 | Miss Giddens
    103 |    903 | T.E. Lawrence
    104 |    904 | Michael
    105 |    905 | Antonio Salieri
    106 |    906 | Rick Deckard
    107 |    907 | Alice Harford
    108 |    908 | McManus
    110 |    910 | Eddie Adams
    111 |    911 | Alvy Singer
    112 |    912 | San
    113 |    913 | Andy Dufresne
    114 |    914 | Lester Burnham
    115 |    915 | Rose DeWitt Bukater
    116 |    916 | Sean Maguire
    117 |    917 | Ed
    118 |    918 | Renton
    120 |    920 | Elizabeth Darko
    121 |    921 | Older Jamal
    122 |    922 | Ripley
    114 |    923 | Bobby Darin
    109 |    909 | J.J. Gittes
    119 |    919 | Alfred Borden
Sample table: actor
 act_id |      act_fname       |      act_lname       | act_gender
--------+----------------------+----------------------+------------
    101 | James                | Stewart              | M
    102 | Deborah              | Kerr                 | F
    103 | Peter                | OToole               | M
    104 | Robert               | De Niro              | M
    105 | F. Murray            | Abraham              | M
    106 | Harrison             | Ford                 | M
    107 | Nicole               | Kidman               | F
    108 | Stephen              | Baldwin              | M
    109 | Jack                 | Nicholson            | M
    110 | Mark                 | Wahlberg             | M
    111 | Woody                | Allen                | M
    112 | Claire               | Danes                | F
    113 | Tim                  | Robbins              | M
    114 | Kevin                | Spacey               | M
    115 | Kate                 | Winslet              | F
    116 | Robin                | Williams             | M
    117 | Jon                  | Voight               | M
    118 | Ewan                 | McGregor             | M
    119 | Christian            | Bale                 | M
    120 | Maggie               | Gyllenhaal           | F
    121 | Dev                  | Patel                | M
    122 | Sigourney            | Weaver               | F
    123 | David                | Aston                | M
    124 | Ali                  | Astin                | F
Sample table: director
 dir_id |      dir_fname       |      dir_lname
--------+----------------------+----------------------
    201 | Alfred               | Hitchcock
    202 | Jack                 | Clayton
    203 | David                | Lean
    204 | Michael              | Cimino
    205 | Milos                | Forman
    206 | Ridley               | Scott
    207 | Stanley              | Kubrick
    208 | Bryan                | Singer
    209 | Roman                | Polanski
    210 | Paul                 | Thomas Anderson
    211 | Woody                | Allen
    212 | Hayao                | Miyazaki
    213 | Frank                | Darabont
    214 | Sam                  | Mendes
    215 | James                | Cameron
    216 | Gus                  | Van Sant
    217 | John                 | Boorman
    218 | Danny                | Boyle
    219 | Christopher          | Nolan
    220 | Richard              | Kelly
    221 | Kevin                | Spacey
    222 | Andrei               | Tarkovsky
    223 | Peter                | Jackson
Sample table: movie_direction
 dir_id | mov_id
--------+--------
    201 |    901
    202 |    902
    203 |    903
    204 |    904
    205 |    905
    206 |    906
    207 |    907
    208 |    908
    209 |    909
    210 |    910
    211 |    911
    212 |    912
    213 |    913
    214 |    914
    215 |    915
    216 |    916
    217 |    917
    218 |    918
    219 |    919
    220 |    920
    218 |    921
    215 |    922
    221 |    923

Sample Solution:

-- Selecting specific columns from the actor, movie_cast, movie_direction, director, and movie tables
SELECT act_fname, act_lname, mov_title, role
-- Joining the actor and movie_cast tables using the act_id column
FROM actor	
JOIN movie_cast 
  ON actor.act_id = movie_cast.act_id
-- Joining the result with the movie_direction table using the mov_id column
JOIN movie_direction 
  ON movie_cast.mov_id = movie_direction.mov_id
-- Joining the result with the director table using the dir_id column
JOIN director 	
  ON movie_direction.dir_id = director.dir_id
-- Joining the result with the movie table using the mov_id column
JOIN movie 
  ON movie.mov_id = movie_direction.mov_id
-- Filtering the result to include only records where act_fname is equal to dir_fname and act_lname is equal to dir_lname
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)

Code Explanation :

The above query in SQL that selects the first name and last name of an actor, the title of a movie, and the role the actor played in the movie. It only includes movies where the actor also directed the movie.
The query joins the actor table with the movie_cast table based on the act_id column, the movie_direction table with the movie_cast table based on the mov_id column, the director table with the movie_direction table using the dir_id column, and finally the movie table with the movie_direction table based on the mov_id column.
The WHERE clause filters the results to only include movies where the actor also directed the movie. It does this by checking that the actor's first name matches the director's first name, and the actor's last name matches the director's last name.

Alternative Solutions:

Using a Self-Join:


SELECT a1.act_fname, a1.act_lname, m.mov_title, mc.role
FROM actor a1
JOIN movie_cast mc ON a1.act_id = mc.act_id
JOIN movie_direction md ON mc.mov_id = md.mov_id
JOIN director d ON md.dir_id = d.dir_id
JOIN movie m ON m.mov_id = md.mov_id
JOIN actor a2 ON d.dir_fname = a2.act_fname AND d.dir_lname = a2.act_lname;

Explanation:

This SQL query involves a self-join on the actor table. It joins the actor table twice (as a1 and a2), allowing us to match actors with directors based on their first and last names.

Using a Self-Join:


SELECT a.act_fname, a.act_lname, m.mov_title, mc.role
FROM actor a
JOIN movie_cast mc ON a.act_id = mc.act_id
JOIN movie_direction md ON mc.mov_id = md.mov_id
JOIN director d ON md.dir_id = d.dir_id
JOIN movie m ON m.mov_id = md.mov_id
WHERE (a.act_fname, a.act_lname) IN (SELECT d.dir_fname, d.dir_lname FROM director d);

Explanation:

This SQL query uses a subquery with an IN clause to check if the combination of an actor's first and last name matches any director's first and last name. This way, it filters the results to only include matches.

Relational Algebra Expression:

Relational Algebra Expression: Find the first and last name of an actor with their role in the movie which was also directed by themselve.

Relational Algebra Tree:

Relational Algebra Tree: Find the first and last name of an actor with their role in the movie which was also directed by themselve.

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

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql-exercises/movie-database-exercise/sql-exercise-movie-database-44.php