Assignment#$2$

Write a stored procedure called projects$\_$at$\_$this$\_$location that receives a location and returns the projects $($if any$)$ at that location as shown in the following sample run: $(2$ points$)$

exec projects$\_$at$\_$this$\_$location$(\text{'}$Stafford$\text{'})$;

Projects at This Location

Computerization

Newbenefits

Write a function called return$\_$location that receives a project number for some project and returns the location of this project. For example, if the function receives $1,$ it returns Bellaire. $(2$ points$)$

Write an anonymous block to test the above function. Format the output like this assuming that the project number used is $1$ : $(1$ point$)$

The location of this project is Bellaire

For the following questions, create the following PRODUCT table and make sure to hand in the CREATE TABLE command for this table: $(1$ point$)$

Code : VARCHAR$(20)$

Description : VARCHAR$(20)$

Price : DECIMAL$(6,2)$

Discount : NUMBER

SalePrice : DECIMAL$(6,2)$

Designate Code as the primary key.

Write a statement level trigger called trg$\_$prod that when the Price and Discount of a product are inserted, the trigger calculates the SalePrice as the Price minus the product of Price and Discount. $(2$ points$)$

Test it like this:

INSERT INTO PRODUCT$($Code$,$Description,Price,Discount$)$ VALUES $(\text{'}$AB$559$DQ$\text{'},$ 'FLAT SCREEN TV$\text{'},3000,0\mathrm{.}05)$

Check that the trigger set the SalePrice as indicated above which is going to be $2850.$

Convert the trigger in the previous question from statement level to row level. Test it by inserting following product: $(2$ points$)$

INSERT INTO PRODUCT$($Code$,$Description,Price,Discount$)$ VALUES $(\text{'}$AT$830$TP$\text{'},$ 'VACUUM CLEANER', $950,0\mathrm{.}07)$

Check that the trigger set the SalePrice which is going to be $883\mathrm{.}5$

In additon to submitting the code,make sure to include screenshots of testing the code if it workes.