Write a script that creates and calls a stored procedure

Assignment Help PL-SQL Programming
Reference no: EM13679876

1. Write a script that creates and calls a stored procedure named spInsertProduct that inserts a row into the Products table. This stored procedure should accept five parameters. One parameter for each of these columns: CategoryID, ProductCode, ProductName, ListPrice, and DiscountPercent.

If the value for the ListPrice column is a negative number, the stored procedure should raise an error that indicates that this column doesn't accept negative numbers. Similarly, the procedure should raise an error if the value for the DiscountPercent column is a negative number.

When inserting data into the Products table, set the Description column to an empty string and set the DateAdded column to the current date.

Code at least two EXEC statements that test this procedure (one successful, one failure)

2. Write a script that creates and calls a stored procedure named spUpdateProductDiscount that updates the DiscountPercent column in the Products table. This procedure should have one parameter for the product ID and another for the discount percent.

If the value for the DiscountPercent column is a negative number, the stored procedure should raise an error that indicates that the value for this column must be a positive number.

Code at least two EXEC statements that test this procedure.

Trigger

3. Create a trigger named Products_INSERT that setsDateAdded column of the Products table to current date if the value for that column is null.

Test this trigger with the following
INSERT INTO Products (CategoryID, ProductCode, ProductName, Description, ListPrice, DiscountPercent) VALUES (1, 'G5122', 'Gretsch G5122 Double Cutaway Hollowbody', '', 999.99, 32);

Check and make sure the date was inserted with a SELECT statement.

4. Create a trigger named Products_UPDATE that checks the new value for the DiscountPercent column of the Products table. This trigger should raise an appropriate error if the discount percent is greater than 100 or less than 0.

If the new discount percent is between 0 and 1, this trigger should modify the new discount percent by multiplying it by 100. That way, a discount percent of .2 becomes 20.

Test this trigger with the following

UPDATE Products
SET DiscountPercent = .25
WHERE ProductID = 1;

Check to make sure the DiscountPercent is 25 with a SELECT statement.

Cursors

5. Write a script that creates a cursor for a SELECT query that consists of the ProductName and ListPrice columns for each product with a list price that's greater than $700. The rows in this result set should be sorted in descending sequence by list price. Then, the script should print the product name and list price for each product so it looks something like this:

Gibson SG, $2517.00

Gibson Les Paul, $1199.00

6. Write a script to declare and use a cursor for the following SELECT statement. Use a WHILE loop to fetch each row in the result set. Fetch each row into a set of local variables. Use the PRINT statement to return each row in the format "Name, $0.00" to the Messages tab.

SELECT LastName, AVG(ShipAmount) AS ShipAmountAvg

FROM Customers JOIN Orders

    ON Customers.CustomerID = Orders.CustomerID

GROUP BY LastName;

Reference no: EM13679876

Questions Cloud

Comment on michael and susan approaches : Comment on Michael and Susan approaches
Company date acquired cost date sold sales price : Company Date acquired Cost Date sold Sales price
Account titles and explanation : Account Titles and Explanation
Identify and describe the cash-based liquidity measures : Identify and describe the cash-based liquidity measures.
Write a script that creates and calls a stored procedure : Write a script that creates and calls a stored procedure named spInsertProduct that inserts a row into the Products table. This stored procedure should accept five parameters.
Theta counters clockwise from the left direction : A proton is traveling horizontally to the right at 4.20×10^6 m/s . Discover (a) the magnitude and (b) direction of the weakest electric field that can bring the proton uniformly to rest over a distance of 3.10cm.
Is an award of monetary damages an adequate remedy : What is the likelihood that the daughter's suit will succeed? State your reasons. Is contract rescission a potential remedy? Why or why not? Is an award of monetary damages an adequate remedy? Why or why not?
Glasier health products : Glasier Health Products
Impact the productivity of an organization : Explore how individual differences, personality traits, and perspectives impact the productivity of an organization - review learning theories and their relationship to organizational performance

Reviews

Write a Review

PL-SQL Programming Questions & Answers

  Write an sql statement to produce a single column called

write an sql statement to produce a single column called itemlocation that combines the skudescriptionthe phrase is

  Develop and demonstrate sql skills

Write a script that provides all of the information in, and duplicates the formatting of, Oracle's SQL*Plus describe command. Additionally, the output should add the comments on the rows.

  Design new transaction by sql statements

Code SQL statements to update view as follows: change spelling of Mark Tobey to Mark Toby. Design new Transaction for Mark Toby. Suppose you have essential transaction, work, and customer data.

  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

  The cast function to return the date added column

A column that uses the CAST function to return the DateAdded column with its full time only (hour, minutes, seconds, and milliseconds)

  Who are the opposition leaders

Write SQL SELECT statements to retrieve the following information from the prime_minister database.

  Write a pl-sql block

Write a PL/SQ block - Select the average salary of all employees in department number 20.

  Write a pl/sql block and declare a variable v_sal

Write a PL/SQL block and declare a variable  v_sal  of the type NUMBER. Include the following statement in the Execution section:

  Can we offer an example of a query

Can we offer an example of a Query in which we can use a parentheses to affect the order of execution in which the WHERE Clause has both AND and OR Logical Operators?

  Review problem and run required statement to create table

Please note that for "Table Employees" due to length of the table description and page size limit, I have broken it into 2 parts for better visibility, please make sure to use all the given attributes from the table.

  Write pl-sql block to display the last name of people

Write a PL/SQL block that displays the last name and salary of the following people. Each of these can be done separately as PL/SQL, first to test and get the output lines then encapsulated in the procedure.

  Implement the tables in oracles 11g relational database

Explain the security mechanisms available for a database and how the data will be protected.

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