w3resource

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 Expression: Find the actors with all information who played a role in the movie 'Annie Hall'.

Relational Algebra Tree:

Relational Algebra Tree: Find the actors with all information who played a role in the movie 'Annie Hall'.

Practice Online


Movie database model

Query Visualization:

Duration:

Query visualization of Find the actors with all information who played a role in the movie 'Annie Hall' - Duration

Rows:

Query visualization of Find the actors with all information who played a role in the movie 'Annie Hall' - Rows

Cost:

Query visualization of Find the actors with all information who played a role in the movie 'Annie Hall' - 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.



Follow us on Facebook and Twitter for latest update.