=============================v 29 ========================
select last_name, salary,job_id, d.department_id, department_name,
(select count(employee_id) from employees where department_id=d.department_id) nr,
(select avg(salary) from employees where department_id=d.department_id) medie
from employees e right join departments d on(e.department_id=d.department_id);
=============================v 29 ========================
=============================v 34 ========================
select last_name, salary,job_id, d.department_id, department_name, nr, medie
from employees e right join
                    (select department_id, department_name,count(employee_id) nr, avg(salary) medie
                     from employees right join departments using(department_id)
                    group by department_id,department_name) d on(e.department_id=d.department_id);

with aux as
        (select department_id, department_name,count(employee_id) nr, avg(salary) medie
                     from employees right join departments using(department_id)
                    group by department_id,department_name)

select last_name, salary,job_id, d.department_id, department_name, nr, medie
from employees e right join aux d on(e.department_id=d.department_id);
=============================v 34 ========================
=============================v 22 ========================
select afis.last_name, afis.salary,afis.job_id, d.department_id, department_name, count(t_count_avg.employee_id) nr,
avg(t_count_avg.salary) medie
from employees afis right join departments d on(afis.department_id=d.department_id)
join employees t_count_avg on(afis.department_id=t_count_avg.department_id)
group by afis.last_name, afis.salary,afis.job_id, d.department_id, department_name;
=============================v 22 ========================
=============================v 28 ========================
select (select count(*) from employees) ,
(select count(*) from employees where to_char(hire_date,'yyyy')=1997) An1997,
(select count(*) from employees where to_char(hire_date,'yyyy')=1998) An1998,
(select count(*) from employees where to_char(hire_date,'yyyy')=1999) An1999,
(select count(*) from employees where to_char(hire_date,'yyyy')=2000) An2000
from dual;
=============================v 28 ========================