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 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
Find the names of all reviewers who have contributed three or more ratings.
assistant
SELECT T2.name FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID = T2.rID GROUP BY T1.rID HAVING COUNT(*) >= 3
human
Find the ids of reviewers who didn't only give 4 star.
assistant
SELECT rID FROM Rating WHERE stars != 4
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 names of reviewers who had rated 3 star and 4 star?
assistant
SELECT T2.name FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID = T2.rID WHERE T1.stars = 3 INTERSECT SELECT T2.name FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID = T2.rID WHERE T1.stars = 4
human
How many reviewers listed?
assistant
SELECT COUNT( * ) FROM Reviewer ;