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