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 didn't only give 4 star.
assistant
SELECT rID FROM Rating WHERE stars != 4
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
Find the names of all reviewers who have ratings with a NULL value for the date.
assistant
SELECT DISTINCT name FROM Reviewer AS T1 JOIN Rating AS T2 ON T1.rID = T2.rID WHERE ratingDate = 'null'
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 ;