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.

Practice Online


Movie database model

Query Visualization:

Duration:

Query visualization of 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 - Duration

Rows:

Query visualization of 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 - Rows

Cost:

Query visualization of 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 - 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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

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