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 average rating star for each movie that are not reviewed by Brittany Harris.
assistant
SELECT mID , avg(stars) FROM Rating WHERE mID NOT IN (SELECT T1.mID FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID = T2.rID WHERE T2.name = 'Brittany Harris') GROUP BY mID
human
Find the average rating star for each movie that received at least 2 ratings.
assistant
SELECT mID , avg(stars) FROM Rating GROUP BY mID HAVING count(*) >= 2
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 is the total number of ratings that has more than 3 stars?
assistant
SELECT count(*) FROM Rating WHERE stars > 3
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 number of stars that each reviewer awards for a movie?
assistant
SELECT T2.name , avg ( T1.stars ) FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID = T2.rID GROUP BY T2.name ;