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:

1. SELECT movie_title, movie_year, CONCAT(TRIM(director_first_name), TRIM(director_last_name)) as director_name,
2. CONCAT(TRIM(actor_first_name), TRIM(actor_last_name)) as actor_name, role
3. FROM movies
4. NATURAL JOIN movies_directors
5. NATURAL JOIN movies_cast
6. NATURAL JOIN directors
7. NATURAL JOIN actors
8. WHERE movie_time=(SELECT MIN(movie_time) FROM movies);