Tricky SQL queries: joining, grouping, having

Question

I have following tables for an SQL test; See SQLFiddle here

> SELECT * FROM `Movie`;
+-----+-------------------------+------+------------------+
| mID | title                   | year | director         |
+-----+-------------------------+------+------------------+
| 101 | Gone with the Wind      | 1939 | Victor Fleming   |
| 102 | Star Wars               | 1977 | George Lucas     |
| 103 | The Sound of Music      | 1965 | Robert Wise      |
| 104 | E.T.                    | 1982 | Steven Spielberg |
| 105 | Titanic                 | 1997 | James Cameron    |
| 106 | Snow White              | 1937 | <null>           |
| 107 | Avatar                  | 2009 | James Cameron    |
| 108 | Raiders of the Lost Ark | 1981 | Steven Spielberg |
+-----+-------------------------+------+------------------+
> SELECT * FROM Rating;
+-----+-----+-------+------------+
| rID | mID | stars | ratingDate |
+-----+-----+-------+------------+
| 201 | 101 | 2     | 2012-01-22 |
| 201 | 101 | 4     | 2013-01-27 |
| 202 | 106 | 4     | <null>     |
| 203 | 103 | 2     | 2008-01-20 |
| 203 | 108 | 4     | 2002-01-12 |
| 203 | 108 | 2     | 2009-01-30 |
| 204 | 101 | 3     | 2010-01-09 |
| 205 | 103 | 3     | 2010-01-27 |
| 205 | 104 | 2     | 2010-01-22 |
| 205 | 108 | 4     | <null>     |
| 206 | 107 | 3     | 2013-01-15 |
| 206 | 106 | 5     | 2014-01-19 |
| 207 | 107 | 5     | 2000-01-20 |
| 208 | 104 | 3     | 1999-01-02 |
+-----+-----+-------+------------+
> SELECT * FROM Reviewer;
+-----+------------------+
| rID | name             |
+-----+------------------+
| 201 | Sarah Martinez   |
| 202 | Daniel Lewis     |
| 203 | Brittany Harris  |
| 204 | Mike Anderson    |
| 205 | Chris Jackson    |
| 206 | Elizabeth Thomas |
| 207 | James Cameron    |
| 208 | Ashley White     |
+-----+------------------+

I have solved all questions, except these two:

1.) For each movie that has at least one rating, find the movie title and total number of stars, the highest star and the person who gave highest star.

What I got:

SELECT m.title, ra.stars, re.name
FROM Movie m
JOIN(
    SELECT R.*
    FROM Rating R
    JOIN(
        SELECT mid, MAX(stars) AS Stars
        FROM Rating
        GROUP BY mid
    ) D ON R.mid = D.mid AND R.Stars = D.Stars
) Ra ON m.mid = ra.mid
JOIN Reviewer re ON ra.rid = re.rid;
+-------------------------+-------+------------------+
| title                   | stars | name             |
+-------------------------+-------+------------------+
| Gone with the Wind      | 4     | Sarah Martinez   |
| Raiders of the Lost Ark | 4     | Brittany Harris  |
| The Sound of Music      | 3     | Chris Jackson    |
| Raiders of the Lost Ark | 4     | Chris Jackson    |
| Snow White              | 5     | Elizabeth Thomas |
| Avatar                  | 5     | James Cameron    |
| E.T.                    | 3     | Ashley White     |
+-------------------------+-------+------------------+

What is missing: I cannot find a way to add SUM(stars) per movie to the table.

2.) 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.

What I got so far:

SELECT title, name
FROM Movie m
JOIN Rating ra ON m.mid = ra.mid
JOIN Reviewer re ON ra.rid = re.rid
GROUP BY title, name
HAVING COUNT(*) > 1;
+-------------------------+-----------------+
| title                   | name            |
+-------------------------+-----------------+
| Gone with the Wind      | Sarah Martinez  |
| Raiders of the Lost Ark | Brittany Harris |
+-------------------------+-----------------+

What is missing: I have all movies that were rated twice by the same reviewer, however I do not know how to filter for cases were the latest review has more stars than the earlier.

I would highly appreciate if anyone could point me in the right direction here. Stack overflow has been extremely helpful today for me :)

EDIT: Add my attempts and what is missing.

Answer 1

This should give you what you want for number 1, for number 2 I would need the data in a sqlfiddle to play around with. In the meantime, I would suggest looking at lag although some combination of first and last might give you what you want. *Note this will not give you the EXACT answer, it is meant to be a reference.

select mov.title, sum(rat.stars), max(rat.stars), rev.name
from Movie mov,
  Rating rat,
  Reviewer rev
where mov.mid = rat.mid
and rat.rid = rev.rid
group by mov.title;