w3resource

SQL exercises on movie Database: Find all the actors who have not acted in any movie between 1990 and 2000

SQL movie Database: Join Exercise-5 with Solution

5. From the following table, write a SQL query to find out which actors have not appeared in any movies between 1990 and 2000 (Begin and end values are included.). Return actor first name, last name, movie title and release year.

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 actor first names, last names, movie titles, and movie years
-- Using the 'actor' table
SELECT act_fname, act_lname, mov_title, mov_year
FROM actor
-- Joining with 'movie_cast' using explicit ON clause to link actors with movies
JOIN movie_cast ON actor.act_id = movie_cast.act_id
-- Further joining with 'movie' using explicit ON clause to get additional movie details
JOIN movie ON movie_cast.mov_id = movie.mov_id
-- Filtering for rows where the movie year is not between 1990 and 2000
WHERE mov_year NOT BETWEEN 1990 AND 2000;

Sample Output:

act_fname		act_lname		mov_title		mov_year
--------------------------------------------------------------------------------
James			Stewart			Vertigo			1958
Deborah			Kerr			The Innocents		1961
Peter			OToole			Lawrence of Arabia	1962
Robert			De Niro			The Deer Hunter		1978
F. Murray		Abraham			Amadeus			1984
Harrison		Ford			Blade Runner		1982
Woody			Allen			Annie Hall		1977
Jon			Voight			Deliverance		1972
Maggie			Gyllenhaal		Donnie Darko		2001
Dev			Patel			Slumdog Millionaire	2008
Sigourney		Weaver			Aliens			1986
Kevin			Spacey			Beyond the Sea		2004
Jack			Nicholson		Chinatown		1974
Christian		Bale			The Prestige		2006

Code Explanation:

The said query in SQL that joins the tables actor, movie_cast, and movie, and retrieves the first name and last name of the actors, the title of the movies they acted in, and the year of the movies. The query returns only those movies that were not released between 1990 and 2000.
The JOIN clause joins the actor table with the movie_cast table on the act_id column, and then join the resulting table with the movie table on the mov_id column.
The condition filters those movies that were not released between 1990 and 2000.

Alternative Solutions:

Using WHERE Clause with Table Aliases:

SELECT a.act_fname, a.act_lname, c.mov_title, c.mov_year
FROM actor a, movie_cast b, movie c
WHERE a.act_id=b.act_id
AND b.mov_id=c.mov_id
AND c.mov_year NOT BETWEEN 1990 and 2000;

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 release years not between 1990 and 2000 and selects the actor's first name, last name, movie title, and year.

Relational Algebra Expression:

Relational Algebra Expression: Find all the actors who have not acted in any movie between 1990 and 2000.

Relational Algebra Tree:

Relational Algebra Tree: Find all the actors who have not acted in any movie between 1990 and 2000.

Relational Algebra Expression:

Relational Algebra Expression: Find all the actors who have not acted in any movie between 1990 and 2000.

Relational Algebra Tree:

Relational Algebra Tree: Find all the actors who have not acted in any movie between 1990 and 2000.

Practice Online



Query Visualization for Sample Solution:

Duration:

Query visualization of Find all the actors who acted in a movie before 1990 and also in a movie after 2000 - Duration

Rows:

Query visualization of Find all the actors who acted in a movie before 1990 and also in a movie after 2000 - Rows

Cost:

Query visualization of Find all the actors who acted in a movie before 1990 and also in a movie after 2000 - Cost

Query Visualization for alternate Sample Solution:

Duration:

Query visualization of Find all the actors who acted in a movie before 1990 and also in a movie after 2000 - Duration

Rows:

Query visualization of Find all the actors who acted in a movie before 1990 and also in a movie after 2000 - Ro

Cost:

Query visualization of Find all the actors who acted in a movie before 1990 and also in a movie after 2000 - 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 who directed a movie that casted a role as ‘Sean Maguire’. Return director first name, last name and movie title.
Next: From the following tables, write a SQL query to find the directors with number of genres movies. Group the result set on director first name, last name and generic title. Sort the result-set in ascending order by director first name and last name. Return director first name, last name and number of genres movies.

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