Practice - Control Structures


Contents

  1. 1 Exercice 1
  2. 2 Exercice 2 
  3. 3 Exercice 3
  4. 4 Exercice 4

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