Homework

Homework 1. List all movies and their average ratings. You need to have a column called “Movie Title” and a column called “average rating”; SELECT MOVIETITLE, AVG(RATING) AS “AVERAGE RATING” FROM RATING WHERE MOVIEID IN); -- 2. List the average rating for each horror movie. You need to have a column called “Movie Title” and a column called “average rating”; SELECT MOVIE.MOVIETITLE, AVG(RATING) AS “AVERAGE RATING” FROM MOVIE, rating INNER JOIN RATING ON MOVIE.MOVIEID=RATING.MOVIEID WHERE MOVIE.HORROR=1 ORDER BY MOVIE.MOVIEID; -- 3. List the average rating by occupation. You need to have a column called “Occupation” and a column called “average rating”. Sort your output by occupation, then by rating; SELECT AVG(RATING) AS “AVERAGE RATING”, OCCUPATION FROM RATING R INNER JOIN VIEWER ON R.USERID=VIEWER.USERID ORDER BY VIEWER.OCCUPATION, R.AVERAGE RATING; -- 4. List all “bad” movies: these are the movies with average rating less than 2. You need to have a column called “Movie Title” and a column called “average rating”. Sort your results by rating in decreasing order; SELECT AVG(RATING) as “AVERAGE_RATING”, MOVIE.MOVIETITLE FROM RATING R left outer JOIN MOVIE ON R.MOVIEID=MOVIE.MOVIEID WHERE R.AVERAGE_RATING<2 order BY AVERAGE_RATING DESC; --5. List all movies released in 1990 and the LOWEST rating for each of them. You need to have a column called “Movie Title” and a column called “worst rating”. Sort your results by rating in decreasing order; SELECT MOVIETITLE, MIN(RATING) FROM MOVIE WHERE MOVIETITLE LIKE ?�%1991%’ JOIN RATING ON MOVIE.MOVIEID=RATING.MOVIEID GROUP BY MIN(RATING); --6. Find all movies with a PERFECT score, i.e., movies that received a rating of 5 from ALL reviewers who rated it. You need to just list the name of the movies (Think about how to represent a PERFECT movie);