w3resource

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.

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 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 Expression: Find all the years which produced a movie that received a rating of 3 or 4.

Relational Algebra Tree:

Relational Algebra Tree: Find all the years which produced a movie that received a rating of 3 or 4.

Relational Algebra Expression:

Relational Algebra Expression: Find all the years which produced a movie that received a rating of 3 or 4.

Relational Algebra Tree:

Relational Algebra Tree: Find all the years which produced a movie that received a rating of 3 or 4.

Relational Algebra Expression:

Relational Algebra Expression: Find all the years which produced a movie that received a rating of 3 or 4.

Relational Algebra Tree:

Relational Algebra Tree: Find all the years which produced a movie that received a rating of 3 or 4.

Relational Algebra Expression:

Relational Algebra Expression: Find all the years which produced a movie that received a rating of 3 or 4.

Relational Algebra Tree:

Relational Algebra Tree: Find all the years which produced a movie that received a rating of 3 or 4.

Practice Online


Movie database model

Query Visualization for Sample Solution:

Duration:

Query visualization of Find all the years which produced a movie that received a rating of 3 or 4 - Duration

Rows:

Query visualization of Find all the years which produced a movie that received a rating of 3 or 4 - Rows

Cost:

Query visualization of Find all the years which produced a movie that received a rating of 3 or 4 - Cost

Query Visualization for alternate Sample Solution:

Duration:

Query visualization of Find all the years which produced a movie that received a rating of 3 or 4 - Duration

Rows:

Query visualization of Find all the years which produced a movie that received a rating of 3 or 4 - Rows

Cost:

Query visualization of Find all the years which produced a movie that received a rating of 3 or 4 - Cost

Query Visualization for second alternate Sample Solution:

Duration:

Query visualization of Find all the years which produced a movie that received a rating of 3 or 4 - Duration

Rows:

Query visualization of Find all the years which produced a movie that received a rating of 3 or 4 - Rows

Cost:

Query visualization of Find all the years which produced a movie that received a rating of 3 or 4 - Cost

Query Visualization for third alternate Sample Solution:

Duration:

Query visualization of Find all the years which produced a movie that received a rating of 3 or 4 - Duration

Rows:

Query visualization of Find all the years which produced a movie that received a rating of 3 or 4 - Rows

Cost:

Query visualization of Find all the years which produced a movie that received a rating of 3 or 4 - 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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.