SQL Project — Working on Movie Database

1. From the movie table, find the name and year of the movies. Return movie title, movie release year.

2. From the movie table, find when the movie ‘American Beauty’ released. Return movie release year.

3. From the movie table, find the movie, which was made in the year 1999. Return movie title.

4. From the movie table, find those movies, which was made before 1998. Return movie title.

5. From the movie and reviewer tables, find the name of all reviewers and movies together in a single list.

6. From the rating and reviewer tables, find all reviewers who have rated 7 or more stars to their rating. Return reviewer name.

7. From the movie and rating tables, find the movies without any rating. Return movie title.

8. From the movie table, find the movies with ID 905 or 907 or 917. Return movie title.

9. From the movie table, find those movie titles, which include the words ‘Boogie Nights’. Sort the result-set in ascending order by movie year. Return movie ID, movie title and movie release year.

10. From the actor table, find those actors whose first name is ‘Woody’ and the last name is ‘Allen’. Return actor ID

11. From the movie, movie_cast, and actor tables, find the actors who played a role in the movie ‘Annie Hall’. Return all the fields of actor table.

12. From the movie, movie_direction, and director tables, find the director who directed a movie that casted a role for ‘Eyes Wide Shut’. Return director first name, last name.

13. From the movie table, find those movies, which released in the country besides UK. Return movie title, movie year, movie time, date of release, releasing country.

14. From the movie, movie_cast, actor, movie_direction, director, rating, and reviewer tables, find those movies where reviewer is unknown. Return movie title, year, release date, director first name, last name, actor first name, last name.

15. From the movie, movie_direction, and director tables, find those movies directed by the director whose first name is ‘Woddy’ and last name is ‘Allen’. Return movie title.

16. From the movie and rating tables, find those years, which produced at least one movie and that, received a rating of more than three stars. Sort the result-set in ascending order by movie year. Return movie year.

17. From the movie and rating tables, find those movies, which have no ratings. Return movie title.

18. From the rating and reviewer tables, find those reviewers who have rated nothing for some movies. Return reviewer name.

19. From the movie, rating, and reviewer tables, find those movies, which reviewed by a reviewer and got a rating. Sort the result-set in ascending order by reviewer name, movie title, review Stars. Return reviewer name, movie title, review Stars.

20. From the movie, rating, and reviewer tables, find those reviewers who rated more than one movie. Group the result set on reviewer’s name, movie title. Return reviewer’s name, movie title.

21. From the movie and rating tables, find those movies, which have received highest number of stars. Group the result set on movie title and sorts the result-set in ascending order by movie title. Return movie title and maximum number of review stars.

22. From the movie, rating, and reviewer tables, find all reviewers who rated the movie ‘American Beauty’. Return reviewer name.

23. From the movie, rating, and reviewer tables, find the movies, which have reviewed by any reviewer body except by ‘Paul Monks’. Return movie title.

24. From the movie, rating, and reviewer tables, find the lowest rated movies. Return reviewer name, movie title, and number of stars for those movies.

25. From the movie, movie_direction, and director tables, find the movies directed by ‘James Cameron’. Return movie title.

26. Find the name of those movies where one or more actors acted in two or more movies.

27. From the rating and reviewer tables, find the name of all reviewers who have rated their ratings with a NULL value. Return reviewer name.

28. From the movie, movie_cast, and actor tables, find the actors who were cast in the movie ‘Annie Hall’. Return actor first name, last name and role.

29. From the movie, movie_direction, and director tables, find the director who directed a movie that casted a role for ‘Eyes Wide Shut’. Return director first name, last name and movie title.

30. From the movie, movie_cast, and director tables, find who directed a movie that casted a role as ‘Sean Maguire’. Return director first name, last name and movie title.

31. From the movie, movie_cast, and actor tables, 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.

32. From the movie_direction, director, movie_genres, and genres tables, 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.

33. From the movie, movie_genres, and genres tables, find the movies with year and genres. Return movie title, movie year and generic title.

34. From the movie, movie_direction, director, movie_genres, and genres tables, find all the movies with year, genres, and name of the director.

35. From the movie, movie_direction, and director tables, 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.

36. From the movie, movie_genres, and genres tables, compute the average time and count number of movies for each genre. Return genre title, average time and number of movies for each genre.

37. From the movie, movie_cast, actor, movie_direction, and director tables, find movies with the lowest duration. Return movie title, movie year, director first name, last name, actor first name, last name and role.

38. From the movie and rating tables, 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.

39. From the movie, rating, and reviewer tables, 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.

40. From the movie and rating tables, 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.

41. From the movie, movie_direction, director, and rating tables, find those movies, which have received ratings. Return movie title, director first name, director last name and review stars.

42. 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.

43. From the movie, movie_cast, actor, movie_direction, and director tables, 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.

44. From the movie, movie_cast, actor, movie_direction, and director tables, find those actors who have directed their movies. Return actor first name, last name, movie title and role.

45. From the movie, movie_cast, and actor tables, find the cast list of the movie ‘Chinatown’. Return first name, last name.

46. From the movie, movie_cast, and actor tables, find those movies where actor’s first name is ‘Harrison’ and last name is ‘Ford’. Return movie title.

47. From the movie and rating tables, find the highest-rated movies. Return movie title, movie year, review stars and releasing country.

48. From the movie, movie_genres, genres, and rating tables, find the highest-rated ‘Mystery Movies’. Return the title, year, and rating.

49. From the movie, movie_genres, genres, and rating tables, 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.

50. From the movie, movie_cast, actor, movie_direction, director, movie_genres, genres, and rating tables, 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.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store