w3resource

SQL Exercises, Practice, Solution - JOINS exercises on movie Database

SQL [24 exercises with solution]

[An editor is available at the bottom of the page to write and execute the scripts.]

Sample Database:

Movie database model

1. From the following tables, write a SQL query to find the name of all reviewers who have rated their ratings with 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 tables, write a SQL query to find the actors who were 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 tables, write a SQL query to find the director who directed a movie that casted a role for '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 who directed a movie that casted 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 tables, write a SQL query to find the actors who have not acted in any movie between1990 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 tables, write a SQL query to find the directors with number of genres movies. 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 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.  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 tables, write a SQL query to find movies with the lowest 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 tables, write a SQL query to find those years when a movie received a rating of 3 or 4. Sort the result in increasing order on movie year. Return move 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 tables, write a SQL query to find those movies that have at least one rating and received highest number of 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 tables, write a SQL query to find those movies, which 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. Write a query in SQL to find the movie title, actor first and last name, and the role for those movies where one or more actors acted in two or more movies.  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 tables, write a SQL query to find those actors who have directed their movies. 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

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.