SQL exercises on movie Database – Exercises, Practice, Solution – w3resource
SQL Exercises, Practice, Solution – JOINS exercises on movie Database
Last update on August 19 2022 21:50:36 (UTC/GMT +8 hours)
SQL [24 exercises with solution]
You may read our SQL Joins, SQL Left Join, SQL Right Join tutorial before solving the following exercises.
[An editor is available at the bottom of the page to write and execute the scripts.]
Sample Database:
1. From the following table, write a SQL query to find all reviewers whose ratings contain a NULL value. Return reviewer name. Go to the editor
Sample table: reviewer
Sample table: rating
Sample Output:
rev_name -------------------------------- Neal Wruck Scott LeBrun (2 rows)
Click me to see the solution
2. From the following table, write a SQL query to find out who was cast in the movie ‘Annie Hall’. Return actor first name, last name and role. Go to the editor
Sample table: actor
Sample table: movie_cast
Sample table : movie
Sample Output:
act_fname | act_lname | role ----------------------+----------------------+-------------------------------- Woody | Allen | Alvy Singer (1 row)
Click me to see the solution
3. From the following table, write a SQL query to find the director who directed a movie that featured a role in ‘Eyes Wide Shut’. Return director first name, last name and movie title. Go to the editor
Sample table: director
Sample table: movie_direction
Sample table: movie_cast
Sample table: movie
Sample Output:
dir_fname | dir_lname | mov_title ----------------------+----------------------+---------------------------------- Stanley | Kubrick | Eyes Wide Shut (1 row)
Click me to see the solution
4. From the following tables, write a SQL query to find the director of a movie that cast a role as Sean Maguire. Return director first name, last name and movie title. Go to the editor
Sample table: director
Sample table: movie_direction
Sample table: movie_cast
Sample table: movie
Sample Output:
dir_fname | dir_lname | mov_title ----------------------+----------------------+---------------------------------------------------- Gus | Van Sant | Good Will Hunting (1 row)
Click me to see the solution
5. From the following table, write a SQL query to find out which actors have not appeared in any movies between 1990 and 2000 (Begin and end values are included.). Return actor first name, last name, movie title and release year. Go to the editor
Sample table: actor
Sample table: movie_cast
Sample table: movie
Sample Output:
act_fname act_lname mov_title mov_year -------------------------------------------------------------------------------- James Stewart Vertigo 1958 Deborah Kerr The Innocents 1961 Peter OToole Lawrence of Arabia 1962 Robert De Niro The Deer Hunter 1978 F. Murray Abraham Amadeus 1984 .....
Click me to see the solution
6. From the following table, write a SQL query to find the directors who have directed films in a variety of genres. 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. Go to the editor
Sample table: director
Sample table: movie_direction
Sample table: genres
Sample table: movie_genres
Sample Output:
dir_fname | dir_lname | gen_title | count ----------------------+----------------------+----------------------+------- Alfred | Hitchcock | Mystery | 1 Bryan | Singer | Crime | 1 Danny | Boyle | Drama | 2 David | Lean | Adventure | 1 .....
Click me to see the solution
7. From the following table, write a SQL query to find the movies with year and genres. Return movie title, movie year and generic title. Go to the editor
Sample table: movie
Sample table: genres
Sample table: movie_genres
Sample Output:
mov_title | mov_year | gen_title ----------------------------------------------------+----------+---------------------- Aliens | 1986 | Action Deliverance | 1972 | Adventure Lawrence of Arabia | 1962 | Adventure Princess Mononoke | 1997 | Animation ....
Click me to see the solution
8. From the following tables, write a SQL query to find all the movies with year, genres, and name of the director. Go to the editor
Sample table: movie
Sample table: genres
Sample table: movie_genres
Sample table: director
Sample table: movie_direction
Sample Output:
mov_title | mov_year | gen_title | dir_fname | dir_ ----------------------------------------------------+----------+----------------------+----------------------+---------- Vertigo | 1958 | Mystery | Alfred | Hitchcock The Innocents | 1961 | Horror | Jack | Clayton Lawrence of Arabia | 1962 | Adventure | David | Lean The Deer Hunter | 1978 | War | Michael | Cimino -- More --
Click me to see the 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. Go to the editor
Sample table: movie
Sample table: director
Sample table: movie_direction
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 .....
Click me to see the solution
10. From the following table, write a SQL query to calculate the average movie length and count the number of movies in each genre. Return genre title, average time and number of movies for each genre. Go to the editor
Sample table: movie
Sample table: genres
Sample table: movie_genres
Sample Output:
gen_title | avg | count ----------------------+----------------------+------- Adventure | 162.5000000000000000 | 2 Comedy | 93.0000000000000000 | 1 Drama | 134.2500000000000000 | 4 Horror | 100.0000000000000000 | 1 .....
Click me to see the solution
11. From the following table, write a SQL query to find movies with the shortest duration. Return movie title, movie year, director first name, last name, actor first name, last name and role. Go to the editor
Sample table: movie
Sample table: actor
Sample table: director
Sample table: movie_direction
Sample table : movie_cast
Sample Output:
mov_title | mov_year | dir_fname | dir_lname | act_fname | act_lname | role ----------------------------------------------------+----------+----------------------+----------------------+----------------------+----------------------+------------------------------- Annie Hall | 1977 | Woody | Allen | Woody | Allen | Alvy Singer (1 rows)
Click me to see the solution
12. From the following table, write a SQL query to find the years in which a movie received a rating of 3 or 4. Sort the result in increasing order on movie year. Go to the editor
Sample table: movie
Sample table: rating
Sample Output:
mov_year
----------
1997
(1 row)
Click me to see the solution
13. From the following tables, write a SQL query to get the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars. Go to the editor
Sample table : movie
Sample table: rating
Sample table: reviewer
Sample Output:
rev_name | mov_title | rev_stars --------------------------------+----------------------------------------------------+----------- Brandt Sponseller | Aliens | 8.40 Flagrant Baronessa | Lawrence of Arabia | 8.30 Hannah Steele | Donnie Darko | 8.10 Jack Malvern | The Innocents | 7.90 .....
Click me to see the solution
14. From the following table, write a SQL query to find those movies that have at least one rating and received the most stars. Sort the result-set on movie title. Return movie title and maximum review stars. Go to the editor
Sample table: movie
Sample table: rating
Sample Output:
mov_title | max ----------------------------------------------------+------ Aliens | 8.40 American Beauty | 7.00 Annie Hall | 8.10 Avatar | 7.30 .....
Click me to see the solution
15. From the following table, write a SQL query to find out which movies have received ratings. Return movie title, director first name, director last name and review stars. Go to the editor
Sample table: movie
Sample table: rating
Sample table: movie_direction
Sample table: director
Sample Output:
mov_title | dir_fname | dir_lname | rev_stars ----------------------------------------------------+----------------------+----------------------+----------- Vertigo | Alfred | Hitchcock | 8.40 The Innocents | Jack | Clayton | 7.90 Lawrence of Arabia | David | Lean | 8.30 Blade Runner | Ridley | Scott | 8.20 .....
Click me to see the solution
16. From the following table, write a SQL query to find movies in which one or more actors have acted in more than one film. Return movie title, actor first and last name, and the role. Go to the editor
Sample table: movie
Sample table: movie_cast
Sample table: actor
Sample Output:
mov_title | act_fname | act_lname | role ----------------------------------------------------+----------------------+----------------------+-------------------------------- American Beauty | Kevin | Spacey | Lester Burnham Beyond the Sea | Kevin | Spacey | Bobby Darin (2 rows)
Click me to see the solution
17. From the following tables, write a SQL query to find the actor whose first name is ‘Claire’ and last name is ‘Danes’. Return director first name, last name, movie title, actor first name and last name, role. Go to the editor
Sample table: movie
Sample table: movie_cast
Sample table: actor
Sample table: director
Sample table: movie_direction
Sample Output:
dir_fname | dir_lname | mov_title | act_fname | act_lname | role ----------------------+----------------------+----------------------------------------------------+----------------------+----------------------+-------------------------------- Hayao | Miyazaki | Princess Mononoke | Claire | Danes | San (1 row)
Click me to see the solution
18. From the following table, write a SQL query to find for actors whose films have been directed by them. Return actor first name, last name, movie title and role. Go to the editor
Sample table: movie
Sample table: movie_cast
Sample table: actor
Sample table: director
Sample table: movie_direction
Sample Output:
act_fname | act_lname | mov_title | role ----------------------+----------------------+----------------------------------------------------+-------------------------------- Woody | Allen | Annie Hall | Alvy Singer Kevin | Spacey | Beyond the Sea | Bobby Darin (2 rows)
Click me to see the solution
19. From the following tables, write a SQL query to find the cast list of the movie ‘Chinatown’. Return first name, last name. Go to the editor
Sample table: movie
Sample table: movie_cast
Sample table: actor
Sample Output:
act_fname | act_lname ----------------------+---------------------- Jack | Nicholson (1 row)
Click me to see the solution
20. From the following tables, write a SQL query to find those movies where actor’s first name is ‘Harrison’ and last name is ‘Ford’. Return movie title. Go to the editor
Sample table: movie
Sample table: movie_cast
Sample table: actor
Sample Output:
mov_title ---------------------------------------------------- Blade Runner (1 row)
Click me to see the solution
21. From the following tables, write a SQL query to find the highest-rated movies. Return movie title, movie year, review stars and releasing country. Go to the editor
Sample table : movie
Sample table : rating
Sample Output:
mov_title | mov_year | rev_stars | mov_rel_country ----------------------------------------------------+----------+-----------+----------------- The Usual Suspects | 1995 | 8.60 | UK (1 row)
Click me to see the solution
22. From the following tables, write a SQL query to find the highest-rated ‘Mystery Movies’. Return the title, year, and rating. Go to the editor
Sample table: movie
Sample table: genres
Sample table: movie_genres
Sample table: rating
Sample Output:
mov_title | mov_year | rev_stars ----------------------------------------------------+----------+----------- Vertigo | 1958 | 8.40 (1 row)
Click me to see the solution
23. From the following tables, write a SQL query to find the years when most of the ‘Mystery Movies’ produced. Count the number of generic title and compute their average rating. Group the result set on movie release year, generic title. Return movie year, generic title, number of generic title and average rating. Go to the editor
Sample table: movie
Sample table: genres
Sample table: movie_genres
Sample table: rating
Sample Output:
mov_year | gen_title | count | avg
----------+----------------------+-------+--------------------
1958 | Mystery | 1 | 8.4000000000000000
(1 row)
Click me to see the solution
24. From the following tables, write a query in SQL to generate a report, which contain the fields movie title, name of the female actor, year of the movie, role, movie genres, the director, date of release, and rating of that movie. Go to the editor
Sample table: movie
Sample table: genres
Sample table: movie_genres
Sample table: rating
Sample table: actor
Sample table: director
Sample table: movie_direction
Sample table: movie_cast
Sample Output:
mov_title | act_fname | act_lname | mov_year | role | gen_title | dir_fname | dir_lname | mov_dt_rel | rev_stars ----------------------------------------------------+----------------------+----------------------+----------+--------------------------------+----------------------+----------------------+----------------------+------------+----------- The Innocents | Deborah | Kerr | 1961 | Miss Giddens | Horror | Jack | Clayton | 1962-02-19 | 7.90 Princess Mononoke | Claire | Danes | 1997 | San | Animation | Hayao | Miyazaki | 2001-10-19 | 8.40 Aliens | Sigourney | Weaver | 1986 | Ripley | Action | James | Cameron | 1986-08-29 | 8.40 (3 rows)
Click me to see the solution
Keep Learning: SQL Joins, SQL Left Join, SQL Right Join, SQL Equi Join, SQL Non Equi Join, SQL Inner Join, SQL Natural Join, SQL Cross Join, SQL Outer Join, SQL Full Outer Join, SQL Self Join.
Practice Online
More to Come !
Query visualizations are generated using Postgres Explain Visualizer (pev)
Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.
