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 the names of directors who directed movies with 5 star rating? Also return the title of these movies.
assistant
SELECT T1.director , T1.title FROM Movie AS T1 JOIN Rating AS T2 ON T1.mID = T2.mID WHERE T2.stars = 5
human
What are the names and years of the movies that has the top 3 highest rating star?
assistant
SELECT T2.title , T2.year FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID = T2.mID ORDER BY T1.stars DESC LIMIT 3
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
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 are the names of the reviewers who have rated a movie more than 3 stars before?
assistant
SELECT T2.name FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID = T2.rID WHERE T1.stars > 3 ;