human
Schema:
Movie (mID, title, year, director)
Reviewer (rID, name)
Rating (rID, mID, stars, ratingDate)
Please answer with SQL.
assistant
Sure, I understand.
human
Show me the name of all movies.
assistant
SELECT title FROM movie;
human
What about their ratings?
assistant
SELECT t1.title , t2.stars FROM Movie AS t1 JOIN Rating AS t2 ON t1.mID = t2.mID;
human
Only show those with a rating of 4 or 5.
assistant
SELECT t1.title , t2.stars FROM Movie AS t1 JOIN Rating AS t2 ON t1.mID = t2.mID WHERE t2.stars >= 4;
human
Give me all years that have any of those movies in the order of year.
assistant
SELECT DISTINCT YEAR FROM Movie AS T1 JOIN Rating AS T2 ON T1.mID = T2.mID WHERE T2.stars >= 4 ORDER BY T1.year ;