w3resource

SQL exercises on movie Database: Find all the movies with title, year, date of release, duration, and name of the director which released before 1st January 1989, and sort the result in descending order on release date

SQL movie Database: Join Exercise-9 with Solution

9. From the following tables, write a SQL query to find the movies released before 1st January 1989. Sort the result-set in descending order by date of release. Return movie title, release year, date of release, duration, and first and last name of the director.

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: director
 dir_id |      dir_fname       |      dir_lname
--------+----------------------+----------------------
    201 | Alfred               | Hitchcock
    202 | Jack                 | Clayton
    203 | David                | Lean
    204 | Michael              | Cimino
    205 | Milos                | Forman
    206 | Ridley               | Scott
    207 | Stanley              | Kubrick
    208 | Bryan                | Singer
    209 | Roman                | Polanski
    210 | Paul                 | Thomas Anderson
    211 | Woody                | Allen
    212 | Hayao                | Miyazaki
    213 | Frank                | Darabont
    214 | Sam                  | Mendes
    215 | James                | Cameron
    216 | Gus                  | Van Sant
    217 | John                 | Boorman
    218 | Danny                | Boyle
    219 | Christopher          | Nolan
    220 | Richard              | Kelly
    221 | Kevin                | Spacey
    222 | Andrei               | Tarkovsky
    223 | Peter                | Jackson
Sample table: movie_direction
 dir_id | mov_id
--------+--------
    201 |    901
    202 |    902
    203 |    903
    204 |    904
    205 |    905
    206 |    906
    207 |    907
    208 |    908
    209 |    909
    210 |    910
    211 |    911
    212 |    912
    213 |    913
    214 |    914
    215 |    915
    216 |    916
    217 |    917
    218 |    918
    219 |    919
    220 |    920
    218 |    921
    215 |    922
    221 |    923

Sample Solution:

-- Selecting specific columns from the tables movie, movie_direction, and director
SELECT movie.mov_title, mov_year, mov_dt_rel,
       mov_time,dir_fname, dir_lname 
-- Joining the movie and movie_direction tables based on the mov_id column
FROM movie
JOIN  movie_direction 
   ON movie.mov_id = movie_direction.mov_id
-- Joining the result with the director table based on the dir_id column
JOIN director 
   ON movie_direction.dir_id=director.dir_id
-- Filtering the result to include only records with mov_dt_rel before '01/01/1989'
WHERE mov_dt_rel < '01/01/1989'
-- Ordering the result by mov_dt_rel in descending order
ORDER BY mov_dt_rel DESC;

Sample Output:

                     mov_title                      | mov_year | mov_dt_rel | mov_time |      dir_fname       |      dir_lname
----------------------------------------------------+----------+------------+----------+----------------------+----------------------
 Aliens                                             |     1986 | 1986-08-29 |      137 | James                | Cameron
 Amadeus                                            |     1984 | 1985-01-07 |      160 | Milos                | Forman
 Deliverance                                        |     1972 | 1982-10-05 |      109 | John                 | Boorman
 Blade Runner                                       |     1982 | 1982-09-09 |      117 | Ridley               | Scott
 The Deer Hunter                                    |     1978 | 1979-03-08 |      183 | Michael              | Cimino
 Annie Hall                                         |     1977 | 1977-04-20 |       93 | Woody                | Allen
 Chinatown                                          |     1974 | 1974-08-09 |      130 | Roman                | Polanski
 Lawrence of Arabia                                 |     1962 | 1962-12-11 |      216 | David                | Lean
 The Innocents                                      |     1961 | 1962-02-19 |      100 | Jack                 | Clayton
 Vertigo                                            |     1958 | 1958-08-24 |      128 | Alfred               | Hitchcock
(10 rows)

Code Explanation :

The said query in SQL which selects the movie title, year, release date, duration, director first name, and director last name for all movies that were released before January 1st, 1989. The results are sorted in descending order by release date.
The JOIN clause joins the movie table, movie_direction table, and director table based on the movie ID and director ID.
The WHERE clause filters the results to include only those movies that were released before January 1st, 1989.
Then sorts the results in descending order by release date.

Alternative Solutions:

Using WHERE Clause with Table Aliases:


SELECT m.mov_title, m.mov_year, m.mov_dt_rel, m.mov_time, d.dir_fname, d.dir_lname
FROM movie m, movie_direction md, director d
WHERE m.mov_id = md.mov_id
  AND md.dir_id = d.dir_id
  AND m.mov_dt_rel < '1989-01-01'
ORDER BY m.mov_dt_rel DESC;

Explanation:

This query uses the 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 released before January 1, 1989, and selects the movie title, year, release date, runtime, director's first name, and last name. The results are ordered by release date in descending order.

Relational Algebra Expression:

Relational Algebra Expression: Find all the movies with title, year, date of release, duration, and name of the director which released before 1st January 1989, and sort the result in descending order on release date.

Relational Algebra Tree:

Relational Algebra Tree: Find all the movies with title, year, date of release, duration, and name of the director which released before 1st January 1989, and sort the result in descending order on release date.

Practice Online


Movie database model

Query Visualization:

Duration:

Query visualization of Find all the movies with title, year, date of release, duration, and name of the director which released before 1st January 1989, and sort the result in descending order on release date - Duration

Rows:

Query visualization of Find all the movies with title, year, date of release, duration, and name of the director which released before 1st January 1989, and sort the result in descending order on release date - Rows

Cost:

Query visualization of Find all the movies with title, year, date of release, duration, and name of the director which released before 1st January 1989, and sort the result in descending order on release date - 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 all the movies with year, genres, and name of the director.
Next: From the following tables, write a SQL query to compute the average time and count number of movies for each genre. Return genre title, average time and number of movies for each genre.

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.