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 ids of reviewers who did not give 4 star.
assistant
SELECT rID FROM Rating EXCEPT SELECT rID FROM Rating WHERE stars = 4
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
For any rating where the name of reviewer is the same as the director of the movie, return the reviewer name, movie title, and number of stars.
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
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
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 names of all reviewers who rated Gone with the Wind.
assistant
SELECT DISTINCT T3.name FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID = T2.mID JOIN Reviewer AS T3 ON T1.rID = T3.rID WHERE T2.title = "Gone with the Wind" ;