w3resource

SQL exercises on movie Database: Find all the movies with year and genres

SQL movie Database: Join Exercise-7 with Solution

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 Expression: Find all the movies with year and genres.

Relational Algebra Tree:

Relational Algebra Tree: Find all the movies with year and genres.

Practice Online


Movie database model

Query Visualization:

Duration:

Query visualization of Find all the movies with year and genres - Duration

Rows:

Query visualization of Find all the movies with year and genres - Rows

Cost:

Query visualization of Find all the movies with year and genres - 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 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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.