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);