Case 3: Working with More Movie Rentals Because business is growing and the movi
ID: 3910714 • Letter: C
Question
Case 3: Working with More Movie Rentals
Because business is growing and the movie stock is increasing at More Movie Rentals, the manager wants to do more inventory evaluations.
One item of interest is any movie with a total stock value of $75 or more. The manager wants to focus on the revenue these movies are generating to make sure the stock level is warranted. To make these stock queries more efficient, If the stock value is $75 or more the application team decides to add a column named STK_FLAG to the MM_MOVIE table that stores an asterisk (*) to make these stock queries more efficient. Otherwise, the value should be NULL.
Add the column and create an anonymous block containing a CURSOR FOR loop to perform this task. The company plans to run this program monthly to update the STK_FLAG column before the inventory evaluation.
Query Result x ?13 EE) Ee SQL | All Rows Fetched: 12 in 0.016 seconds MOVIE IDMOVIE TITLE MOVIE_CAT ID MOVIE VALUE MOVIE QTY 1 Alien 2 Bladerunner 3 Star Wars 4 Texas Chainsaw Masacre 5 Jaws 6 The good, the bad and the ugly 7 Silverado 8 Duck Soup 9 Planes, trains and automobiles 10 15 10 Waking Ned Devine 1l Deep Blue Se 12 The Fifth Element 12 14 15Explanation / Answer
Q1) Add the column and create an anonymous block containing a CURSOR FOR loop to perform this task. The company plans to run this program monthly to update the STK_FLAG column before the inventory evaluation.
// Adding a Column STK_FLAG to MM_MOVIE Table
ALTER TABLE MM_MOVIE ADD STK_FLAG CHAR(1);
// Annonymous PL/SQL Block is give below:-
BEGIN
FOR MOVIE_rec in c1 LOOP
IF MOVIE_rec.TOTAL_STOCK >= 75 THEN
UPDATE MM_MOVIE SET STK_FLAG = '*' WHERE MM_MOVIE.MOVIE_ID := MOVIE_rec.MOVIE_ID;
ELSE
UPDATE MM_MOVIE SET STK_FLAG = '' WHERE MM_MOVIE.MOVIE_ID := MOVIE_rec.MOVIE_ID;
END IF;
END LOOP;
COMMIT;
END;
/
Please let me know in case of any clarifications required. Thanks!
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.