Practice - Control Structures

Exercice 1

Write a script which examines table Employees. It displays the id gaps in the employee_id column as follows:

anonymous block completed

EmployeeId column has values starting from 100 to 208

There is no employee with code: 178

There is no employee with code: 198

Solutions

SET serveroutput ON;

DECLARE

i NUMBER ;

min_id NUMBER;

max_id NUMBER;

nr NUMBER;

fname VARCHAR2(100);

BEGIN

SELECT MIN(employee_id) INTO min_id FROM employees;

SELECT MAX(employee_id) INTO max_id FROM employees;

dbms_output.put_line('EmployeeId column has values starting from '||min_id||' to '||max_id);

FOR i IN min_id..max_id

LOOP

SELECT COUNT(employee_id) INTO nr FROM employees WHERE employee_id=i;

if(nr=0) then

dbms_output.put_line('There is no employee with code: '||i);

end if;

END LOOP;

END;

Exercice 2

Modify the script at exercice 1 in order to count the number of gaps in the employees table.

After that the script will add as many raws as counted gaps.

For each new employee copy the data of the employee with the smallest ID in the table;

Insert only the columns which do not accept NULL values (employee_Id,First_name,last_name,hire_date,job_id).

Use EMPLOYEES_SEQ sequence to generate correct IDs for the employee.

For each inserted employee the script should display a message similar to:

A new employee with employee_id=336 is inserted;

The script should submit the results before it ends;

Solution

SET serveroutput ON;

DECLARE

i NUMBER ;

min_id NUMBER;

max_id NUMBER;

nr NUMBER;

fname VARCHAR2(100);

missing number :=0;

current_id number;

BEGIN

SELECT MIN(employee_id) INTO min_id FROM employees;

SELECT MAX(employee_id) INTO max_id FROM employees;

dbms_output.put_line('EmployeeId column has values starting from '||min_id||' to '||max_id);

FOR i IN min_id..max_id

LOOP

SELECT COUNT(employee_id) INTO nr FROM employees WHERE employee_id=i;

if(nr=0) then

dbms_output.put_line('There is no employee with code: '||i);

missing:=missing+1;

end if;

END LOOP;

FOR i in 1..missing Loop

insert into employees(employee_id,first_name,last_name,hire_date,job_id)

select employees_seq.nextval,first_name,last_name,hire_date,job_id

from employees where employee_id=100;

select employees_seq.nextval into current_id

from dual;

dbms_output.put_line('A new employee with employee_id='||current_id||' is inserted;');

end Loop;

commit;

END;

Exercice 3

Write a script which examines table Locations. It displays the id gaps in the location_id column as follows:

Location_id column has values starting from 1000 to 3505

There is no location with code: 1001

There is no location with code: 1002

There is no location with code: 1003

There is no location with code: 1004

There is no location with code: 1005

Solution

SET serveroutput ON;

DECLARE

i NUMBER ;

min_id NUMBER;

max_id NUMBER;

nr NUMBER;

fname VARCHAR2(100);

BEGIN

SELECT MIN(location_id) INTO min_id FROM locations;

SELECT MAX(location_id) INTO max_id FROM locations;

dbms_output.put_line('Location_id column has values starting from '||min_id||' to '||max_id);

FOR i IN min_id..max_id

LOOP

SELECT COUNT(location_id) INTO nr FROM locations WHERE location_id=i;

if(nr=0) then

dbms_output.put_line('There is no location with code: '||i);

end if;

END LOOP;

END;

Exercice 4

MOdify script at exercice 1 in the following manner. For each found employee the script should examine the following conditions:

- if the employee has been working in the current position for:

- more than 15 years a bonus of 1000 euro should be assigned

- more than 10 years a bonus of 600 euro should be assigned

- more than 5 years a bonus of 300 euro should be assigned

- less than 5 a bonus of 100 euro should be assigned

For each employee the script will display a message similar to:

Employee:177 with name: Jack and lastname: Livingston will get a bonus of: 300 euro

There is no employee with code: 178

Employee:179 with name: Charles and lastname: Johnson will get a bonus of: 300 euro

Employee:180 with name: Winston and lastname: Taylor will get a bonus of: 300 euro

Solution