How to get the results of this particular "query",

Question

NOTE. I accidentally put here another sentence of the question (a massive apology on my part), I updated this post from March 14 to 23:21 with the right question.

I spent several hours trying to figure this issue out without help, but I realized that I spent too much time on work and had to ask someone before. I had a decent crack in it and came so close, but I can’t get the final solution that I need. I should get:

For all cases where the same reviewer rated the same film twice and gave it a higher rating a second time, return the reviewer’s name and film’s name.

This is the request I managed to get here:

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(*) >= 2 ORDER BY reviewer.name DESC 

(I have the feeling that the WHERE clause is missing from the above query, but I'm not sure where to place it)

(From what I learned, RIGHT and FULL OUTER JOINs are not currently supported in SQLite)

And here are the tables and data (in the photos) ...

... and the DB 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, 'ET', 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'); 

I have another relatively similar question like this, but if I get some help with this, I should be able to apply patterns and methods from this to the next.

Thanks in advance!:)

Answer 1

I added an inner join to the view that returns the maximum numbers on the movie. Due to the internal connection between films and ratings, only films with ratings will be received. Join the main query to get the maximum numbers per movie.

Note: you have stated that you want to order by the name of the film, but you request a reviewer.

 SELECT reviewer.name, movie.title, rating.stars, maxStarsPerMovie.MaxStars FROM (reviewer JOIN rating ON reviewer.rid = rating.rid) JOIN movie ON movie.mid = rating.mid join ( select movie.mid, max(rating.stars) MaxStars from movie inner join rating on movie.mid = rating.mid group by movie.mid ) maxStarsPerMovie on movie.mid = maxStarsPerMovie.mid ORDER BY reviewer.name DESC 

EDIT: requiremets changed. This query will return a list of reviewers who later changed their minds in favor of the film. He does this by joining the ratings for the second time, adding two filters by stars and a date to join.

 SELECT reviewer.name, movie.title, rating.ratingDate, rating.stars, newRating.ratingDate newRatingDate, newRating.Stars newRatingStars FROM (reviewer JOIN rating ON reviewer.rid = rating.rid) JOIN movie ON movie.mid = rating.mid inner join rating newRating on newRating.mid = movie.mid and newRating.rid = reviewer.rid and newRating.ratingdate > rating.ratingdate and newRating.stars > rating.stars ORDER BY reviewer.name, movie.title 

Answer 2

From the description of the requirement:

Return the name of the movie and the number of stars (sorted by movie name). For each movie that has at least one rating, and find the maximum number of stars that received the movie.

Reviewer details do not look necessary - only the film and maximum stars.

Therefore, I suggest:

 SELECT movie.mid, MAX(movie.title) as title, MAX(rating.stars) as max_stars FROM rating JOIN movie ON movie.mid = rating.mid GROUP BY movie.mid ORDER BY 2, 1 

Answer 3

You probably did this for the Stanford SQL mini-course, as I did. Here is what I got for my answer (I had no experience with SQL before watching lectures, so I hope this is not too scary):

Start with a query that finds each rID for the reviewer who rated the film twice and gave a second score:

  select R1.rID from Rating R1, Rating R2 where R1.mID = R2.mID and R1.rID = R2.rID and R1.ratingDate < R2.ratingDate and R2.stars > R1.stars; 

they think of R1 as the first rating for a particular film by a specific reviewer, and R2 as the second. We need to talk about 2 reviews of the same film by the same person, therefore R1.mID = R2.mID and R1.rID = R2.rID. Then, to make sure that R1 was really the first, in chronological order, we set R1.ratingDate <R2.ratingDate, and to make sure that R2 really got the higher score, we set R2.stars> R1.stars.

You can verify that this gives us rID 201, which is the correct answer (you can verify by checking the data). Now we need to display the name and title of the movie reviewer, not rID.

I did this by doing a cross product of all three relationships (I suppose would using joins be cleaner?), Removing duplicates, and using the query above as a subquery where where:

  select distinct name, title from Movie, Reviewer, Rating where Movie.mID = Rating.mID and Reviewer.rID = Rating.rID and Rating.rID in (select R1.rID from Rating R1, Rating R2 where R1.mID =R2.mID and R1.rID = R2.rID and R1.ratingDate < R2.ratingDate and R2.stars > R1.stars); 

In the where clause, I simply made the cross products into natural joins by setting the corresponding mID and rID to be equal, and made sure that the rIDs (called Rating.rID for ambiguity) were determined by the initial query I wrote.

Answer 4

I would suggest using self-connectivity in the subquery to identify the reviewer, and then use the result to get the reviewer’s name and movie name.

 SELECT name, title FROM Reviewer R join (SELECT Ra.mID as mID, Ra.rID as rID FROM Rating Ra JOIN Rating Rb ON Ra.mID=Rb.mID WHERE Ra.rID=Rb.rID AND Ra.ratingDate<Rb.ratingDate AND Ra.stars<Rb.stars) AS Tmp ON R.rID=Tmp.rID JOIN Movie M ON M.mID=Tmp.mID