title: How to get results of this particular "query"
body: <p><strong>NOTE: I accidentally put another question's sentence in here (massive apologies on my part), I have updated this post as of Wednesday 14th March at 23:21pm with the correct question.</strong></p>
  
  <p>I have spent a few hours trying to figure out this question without anyone's help but have realised I have wasted too much productive time and should've asked someone sooner. I had a decent crack at this and have come so close but cannot get the final solution I need. What I am supposed to get is: </p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>This is the query I managed to get here:</p>
  
  <pre><code>SELECT reviewer.name, movie.title, rating.stars  
  FROM (reviewer JOIN rating ON reviewer.rid = rating.rid)  
  JOIN movie ON movie.mid = rating.mid  
  GROUP BY reviewer.name 
  HAVING COUNT(*) &gt;= 2  
  ORDER BY reviewer.name DESC
  </code></pre>
  
  <p>(I have a feeling there is a missing WHERE clause from the above query, but am not sure where to place it)</p>
  
  <p>(From what I have learned, RIGHT and FULL OUTER JOINs are not currently supported in SQLite)</p>
  
  <p>And here are the tables and data (in pictures)...</p>
  
  <p><img src="https://i.stack.imgur.com/yn6BS.png" alt="Movie"></p>
  
  <p><img src="https://i.stack.imgur.com/hBBdS.png" alt="Reviewer"></p>
  
  <p><img src="https://i.stack.imgur.com/WbLyo.png" alt="Rating"></p>
  
  <p>... And the DB code...</p>
  
  <pre><code>/* Delete the tables if they already exist */
  drop table if exists Movie;
  drop table if exists Reviewer;
  drop table if exists Rating;
  
  /* Create the schema for our tables */
  create table Movie(mID int, title text, year int, director text);
  create table Reviewer(rID int, name text);
  create table Rating(rID int, mID int, stars int, ratingDate date);
  
  /* Populate the tables with our data */
  insert into Movie values(101, 'Gone with the Wind', 1939, 'Victor Fleming');
  insert into Movie values(102, 'Star Wars', 1977, 'George Lucas');
  insert into Movie values(103, 'The Sound of Music', 1965, 'Robert Wise');
  insert into Movie values(104, 'E.T.', 1982, 'Steven Spielberg');
  insert into Movie values(105, 'Titanic', 1997, 'James Cameron');
  insert into Movie values(106, 'Snow White', 1937, null);
  insert into Movie values(107, 'Avatar', 2009, 'James Cameron');
  insert into Movie values(108, 'Raiders of the Lost Ark', 1981, 'Steven Spielberg');
  
  insert into Reviewer values(201, 'Sarah Martinez');
  insert into Reviewer values(202, 'Daniel Lewis');
  insert into Reviewer values(203, 'Brittany Harris');
  insert into Reviewer values(204, 'Mike Anderson');
  insert into Reviewer values(205, 'Chris Jackson');
  insert into Reviewer values(206, 'Elizabeth Thomas');
  insert into Reviewer values(207, 'James Cameron');
  insert into Reviewer values(208, 'Ashley White');
  
  insert into Rating values(201, 101, 2, '2011-01-22');
  insert into Rating values(201, 101, 4, '2011-01-27');
  insert into Rating values(202, 106, 4, null);
  insert into Rating values(203, 103, 2, '2011-01-20');
  insert into Rating values(203, 108, 4, '2011-01-12');
  insert into Rating values(203, 108, 2, '2011-01-30');
  insert into Rating values(204, 101, 3, '2011-01-09');
  insert into Rating values(205, 103, 3, '2011-01-27');
  insert into Rating values(205, 104, 2, '2011-01-22');
  insert into Rating values(205, 108, 4, null);
  insert into Rating values(206, 107, 3, '2011-01-15');
  insert into Rating values(206, 106, 5, '2011-01-19');
  insert into Rating values(207, 107, 5, '2011-01-20');
  insert into Rating values(208, 104, 3, '2011-01-02');
  </code></pre>
  
  <p>I have another relatively similar question like this, but if I get some help on this one I should be able to apply the patterns and techniques from this one to the next one.</p>
  
  <p>Thanks in advance! :)</p>