Description
1. (10 points) Write a PL/SQL program which will insert a new record into the Departments table. The new department has department_id as 111, department name as ‘Fortest”, manager_ID as 100, location ID as 1700. After insertion, your program should display the contents of this record.
Note: please do not rollback this command, next question Q2 will delete this record.
2. (10 points) Write a PL/SQL program, in which you will delete the record with department_id of 111 that was just inserted in Q1.
Use the returning clause to store the related info into some variables, then print out the department name and the manager id for the record just deleted. If you need help, please refer to the example 3 on page 9 in Note2_PLSQL_Fundamentals, section 7.
3. (10 points)
Write an anonymous PL/SQL block (using DML command).
Change the department with department ID as 230, change the manager_ID to 103.
After update, print out the department name of this department, please use the returning
clause to get the department name.
4. (10 points) Write a PL/SQL block program, using CASE statement. This question is based on the table EMPLOYEES.
First, retrieve the number ( how many) employees work in a department, say department ID is 80. Save this number into a variable.
Declare a variable with name as dept_size, data type as varchar2 (20),
Using Case statement,
if there are 30 or more than 30 employees work in that department, then the variable dept_size (size of that department) will be assigned a value of ‘LARGE’.
if the number of workers is between 8 to 29, then dept_size value will be ‘MEDIUM’;
if the number of workers is 7 or less, then dept_size value will be ‘SAMLL’;
your program will print out a message to describe this info.
** the select statement might help if you have difficult in SQL
select count (*) into … from employees
where department_ID = …;
** self exercise, you may change the CASE statements with IF THEN, ELSIF THEN.
5. (15 points) This question is based on the table EMPLOYEES.
Assume that the company has decided a one-time bonus for all the employees, the policy is as below.
The total bonus consists of three parts.
a) Basic part for each employee, that amount is $1000.
b) Part two, work-year bonus,
for those employees that have not worked to 25 years, there is no this part 2 extra bonus.
for those employees that have worked 25 years or more, but less than 27, their bonus will add $500;
for those employees that have worked 27 years or more, their bonus will be $700.
This expression may help you to calculate the work year:
floor (months_between (sysdate, hire_date)/12)
c) Part three, according to salary range, each may get the salary-based third part:
for those their salary > $10, 000, then get $1,100;
for those their salary >= $5,000 and salary <= $10,000, then get $1,000;
for those their salary < $5,000, then get $900.
Write an anonymous PL/SQL program, retrieve the necessary information for an employee with a certain Employee_ID = 206, calculate the second and third part bonus according the info related this employee. Print out the final amount of bonus that employee should get.
As a good practice for professional programmer, please use your pen to calculate the bonus for this employee, to check if your program output matches your hand calculation. If in the real business world, you better to check more cases as indicated below.
= = =
Note:
The question output only requires one employee with ID = 206.
Extra/Optional requests. For your self study only, as a programmer in real business, better to test a few more cases to confirm the coding is right. Such as choosing more ID’s to see if the result is as expected (calculate by hands). Below is for your reference:
Column salary format $99,999
SELECT employee_id, salary,
floor (months_between (sysdate, hire_date)/12) Years
FROM employees
where employee_id in ( 128, 192, 206)
EMPLOYEE_ID SALARY YEARS
———– ——– ———-
128 $2,200 22
192 $4,000 26
206 $8,300 27
6. (10 points) This is a simple question, just let us review the similarity and difference between these three iteration methods. Recall, we need to pay attention to the management of the counter and when to exit.
Write an anonymous PL/SQL blocks, inside the execution section, the program will use all three loop methods:
• basic loop,
• for loop and
• while loop
to display the values of a variable that changes from 15 to 17 (inclusive), that means for each loop, it will print out exactly from 15, then 16, then 17. No more, no less.
7. (10 points)
Most the time in this class, we only use the packaged procedure “ DBMS_OUTPUT.PUT_LINE” . In this question we will use the procedure DBMS_OUTPUT.PUT which will not include a “new line” (refer to page 16 in Note2 for details).
The code below will printout a matrix; please change codes from the numeric for loop to the WHILE loop, and get the same printout.
BEGIN
FOR i IN 1..9 LOOP
DBMS_OUTPUT.PUT(‘|’);
FOR j IN 1..9 LOOP
DBMS_OUTPUT.PUT (‘ ‘ || i || j || ‘ ‘);
END loop;
DBMS_OUTPUT.PUT_LINE(‘|’);
END loop;
END;
/
Output:
| 11 12 13 14 15 16 17 18 19 |
| 21 22 23 24 25 26 27 28 29 |
| 31 32 33 34 35 36 37 38 39 |
| 41 42 43 44 45 46 47 48 49 |
| 51 52 53 54 55 56 57 58 59 |
| 61 62 63 64 65 66 67 68 69 |
| 71 72 73 74 75 76 77 78 79 |
| 81 82 83 84 85 86 87 88 89 |
| 91 92 93 94 95 96 97 98 99 |
8. (10 points)
First, create a test table using codes below:
CREATE TABLE EMP_TEST as select * from employees
where department_id in (20, 30, 60);
— SELECT * from EMP_TEST; — to check if that works,
Write an anonymous PL/SQL block based on that newly created table EMP_TEST.
Using implicit cursor attribute SQL%ROWCOUNT to display how many rows are affected for each of the two DML statements as below:
a) give a raise of 10% of their current salary for those employees who works in department_id = 30 and their current salary is equal to or less than $3,000;
b) delete all the employees who work for department assigned 20;
9. (15 points) Write an anonymous PL/SQL block, based on table employees.
The program will print out the employee ID, full name (using concatenate operator to merge the last and first name; you decide which format you like), salary of all employees in department 30.
In this question, you are required to define a cursor that will retrieve the info of employees in department 30, (select * from … where department_id = .. .).
Also define a table based record variable (employees%ROWTYPE) for the FETCH INTO statement.
When printing out, you will use the dot notation to access each column, such as
record_variable_name_.employee_id.
