w3resource
SQL exercises

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. Write a query in SQL to list all the actors who have not acted in any movie between 1990 and 2000.

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

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 - Rows

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: Write a query in SQL to find the name of movie and director (first and last names) who directed a movie that casted a role as Sean Maguire.
Next: Write a query in SQL to list first and last name of all the directors with number of genres movies they directed with genres name, and arranged the result alphabetically with the first and last name of the director.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming