SQL exercises on movie Database: Find the actors with all information who played a role in the movie 'Annie Hall'
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
.....
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
.....
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
......
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:
Go to:
PREV : 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.
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.