SQL exercises on movie Database: Find all the years which produced at least one movie and that received a rating of more than 3 stars and the result should come in increasing order or year

SQL movie Database: Subquery Exercise-6 with Solution

6. From the following tables, write a SQL query to determine those years in which there was at least one movie that received a rating of at least three stars. Sort the result-set in ascending order by movie year. Return movie year.

Sample table: movie

Sample table: rating

Sample Solution:

SELECT DISTINCT mov_year 
FROM movie 
WHERE mov_id IN (
SELECT mov_id 
FROM rating 
WHERE rev_stars>3) 
ORDER BY mov_year;

Sample Output:

 mov_year
----------
     1958
     1961
     1962
     1977
     1982
     1986
     1995
     1997
     1999
     2001
     2004
     2008
     2009
(13 rows)

Code Explanation:

The said query in SQL that selects distinct movie years from the 'movie' table where the corresponding movie IDs have at least one review with a rating greater than 3. The results are ordered by the movie year in ascending order.
The query uses a subquery to find the movie IDs with high ratings, and then selects the distinct movie years corresponding to those IDs. The "DISTINCT" keyword ensures that only unique movie years are returned.

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 those movies directed by the director whose first name is 'Woddy' and last name is 'Allen'. Return movie title.
Next: From the following table, write a SQL query to find those movies, which have no ratings. Return movie title.