w3resource

SQL exercises on movie Database: Find the movie in which the actor appeared whose first and last name are 'Harrison' and 'Ford'

SQL movie Database: Join Exercise-20 with Solution

20. From the following tables, write a SQL query to find those movies where actor’s first name is 'Harrison' and last name is 'Ford'. Return movie title.

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 Solution:

-- Selecting the movie title (mov_title) from the movie table
SELECT m.mov_title
-- Joining the movie and movie_cast tables using the mov_id column
FROM movie m
JOIN movie_cast c 
ON  m.mov_id = c.mov_id
-- Filtering the result to include only records where c.act_id is in the subquery result
WHERE c.act_id IN ( 
  -- Subquery: Selecting act_id from the actor table where act_fname is 'Harrison' and act_lname is 'Ford'
  Select act_id 
  FROM actor 
  WHERE act_fname='Harrison' 
  AND act_lname='Ford'
);

Sample Output:

                     mov_title
----------------------------------------------------
 Blade Runner
(1 row)

Code Explanation :

The said query in SQL that selects the title of all movies that feature Harrison Ford as a cast member.
The JOIN clause joins the movie table with the movie_cast table based on the mov_id column .
The WHERE clause filters the results to include only those rows where the act_id in the movie_cast table is included in a subquery that selects the act_id for Harrison Ford from the actor table. The subquery is executed first to retrieve the act_id for Harrison Ford.

Alternative Solutions:

Using EXISTS Clause:


SELECT m.mov_title
FROM movie m
JOIN movie_cast c ON m.mov_id = c.mov_id
WHERE EXISTS (
    SELECT 1
    FROM actor a
    WHERE a.act_id = c.act_id
    AND a.act_fname = 'Harrison'
    AND a.act_lname = 'Ford'
);

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 title.

Using a JOIN and Subquery in ON Clause:


SELECT m.mov_title
FROM movie m
JOIN movie_cast c ON m.mov_id = c.mov_id
JOIN actor a ON c.act_id = a.act_id AND a.act_fname = 'Harrison' AND a.act_lname = 'Ford';

Explanation:

This SQL query uses a JOIN to connect the movie, movie_cast, and actor tables. The condition for the JOIN includes both the regular condition and a condition involving a subquery in the ON clause, which filters for the actor with the specified first and last name.

Practice Online


Movie database model

Query Visualization:

Duration:

Query visualization of Find the movie in which the actor appeared whose first and last name are 'Harrison' and 'Ford' - Duration

Rows:

Query visualization of Find the movie in which the actor appeared whose first and last name are 'Harrison' and 'Ford' - Rows

Cost:

Query visualization of Find the movie in which the actor appeared whose first and last name are 'Harrison' and 'Ford' - Cost

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

Previous: From the following tables, write a SQL query to find the cast list of the movie ‘Chinatown’. Return first name, last name.
Next: From the following tables, write a SQL query to find the highest-rated movies. Return movie title, movie year, review stars and releasing country.

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