### 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;`