SQL exercises on movie Database: Find the actors with all information who played a role in the movie 'Annie Hall'
SQL movie Database: Subquery Exercise-1 with Solution
1. From the following tables, write a SQL query to find the actors who played a role in the movie 'Annie Hall'. Return all the fields of actor table.
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 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 all columns from the 'actor' table
-- Filtering results where 'act_id' is in the subquery result
SELECT *
FROM actor
WHERE act_id IN(
-- Selecting 'act_id' from 'movie_cast' where 'mov_id' is in the subquery result
SELECT act_id
FROM movie_cast
WHERE mov_id IN (
-- Selecting 'mov_id' from 'movie' where 'mov_title' is 'Annie Hall'
SELECT mov_id
FROM movie
WHERE mov_title='Annie Hall'
)
);
Sample Output:
act_id | act_fname | act_lname | act_gender
--------+----------------------+----------------------+------------
111 | Woody | Allen | M
(1 row)
Code Explanation:
The query in SQL that retrieves all information on actors who appeared in the movie 'Annie Hall'. The query uses a subquery to find the mov_id of the movie 'Annie Hall', and then searches the movie_cast table to find all actors who appeared in that movie by matching the mov_id to the inner subquery.
The outer query returns all information on the actors found in the previous step, by matching their act_id values to those in the actor table.
Alternative Solutions:
Alternative 1:
SELECT a.*
FROM actor a
WHERE EXISTS (
SELECT 1
FROM movie_cast mc
JOIN movie m ON mc.mov_id = m.mov_id
WHERE mc.act_id = a.act_id
AND m.mov_title = 'Annie Hall'
);
Explanation:
This query uses an EXISTS subquery to check if there exists a record in 'movie_cast' and 'movie' tables for the movie "Annie Hall" and the corresponding actor.
Alternative 2:
SELECT a.*
FROM actor a
JOIN movie_cast mc ON a.act_id = mc.act_id
JOIN movie m ON mc.mov_id = m.mov_id
WHERE m.mov_title = 'Annie Hall';
Explanation:
This query performs inner joins between the 'actor', 'movie_cast', and 'movie' tables based on their respective IDs. It filters the result to include only actors from the movie "Annie Hall".
Alternative 3:
SELECT a.*
FROM actor a
JOIN movie_cast mc ON a.act_id = mc.act_id
WHERE mc.mov_id IN (
SELECT mov_id
FROM movie
WHERE mov_title = 'Annie Hall'
);
Explanation:
This query uses a subquery to get the mov_id for the movie "Annie Hall" and then joins it with the 'movie_cast' table to get the corresponding actors.
Alternative 4:
SELECT a.*
FROM actor a, movie_cast mc, movie m
WHERE a.act_id = mc.act_id
AND mc.mov_id = m.mov_id
AND m.mov_title = 'Annie Hall';
Explanation:
This query performs a Cartesian join and applies the necessary conditions in the WHERE clause to get the result.
Relational Algebra Expression:
Relational Algebra Tree:
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: SQL SUBQUERIES Exercises on movie Database
Next: From the following tables, write a SQL query to find the director who directed a movie that casted a role for 'Eyes Wide Shut'. Return director first name, last name.
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-12.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics