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:

SELECT mov_year,gen_title,count(gen_title), avg(rev_stars)
FROM movie 
NATURAL JOIN movie_genres 
NATURAL JOIN genres
NATURAL JOIN rating
WHERE gen_title='Mystery' 
GROUP BY mov_year,gen_title;

Sample Output:

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

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.