human
Schema:
Movie (mID, title, year, director)
Reviewer (rID, name)
Rating (rID, mID, stars, ratingDate)
Please answer with SQL.
assistant
Sure, I understand.
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 names of the movies that are either made after 2000 or reviewed by Brittany Harris?
assistant
SELECT DISTINCT T2.title FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID = T2.mID JOIN Reviewer AS T3 ON T1.rID = T3.rID WHERE T3.name = 'Brittany Harris' OR T2.year > 2000
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 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 ids of the movies that are not reviewed by Brittany Harris.
assistant
SELECT mID FROM Rating EXCEPT SELECT T1.mID FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID = T2.rID WHERE T2.name = 'Brittany Harris'
human
What is the average rating for each movie that has never been 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 ;