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 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
What are the titles of all movies that have rating star is between 3 and 5?
assistant
SELECT T2.title FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID = T2.mID WHERE T1.stars BETWEEN 3 AND 5
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
Return reviewer name, movie title, stars, and ratingDate. And sort the data first by reviewer name, then by movie title, and lastly by number of stars.
assistant
SELECT T3.name , T2.title , T1.stars , T1.ratingDate FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID = T2.mID JOIN Reviewer AS T3 ON T1.rID = T3.rID ORDER BY T3.name , T2.title , T1.stars
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
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 ;