w3resource logo
SQL Exercises

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. Write a query in SQL to find the name of all reviewers who have rated their ratings with a NULL value.  Go to the editor

Sample table: reviewer


Sample table: rating


Click me to see the solution

2. Write a query in SQL to list the first and last names of all the actors who were cast in the movie 'Annie Hall', and the roles they played in that production.  Go to the editor

Sample table: actor


Sample table: movie_cast


Sample table : movie


Click me to see the solution

3. Write a query in SQL to find the name of movie and director (first and last names) who directed a movie that casted a role for 'Eyes Wide Shut'.  Go to the editor

Sample table: director


Sample table: movie_direction


Sample table: movie_cast


Sample table: movie


Click me to see the solution

4. Write a query in SQL to find the name of movie and director (first and last names) who directed a movie that casted a role as Sean Maguire.  Go to the editor

Sample table: director


Sample table: movie_direction


Sample table: movie_cast


Sample table: movie


Click me to see the solution

5. Write a query in SQL to list all the actors who acted in a movie before 1990 and also in a movie after 2000.  Go to the editor

Sample table: actor


Sample table: movie_cast


Sample table: movie


Click me to see the solution

6. Write a query in SQL to list first and last name of all the directors with number of genres movies the directed with genres name, and arranged the result alphabetically with the first and last name of the director.  Go to the editor

Sample table: director


Sample table: movie_direction


Sample table: genres


Sample table: movie_genres


Click me to see the solution

7. Write a query in SQL to list all the movies with year and genres.  Go to the editor

Sample table: movie


Sample table: genres


Sample table: movie_genres


Click me to see the solution

8. Write a query in SQL to list 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


Click me to see the solution

9. Write a query in SQL to list all the movies with title, year, date of release, movie duration, and first and last name of the director which released before 1st january 1989, and sort the result set according to release date from highest date to lowest.  Go to the editor

Sample table: movie


Sample table: director


Sample table: movie_direction


Click me to see the solution

10. Write a query in SQL to compute a report which contain the genres of those movies with their average time and number of movies for each genres.  Go to the editor

Sample table: movie


Sample table: genres


Sample table: movie_genres


Click me to see the solution

11. Write a query in SQL to find those lowest duration movies along with the year, director's name, actor's name and his/her role in that production.  Go to the editor

Sample table: movie


Sample table: actor


Sample table: director


Sample table: movie_direction


Sample table : movie_cast

Click me to see the solution

12. Write a query in SQL to find all the years which produced a movie that received a rating of 3 or 4, and sort the result in increasing order.  Go to the editor

Sample table: movie


Sample table: rating


Click me to see the solution

13. Write a query in SQL to return 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


Click me to see the solution

14. Write a query in SQL to find movie title and number of stars for each movie that has at least one rating and find the highest number of stars that movie received and sort the result by movie title.  Go to the editor

Sample table: movie


Sample table: rating


Click me to see the solution

15. Write a query in SQL to find the director's first and last name together with the title of the movie(s) they directed and received the rating.  Go to the editor

Sample table: movie


Sample table: rating


Sample table: movie_direction


Sample table: director


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


Click me to see the solution

17. Write a query in SQL to find the first and last name of a director and the movie he or she directed, and the actress appeared which first name was Claire and last name was Danes along with her role in that movie.  Go to the editor

Sample table: movie


Sample table: movie_cast


Sample table: actor


Sample table: director


Sample table: movie_direction


Click me to see the solution

18. Write a query in SQL to find the first and last name of an actor with their role in the movie which was also directed by themselve.  Go to the editor

Sample table: movie


Sample table: movie_cast


Sample table: actor


Sample table: director


Sample table: movie_direction


Click me to see the solution

19. Write a query in SQL to find the cast list for the movie Chinatown.  Go to the editor

Sample table: movie


Sample table: movie_cast


Sample table: actor


Click me to see the solution

20. Write a query in SQL to find the movie in which the actor appeared whose first and last name are 'Harrison' and 'Ford'.  Go to the editor

Sample table: movie


Sample table: movie_cast


Sample table: actor


Click me to see the solution

21. Write a query in SQL to find the highest-rated movie, and report its title, year, rating, and releasing country.  Go to the editor

Sample table : movie

Sample table : rating

Click me to see the solution

22. Write a query in SQL to find the highest-rated Mystery movie, and report the title, year, and rating.  Go to the editor

Sample table: movie


Sample table: genres


Sample table: movie_genres


Sample table: rating


Click me to see the solution

23. Write a query in SQL to generate a report which shows the year when most of the Mystery movies produces, and number of movies and their average rating.  Go to the editor

Sample table: movie


Sample table: genres


Sample table: movie_genres


Sample table: rating


Click me to see the solution

24. Write a query in SQL to generate a report which contain the columns 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


Click me to see the solution

Practice Online


More to Come !

Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.