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 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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics