w3resource

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

SQL [16 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 list all the information of the actors who played a role in the movie 'Annie Hall'.  Go to the editor

Sample table: actor


Sample table: movie_cast


Sample table: movie


Click me to see the solution

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

Sample table: director


Sample table: movie_direction


Sample table: movie_cast


Sample table: movie


Click me to see the solution

3. Write a query in SQL to list all the movies which released in the country other than UK.  Go to the editor

Sample table: movie


Click me to see the solution

4. Write a query in SQL to find the movie title, year, date of release, director and actor for those movies which reviewer is unknown.  Go to the editor

Sample table: movie


Sample table: actor


Sample table: director


Sample table: movie_direction


Sample table: movie_cast


Sample table: reviewer


Sample table: rating


Click me to see the solution

5. Write a query in SQL to find the titles of all movies directed by the director whose first and last name are Woddy Allen.  Go to the editor

Sample table: movie


Sample table: director


Sample table: movie_direction


Click me to see the solution

6. Write a query in SQL to find all the years which produced at least one movie and that received a rating of more than 3 stars. Show the results in increasing order.  Go to the editor

Sample table: movie


Sample table: rating


Click me to see the solution

7. Write a query in SQL to find the titles of all movies that have no ratings.  Go to the editor

Sample table: movie


Sample table: rating


Click me to see the solution

8. Write a query in SQL to find the names of all reviewers who have ratings with a NULL value.  Go to the editor

Sample table: reviewer


Sample table: rating


Click me to see the solution

9. Write a query in SQL to return the reviewer name, movie title, and stars for those movies which reviewed by a reviewer and must be rated. Sort the result by reviewer name, movie title, and number of stars.  Go to the editor

Sample table: reviewer


Sample table: rating


Sample table: movie


Click me to see the solution

10. Write a query in SQL to find the reviewer's name and the title of the movie for those reviewers who rated more than one movies.  Go to the editor

Sample table: reviewer


Sample table: rating


Sample table : movie


Click me to see the solution

11. Write a query in SQL to find the movie title, and the highest number of stars that movie received and arranged the result according to the group of a movie and the movie title appear alphabetically in ascending order.  Go to the editor

Sample table: rating


Sample table: movie


Click me to see the solution

12. Write a query in SQL to find the names of all reviewers who rated the movie American Beauty.  Go to the editor

Sample table: reviewer


Sample table: rating


Sample table: movie


Click me to see the solution

13. Write a query in SQL to find the titles of all movies which have been reviewed by anybody except by Paul Monks.  Go to the editor

Sample table: reviewer


Sample table: rating


Sample table: movie


Click me to see the solution

14. Write a query in SQL to return the reviewer name, movie title, and number of stars for those movies which rating is the lowest one.  Go to the editor

Sample table: reviewer


Sample table: rating


Sample table: movie


Click me to see the solution

15. Write a query in SQL to find the titles of all movies directed by James Cameron.  Go to the editor

Sample table: director


Sample table: movie_direction


Sample table: movie


Click me to see the solution

16. Write a query in SQL to find the name of 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

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.