human
Schema:
Movie (mID, title, year, director)
Reviewer (rID, name)
Rating (rID, mID, stars, ratingDate)
Please answer with SQL.
assistant
Sure, I understand.
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 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 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 title and star rating of the movie that got the least rating star for each reviewer.
assistant
SELECT T2.title , T1.rID , T1.stars , min(T1.stars) FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID = T2.mID GROUP BY T1.rID
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 different reviewer names, movie titles, and stars for every rating where the reviewer had the same name as the director?
assistant
SELECT DISTINCT T3.name , T2.title , T1.stars FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID = T2.mID JOIN Reviewer AS T3 ON T1.rID = T3.rID WHERE T2.director = T3.name ;