Skip to main content

Actors and their Movies

Write a SQL Query to find the name of those movies where one or more actors acted in two or more movies.
Output Schema:
movie_title
NOTE:
  • Output column name has to match the given output schema.
  • Any name is the concatenation(without any delimiter) of first and last name if present
  • (E.g. if director_first_name is ‘Alfred’ and director_last_name is ‘Hitchcock’ then director_name is ‘AlfredHitchcock’)
Example Output:
movie_title
Vertigo
Schema Design:
Schema Description
Solutions:
  1. SELECT movie_title
  2. FROM movies
  3. JOIN movies_cast
  4. ON movies_cast.movie_id=movies.movie_id
  5. JOIN actors
  6. ON movies_cast.actor_id=actors.actor_id
  7. WHERE actors.actor_id IN (
  8. SELECT actor_id
  9. FROM movies_cast
  10. GROUP BY actor_id HAVING COUNT(*)>=2);

Comments

  1. Unknown17 June 2018 at 10:14

    Good

    ReplyDelete
  2. thehelpfulguy31 August 2019 at 08:42

    I wrote the following query which didn't work. Can somebody please point out the fault?
    select distinct movie_title
    from movies_cast natural join movies
    group by actor_id
    having count(*)>=2;

    ReplyDelete
    Replies
    1. anh doan27 April 2024 at 10:00

      when you group by actor_id you actually lost all the movie name but keep only 1 from the group by clause. Therefore, it is better if you capture the actor_id only and then select all the movie name that has the actor_id that you have captured
      select movie_title from movies
      where movie_id in (
      select movie_id from movies_cast
      where actor_id in (
      select actor_id from movies_cast
      group by actor_id
      having count(movie_id) >=2
      )
      )

      Delete
  3. edmunnagarajan3 March 2022 at 08:13

    Casino de Nacional in Indio, CA - JCM Hub
    › casino-de-nacional-in › 남원 출장안마 casino-de-nacional-in Casino de Nacional in Indio, CA - JCM Hub provides users with the opportunity to live 아산 출장샵 up to their dreams and enjoy 김제 출장마사지 the 서울특별 출장마사지 gaming experience from just a few clicks 순천 출장마사지 of your mouse.

    ReplyDelete

Post a Comment

Popular posts from this blog

Movie Character

Write a SQL Query to find the movie_title and name of director (first and last names combined) who directed a movie that casted a role as ‘SeanMaguire’. Output Schema: director_name,movie_title NOTE: Output column name has to match the given output schema. Any name is the concatenation(without any delimiter) of first and last name if present (E.g. if director_first_name is ‘Alfred’ and director_last_name is ‘Hitchcock’ then director_name is ‘AlfredHitchcock’) Example Output: director_name,movie_title AlfredHitchcock,Vertigo Solutions: select CONCAT ( TRIM ( directors . director_first_name ), TRIM ( directors . director_last_name )) as director_name , movie_title from ((( directors inner join movies_directors on directors . director_id = movies_directors . director_id ) inner join movies on movies . movie_id = movies_directors . movie_id ) inner join movies_cast on movies . mo...
Post a Comment
Read more

Short Films

Write a SQL Query to find those lowest duration movies along with the year, director’s name(first and last name combined), actor’s name(first and last name combined) and his/her role in that production. Output Schema: movie_title,movie_year,director_name,actor_name,role NOTE: Output column name has to match the given output schema. Any name is the concatenation(without any delimiter) of first and last name if present (E.g. if director_first_name is ‘Alfred’ and director_last_name is ‘Hitchcock’ then director_name is ‘AlfredHitchcock’) Example Output: movie_title,movie_year,director_name,actor_name,role Vertigo,1958,AlfredHitchcock,JamesStewart,JohnFerguson Schema Design: Solutions : SELECT movie_title , movie_year , CONCAT ( TRIM ( director_first_name ), TRIM ( director_last_name )) as director_name , CONCAT ( TRIM ( actor_first_name ), TRIM ( actor_last_name )) as actor_name , role FROM movies NATURAL JOIN movies_directors NATURA...
Post a Comment
Read more