w3resource

SQL exercises on movie Database: Display the name and year of the movies

SQL movie Database: Basic Exercise-1 with Solution

1. From the following table, write a SQL query to find the name and year of the movies. Return movie title, movie release year.

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 columns 'mov_title' and 'mov_year' from the 'movie' table
SELECT mov_title, mov_year
FROM movie;

Sample Output:

                     mov_title                      | mov_year
----------------------------------------------------+----------
 Vertigo                                            |     1958
 The Innocents                                      |     1961
 Lawrence of Arabia                                 |     1962
 The Deer Hunter                                    |     1978
 Amadeus                                            |     1984
 Blade Runner                                       |     1982
 Eyes Wide Shut                                     |     1999
 The Usual Suspects                                 |     1995
 Chinatown                                          |     1974
 Boogie Nights                                      |     1997
 Annie Hall                                         |     1977
 Princess Mononoke                                  |     1997
 The Shawshank Redemption                           |     1994
 American Beauty                                    |     1999
 Titanic                                            |     1997
 Good Will Hunting                                  |     1997
 Deliverance                                        |     1972
 Trainspotting                                      |     1996
 The Prestige                                       |     2006
 Donnie Darko                                       |     2001
 Slumdog Millionaire                                |     2008
 Aliens                                             |     1986
 Beyond the Sea                                     |     2004
 Avatar                                             |     2009
 Seven Samurai                                      |     1954
 Spirited Away                                      |     2001
 Back to the Future                                 |     1985
 Braveheart                                         |     1995
(28 rows)

Code Explanation:

The provided statement in SQL selects the "mov_title" (movie titles) and "mov_year" (release years of movie) columns from the table 'movie'.

Relational Algebra Expression:

Relational Algebra Expression: Display the name and year of the movies.

Relational Algebra Tree:

Relational Algebra Tree: Display the name and year of the movies.

Practice Online


Movie database model

Query Visualization:

Duration:

Query visualization of Display the name and year of the movies - Duration

Rows:

Query visualization of Display the name and year of the movies - Rows

Cost:

Query visualization of Display the name and year of the movies - Cost

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: SQL BASIC exercises on movie Database
Next: From the following table, write a SQL query to find when the movie 'American Beauty' released. Return movie release year.

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.