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 excluding NULL?
assistant
SELECT distinct director FROM Movie WHERE director != 'null';
human
What about the highest rating their movies have ever received?
assistant
SELECT T2.director , MAX( T1.stars ) FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID = T2.mID WHERE director != 'null' GROUP BY director;
human
Also give me those movies' title.
assistant
SELECT T2.title , T1.stars , T2.director , MAX( T1.stars ) FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID = T2.mID WHERE director != "null" GROUP BY director ;