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 lowest and highest rating star?
assistant
SELECT max(stars) , min(stars) FROM Rating
human
What are the titles of all movies that have rating star is between 3 and 5?
assistant
SELECT T2.title FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID = T2.mID WHERE T1.stars BETWEEN 3 AND 5
human
Find the names of reviewers who had given higher than 3 star ratings.
assistant
SELECT T2.name FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID = T2.rID WHERE T1.stars > 3
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
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 ;