Please note, this is a STATIC archive of website www.w3resource.com from 19 Jul 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
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 find those years, which produced at least one movie and that, received a rating of more than 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)

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.



SQL: Tips of the Day

SQL Server SELECT into existing table. 

INSERT INTO dbo.TABLETWO
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:

INSERT INTO dbo.TABLETWO
  (col1, col2)
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

Database: SQL Server

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