SQL exercises on movie Database: Find all the movies with year and genres
7. From the following tables, write a SQL query to find the movies with year and genres. Return movie title, movie year and generic title.
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 table: genres
gen_id | gen_title --------+---------------------- 1001 | Action 1002 | Adventure 1003 | Animation 1004 | Biography 1005 | Comedy 1006 | Crime 1007 | Drama 1008 | Horror 1009 | Music 1010 | Mystery 1011 | Romance 1012 | Thriller 1013 | War
Sample table: movie_genres
mov_id | gen_id
--------+--------
922 | 1001
917 | 1002
903 | 1002
912 | 1003
911 | 1005
908 | 1006
913 | 1006
926 | 1007
928 | 1007
918 | 1007
921 | 1007
902 | 1008
923 | 1009
907 | 1010
927 | 1010
901 | 1010
914 | 1011
906 | 1012
904 | 1013
Sample Solution:
-- Selecting specific columns from the tables movie, movie_genres, and genres
SELECT mov_title, mov_year, gen_title
-- Performing a natural join between the movie and movie_genres tables
FROM movie
NATURAL JOIN movie_genres
-- Performing another natural join between the result and the genres table
NATURAL JOIN genres;
Sample Output:
mov_title | mov_year | gen_title
----------------------------------------------------+----------+----------------------
Aliens | 1986 | Action
Deliverance | 1972 | Adventure
Lawrence of Arabia | 1962 | Adventure
Princess Mononoke | 1997 | Animation
Annie Hall | 1977 | Comedy
The Usual Suspects | 1995 | Crime
The Shawshank Redemption | 1994 | Crime
Seven Samurai | 1954 | Drama
Back to the Future | 1985 | Drama
Trainspotting | 1996 | Drama
Slumdog Millionaire | 2008 | Drama
The Innocents | 1961 | Horror
Beyond the Sea | 2004 | Music
Eyes Wide Shut | 1999 | Mystery
Spirited Away | 2001 | Mystery
Vertigo | 1958 | Mystery
American Beauty | 1999 | Romance
Blade Runner | 1982 | Thriller
The Deer Hunter | 1978 | War
Aliens | 1986 | Action
Deliverance | 1972 | Adventure
Lawrence of Arabia | 1962 | Adventure
Princess Mononoke | 1997 | Animation
Annie Hall | 1977 | Comedy
The Usual Suspects | 1995 | Crime
The Shawshank Redemption | 1994 | Crime
Seven Samurai | 1954 | Drama
Back to the Future | 1985 | Drama
Trainspotting | 1996 | Drama
Slumdog Millionaire | 2008 | Drama
The Innocents | 1961 | Horror
Beyond the Sea | 2004 | Music
Eyes Wide Shut | 1999 | Mystery
Spirited Away | 2001 | Mystery
Vertigo | 1958 | Mystery
American Beauty | 1999 | Romance
Blade Runner | 1982 | Thriller
The Deer Hunter | 1978 | War
Aliens | 1986 | Action
Deliverance | 1972 | Adventure
Lawrence of Arabia | 1962 | Adventure
Princess Mononoke | 1997 | Animation
Annie Hall | 1977 | Comedy
The Usual Suspects | 1995 | Crime
The Shawshank Redemption | 1994 | Crime
Seven Samurai | 1954 | Drama
Back to the Future | 1985 | Drama
Trainspotting | 1996 | Drama
Slumdog Millionaire | 2008 | Drama
The Innocents | 1961 | Horror
Beyond the Sea | 2004 | Music
Eyes Wide Shut | 1999 | Mystery
Spirited Away | 2001 | Mystery
Vertigo | 1958 | Mystery
American Beauty | 1999 | Romance
Blade Runner | 1982 | Thriller
The Deer Hunter | 1978 | War
Aliens | 1986 | Action
Deliverance | 1972 | Adventure
Lawrence of Arabia | 1962 | Adventure
Princess Mononoke | 1997 | Animation
Annie Hall | 1977 | Comedy
The Usual Suspects | 1995 | Crime
The Shawshank Redemption | 1994 | Crime
Seven Samurai | 1954 | Drama
Back to the Future | 1985 | Drama
Trainspotting | 1996 | Drama
Slumdog Millionaire | 2008 | Drama
The Innocents | 1961 | Horror
Beyond the Sea | 2004 | Music
Eyes Wide Shut | 1999 | Mystery
Spirited Away | 2001 | Mystery
Vertigo | 1958 | Mystery
American Beauty | 1999 | Romance
Blade Runner | 1982 | Thriller
The Deer Hunter | 1978 | War
Aliens | 1986 | Action
Deliverance | 1972 | Adventure
Lawrence of Arabia | 1962 | Adventure
Princess Mononoke | 1997 | Animation
Annie Hall | 1977 | Comedy
The Usual Suspects | 1995 | Crime
The Shawshank Redemption | 1994 | Crime
Seven Samurai | 1954 | Drama
Back to the Future | 1985 | Drama
Trainspotting | 1996 | Drama
Slumdog Millionaire | 2008 | Drama
The Innocents | 1961 | Horror
Beyond the Sea | 2004 | Music
Eyes Wide Shut | 1999 | Mystery
Spirited Away | 2001 | Mystery
Vertigo | 1958 | Mystery
American Beauty | 1999 | Romance
Blade Runner | 1982 | Thriller
The Deer Hunter | 1978 | War
Aliens | 1986 | Action
Deliverance | 1972 | Adventure
Lawrence of Arabia | 1962 | Adventure
Princess Mononoke | 1997 | Animation
Annie Hall | 1977 | Comedy
The Usual Suspects | 1995 | Crime
The Shawshank Redemption | 1994 | Crime
Seven Samurai | 1954 | Drama
Back to the Future | 1985 | Drama
Trainspotting | 1996 | Drama
Slumdog Millionaire | 2008 | Drama
The Innocents | 1961 | Horror
Beyond the Sea | 2004 | Music
Eyes Wide Shut | 1999 | Mystery
Spirited Away | 2001 | Mystery
Vertigo | 1958 | Mystery
American Beauty | 1999 | Romance
Blade Runner | 1982 | Thriller
The Deer Hunter | 1978 | War
Aliens | 1986 | Action
Deliverance | 1972 | Adventure
Lawrence of Arabia | 1962 | Adventure
Princess Mononoke | 1997 | Animation
Annie Hall | 1977 | Comedy
The Usual Suspects | 1995 | Crime
The Shawshank Redemption | 1994 | Crime
Seven Samurai | 1954 | Drama
Back to the Future | 1985 | Drama
Trainspotting | 1996 | Drama
Slumdog Millionaire | 2008 | Drama
The Innocents | 1961 | Horror
Beyond the Sea | 2004 | Music
Eyes Wide Shut | 1999 | Mystery
Spirited Away | 2001 | Mystery
Vertigo | 1958 | Mystery
American Beauty | 1999 | Romance
Blade Runner | 1982 | Thriller
The Deer Hunter | 1978 | War
(133 rows)
Code Explanation:
The said query in SQL that joins the tables movie, movie_genres, and genres, and retrieves the title and year of the movies, and the genre titles for each movie. The query uses the NATURAL JOIN keyword to join the tables based on columns with the same name in each table.
This joins the movie table with the movie_genres table to get the genre for each movie. Then joins the resulting table with the genres table to get the genre title for each genre ID.
Relational Algebra Expression:
Relational Algebra Tree:
Go to:
PREV : 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.
NEXT : From the following tables, write a SQL query to find all the movies with year, genres, and name of the director.
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.