SQL exercises on movie Database: Compute a report which shows the year when most of the Mystery movies produces, and number of such movies and their average rating

SQL movie Database: Join Exercise-23 with Solution

23. From the following tables, write a SQL query to find the years when most of the ‘Mystery Movies’ produced. Count the number of generic title and compute their average rating. Group the result set on movie release year, generic title. Return movie year, generic title, number of generic title and average rating.

Sample table: movie

Sample table: genres

Sample table: movie_genres

Sample table: rating

Sample Solution:

-- Selecting mov_year, gen_title, the count of gen_title, and the average rev_stars
FROM movie 
NATURAL JOIN movie_genres 
NATURAL JOIN genres
NATURAL JOIN rating
-- Filtering the result to include only records where gen_title is 'Mystery'
WHERE gen_title='Mystery' 
-- Grouping the result by mov_year and gen_title to perform aggregate functions on each group
GROUP BY mov_year, gen_title;

Sample Output:

 mov_year |      gen_title       | count |        avg
----------+----------------------+-------+--------------------
     1958 | Mystery              |     1 | 8.4000000000000000
(1 row)

Code Explanation :

The said query in SQL that retrieves the count of movies and average rating for the Mystery genre in each year.
The NATURAL JOIN keyword is used to join the tables based on their common column names. This avoids having to explicitly specify the join conditions.
The WHERE clause filters the rows which limits the results to only movies that belong to the 'Mystery' genre.
The GROUP BY clause groups the results by mov_year and gen_title, which means that the count and average rating will be calculated for each unique combination of year and genre.

Alternative Solution:

Using a Self-Join:

SELECT m1.mov_year, g1.gen_title, 
       COUNT(g1.gen_title), AVG(r1.rev_stars)
FROM movie m1
JOIN movie_genres mg1 ON m1.mov_id = mg1.mov_id
JOIN genres g1 ON mg1.gen_id = g1.gen_id
JOIN rating r1 ON m1.mov_id = r1.mov_id
JOIN movie m2 ON m1.mov_year = m2.mov_year
JOIN movie_genres mg2 ON m2.mov_id = mg2.mov_id
JOIN genres g2 ON mg2.gen_id = g2.gen_id
WHERE g1.gen_title = 'Mystery' 
      AND g2.gen_title = 'Mystery'
GROUP BY m1.mov_year, g1.gen_title;

Explanation:

This SQL query involves a self-join on the movie table (m1 and m2). It selects movies with the genre 'Mystery' and groups them by year and genre title.

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 highest-rated ‘Mystery Movies’. Return the title, year, and rating.
Next: From the following tables, write a query in SQL to generate a report, which contain the fields movie title, name of the female actor, year of the movie, role, movie genres, the director, date of release, and rating of that movie.