Skip to main content

Posts

Neutral Reviewers

Write a SQL Query to find the name of all reviewers who have rated their ratings with a NULL value. Output Schema: reviewer_name NOTE:  Output column name has to match the given output schema. Example Output: reviewer_name MaxPlank NeilsBohr Schrodinger Schema Design: Solutions: SELECT reviewer_name FROM reviewers INNER JOIN ratings on reviewers.reviewer_id=ratings.reviewer_id WHERE reviewer_stars IS NULL;
Recent posts

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

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

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