I am currently trying to self teach myself PL/SQL with materials from a class a coworker took in college, unfortunately he doesnt have the time to assist me and pointed me to google groups.
Could someone complete this assignment for me so that I could try to come to an understanding of how it is put together? This is using the ORACLE HR sample database that comes with the Oracle 10g express edition. Use Oracle SQL*PLUS, TOAD, or SQL developer to create scripts A. (15 points) 1. Write a PL/SQL code using an explicit cursor to display all the employees’ name concatenated with their phone number if they earn more than 10,000. Also display the total row count at the end of the output. Use DBMA_OUTPUT package to display your result. 2. Write a script to display the employee name (full name), job title, and department name for a given location. The search condition should allow for case-insensitive searches of the department location, i.e. your script should be able to accept a given location name in any upper/lower case combination. B. Write script to fulfill the requirements of the following work: (15 points) 1. Create a sequence to be used with the primary key column of the regions table. The sequence should start at 200 and have a maximum value of 400. Have your sequence increment by 5. Name the sequence REGIONID_SEQ. 2. Write a script to display the following information about your sequences: sequence name, maximum value, increment size, and last number. 3. Insert a new region record by using the new sequence and list the table to prove it. 4. Delete all the job_history records from the table. List job_history table to make sure your deletion work and then recover your deletion work. Don’t forget to commit all the changes. C. (15 points) 1. Use create table Department_copy as select * from departments statement to create a copy of Departments table. Create a procedure called ADD_DEPT to insert a new department into the Departments_copy table. Your procedure just needs to get the new department name and manager_id as inputs. 2. For the new department_id, you need to call a function to get the maximum department id used in the current Departments_copy table, then add 10 to that number and use it as the new department¬_id. 3. Test your work and prove they are correct. Bonus Points: (10 points) 1. Create a table called Employees_copy by using create table Employees_copy as select * from employees statement. 2. Create an audit table to store username, date, data manipulation command (update salary, insert, or delete) and before and after images of your employe_id and salary fields whenever you insert records into, delete records from the Employees_copy table or after you update the salary field of your Employees_copy table. 3. Create a trigger to insert these records into this audit table in case these commands are executed. 4. Issue one insert, one delete and one update salary commands to your Employees_copy table to make sure your audit table captures all these 3 events. -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
