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 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 is the average rating star for each reviewer?
assistant
SELECT T2.name , avg(T1.stars) FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID = T2.rID GROUP BY T2.name
human
What is the total number of ratings that has more than 3 stars?
assistant
SELECT count(*) FROM Rating WHERE stars > 3
human
How many movie reviews does each director get?
assistant
SELECT count(*) , T1.director FROM Movie AS T1 JOIN Rating AS T2 ON T1.mID = T2.mID GROUP BY T1.director
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
For each movie that received more than 3 reviews, what is the average rating?
assistant
SELECT mID , avg ( stars ) FROM Rating GROUP BY mID HAVING COUNT( * ) >= 2 ;