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
SELECT DISTINCT mov_year FROM movie WHERE mov_id IN ( SELECT mov_id FROM rating WHERE rev_stars>3) ORDER BY mov_year;
mov_year ---------- 1958 1961 1962 1977 1982 1986 1995 1997 1999 2001 2004 2008 2009 (13 rows)
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.
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.
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
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
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