Skip to main content

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:

  1. select CONCAT(TRIM(directors.director_first_name), TRIM(directors.director_last_name))
  2. as director_name ,movie_title
  3. from (((directors inner join movies_directors
  4. on directors.director_id=movies_directors.director_id)inner join
  5. movies on movies.movie_id=movies_directors.movie_id)inner join
  6. movies_cast on movies.movie_id=movies_cast.movie_id) where
  7. movies_cast.role='SeanMaguire';


Thank You

Comments

Post a Comment

Popular posts from this blog

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: Solutions: SELECT movie_title FROM movies JOIN movies_cast ON movies_cast . movie_id = movies . movie_id JOIN actors ON movies_cast . actor_id = actors . actor_id WHERE actors . actor_id IN ( SELECT actor_id FROM movies_cast GROUP BY actor_id HAVING COUNT (*)>= 2 );
4 comments
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