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 all years that have a movie that received a rating of 4 or 5, and sort them in increasing order of year.
assistant
SELECT DISTINCT YEAR FROM Movie AS T1 JOIN Rating AS T2 ON T1.mID = T2.mID WHERE T2.stars >= 4 ORDER BY T1.year
human
Find the titles of all movies that have no ratings.
assistant
SELECT title FROM Movie WHERE mID NOT IN (SELECT mID FROM Rating)
human
What are the movie titles and average rating of the movies with the lowest average rating?
assistant
SELECT T2.title , avg(T1.stars) FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID = T2.mID GROUP BY T1.mID ORDER BY avg(T1.stars) LIMIT 1
human
Find the average rating star for each movie that received at least 2 ratings.
assistant
SELECT mID , avg(stars) FROM Rating GROUP BY mID HAVING count(*) >= 2
human
Find the title and score of the movie with the lowest rating among all movies directed by each director.
assistant
SELECT T2.title , T1.stars , T2.director , min(T1.stars) FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID = T2.mID GROUP BY T2.director
human
Find the movies with the highest average rating. Return the movie titles and average rating.
assistant
SELECT T2.title , avg ( T1.stars ) FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID = T2.mID GROUP BY T1.mID ORDER BY avg ( T1.stars ) DESC LIMIT 1 ;