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
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 Tree:
Go to:
PREV : 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.
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.