w3resource

SQL exercises on movie Database: Find the name of movie and director who directed a movie that casted a role as Sean Maguire

SQL movie Database: Join Exercise-4 with Solution

4. From the following tables, write a SQL query to find the director of a movie that cast a role as Sean Maguire. 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
-- Joining with 'movie_direction' using explicit ON clause to link directors with movies
JOIN movie_direction ON director.dir_id = movie_direction.dir_id
-- Further joining with 'movie' using explicit ON clause to get additional movie details
JOIN movie ON movie_direction.mov_id = movie.mov_id
-- Completing the join with 'movie_cast' to associate roles with the movies
JOIN movie_cast ON movie_cast.mov_id = movie.mov_id
-- Filtering for rows where the role is 'Sean Maguire'
WHERE role = 'Sean Maguire';

Sample Output:

      dir_fname       |      dir_lname       |                     mov_title
----------------------+----------------------+----------------------------------------------------
 Gus                  | Van Sant             | Good Will Hunting
(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 "Sean Maguire".
The JOIN clauses connect the four tables together using their primary and foreign keys. This links the director and movie_direction tables by their director IDs, the movie_direction and movie tables by their movie IDs, and the movie_cast and movie tables by their movie IDs.
The WHERE clause filters the results to only include rows where the role column in movie_cast is "Sean Maguire".

Alternative Solutions:

Using WHERE clause explicitly:


SELECT dir_fname, dir_lname, mov_title
FROM  director, movie_direction, movie, movie_cast
WHERE director.dir_id=movie_direction.dir_id
AND movie_direction.mov_id=movie.mov_id
AND movie.mov_id=movie_cast.mov_id
AND movie_cast.role='Sean Maguire';

Explanation:

This SQL query uses a series of inner joins between the director, movie_direction, movie, and movie_cast tables using their respective IDs. The conditions ensure that the relationships between these tables are satisfied. Finally, the query filters the results by specifying that the role must be 'Sean Maguire'.

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 = 'Sean Maguire';

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 roles where the character is 'Sean Maguire' 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 as Sean Maguire.

Relational Algebra Tree:

Relational Algebra Tree: Find the name of movie and director who directed a movie that casted a role as Sean Maguire.

Practice Online


Movie database model

Query Visualization for Sample Solution:

Duration:

Query visualization of Find the name of movie and director who directed a movie that casted a role as Sean Maguire - Duration

Rows:

Query visualization of Find the name of movie and director who directed a movie that casted a role as Sean Maguire - Rows

Cost:

Query visualization of Find the name of movie and director who directed a movie that casted a role as Sean Maguire - Cost

Query Visualization for alternate Sample Solution:

Duration:

Query visualization of Find the name of movie and director who directed a movie that casted a role as Sean Maguire - Duration

Rows:

Query visualization of Find the name of movie and director who directed a movie that casted a role as Sean Maguire - Rows

Cost:

Query visualization of Find the name of movie and director who directed a movie that casted a role as Sean Maguire - Cost

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

Previous: Write a query in SQL to find the name of movie and director (first and last names) who directed a movie that casted a role for 'Eyes Wide Shut'.
Next: From the following table, write a SQL query to find the actors who have not acted in any movie between1990 and 2000 (Begin and end values are included.). Return actor first name, last name, movie title and release year.

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