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 tables, write a SQL query to find the actors who have not acted in any movie between1990 and 2000 (Begin and end values are included.). Return actor first name, last name, movie title and release year.

Sample table: actor

Sample table: movie_cast

Sample table: movie

Sample Solution:

SELECT act_fname, act_lname, mov_title, mov_year
FROM actor
JOIN movie_cast 
ON actor.act_id=movie_cast.act_id
JOIN movie 
ON movie_cast.mov_id=movie.mov_id
WHERE mov_year NOT BETWEEN 1990 and 2000;

OR

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;

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

Relational Algebra Expression:

Relational Algebra Tree:

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Query Visualization for Sample Solution:

Duration:

Rows:

Cost:

Query Visualization for alternate Sample Solution:

Duration:

Rows:

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.