w3resource

SQL exercises on movie Database: Find movie title and number of stars for each movie that has at least one rating and find the highest number of stars that movie received

SQL movie Database: Join Exercise-14 with Solution

14. From the following table, write a SQL query to find those movies that have at least one rating and received the most stars. Sort the result-set on movie title. Return movie title and maximum review stars.

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 mov_title and the maximum rev_stars for each movie
SELECT mov_title, MAX(rev_stars)
-- Joining the movie and rating tables using the mov_id column
FROM movie
INNER JOIN rating USING(mov_id)
-- Grouping the result by mov_title to perform aggregate functions on each group
GROUP BY mov_title 
-- Filtering the grouped result to include only groups with a maximum rev_stars greater than 0
HAVING MAX(rev_stars) > 0
-- Ordering the result by mov_title in ascending order
ORDER BY mov_title;

Sample Output:

                     mov_title                      | max
----------------------------------------------------+------
 Aliens                                             | 8.40
 American Beauty                                    | 7.00
 Annie Hall                                         | 8.10
 Avatar                                             | 7.30
 Beyond the Sea                                     | 6.70
 Blade Runner                                       | 8.20
 Boogie Nights                                      | 3.00
 Braveheart                                         | 7.70
 Donnie Darko                                       | 8.10
 Good Will Hunting                                  | 4.00
 Lawrence of Arabia                                 | 8.30
 Princess Mononoke                                  | 8.40
 Slumdog Millionaire                                | 8.00
 The Innocents                                      | 7.90
 The Usual Suspects                                 | 8.60
 Titanic                                            | 7.70
 Vertigo                                            | 8.40
(17 rows)

Code Explanation :

The said query in SQL that selects the movie title and maximum rating stars for each movie, where the maximum rating stars are greater than 0. The results are then ordered by movie title in ascending order.
The movie table joins with rating table using mov_id column.
The GROUP BY clause is used to group the results by mov_title so that the MAX function can be used to get the maximum rating stars for each movie. The HAVING clause is used to filter out movies that have a maximum rating of 0 or no ratings at all. Finally, the ORDER BY clause is used to sort the results by movie title in ascending order.

Alternative Solutions:

Using JOIN with ON Clause, GROUP BY, and HAVING Clause:


SELECT mov_title, MAX(rev_stars)
FROM movie
JOIN rating ON movie.mov_id = rating.mov_id
GROUP BY mov_title 
HAVING MAX(rev_stars) > 0
ORDER BY mov_title;

Explanation:

This SQL query combines the movie and rating tables using a JOIN with explicit ON clauses. It then groups the results by mov_title using GROUP BY. The HAVING clause filters the groups to include only those with the maximum review stars greater than 0. Finally, the results are ordered by movie title.

Using WHERE Clause with Table Aliases, GROUP BY, and HAVING Clause:


SELECT m.mov_title, MAX(r.rev_stars)
FROM movie m, rating r
WHERE m.mov_id = r.mov_id
GROUP BY m.mov_title 
HAVING MAX(r.rev_stars) > 0
ORDER BY m.mov_title;

Explanation:

This SQL query uses the comma-separated syntax for joining tables and specifies the join condition in the WHERE clause. It then groups the results by mov_title using GROUP BY. The HAVING clause filters the groups to include only those with the maximum review stars greater than 0. Finally, the results are ordered by movie title.

Relational Algebra Expression:

Relational Algebra Expression: Find movie title and number of stars for each movie that has at least one rating and find the highest number of stars that movie received.

Relational Algebra Tree:

Relational Algebra Tree: Find movie title and number of stars for each movie that has at least one rating and find the highest number of stars that movie received.

Practice Online


Movie database model

Query Visualization:

Duration:

Query visualization of Find movie title and number of stars for each movie that has at least one rating and find the highest number of stars that movie received - Duration

Rows:

Query visualization of Find movie title and number of stars for each movie that has at least one rating and find the highest number of stars that movie received - Rows

Cost:

Query visualization of Find movie title and number of stars for each movie that has at least one rating and find the highest number of stars that movie received - 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 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.
Next: From the following tables, write a SQL query to find those movies, which have received ratings. Return movie title, director first name, director last name and review 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.