﻿ SQL exercises on movie Database: Determine the years in which at least one movie received a three-star rating - w3resource

# 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.

## Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

## SQL: Tips of the Day

What is the best way to paginate results in SQL Server?

```SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
FROM      Orders
WHERE     OrderDate >= '1980-01-01'
) AS RowConstrainedResult
WHERE   RowNum >= 1
AND RowNum < 20
ORDER BY RowNum
```

Database: SQL Server

Ref: https://bit.ly/3MGrNlk

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook