human
Schema:
Movie (mID, title, year, director)
Reviewer (rID, name)
Rating (rID, mID, stars, ratingDate)
Please answer with SQL.
assistant
Sure, I understand.
human
Return all reviewer names and movie names together in a single list.
assistant
SELECT name FROM Reviewer UNION SELECT title FROM Movie
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 ids of reviewers who did not give 4 star.
assistant
SELECT rID FROM Rating EXCEPT SELECT rID FROM Rating WHERE stars = 4
human
Find the ids of reviewers who didn't only give 4 star.
assistant
SELECT rID FROM Rating WHERE stars != 4
human
What is the reviewer id of Daniel Lewis?
assistant
SELECT rID FROM Reviewer WHERE name = 'Daniel Lewis'
human
What is the id of the reviewer whose name has substring Mike ?
assistant
SELECT rID FROM Reviewer WHERE name LIKE "%Mike%" ;