$3.$ Create a new view that shows the same information as #$2$ EXCEPT it should show the average rating of each movie. Use the view from #$2$ in the FROM clause. Name the average rating column AvgRating.

$4.$Using the view from #$3,$ show each genre with its avgrating.

$5.$ Show all movies with a release year between $1970$ and $1990$ where the movie's genre has an average rating greater than $7\mathrm{.}0.$ Only show the movie id$,$ title, genre, and year columns

$6.$ Show all directors who have not directed any dramas.

$7.$ Assume there is a payment history table with a column called payment$\_$type that indicates if a payment is regular salary, a bonus, or an award. What question does this correlated query answer? Begin your answer with "This query answers the question: who are the employees..."

SELECT employee$\_$id$,$ first$\_$name, last$\_$name

FROM employees e$1$

WHERE NOT EXISTS $($SELECT ph$.$lastname

FROM payment$\_$history ph

WHERE ph$.$emp$\_$id $=$ e$1.$employee$\_$id

AND ph$.$payment$\_$type $=$award$)$