I only need questions 6 & 9 finished. As part of question 4, I used FME workbench to import the tables needed for question 6 & 9 (tblcity & tblstate) into Oracle SQL Developer. I’m supposed to do questions 6 & 9 in Oracle SQL Developer. So as long as I can copy & paste the code into Oracle SQL Developer later I will be fine!
Question 6: Write a PL/SQL stored procedure that receives the keyword ALL or the name of a city as a parameter. When the keyword ALL is received, the procedure should display, with descriptive words, the proportion of the state population that lives in each city. When the name of a city is received, the procedure should display, a) the proportion of the state population that lives in the city, and b) the states population density. Use a single SELECT statement which joins the tables together either using a table join or a WHERE clause. Include an exception section for when no records are returned, too many records are returned or when the state population is zero (division by zero).
Include a driving procedure (anonymous program block) that obtains the name of a city or the keyword ALL from the user and runs your stored procedure using the value obtained.
Question 9: Create a table DBMonitor to audit insertion, update and deletion operations on the tblCity table. DBMonitor has the following columns: transaction date, user performing the transaction, transaction type, old value, new value, table name, transaction type number.
Write a PL/SQL stored function that receives the transaction type (insert, update or delete) as a parameter and returns the number of transactions in the DBMonitor table of the transaction type received.
When a record is inserted or updated or deleted, insert a record into the DBMonitor table. The record must record information for all of the columns in the DBMonitor table: transaction date, user performing the transaction, transaction type, old value, new value, table name and transaction type number. The transaction type number is the number of transactions in the DBMonitor table of the type that caused the trigger to run. For example, if the transaction that caused the trigger to run is the fourth insert transaction, then the transaction type number should be 4. Use the stored function to obtain the number of transactions in the DBMonitor table of the type that caused the trigger to run.
For the two old and new value columns in DBMonitor, make sure that you record all the column names with their old or new values. Dont create a separate old and new value column in DBMonitor for each column.
Only one row is inserted into DBMonitor for each affected row when the trigger fires, no matter how many columns are modified.
Hint: A useful keyword for this question is: USER
Include a driving procedure (anonymous program block) that tests insertion, update and a deletion operations on the tblCity table.
