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
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 Tree:
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 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.