Create a script file containing the pl/sql code

Assignment Help PL-SQL Programming
Reference no: EM13802814

The purpose of this lab is to work with basic PL/SQL syntax to create an anonymous block of code. In the lab, you will be using SQL*Plus to modify one of the tables in the MovieRental schema and then write a simple block of code to update the table with some new data and then execute the code in SQL*Plus. As an additional task in the lab, you will be asked to modify the existing PL/SQL block of code given to you to add exception handling and then execute it in SQL*Plus. Both of these concepts will help enforce the material covered in this second week.

For the lab, you will need to create a script file containing the PL/SQL code that will address the lab steps below. Run the script file in your SQL*Plus session using the SET ECHO ON session command at the beginning to capture both the PL/SQL block code and output from Oracle after the block of code has executed. To successfully test the code in Step 3, you will need to copy/paste your code into SQL*Plus for each movie ID as you change the value for the host variable. Spool your output to a file named with your last name plus lab 2 and give the file a text (.txt) extension. For example, if your last name was Johnson then the file would be named johnson_lab2.txt. Submit both the spooled output AND the script file for grading of the lab.

Step 1:

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.

Step 2:

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.

Step 3:

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.

Reference no: EM13802814

Questions Cloud

Who is audrey hepburn and what did she do : Who is Audrey Hepburn? what did she do? What does her quote The beauty of a woman is not in a facial mode but the true beauty in a woman is reflected in her soul.
What is the relationship between legal and ethical issues : How might these issues be relevant to organizational and personal decisions? What is the relationship between legal and ethical issues?
Decreases as output increases-vertically integrated firm : Which of the following always decreases as output increases? The Southern Tree Trimming Corporation reported an accounting profit of $35,000 and a normal rate of return of 15 percent on capital and enterprise of $30,000. The opportunity cost of labor..
Summarize the results of your self-assessment of cultural : The assignment: (1-2 pages) Summarize the results of your self-assessment of cultural intelligence. Explain how and why your cultural intelligence might influence your ability to lead in a setting that is highly diverse. Be specific and use examples ..
Create a script file containing the pl/sql code : For the lab, you will need to create a script file containing the PL/SQL code that will address the lab steps below. Run the script file in your SQL*Plus session using the SET ECHO ON session command at the beginning to capture both the PL/SQL blo..
What role does the usa play in globalization : What role does the USA play in globalization?
He five with the mean listed first and the standard deviatio : Need help with 95% confidence interval. Have 5 that need to be figured out. The five with the Mean listed first and the standard deviation next are as follows: Mean: 20,330.5; SD: 53,510.2 Mean: 5.417; SD: 4.336 Mean: 2.000 SD: 3.684 Mean: 1.637 SD: ..
Make the most accurate diagnosis possible from info : Instructions: For each of the following case studies, play the role of a clinician and make the most accurate diagnosis possible from the given information (Clinical Axis I disorders only - NO Personality Disorders). Your answer may be just a few wor..
Implementing improvements in its supply chain : A comparison of two computer manufacturers found that by implementing improvements in its supply chain, one company was able to reduce its average supply of key components to only 3-6 days’ supply of inventory, as compared with their competitor, whic..

Reviews

Write a Review

PL-SQL Programming Questions & Answers

  Create a database model

Create a database model and Submit the table creation statements for the Database Model.

  Write pl-sql procedures and functions

Write PL/SQL procedures and functions to populate and query that database

  Sql questions

Write a query to display using the employees table the EMPLOYEE_ID, FIRST_NAME, LAST_NAME and HIRE_DATE of every employee who was hired after to 1 January, 1995.

  Run the lab_03_01.sql script

Run the lab_03_01.sql script in the attached file to create the SAL_HISTORY table. Display the structure of the SAL_HISTORY table.

  Write sql queries

Write a query to display the last name, department number, and salary of any employee whose department number and salary both match the department number and salary of any employee who earns a commission.

  Explaining sql insert statement to insert new row in cds

Write down a SQL insert statement to insert new row in "CDS" table.

  Write down name of actors in ascending order

Write down actors (or actress, your choice, but not both) who have won at least two (2) Academy Awards for best actor/actress. Provide the actor name, movie title & year. Order the result by actor name."

  What is an sql injection attack

What is an SQL injection attack? Explain how it works, and what precautions must be taken to prevent SQL injection attacks.What are two advantages of encrypting data stored in the database?

  Determine resonant frequency in series rlc resonant circuit

Given the series RLC resonant circuit in the figure, operating at variable frequency, determine: The resonant frequency ω o ,  The circuit’s quality factor Q , The cut-off frequencies, f 1  & f 2  and the bandwidth BW

  Query that uses cube operator to return lineitemsum

Write summary query which uses CUBE operator to return LineItemSum (which is the sum of InvoiceLineItemAmount) group by Account(an alias for AccountDesciption).

  Query to show customers were missing for existing orders

As DBA, your manager called a meeting and asked why there are so many orders for customers that don't exist in the customer table. Write query which would shows which customers were missing for existing orders. Use a join or a subquery.

  Sql query into a relational algebra statement

Turn this SQL query into a relational algebra statement? SELECT Request.reqfor, Ordering.invamt, Ordering.invnbr, Ordering.invdat

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd