human
Schema:
Movie (mID, title, year, director)
Reviewer (rID, name)
Rating (rID, mID, stars, ratingDate)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the names of all reviewers.
assistant
SELECT T2.name FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID = T2.rID GROUP BY T2.name;
human
What is the average rating of 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
List the names of all reviewers based on their average rating in ascending order.
assistant
SELECT T2.name FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID = T2.rID GROUP BY T2.name ORDER BY AVG ( T1.stars ) ASC;
human
List all movie id associated with Brittany Harris.
assistant
SELECT T1.mID FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID = T2.rID WHERE T2.name = 'Brittany Harris';
human
List all movie id associated with Brittany Harris and their rating date.
assistant
SELECT T1.mID , T1.ratingDate FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID = T2.rID WHERE T2.name = "Brittany Harris" ;