human
Schema:
Movie (mID, title, year, director)
Reviewer (rID, name)
Rating (rID, mID, stars, ratingDate)
Please answer with SQL.
assistant
Sure, I understand.
human
What are the movie titles and average rating of the movies with the lowest average rating?
assistant
SELECT T2.title , avg(T1.stars) FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID = T2.mID GROUP BY T1.mID ORDER BY avg(T1.stars) LIMIT 1
human
What are the titles of all movies that have rating star is between 3 and 5?
assistant
SELECT T2.title FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID = T2.mID WHERE T1.stars BETWEEN 3 AND 5
human
What are the names and years of the movies that has the top 3 highest rating star?
assistant
SELECT T2.title , T2.year FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID = T2.mID ORDER BY T1.stars DESC LIMIT 3
human
What is the maximum stars and year for the most recent movie?
assistant
SELECT max(T1.stars) , T2.year FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID = T2.mID WHERE T2.year = (SELECT max(YEAR) FROM Movie)
human
What is the average rating stars and title for the oldest movie?
assistant
SELECT avg(T1.stars) , T2.title FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID = T2.mID WHERE T2.year = (SELECT min(YEAR) FROM Movie)
human
In what years did a movie receive a 4 or 5 star rating, and list the years from oldest to most recently?
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 ;