w3resource

SQL exercises on movie Database: Compute a report which contain the genres of those movies with their average time and number of movies for each genres

SQL movie Database: Join Exercise-10 with Solution

10. From the following table, write a SQL query to calculate the average movie length and count the number of movies in each genre. Return genre title, average time and number of movies for each genre.

Sample table: movie
 mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
    901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
    902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
    903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
    904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
    905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
    906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
    907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
    908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
    909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
    910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
    911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
    912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
    913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
    914 | American Beauty                                    |     1999 |      122 | English         |            | UK
    915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
    916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
    917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
    918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
    919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
    920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
    921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
    922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
    923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
    924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
    926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
    927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
    928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
    925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK
Sample table: genres
 gen_id |      gen_title
--------+----------------------
   1001 | Action
   1002 | Adventure
   1003 | Animation
   1004 | Biography
   1005 | Comedy
   1006 | Crime
   1007 | Drama
   1008 | Horror
   1009 | Music
   1010 | Mystery
   1011 | Romance
   1012 | Thriller
   1013 | War

Sample table: movie_genres

 mov_id | gen_id
--------+--------
    922 |   1001
    917 |   1002
    903 |   1002
    912 |   1003
    911 |   1005
    908 |   1006
    913 |   1006
    926 |   1007
    928 |   1007
    918 |   1007
    921 |   1007
    902 |   1008
    923 |   1009
    907 |   1010
    927 |   1010
    901 |   1010
    914 |   1011
    906 |   1012
    904 |   1013

Sample Solution:

-- Selecting columns: gen_title, the average of mov_time, and the count of gen_title
SELECT gen_title, AVG(mov_time), COUNT(gen_title) 
-- Joining the movie, movie_genres, and genres tables using natural joins
FROM movie
NATURAL JOIN  movie_genres
NATURAL JOIN  genres
-- Grouping the result by gen_title to perform aggregate functions on each group
GROUP BY gen_title;

Sample Output:

      gen_title       |         avg          | count
----------------------+----------------------+-------
 Adventure            | 162.5000000000000000 |     2
 Comedy               |  93.0000000000000000 |     1
 Drama                | 134.2500000000000000 |     4
 Horror               | 100.0000000000000000 |     1
 Thriller             | 117.0000000000000000 |     1
 Crime                | 124.0000000000000000 |     2
 Action               | 137.0000000000000000 |     1
 Music                | 118.0000000000000000 |     1
 War                  | 183.0000000000000000 |     1
 Romance              | 122.0000000000000000 |     1
 Animation            | 134.0000000000000000 |     1
 Mystery              | 137.3333333333333333 |     3
(12 rows)

Code Explanation :

The said query in SQL that selects the genre title, average movie duration, and the count of movies for each genre by joining the movie, movie_genres, and genres tables. The results are grouped by genre title.
The joining happens by the tables movie, movie_genres, and genres based on the common columns in each table that is movie ID and genre ID.
This groups the results by genre title.

Alternative Solutions:

Using INNER JOIN and GROUP BY:


SELECT g.gen_title, AVG(m.mov_time) AS avg_mov_time, COUNT(g.gen_title) AS gen_count
FROM movie m
JOIN movie_genres mg ON m.mov_id = mg.mov_id
JOIN genres g ON mg.gen_id = g.gen_id
GROUP BY g.gen_title;

Explanation:

This query uses INNER JOINs to combine the movie, movie_genres, and genres tables based on their respective IDs. It then applies a GROUP BY clause to group the results by genre title . It calculates the average movie time and counts the number of genres .

Using WHERE Clause with Table Aliases and GROUP BY:


SELECT g.gen_title, AVG(m.mov_time) AS avg_mov_time, COUNT(g.gen_title) AS gen_count
FROM movie m, movie_genres mg, genres g
WHERE m.mov_id = mg.mov_id
  AND mg.gen_id = g.gen_id
GROUP BY g.gen_title;

Explanation:

This query uses the older comma-separated syntax for joining tables and specifies the join conditions in the WHERE clause. It then applies a GROUP BY clause to group the results by genre title . It calculates the average movie time and counts the number of genres .

Relational Algebra Expression:

Relational Algebra Expression: Compute a report which contain the genres of those movies with their average time and number of movies for each genres.

Relational Algebra Tree:

Relational Algebra Tree: Compute a report which contain the genres of those movies with their average time and number of movies for each genres.

Practice Online


Movie database model

Query Visualization:

Duration:

Query visualization of Compute a report which contain the genres of those movies with their average time and number of movies for each genres - Duration

Rows:

Query visualization of Compute a report which contain the genres of those movies with their average time and number of movies for each genres - Rows

Cost:

Query visualization of Compute a report which contain the genres of those movies with their average time and number of movies for each genres - 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 find the movies released before 1st January 1989. Sort the result-set in descending order by date of release. Return movie title, release year, date of release, duration, and first and last name of the director.
Next: From the following tables, 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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql-exercises/movie-database-exercise/sql-exercise-movie-database-23.php