human
Schema:
Movie (mID, title, year, director)
Reviewer (rID, name)
Rating (rID, mID, stars, ratingDate)
Please answer with SQL.
assistant
Sure, I understand.
human
What are all the directors?
assistant
SELECT DISTINCT director FROM Movie;
human
How many movies did each of them direct?
assistant
SELECT COUNT( * ) , director FROM Movie GROUP by director;
human
Show me those who directed more than one movie.
assistant
SELECT director FROM Movie GROUP by director having COUNT( * ) > 1;
human
Show me their directed movies' titles, in the order of director name, then movie title.
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 ;