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:
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: movie_cast
Sample table: movie
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
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
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: actor
Sample table: director
Sample table: movie_direction
Sample table: movie_cast
Sample table: reviewer
Sample table: rating
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
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
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
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
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
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
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
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
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
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
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
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
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.