w3resource
SQL exercises

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

SQL movie Database: Join Exercise-7 with Solution

7. Write a query in SQL to list all the movies with year and genres.

Sample table: movie


Sample table: genres


Sample table: movie_genres


Sample Solution:

SELECT mov_title, mov_year, gen_title
FROM movie
NATURAL JOIN movie_genres
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)

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: 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.
Next: Write a query in SQL to list all the movies with year, genres, and name of the director.

What is the difficulty level of this exercise?



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