Reference no: EM13930285

Part 1

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.

Attachment:- Lab 1a results.doc