SQL_Movie-Rating_Query_Exercises.txt

SCHEMA:

Movie ( mID, title, year, director ) 
English: There is a movie with ID number mID, a title, a release year, and a director. 

Reviewer ( rID, name ) 
English: The reviewer with ID number rID has a certain name. 

Rating ( rID, mID, stars, ratingDate ) 
English: The reviewer rID gave the movie mID a number of stars rating (1-5) on a certain ratingDate.

Exercises:

Q1.
1/1 point (graded)
Find the titles of all movies directed by Steven Spielberg.

SELECT title
FROM Movie 
WHERE director = 'Steven Spielberg'


Q2.
1/1 point (graded)
Find all years that have a movie that received a rating of 4 or 5, and sort them in increasing order.

SELECT DISTINCT year
FROM Movie 
WHERE mID in (SELECT mID from Rating WHERE stars >= 4)
ORDER BY year;


Q3
1/1 point (graded)
Find the titles of all movies that have no ratings.

SELECT DISTINCT Title
FROM Movie left outer JOIN Rating
ON Movie.Mid = Rating.Mid
WHERE stars is NULL

Q4
1/1 point (graded)
Some reviewers didn't provide a date with their rating. Find the names of all reviewers who have ratings with a NULL value for the date.

SELECT DISTINCT name 
FROM Rating join Reviewer 
ON Rating.rID = Reviewer.rID
WHERE ratingDATE is NULL


Q5
1/1 point (graded)
Write a query to return the ratings data in a more readable format: reviewer name, movie title, stars, and ratingDate. Also, sort the data, first by reviewer name, then by movie title, and lastly by number of stars.

SELECT name, title, stars, ratingDate 
FROM Movie
JOIN Rating 
ON Movie.mID = Rating.mID
JOIN Reviewer 
ON Reviewer.rID = Rating.rID
ORDER BY name, title, stars;

Q6
1/1 point (graded)
For all cases where the same reviewer rated the same movie twice and gave it a higher rating the second time, return the reviewer's name and the title of the movie.

SELECT name,title
FROM Movie
INNER JOIN Rating USING(mId)
INNER JOIN Rating R2 USING(mId, rId)
INNER JOIN Reviewer USING(rId)
WHERE Rating.stars > R2.stars and Rating.ratingDate > R2.ratingDate

Q7
1/1 point (graded)
For each movie that has at least one rating, find the highest number of stars that movie received. Return the movie title and number of stars. Sort by movie title.


select distinct title,  max(Rating.stars)
FROM Movie inner JOIN Rating using(mID)
GROUP BY title
ORDER BY title

Q8
1/1 point (graded)
For each movie, return the title and the 'rating spread', that is, the difference between highest and lowest ratings given to that movie. Sort by rating spread from highest to lowest, then by movie title.

SELECT title, max(stars) - min(stars) as 'rating_spread'
FROM Movie inner JOIN Rating using(mID)
GROUP BY Movie.title
order by rating_spread desc, title

Q9
1/1 point (graded)
Find the difference between the average rating of movies released before 1980 and the average rating of movies released after 1980. (Make sure to calculate the average rating for each movie, then the average of those averages for movies before 1980 and movies after. Don't just calculate the overall average rating before and after 1980.)