Name:  ___________________________________     Date:  ____________

Note:  Unlike previous classes, you are required to submit your code for evaluation.

Due to an error during data loading, the START_DATE column in the CLASS_SCHEDULE table has dates which are two years off.  In other words, a value of ‘01-JAN-1997’ should actually be ‘01-JAN-1999’.   Use two approaches to fix the dates:  use an anonymous block with a cursor, then use a query with UPDATE.  (Remember to ROLLBACK your changes between your two attempts.)  After you have ensured that your approaches work, use COMMIT.

Create a user-defined function which will return a bonus amount for any instructor based on the following conditions:

· Any instructors who work in SEA or DC would get a bonus of 10% over their base salary

· All instructors who have worked for Aris for 7 years or more get an additional 15% bonus

Provide a listing of all month and year combinations represented in the CLASS_SCHEDULE table in the following format:

January, 1997

February, 1997

etc.

1. Insert a new training site with these values in the TRAINING_SITE table:

‘RS’, ‘ARIS Reston Virginia’, ‘123 Main’, NULL, ‘Reston’, ‘VA’, 22018, ‘ARIS.HTM’

2. Commit the insert

3. Use a query to insert into the COURSE_TRAINING_SITE table the same number of courses for Reston as are currently found for Seattle

4. Create a stored procedure which will produce a report with this format, using a passed argument representing a calendar year:

Today’s Date                                                                                               Page #

Training Site Report for Calendar Year YYYY

Colorado

        Denver (Site Code DNV)

        Classes Scheduled : NNN       

        Percentage of all classes represented by this site:  NN%

Oregon

        Beaverton (Site Code PTL)

        Classes Scheduled : NNN

        Percentage of all classes represented by this site:  NN%

Virginia

        Clifton (Site Code DC)

        Classes Scheduled : NNN

        Percentage of all classes represented by this site:  NN%

        Reston (Site Code RS)

        Classes Scheduled : NNN

        Percentage of all classes represented by this site:  NN%

5. Validate the passed in year to ensure that the report is only generated for years which are represented in the database