Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- 1. Find the titles of all movies directed by Steven Spielberg.
- SELECT title
- FROM Movie
- WHERE director = 'Steven Spielberg';
- -- 2. 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, Rating
- WHERE Movie.mId = Rating.mId AND stars IN (4, 5)
- ORDER BY year;
- SELECT DISTINCT year
- FROM Movie
- INNER JOIN Rating ON Movie.mId = Rating.mId
- WHERE stars IN (4, 5)
- ORDER BY year;
- SELECT DISTINCT year
- FROM Movie
- INNER JOIN Rating USING(mId)
- WHERE stars IN (4, 5)
- ORDER BY year;
- SELECT DISTINCT year
- FROM Movie NATURAL JOIN Rating
- WHERE stars IN (4, 5)
- ORDER BY year;
- -- 3. Find the titles of all movies that have no ratings.
- SELECT title
- FROM Movie
- WHERE mId NOT IN (SELECT mID FROM Rating);
- -- 4. 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 name
- FROM Reviewer
- INNER JOIN Rating USING(rId)
- WHERE ratingDate IS NULL;
- # BY MYSELF
- SELECT title FROM movie
- LEFT join Rating
- on movie.mID = Rating.mID
- where stars is NULL
- -- 5. 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, Rating, Reviewer
- WHERE Movie.mId = Rating.mId AND Reviewer.rId = Rating.rId
- ORDER BY name, title, stars;
- SELECT name, title, stars, ratingDate
- FROM Movie
- INNER JOIN Rating ON Movie.mId = Rating.mId
- INNER JOIN Reviewer ON Reviewer.rId = Rating.rId
- ORDER BY name, title, stars;
- SELECT name, title, stars, ratingDate
- FROM Movie
- INNER JOIN Rating USING(mId)
- INNER JOIN Reviewer USING(rId)
- ORDER BY name, title, stars;
- SELECT name, title, stars, ratingDate
- FROM Movie NATURAL JOIN Rating NATURAL JOIN Reviewer
- ORDER BY name, title, stars;
- #### BY MYSELF
- SELECT name, title, stars, ratingdate from (
- SELECT mID, stars, ratingdate,name from Rating
- join Reviewer
- on Rating.rID = reviewer.rID) as A
- join Movie
- on Movie.mID = A.mID
- order by name, title, stars
- #### BY MYSELF
- -- 6. 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.
- # HOW TO ENSURE THAT THE MOVIE IS REVIEWED TWICE NOT THREE TIMES OR SO?
- SELECT name, title
- FROM Movie
- INNER JOIN Rating R1 USING(mId)
- INNER JOIN Rating R2 USING(rId)
- INNER JOIN Reviewer USING(rId)
- WHERE R1.mId = R2.mId AND R1.ratingDate < R2.ratingDate AND R1.stars < R2.stars;
- SELECT name, title
- FROM Movie
- INNER JOIN Rating R1 USING(mId)
- INNER JOIN Rating R2 USING(rId, mId)
- INNER JOIN Reviewer USING(rId)
- WHERE R1.ratingDate < R2.ratingDate AND R1.stars < R2.stars;
- -- 7. 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 title, MAX(stars)
- FROM Movie
- INNER JOIN Rating USING(mId)
- GROUP BY mId
- ORDER BY title;
- -- 8. 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.
- # My CODING
- SELECT title, max(Rating.stars)-min(Rating.stars) as rating_spread
- FROM movie join rating using (mID)
- group by Rating.mID
- order by rating_spread DESC, title
- -- 9. 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.)
- # MY CODING
- SELECT max(average)-min(average) FROM(
- SELECT avg(avg_stars) as average FROM
- (SELECT title,
- CASE
- when year< 1980 then "before"
- when year>=1980 then "after"
- end as before_or,
- AVG(stars) as avg_stars
- FROM movie
- join rating
- using (mID)
- group by mID)
- group by before_or)
- SELECT AVG(Before1980.avg) - AVG(After1980.avg)
- FROM (
- SELECT AVG(stars) AS avg
- FROM Movie
- INNER JOIN Rating USING(mId)
- WHERE year < 1980
- GROUP BY mId
- ) AS Before1980, (
- SELECT AVG(stars) AS avg
- FROM Movie
- INNER JOIN Rating USING(mId)
- WHERE year > 1980
- GROUP BY mId
- ) AS After1980;
Add Comment
Please, Sign In to add comment