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
Query Visualization:
Duration:
Rows:
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.
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://w3resource.com/sql-exercises/movie-database-exercise/sql-exercise-movie-database-46.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics