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
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 Tree:
Go to:
PREV : 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.
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.