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
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
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
Sample table: rating
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.