SQL exercises on movie Database: Find those lowest duration movies along with the year, director's name, actor's name and his/her role in that production

SQL movie Database: Join Exercise-11 with Solution

11. From the following table, write a SQL query to find movies with the lowest duration. Return movie title, movie year, director first name, last name, actor first name, last name and role.

Sample table: movie

Sample table: actor

Sample table: director

Sample table: movie_direction

Sample table: movie_cast

Sample Solution:

SELECT mov_title, mov_year, dir_fname, dir_lname, 
       act_fname, act_lname, role 
FROM  movie
NATURAL JOIN movie_direction
NATURAL JOIN movie_cast
NATURAL JOIN director
NATURAL JOIN actor
WHERE mov_time=(SELECT MIN(mov_time) FROM movie);

Sample Output:

                     mov_title                      | mov_year |      dir_fname       |      dir_lname       |      act_fname       |      act_lname       |              role
----------------------------------------------------+----------+----------------------+----------------------+----------------------+----------------------+-------------------------------
 Annie Hall                                         |     1977 | Woody                | Allen                | Woody                | Allen                | Alvy Singer
(1 rows)

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following tables, write a SQL query to compute the average time and count number of movies for each genre. Return genre title, average time and number of movies for each genre.
Next: From the following tables, write a SQL query to find those years when a movie received a rating of 3 or 4. Sort the result in increasing order on movie year. Return move year.