INTERVIEWBIT DATABASE SOLUTIONS

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 );

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

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