Use the Module $4$ Project ERD and the tables created for the midterm project to complete the steps listed below.

A$.$ Setting Up Your Script $(5$ points$)$

$1.$ Open a text editor to begin writing your SQL script that will contain your SQL statements. $($You may use any program that allow you to create $.$sgl files $($e$.$g$.,$ Notepad, Wordpad, Notes, Oracle APEX, TextEdit, Notepad$++,$ etc.$)$

$2.$ Name the file M$08\_$Project$\_$LastName$\_$FirstName.sql $($e$.$g$.,$ M$08\_$Project$\_$Doe$\_$John.sql$).$

$3.$ Execute your script to ensure it runs without errors. $($If your code produces errors, work to resolve them before submitting.$)$

$4.$ Save the script and upload it as your submission.

Points will be deducted if a $.$sgl file is not submitted. Your script should contain $1$ view, $4$ sequences, $1$ index, $1$ synonym, $1$ multi$-$line comment, and $3$ single$-$line comments.

B$.$ Creating a View $(10$ points$)$

$1.$ Create a view called TITLE$\_$UNAVAIL to show the movie titles and media id of the media not returned yet. The view should not allow any DML operations.

C$.$ Creating Sequences $(5$ points$)$

Create the following sequences to be used for primary key values:

$1.$ Use a sequence to generate PKs for CUSTOMER$\_$ID in RENTAL$\_$CUSTOMERS table.

Begin at $101$ and increment by $1.$

$2.$ Use a sequence to generate PKs for TITLE$\_$ID in MOVIES table.

Begin at $1$ and increment by $1.$

$3.$ Use a sequence to generate PKs for MEDIA$\_$ID in MEDIA table.

Begin at $92$ and increment by $1.$

$4.$ Use a sequence to generate PKs for ACTOR$\_$ID in ACTOR table.

Begin at $1001$ and increment by $1.1.$ Create an index on the last name column of the Rental Customers table.

E$.$ Creating Synonyms $(5$ points$)$

$1.$ Create a synonym called TU for the TITLE$\_$UNAVAIL view.

F$.$ Writing Queries $(20$ points$)$

Note: You may need to write INSERT statements to add more data to your tables.

$1.$ Read each of the questions listed in the left column of the table below.

$2.$ Write the answer to your question and$/$or the required SQL statement in the corresponding cell in the right column that answers the question. $($Test your statement to confirm the results before submitting.$).$

a$.$ To determine the popularity of the movies in the OracleFlix inventory, write a query to display each movie and the number of times it was rented. Sort your results by the count of rentals in descending order.

b$.$ OracleFlix will be mailing coupons to the homes of frequent renters. They are printing labels for each customer. Write a query to identify the frequent renters $($i$.$e$.,$ customers that have rented more than once$).$ Your results should include the customers' first and last name concatenated, address, city, and state. Also include $2$ columns name length and address length $-$ that display the length of the concatenation of the first and last name and the length of the address. Sort your results by the customers' last names. G$.$ Creating Queries $(10$ points$)$

You have ideas for $2$ inventory and rentals reports that could be helpful to the leadership of the OracleFlix company. Develop the queries to support the reports you plan to propose and explain how they would be helpful to leadership.

Note: You may need to write INSERT statements to add more data to your tables.

$-$ Your query should include the SQL concepts, keywords, and$/$or clauses in column $1.$

$-$ Test your statement to confirm the results before submitting and place your final query in the column $2.$

$-$ Describe the output of your query as if you were presenting it to a client $($e$.$g$.,$ what does it mean, how would it be useful to them$)$ in column $3$

$-$ Save this document once you have added your answers and uploaded it as part of your submission.

$\backslash$begin$\{$tabular$\}\{|$l$|$l$|$l$|\}$

$\backslash$hline $\backslash$begin$\{$tabular$\}\{$c$\}$

Query $\backslash \backslash$

Requirements

$\backslash$end$\{$tabular$\}$ & & $\backslash \backslash$

$\backslash$hline Query $1$ & & Meaning $/$ Value of Query Results $\backslash \backslash$

$-$ At least $1$ join & & $\backslash \backslash$

$-$ GROUP BY & $\backslash \backslash$

$-$ At least $1\backslash \backslash$

multiple$-$row $/$ & & $\backslash \backslash$

aggregate $\backslash \backslash$

function & & $\backslash \backslash$

$\backslash$hline Query $2$ & $\backslash \backslash$

$-$ At least $1$ & & $\backslash \backslash$

subquery & & $\backslash \backslash$

At least $1$ & & $\backslash \backslash$

Column Alias & & $\backslash \backslash$

$\backslash$hline

$\backslash$end$\{$tabular$\}$