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
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
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 dir_fname, dir_lname, mov_title, act_fname, act_lname, 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 'Claire' and act_lname is 'Danes'
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)
Code Explanation :
The said query in SQL which selects the first name and last name of a director, the title of a movie, the first name and last name of an actor, and the role they played in the movie. It only includes movies where Claire Danes appeared.
A join is performed between the actor table and the movie_cast table using the act_id column, then joins the movie_direction table using the mov_id column from the movie_cast table, then joins the director table using the dir_id column from the movie_direction table, and finally joins the movie table using the mov_id column from the movie_direction table.
WHERE clauses are used to filter the search results to include only movies where Claire Danes appeared in one or more of them. It does this by checking that the actor's first name is "Claire" and the last name is "Danes".
Alternative Solutions:
Using EXISTS Clause:
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 EXISTS (
SELECT 1
FROM actor a
WHERE a.act_id = actor.act_id
AND a.act_fname = 'Claire'
AND a.act_lname = 'Danes'
);
Explanation:
This SQL query uses the EXISTS clause with a subquery to check if there exists an actor with the specified first and last name. If such an actor exists, the main query includes the corresponding movie and director information.
Using a Self-Join:
SELECT d1.dir_fname, d1.dir_lname, m1.mov_title, a1.act_fname, a1.act_lname, mc1.role
FROM actor a1
JOIN movie_cast mc1 ON a1.act_id = mc1.act_id
JOIN movie_direction md1 ON mc1.mov_id = md1.mov_id
JOIN director d1 ON md1.dir_id = d1.dir_id
JOIN movie m1 ON md1.mov_id = m1.mov_id
JOIN actor a2 ON a1.act_id = a2.act_id
WHERE a2.act_fname = 'Claire' AND a2.act_lname = 'Danes';
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 filter for actors with specific names (Claire Danes in this case) using the second instance (a2) in the WHERE clause.
Using a Subquery with IN Clause:
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, act_lname) IN (('Claire', 'Danes'));
Explanation:
This SQL query uses a subquery with an IN clause to filter the results for actors with the specified first and last name combination. It checks if the combination matches 'Claire Danes'.
Relational Algebra Expression:
Relational Algebra Tree:
Go to:
PREV : 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.
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.