SQL exercises on movie Database: Find the name of movie and director who directed a movie that casted a role for 'Eyes Wide Shut'
3. From the following table, write a SQL query to find the director who directed a movie that featured a role in 'Eyes Wide Shut'. Return director first name, last name and movie title.
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 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: 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 Solution:
-- Selecting director first names, last names, and movie titles
-- Using the 'director' table
SELECT dir_fname, dir_lname, mov_title
FROM director
-- Performing natural join with 'movie_direction' to link directors with movies
NATURAL JOIN movie_direction
-- Further joining with 'movie' using natural join to get additional movie details
NATURAL JOIN movie
-- Completing the join with 'movie_cast' to associate roles with the movies
NATURAL JOIN movie_cast
-- Filtering for rows where the role is not NULL and the movie title is 'Eyes Wide Shut'
WHERE role IS NOT NULL AND mov_title = 'Eyes Wide Shut';
Sample Output:
dir_fname | dir_lname | mov_title
----------------------+----------------------+----------------------------------
Stanley | Kubrick | Eyes Wide Shut
(1 row)
Code Explanation:
The said query in SQL that retrieves the first name and last name of the director, as well as the title of the movie, and returns only the rows where the role is not null, and the movie title is "Eyes Wide Shut".
The NATURAL JOIN clauses connect the four tables together using their common column names. The director and movie_direction are joined on their shared dir_id, movie_direction and movie are joined on their shared mov_id, and movie and movie_cast are joined on their shared mov_id.
The WHERE clause filters the results to only include rows where the role column is not null that means at least one actor is associated with the movie and the mov_title column is "Eyes Wide Shut".
Alternative Solutions:
Using INNER JOIN:
SELECT d.dir_fname, d.dir_lname, m.mov_title
FROM director d
JOIN movie_direction md ON d.dir_id = md.dir_id
JOIN movie m ON md.mov_id = m.mov_id
JOIN movie_cast mc ON m.mov_id = mc.mov_id
WHERE mc.role IS NOT NULL
AND m.mov_title = 'Eyes Wide Shut';
Explanation:
This query uses INNER JOINs to combine the director, movie_direction, movie, and movie_cast tables based on their respective IDs. It then applies a WHERE clause to filter for movies with the title 'Eyes Wide Shut' and selects the director's first name, last name, and the movie title.
Using JOIN with ON Clause:
SELECT d.dir_fname, d.dir_lname, m.mov_title
FROM director d
JOIN movie_direction md ON d.dir_id = md.dir_id
JOIN movie m ON md.mov_id = m.mov_id
JOIN movie_cast mc ON m.mov_id = mc.mov_id
WHERE mc.role IS NOT NULL
AND m.mov_title = 'Eyes Wide Shut';
Explanation:
This query also uses JOINs with explicit ON clauses to combine the tables. It applies a WHERE clause to filter for movies with the title 'Eyes Wide Shut' and selects the director's first name, last name, and the movie title.
Using WHERE Clause with Table Aliases:
SELECT d.dir_fname, d.dir_lname, m.mov_title
FROM director d, movie_direction md, movie m, movie_cast mc
WHERE d.dir_id = md.dir_id
AND md.mov_id = m.mov_id
AND m.mov_id = mc.mov_id
AND mc.role IS NOT NULL
AND m.mov_title = 'Eyes Wide Shut';
Explanation:
This query uses the older comma-separated syntax for joining tables and specifies the join conditions in the WHERE clause. It then applies a WHERE clause to filter for movies with the title 'Eyes Wide Shut' and selects the director's first name, last name, and the movie title.
Relational Algebra Expression:
Relational Algebra Tree:
Go to:
PREV : Write a query in SQL to list the first and last names of all the actors who were cast in the movie 'Annie Hall', and the roles they played in that production.
NEXT : From the following tables, write a SQL query to find who directed a movie that casted a role as ‘Sean Maguire’. Return director first name, last name and movie title.
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.