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.

 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' table
-- Using a subquery to find mov_id from the 'rating' table based on a condition
-- Specifically, finding mov_id where rev_stars (rating stars) are greater than 3
-- Ordering the result by mov_year in ascending order
SELECT DISTINCT mov_year 
FROM movie 
WHERE mov_id IN (
  -- Subquery to find mov_id based on the condition of rev_stars
  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.

Alternative Solutions:

Using EXISTS:

SELECT DISTINCT m.mov_year 
FROM movie m
WHERE EXISTS (
    SELECT 1
    FROM rating r
    WHERE r.mov_id = m.mov_id AND r.rev_stars > 3
)
ORDER BY m.mov_year;

Explanation:

This solution uses the EXISTS clause to check if there exists a record in the rating table with a rev_stars greater than 3 for a particular movie. If such a record exists, the movie's mov_year is included in the result set.

Using INNER JOIN:

SELECT DISTINCT m.mov_year 
FROM movie m
JOIN rating r ON m.mov_id = r.mov_id
WHERE r.rev_stars > 3
ORDER BY m.mov_year;

Explanation:

This solution employs an inner join between the movie and rating tables based on the mov_id. It filters the result set to include only movies with ratings greater than 3 stars and then selects the distinct mov_year values.

Using EXISTS with Correlated Subquery:

SELECT DISTINCT m.mov_year 
FROM movie m
WHERE EXISTS (
    SELECT 1
    FROM rating r
    WHERE r.mov_id = m.mov_id AND r.rev_stars > 3
)
ORDER BY m.mov_year;

Explanation:

This solution is uses a correlated subquery. It checks for the existence of a record in the rating table with a rev_stars greater than 3 for each movie in the movie table. If such a record exists, the movie's mov_year is included in the result set.

Practice Online

Query Visualization:

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 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.