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;