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 the years in which a movie received a rating of 3 or 4. Sort the result in increasing order on movie year.

 mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
    901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
    902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
    903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
    904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
    905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
    906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
    907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
    908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
    909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
    910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
    911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
    912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
    913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
    914 | American Beauty                                    |     1999 |      122 | English         |            | UK
    915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
    916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
    917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
    918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
    919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
    920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
    921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
    922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
    923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
    924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
    926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
    927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
    928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
    925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK

 mov_id | rev_id | rev_stars | num_o_ratings
--------+--------+-----------+---------------
    901 |   9001 |      8.40 |        263575
    902 |   9002 |      7.90 |         20207
    903 |   9003 |      8.30 |        202778
    906 |   9005 |      8.20 |        484746
    924 |   9006 |      7.30 |
    908 |   9007 |      8.60 |        779489
    909 |   9008 |           |        227235
    910 |   9009 |      3.00 |        195961
    911 |   9010 |      8.10 |        203875
    912 |   9011 |      8.40 |
    914 |   9013 |      7.00 |        862618
    915 |   9001 |      7.70 |        830095
    916 |   9014 |      4.00 |        642132
    925 |   9015 |      7.70 |         81328
    918 |   9016 |           |        580301
    920 |   9017 |      8.10 |        609451
    921 |   9018 |      8.00 |        667758
    922 |   9019 |      8.40 |        511613
    923 |   9020 |      6.70 |         13091

Sample Solution:

-- Selecting distinct movie years from the movie and rating tables
SELECT DISTINCT mov_year
-- Joining the movie and rating tables based on the mov_id column
FROM movie, rating
   WHERE movie.mov_id = rating.mov_id 
-- Filtering the result to include only records with rev_stars equal to 3 or 4
   AND rev_stars IN (3, 4)
-- Ordering the result by mov_year in ascending order
ORDER BY mov_year;

Sample Output:

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

Code Explanation :

The said query in SQL which selects the distinct years in which movies received a rating of 3 or 4 stars. The results are sorted in ascending order by year.
This joins the movie and rating tables based on the movie ID and filters the results to only include movies with a rating of 3 or 4 stars.
The results then sorted by year in ascending order.

Alternative Solutions:

Using INNER JOIN and WHERE Clause:

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;

Explanation:

This SQL query retrieves distinct movie years from the movie table where the reviews have a star rating of either 3 or 4. It achieves this by performing an inner join between the movie and rating tables on the mov_id. The WHERE clause filters the results to include only reviews with star ratings 3 or 4. Finally, the results are ordered by movie year.

Using INNER JOIN with USING Clause:

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

Explanation:

This SQL query retrieves distinct movie years from the movie table where the reviews have a star rating of either 3 or 4. It achieves this by performing an inner join between the movie and rating tables using the mov_id. The WHERE clause filters the results to include only reviews with star ratings 3 or 4. Finally, the results are ordered by movie year.

Using NATURAL JOIN and WHERE Clause:

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

Explanation:

This SQL query retrieves distinct movie years from the movie table where the reviews have a star rating of either 3 or 4. It uses a NATURAL JOIN to combine the movie and rating tables based on their common columns. The WHERE clause filters the results to include only reviews with star ratings 3 or 4. Finally, the results are ordered by movie year.

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.