w3resource

SQL exercises on movie Database: Find the first and last names of all the actors who were cast in the movie 'Annie Hall', and the roles they played in that production

SQL movie Database: Join Exercise-2 with Solution

2. From the following table, write a SQL query to find out who was cast in the movie 'Annie Hall'. Return actor first name, last name and role.

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

      act_fname       |      act_lname       |              role
----------------------+----------------------+--------------------------------
 Woody                | Allen                | Alvy Singer
(1 row)

Sample Solution:

-- Selecting actor first names, last names, and roles
-- Using the 'actor' table
SELECT act_fname, act_lname, role
FROM actor
-- Joining with the 'movie_cast' table on act_id
JOIN movie_cast ON actor.act_id = movie_cast.act_id
-- Joining with the 'movie' table on mov_id and filtering for the movie 'Annie Hall'
JOIN movie ON movie_cast.mov_id = movie.mov_id AND movie.mov_title = 'Annie Hall';

Sample Output:

      act_fname       |      act_lname       |              role
----------------------+----------------------+--------------------------------
 Woody                | Allen                | Alvy Singer
(1 row)

Code Explanation:

The said query in SQL that retrieves the first name, last name, and role of actors who appear in the movie "Annie Hall".
The JOIN clauses connect the three tables together using their primary and foreign keys. This links the actor and movie_cast tables by their actor IDs, and the movie_cast and movie tables by their movie IDs.
The WHERE clause filters the results to only include actors who appear in the movie "Annie Hall".

Alternative Solutions:

Using INNER JOIN:


SELECT actor.act_fname, actor.act_lname, movie_cast.role
FROM actor
INNER JOIN movie_cast ON actor.act_id = movie_cast.act_id
INNER JOIN movie ON movie_cast.mov_id = movie.mov_id
WHERE movie.mov_title = 'Annie Hall';

Explanation:

This query uses INNER JOINs to combine the actor, movie_cast, and movie tables based on their respective IDs. It then applies a WHERE clause to filter for movies with the title 'Annie Hall' and selects the actor's first name, last name, and role.

Using WHERE Clause with Table Aliases:


SELECT a.act_fname, a.act_lname, mc.role
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 uses the older comma-separated syntax for joining tables and specifies the join conditions in the WHERE clause. It then applies a WHERE clause to filter for movies with the title 'Annie Hall' and selects the actor's first name, last name, and role.

Relational Algebra Expression:

Relational Algebra Expression: Find the first and last names of all the actors who were cast in the movie 'Annie Hall', and the roles they played in that production.

Relational Algebra Tree:

Relational Algebra Tree: Find the first and last names of all the actors who were cast in the movie 'Annie Hall', and the roles they played in that production.

Practice Online


Movie database model

Query Visualization:

Duration:

Query visualization of Find the first and last names of all the actors who were cast in the movie 'Annie Hall', and the roles they played in that production - Duration

Rows:

Query visualization of Find the first and last names of all the actors who were cast in the movie 'Annie Hall', and the roles they played in that production - Rows

Cost:

Query visualization of Find the first and last names of all the actors who were cast in the movie 'Annie Hall', and the roles they played in that production - 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 name of all reviewers who have rated their ratings with a NULL value. Return reviewer name.
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 and movie title.

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