Task 2 Stored PL/SQL procedure Implement a stored PL/SQL procedure PARTSUPPLIER that lists information about the supplier key and supplier name supplying parts. The procedure first computes the total number of suppliers supplying the specified part. The procedure then extracts the supplier's information supplying the specified part. The information to be displayed include the part key, part name, the supplier key and the supplier's name. An example of a segment of the output for the list of suppliers supplying a specified part (part 59396) is as follow: part key: 59396, orange cream sandy lavender drab Number of suppliers supplying the part: 4 supplier Key 166 Supplier#000000166 935 Supplier#000000935 1704 Supplier#000001704 2397 Supplier#000002397 supplier Name It is up to you to decide if you want to handle exception in your procedure. Deliverables Submit your spooled file solution2.Ist (or solution2.pdf) that contains your SQL script and the output from the execution of the script. The report must have no errors related to the implementation of your task and it must list all PL/SQL and SQL statements processed. Remember to set ECHO option of SQL*Plus to ON!

Richardson Ski Racing (RSR) sells equipment needed for downhill ski racing. One of RSR's products is fencing used on downhill courses. The fence product comes in 150-foot rolls and sells for $215 per roll. However, RSR offers quantity discounts. The following table shows the price per roll depending on order size: Quantity Ordered To Price per Roll From 1 70 $215 71 140 $195 141 280 $175 281 and up $155 Click on the datafile logo to reference the data. DATA file (a) Use the VLOOKUP function with the preceding pricing table to determine the total revenue from these orders. (b) Use the COUNTIF function to determine the number of orders in each price bin. From Number of To Price per Roll 70 $215 !!! 140 $195 280 $175 and up $155 1 71 141 281 Orders 172

If we implement a queue using an array where we treat the array as if its two ends are "taped together", we call that a linear array. O True False

Q1. Write a SELECT statement that returns these columns from the Invoices table: The invoice _total column A column that uses the ROUND function to return the invoice_total column with 1 decimal digit A column that uses the ROUND function to return the invoice_total column with no decimal digits Q1. Write a SELECT statement that returns these columns from the Invoices table: • The invoice_total column • A column that uses the ROUND function to return the invoice_total column with 1 decimal digit • A column that uses the ROUND function to return the invoice_total column with no decimal digits invoice_total one digit 3813.33 3813.3 zero_digits 3813

calculate_new_balance Given a starting balance (a number), and a list of transaction tuples, calculate the final balance for an account. Transaction tuples are of the shape ("description", amount, "withdrawal")  , or ("description", amount, "deposit").  The last entry in the tuple will be either "withdrawal" or "deposit". Every withdrawal decreases the balance of the account by the specified amount, and every deposit increases the balance. The return value is the new account balance, as a number. (which could be negative) Sample calls should look like: >>> calculate_new_balance(100, [("payday", 20, "deposit"), ("new shoes", 50, "withdrawal"), ("illicit winnings", 200, "deposit")])270>>> calculate_new_balance(100, [])100