DBM 405 LAB 2: Simple PL/SQL Applications

Type: Instant Download

Format: Notepad

Version: Current 2013-14 (Click on tutorial image to view snapshot)

Frequently Asked Questions

What is included in the full course package? The entire course study guide includes the output file homework solution in notepad for DBM 405 LAB 2: Simple PL/SQL Applications (bachelors program)

Your tutorial will include these files:

• DBM 405 Lab 2 outputfile (Notepad document)

What if I find a question is missing? Just shoot us an email or contact us via live chat. Our expert DBM tutors will add the answers to the study guide within 1 to 3 days. You will receive a free update to the study guide with the answers you need. 

Will this help me with DBM 405 problems? Yes, this guide is designed to help students get through the DBM 405 Lab 2 exercise with ease.

DBM 405 LAB 2: Simple PL/SQL Applications assignment description:

As 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 new column to the MM_MOVIE table as a CHAR data type.

Execute a DESC MM_MOVIE on the table both before you add the new column and after the column is added.

Note: Since this is code will be in your script file, you will need to comment it out after the first time you have execute the ALTER TABLE statement successfully to avoid getting errors each additional time your script file is run.

Create an anonymous block of PL/SQL code that contains a CURSOR FOR loop to accomplish the task described above in Step 1. Your loop will need to interrogate the value (using an IF statement) found in the movie_qty field of the cursor loop variable to see if it is >= 75. If this is true then you will need to update the new column in the table with an '*' WHERE CURRENT OF the table. If the quantity is not >= 75 (the ELSE side of the IF statement) then update the new column with a NULL.

Execute a SELECT * from MM_MOVIE both before and after you execute the new PL/SQL block of code to show that the process works.

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;
/

Modify the block of code to add exception handlers for errors that you can and cannot anticipate. You will need to execute the entire code listing shown above each time you wish to test it by changing the value of :g_movie_id for each test.

Once finished, test your exception handling by running the modified block for the following values of :g_movie_id. Be sure that you can capture the value in the :g_movie_id host variable.

• 12 - normal output will display title and number of rentals
• 13 - exception - there is no movie ID for 13
• 1 - exception - Movie with ID 1 has never been rented