extras

-- 1. Find the names of all reviewers who rated Gone with the Wind. 

select distinct name
from Movie
inner join Rating using(mId)
inner join Reviewer using(rId)
where title = "Gone with the Wind";

-- 2. For any rating where the reviewer is the same as the director of the movie, return the reviewer name, movie title, and number of stars. 

select name, title, stars
from Movie
inner join Rating using(mId)
inner join Reviewer using(rId)
where director = name;

-- 3. Return all reviewer names and movie names together in a single list, alphabetized. (Sorting by the first name of the reviewer and first word in the title is fine; no need for special processing on last names or removing "The")

select title
from Movie
union
select name
from Reviewer
order by name, title;

-- 4. Find the titles of all movies not reviewed by Chris Jackson. 

select title
from Movie
where mId not in (
  select mId
  from Rating
  inner join Reviewer using(rId)
  where name = "Chris Jackson"
);

-- 5. For all pairs of reviewers such that both reviewers gave a rating to the same movie, return the names of both reviewers. 

select distinct Re1.name, Re2.name
from Rating R1, Rating R2, Reviewer Re1, Reviewer Re2
where R1.mID = R2.mID
and R1.rID = Re1.rID
and R2.rID = Re2.rID
and Re1.name < Re2.name
order by Re1.name, Re2.name;

-- 6. For each rating that is the lowest (fewest stars) currently in the database, return the reviewer name, movie title, and number of stars.

select name, title, stars
from Movie
inner join Rating using(mId)
inner join Reviewer using(rId)
where stars = (select MIN(stars) from Rating);

-- 7. List movie titles and average ratings, from highest-rated to lowest-rated. If two or more movies have the same average rating, list them in alphabetical order.

select title, AVG(stars) as average
from Movie
inner join Rating using(mId)
group by mId
order by average desc, title;

-- 8. Find the names of all reviewers who have contributed three or more ratings.

select name
from Reviewer
where (select count(*) from Rating where Rating.rId = Reviewer.rId) >= 3;

-- 9. Some directors directed more than one movie. For all such directors, return the titles of all movies directed by them, along with the director name. Sort by director name, then movie title.

select title, director
from Movie M1
where (select count(*) from Movie M2 where M1.director = M2.director) > 1
order by director, title;

-- 10. Find the movie(s) with the highest average rating. Return the movie title(s) and average rating.

select title, AVG(stars) AS average
from Movie
inner join Rating using(mId)
group by mId
having average = (
  select MAX(average_stars)
  from (
    select title, AVG(stars) as average_stars
    from Movie
    inner join Rating using(mId)
    group by mId
  )
);

-- 11. Find the movie(s) with the lowest average rating. Return the movie title(s) and average rating.

select title, AVG(stars) AS average
from Movie
inner join Rating using(mId)
group by mId
having average = (
  select MIN(average_stars)
  from (
    select title, AVG(stars) as average_stars
    from Movie
    inner join Rating using(mId)
    group by mId
  )
);

-- 12. For each director, return the director's name together with the title(s) of the movie(s) they directed that received the highest rating among all of their movies, and the value of that rating. Ignore movies whose director is NULL.

select director, title, MAX(stars)
from Movie
inner join Rating using(mId)
where director is not NULL
group by director;