w3resource

SQL exercises on movie Database: Find the name of movie and director who directed a movie that casted a role for 'Eyes Wide Shut'

SQL movie Database: Join Exercise-3 with Solution

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 Expression: Find the name of movie and director who directed a movie that casted a role for 'Eyes Wide Shut'.

Relational Algebra Tree:

Relational Algebra Tree: Find the name of movie and director who directed a movie that casted a role for 'Eyes Wide Shut'.

Practice Online


Movie database model

Query Visualization:

Duration:

Query visualization of Find the name of movie and director who directed a movie that casted a role for 'Eyes Wide Shut' - Duration

Rows:

Query visualization of Find the name of movie and director who directed a movie that casted a role for 'Eyes Wide Shut' - Rows

Cost:

Query visualization of Find the name of movie and director who directed a movie that casted a role for 'Eyes Wide Shut' - Cost

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

Previous: 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.

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-14.php