SQL exercises on movie Database: Find all the years which produced a movie that received a rating of 3 or 4

SQL movie Database: Join Exercise-12 with Solution

12. From the following table, write a SQL query to find those years when a movie received a rating of 3 or 4. Sort the result in increasing order on movie year. Return move year.

Sample table: movie

Sample table: rating

Sample Solution:

SELECT DISTINCT mov_year
FROM movie, rating
WHERE movie.mov_id = rating.mov_id 
AND rev_stars IN (3, 4)
ORDER BY mov_year;

OR

SELECT DISTINCT mov_year
FROM movie
INNER JOIN rating 
ON movie.mov_id = rating.mov_id
WHERE rev_stars IN (3, 4)
ORDER BY mov_year;

OR

SELECT DISTINCT mov_year
FROM movie
INNER JOIN rating USING(mov_id)
WHERE rev_stars IN (3,4)
ORDER BY mov_year;

OR

SELECT DISTINCT mov_year
FROM movie NATURAL JOIN rating
WHERE rev_stars IN (3, 4)
ORDER BY mov_year;

Sample Output:

 mov_year
----------
     1997
(1 row)

Relational Algebra Expression:

Relational Algebra Tree:

Relational Algebra Expression:

Relational Algebra Tree:

Relational Algebra Expression:

Relational Algebra Tree:

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Query Visualization for Sample Solution:

Duration:

Rows:

Cost:

Query Visualization for alternate Sample Solution:

Duration:

Rows:

Cost:

Query Visualization for second alternate Sample Solution:

Duration:

Rows:

Cost:

Query Visualization for third alternate Sample Solution:

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 movies with the lowest duration. Return movie title, movie year, director first name, last name, actor first name, last name and role.
Next: From the following tables, write a SQL query to get the reviewer name, movie title, and stars in an order that reviewer name will come first, then by movie title, and lastly by number of stars.