Practice - Introduction to PL/SQL



Exercice 1

Write a PL/SQL Script the which finds the average working time (in years) for all employees and then increases the salaries of employees which worked more then the average, by 100 euro .

Solution

set serveroutput on;
DECLARE
vite_pune number(4);

BEGIN
select round(avg(months_between(sysdate,hire_date)/12)) 
    INTO vite_pune
from employees;

update employees
set salary=salary+100
where (months_between(sysdate,hire_date)/12)>vite_pune;

END;

Exercice 2


•Write a PL/SQL Script the which finds the average working time (in years) for all employees and then calculates how many employees worked more then the average. The script outputs the messages:
–The average working time in years is : x
–There are y employees who are working longer than the average
–There are z employees who are working less than the average


Solution

set serveroutput on;
DECLARE
vite_pune number(4);
te_vjeter number(4);
te_rinj number(4);
BEGIN
select round(avg(months_between(sysdate,hire_date)/12)) 
    INTO vite_pune
from employees;

select count(*) into te_vjeter
from employees 
where (months_between(sysdate,hire_date)/12)>vite_pune;

select count(*) into te_rinj
from employees 
where (months_between(sysdate,hire_date)/12)<vite_pune;

dbms_output.put_line('Punonjesit e shoqerise tone kane punuar mesatarisht '||vite_pune||' vite pune');
dbms_output.put_line(te_vjeter||' punonjes kane me shume vite pune se mesatarja ');
dbms_output.put_line(te_rinj||' punonjes kane me pak vite pune se mesatarja ');
END;


Exercice 3

Write a PL/SQL Script the which finds the average salary for all employees and then calculates how many employees have a salry above and below the average. The script outputs the messages:
The average salary is : x
There are y employees who have a salary above the average
There are z employees who have a salary less than the average


Solution

set serveroutput on;
DECLARE
paga_mes number(12);
mire_paguar number(4);
keq_paguar number(4);
BEGIN
select round(avg(SALARY)) 
    INTO paga_mes
from employees;

select count(*) into mire_paguar
from employees 
where salary > paga_mes;

select count(*) into keq_paguar
from employees 
where salary < paga_mes;


dbms_output.put_line('Punonjesit e shoqerise tone paguhen mesatarisht '||paga_mes||' ne muaj.');
dbms_output.put_line(mire_paguar||' punonjes kane page me te larte se mesatarja ');
dbms_output.put_line(keq_paguar||' punonjes kane page me te ulet se mesatarja ');
END;