Movie Rental Database: Oracle Part-2

Submit all of your codes, Oracle responses, and listing of your testing session in a single text or Microsoft Word document.

please see attached file

Now that business is becoming strong and the movie stock is growing for More Movie Rentals, the manager wants to do more inventory evaluations. One item of interest concerns any movie for which the company is holding $75 or more in value. The manager wants to focus on these movies in regards to their revenue generation to ensure the stock level is warranted. To make these stock queries more efficient, the application team decides that a column should be added to the MM_MOVIE table named STK_FLAG that will hold a value '*' if stock is $75 or more. Otherwise the value should be NULL. Add the needed column and create an anonymous block that contains a CURSOR FOR loop to accomplish the task. The company will run this program monthly to update the STK_FLAG column before the inventory evaluations.

Make sure that you display the table structure of MM_MOVIE using DESC, and SELECT all data from this table before and after you do this exercise to show the effect of your block.

Submit all code and Oracle responses. Before submitting, edit your document to remove erroneous attempts.

Part 2

Here is a block that retrieves the movie title and rental count based on a movie id provided via a host variable.

SET SERVEROUTPUT ON

VARIABLE g_movie_id NUMBER
BEGIN
:g_movie_id := 4;
END;
/

DECLARE
v_count NUMBER;
v_title mm_movie.movie_title%TYPE;
BEGIN
SELECT m.movie_title, COUNT(r.rental_id)
INTO v_title, v_count
FROM mm_movie m, mm_rental r
WHERE m.movie_id = r.movie_id
AND m.movie_id = :g_movie_id
GROUP BY m.movie_title;

DBMS_OUTPUT.PUT_LINE(v_title || ': ' || v_count);
END;
/

Add exception handlers for errors that you can and cannot anticipate. Test the block by running it with various values of the host variable.