What are two advantages of using views?

p

Flag question: Question $2$

Question $21$ pts

$2.$ Select everything from the movies view that we created in class.

Screen Shot $2022-07-01$ at $4\mathrm{.}07\mathrm{.}31$ PM$.$png

p

Flag question: Question $3$

Question $35$ pts

$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.

Screen Shot $2022-07-06$ at $2\mathrm{.}20\mathrm{.}14$ PM$.$png

p

Flag question: Question $4$

Question $45$ pts

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

Picture$1-3.$png

p

Flag question: Question $5$

Question $55$ pts

$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

Screenshot $2022-10-26013637.$png

p

Flag question: Question $6$

Question $65$ pts

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

Picture$1-5.$png

p

Flag question: Question $7$

Question $73$ pts

$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$)$