-- algunos select
-- listar cuantos empleados hay por departamento

select DEPARTMENT_ID, count(*)
from employees
group by DEPARTMENT_ID
order by count(*) desc, DEPARTMENT_ID;



________________________________________________________________________________________________________________________________

-- cursores
-- cursor que lista la cantidad de empleados por dpto
declare
cursor curCtaEmpleados is
select DEPARTMENT_ID, count(*)
from employees
group by DEPARTMENT_ID
order by count(*) desc, DEPARTMENT_ID;
vIdDepartamento employees.DEPARTMENT_ID%type;
vTotalEmpleados integer;
begin
open curCtaEmpleados;
loop
fetch curCtaEmpleados into vIdDepartamento, vTotalEmpleados;
exit when curCtaEmpleados%NOTFOUND;
dbms_output.put_line(vIdDepartamento || ' ' ||vTotalEmpleados);
end loop;
close curCtaEmpleados;
end


______________________________________________________________________________________________________________________________


-- cursores
-- cursor que lista los nombres de empleados por dpto
declare
cursor curCtaEmpleados is
select DEPARTMENT_ID, count(*)
from employees
group by DEPARTMENT_ID
order by count(*) desc, DEPARTMENT_ID;
vIdDepartamento employees.DEPARTMENT_ID%type;
vTotalEmpleados integer;
vNombreDepartamento departments.department_name%type;
begin
open curCtaEmpleados;
loop
fetch curCtaEmpleados into vIdDepartamento, vTotalEmpleados;
exit when curCtaEmpleados%NOTFOUND;
select department_name into vNombreDepartamento
from DEPARTMENTS
where department_id = vIdDepartamento;
dbms_output.put_line(vIdDepartamento || ' ' ||vTotalEmpleados);
end loop;
close curCtaEmpleados;
end