Show me the movies directed by James Cameron.
select title from movie where director = "James Cameron"
What about their rating stars?
select t1.stars from rating as t1 join movie as t2 on t1.mid = t2.mid where t2.director = "James Cameron"
Show me the average of them.
select avg(t1.stars) from rating as t1 join movie as t2 on t1.mid = t2.mid where t2.director = "James Cameron"
Give me the titles and directors of movies whose star is greater than that.
select t2.title, t2.director from rating as t1 join movie as t2 on t1.mid = t2.mid where t1.stars > (select avg(t1.stars) from rating as t1 join movie as t2 on t1.mid = t2.mid where t2.director = "James Cameron")