M1 Unit 4 Assignment

Mary Lawson
Unit 4 Assignment
December 12, 2014
Professor Waters
Kaplan University

1. Update the employee table, add a salary column (Number type with 7 length) into the employee table. Use a number format with a 2 digit decimal from the right.

ALTER TABLE employee ADD salary number (7,2);

Select salary from employee;

2. Update the employee table and provide a salary for each employee using the salary column added in the previous problem. Validate the rows are there using a SELECT clause.

UPDATE employee
SET salary = 50000;

select salary from employee;

3. Use a cursor FOR loop to retrieve the blog id, blog url and blog description if the blog id is less than 4 and place it in a cursor variable.
Fetch and process each record and insert a row in the table log for each blog id returned.

DECLARE
CURSOR c1 is SELECT blog_id, blog_url, blog_desc FROM blog ORDER BY blog_id; my_ blog_id Number (2); my_blog_url VARCHAR2 (250); my_blog_desc VARCHAR2 (4000);
BEGIN
OPEN c1; FOR blog_id =<4 IN my_blog_id LOOP FETCH c1 INTO my_blog_id, my_blog_url, my_blog_desc; EXIT WHEN c1%NOTFOUND; /* in case the number requested */ /* is more than the total */ /* number of blog infor */ INSERT INTO temp VALUES (my_blog_desc, my_blog_url, my_blog_id); COMMIT; END LOOP; CLOSE c1;
END;

4.
Create a table-based record cursor that utilizes all of the columns in the division table if the id is 2. Declare the division cursor variable with the table-based cursor attribute. Output the division id and division name to the screen.
DECLARE division_rec division%rowtype;
BEGIN
SELECT * into division_rec FROM division WHERE id = 2;

dbms_output.put_line ('Division ID: ' || division_rec.id); dbms_output.put_line ('Division Name: ' || division_rec.name); END;
/

5. Update the previous problem to have an exception process where if there is no data found it raises an error that prints a message that the division is not in the database.
DECLARE
division_rec division%rowtype;
BEGIN
SELECT * into division_rec FROM division WHERE id = 2 EXIT WHEN division_rec%notfound;

dbms_output.put_line ('Division ID: ' || division_rec.id); dbms_output.put_line ('Division Name: ' || division_rec.name); END;
/

6. Write a PL/SQL block that will reduce the salary of all employees by 5% for employees that were not employed by January 1, 2001 (use correct date format). Use a cursor FOR loop that will update the employee table.

Declare Red_salary number (8,2);
Begin
Select salary * 0.05 into red_salary, start_date
From employee
Where start_date < ’01-Jan-01’;
END;
/

Reference
Feuerstein, S. (2014). Oracle PL covers versions through Oracle database 12c. (Sixth Ed.). Beijing: O'Reilly.

McLaughlin, M. (2014). Oracle Database 12c PL/SQL programming. New York: McGraw-Hill Education.