human
Schema:
Movie (mID, title, year, director)
Reviewer (rID, name)
Rating (rID, mID, stars, ratingDate)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the movies with the highest average rating. Return the movie titles and 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) DESC LIMIT 1
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
Find the titles of all movies that have no ratings.
assistant
SELECT title FROM Movie WHERE mID NOT IN (SELECT mID FROM Rating)
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 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
What are the titles and average ratings for all movies that have 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 ;