/*Question 1:Find the titles of all movies directed by Steven Spielberg.select title from movie where director='Steven Spielberg'Question 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,ratingwhere movie.mid=rating.midand stars in (4,5)order by yearQuestion 3:Find the titles of all movies that have no ratingsselect title from moviewhere mID in(select mid from Movieexceptselect mid from rating)select title from movieexceptselect title from movie,ratingwhere movie.mid=rating.midQuestion 4:Some reviewers didn't provide a date withtheir rating. Find the names of all reviewers who haveratings with a NULL value for the date.select name from reviewer,ratingwhere reviewer.rid=rating.ridand ratingdate is nullQuestion 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,ratingdatefrom movie,rating,reviewerwhere movie.mid=rating.midand reviewer.rid=rating.ridorder by name,title,starsQuestion 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.select name,titlefrom rating as F,Rating as S,Movie,Reviewerwhere F.rID=S.rIDand F.mID=S.mIDand F.stars<S.starsand F.ratingDate<S.ratingDateand F.mID=Movie.mIDand F.rID=Reviewer.rIDselect name,titlefrom movie,reviewer,( select r1.rid,r1.mid from rating as r1,rating as r2where r1.rid=r2.rid and r1.mid=r2.midand r1.ratingdate>r2.ratingdateand r1.stars>r2.stars) as rwhere movie.mid=r.mid and reviewer.rid=r.ridQuestion 7:For each movie, find the highest number of stars that movie received as a rating. Return the movie title and number of stars. Sort by movie title.select title,MAX(stars) as max_starfrom rating,Moviewhere Rating.mID=Movie.mIDgroup by titleorder by titleselect title,maxratingfrom movie,(select mid,max(stars) as maxrating from ratinggroup by mid) as rwhere movie.mid=r.midorder by titleQuestion 8:For each movie, return the title and the‘rating spread(范围)', that is, the difference betweenhighest 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_spreadfrom rating,Moviewhere Rating.mID=Movie.mIDgroup by titleorder by rating_spread desc,titleselect title,spread as "rating spread"from movie, (select mid,max(stars)-min(stars) asspread from rating group by mid) as rwhere movie.mid=r.midorder by spread desc,titleQuestion 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.)select F.avg_star - S.avg_starfrom(select AVG(avg_star) as avg_star from(select Rating.mID,AVG(stars) as avg_starfrom Rating left join Movie on Rating.mID=Movie.mIDwhere year<1980group by Rating.mID) as F) as F,(select AVG(avg_star) as avg_star from(select Rating.mID,AVG(stars) as avg_starfrom Rating left join Movie on Rating.mID=Movie.mIDwhere year>=1980group by Rating.mID) as S) as Sselect r1979.avgm-r1980.avgmfrom(select sum(avgrating)/count(*) as avgMfrom(select avg(stars) as avgrating from rating,moviewhere movie.mid=rating.mid and year<1980group by movie.mid)as r)as r1979,(select sum(avgrating)/count(*) as avgMfrom(select avg(stars) as avgrating from rating,moviewhere movie.mid=rating.mid and year>=1980group by movie.mid)as r)as r1980Question 10:Add the reviewer Roger Ebert to your database, with an rID of 209.insert into Reviewervalues (209,'Roger Ebert')Question 11:Insert 5-star ratings by James Cameron for all moviesin the database. Leave the review date as NULL.insert into Rating(rID,mID,stars)select rid,mID,5 as strasfrom movie,(select rid from Reviewer where name='James Cameron') as Tinsert into rating(rid,mid,stars)select rid,mid,5from reviewer,moviewhere name='James Cameron'Question 12:For all movies that have an average rating of 4 stars or higher, add 25 to the release year.(Update the existing tuples; don't insert new tuples.)update Movie set year=year+25where mID in(select mID from Rating group by mID having AVG(stars)>=4) */。