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 all directors whose movies are rated by Sarah Martinez.
assistant
SELECT DISTINCT T2.director FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID = T2.mID JOIN Reviewer AS T3 ON T1.rID = T3.rID WHERE T3.name = 'Sarah Martinez'
human
For directors who had more than one movie, return the titles and produced years of all movies directed by them.
assistant
SELECT T1.title , T1.year FROM Movie AS T1 JOIN Movie AS T2 ON T1.director = T2.director WHERE T1.title != T2.title
human
What are the names of the directors who made exactly one movie excluding director NULL?
assistant
SELECT director FROM Movie WHERE director != 'null' GROUP BY director HAVING count(*) = 1
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 of the directors who made exactly one movie?
assistant
SELECT director FROM Movie GROUP BY director HAVING count(*) = 1
human
For all directors who have directed more than one movie, what movies have they directed and what are their names?
assistant
SELECT T1.title , T1.director FROM Movie AS T1 JOIN Movie AS T2 ON T1.director = T2.director WHERE T1.title != T2.title ORDER BY T1.director , T1.title ;