Description
Assignment 2 will be presented online during the lab period. You will demonstrate what you have working and after the demonstration email your working code for each question (not before). You can get part marks for something that is not working. That will be determined from your text submission.
Your submission will be a single text-based SQL file (.sql)
Your .sql file needs to contain appropriate header and commenting.
Create a new Worksheet in SQL Developer.
Save the file as: A2<Lastname><Firstname>.sql
Your tables should match the tables we have been working with. So if I run your statement, I should get the same result with my tables.
For each question, the columns’ title and the format of the output result must match the sample result given in that question.
Your submission will be an email with the SQL file as an attachment.
Your subject header would be A1<Lastname><Firstname>
Due Date: April 19 at the start of your lecture period.
Late penalty: 10% per day. Not having the assignment ready to demonstrate at the beginning of the lab period is one day late. When the zoom link is idle for 20 minutes it shuts down. As long as you are in the waiting room, the link will not be idle.
Assignment Marking Scheme
| Task | Marks |
| Comments in code and quality of presentation |
5 |
| Question 1 | 5 |
| Question 2 | 10 |
| Question 3 | 10 |
| Question 4 | 1o |
| Question 5 | 10 |
| Total | 50 |
- Create a user defined function that determines the price markup in the products file. Be prepared to show it being used in a select statement in the select list, the where clause, and the order by clause. When demonstrating this just include the product name, the two different types of prices and the markup.
- PATIENTA and INSURANCEA tables have been provided for you. Use those tables for your assignment Adapt your lab stored procedure to reject any updates where a company tries to pay 25% or less.
- Adapt your lab procedure to use a CURSOR that goes through all the updated rows after the update was applied and reports on the ones where the percentage payout by the company for all the patients exceeds the company’s maximum payout amount.
For this assignment, we will not adjust the insurance payment to the maximum payout they allow, we will just report on it.
- Create an update trigger program for PATIENTA. If the company pays < 40% there may be substantial debt remaining for the Patients. If the amount owing is greater than 10,000 and the percentage paid is less than 40%, use a trigger to store a row in a new table called DCOLLECTION (difficult collections). The hospital will go through these entries and try to get the insurance company to pay a higher percentage.
This is the DCOLLECTIONS table.
When the insurance company pays 80% for all the patients, there will be no row added to DCOLLECTIONS. The patient may still owe more thatn $10,000, but the hospital will not try to get the company to raise the rate.
In this case the rate was below 40% and a few patients still owe more than $10,000 – so rows are added to the DCOLLECTIONS table.
- Create a stored procedure that uses a cursor for a join of any two of ORDERS, PRODUCTS, ORDERDETAILS and CUSTOMERS. In your WHERE condition, have a minimum of five rows returned. Allow one or two parameters to be passed to your stored procedure that limits the amount of rows returned. The cursor should accept these parameter and be capable of producing different results each time it is used. Show your output with dbms_output.put_line.
