w3resource
SQL exercises

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. Write a query in SQL to find those lowest duration movies along with the year, director's name, actor's name and his/her role in that production.

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


Movie database model

Query Visualization:

Duration:

Query visualization of Find those lowest duration movies along with the year, director's name, actor's name and his/her role in that production - Duration

Rows:

Query visualization of Find those lowest duration movies along with the year, director's name, actor's name and his/her role in that production - Rows

Cost:

Query visualization of Find those lowest duration movies along with the year, director's name, actor's name and his/her role in that production - Cost

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

Previous: Write a query in SQL to compute a report which contain the genres of those movies with their average time and number of movies for each genres.
Next: Write a query in SQL to find all the years which produced a movie that received a rating of 3 or 4, and sort the result in increasing order.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming