Q1:
select title
from Movie
where director = "Steven Spielberg";
Q2:
select distinct year
from Movie join Rating using(mID)
where stars = 4 or stars = 5 order by year;
Q3:
select title
from Movie
where Movie.mID not in (select Rating.mID from Rating where stars >= 0);
Q4:
select Reviewer.name
from (Movie join Rating on Movie.mID = Rating.mID)
join Reviewer on Rating.rID = Reviewer.rID
where ratingDate is null;
Q5:
select Reviewer.name as reviewer_name, Movie.title as movie_title, stars, ratingDate
from (Movie join Rating on Movie.mID = Rating.mID)
join Reviewer on Reviewer.rID = Rating.rID order by reviewer.name ASC, Movie.title ASC, stars ASC;
Q6:
select distinct R1.name, M.title
from (Rating as Ra1 join Reviewer R1 on Ra1.rID = R1.rID) join Movie as M on M.mID = Ra1.mID,
Rating as Ra2 join Reviewer R2 on Ra2.rID = R2.rID
where Ra1.rID = Ra2.rID and Ra1.mID = Ra2.mID
and Ra1.ratingDate < Ra2.ratingDate
and Ra2.stars > Ra1.stars;
Q7:
select Movie.title, MAX(stars)
from Movie join Rating on Movie.mID = Rating.mID group by Rating.mID order by Movie.title ASC;
Q8:
select Movie.title, max(stars) - min(stars) as spread
from Movie inner join Rating on Movie.mID = Rating.mID group by Rating.mID order by spread DESC, Movie.title ASC;
Q9:
select avg(astars) - avg(bstars)
from (select avg(stars) as astars
from Movie join Rating on Rating.mID = Movie.mID
where year < 1980 group by Rating.mID),
(select avg(stars) as bstars
from Movie join Rating on Rating.mID = Movie.mID
where year > 1980 group by Rating.mID);