Clase de hoy: Resolvimos el problema de la clase pasada. [Parte 2]

Declare
Cursor myCursor is Select department_id, count(*) as "total empleados"
from employees
where Department_id is not null;
group by department_id
order by department_id;
vDepartamento employees.employee_id%type;
vTotalEmpleados integer;
vNomDepto varchar2(30);
Begin
 Open myCursor;
 Loop
     FETCH myCursor into vDepartamento, VTotalEmpleados;
Exit when myCursor%NOTFOUND;
Select Department_Name into vNomDepto
From Departments
     where department_id = vDepartamento;
DBMS_output.put_line(vNomDepto||' '||vTotalEmpleados);
end Loop;
Close myCursor;

End

Declare
Cursor myCursor is Select department_id, count(*) as "total empleados"
from employees
where Department_id is not null
group by department_id
order by department_id;
vDepartamento employees.employee_id%type;
vTotalEmpleados integer;
Begin
 Open myCursor;
 Loop
     FETCH myCursor into vDepartamento, VTotalEmpleados;
Exit when myCursor%NOTFOUND;
DBMS_output.put_line(vDepartamento||' '||vTotalEmpleados);
end Loop;
Close myCursor;

End




declare
vNumDepto integer := 10;
vNomDepto varchar2(30);

Begin
Select Department_Name into vNomDepto
From Departments
where department_id = 10;
DBMS_output.put_line('El codigo del departamento es : '||vNumDepto);
DBMS_output.put_line('El codigo del departamento es : '||vNomDepto);
End

Clase de hoy: Resolvimos el problema de la clase pasada. [Parte 1]

Select department_id, count(*) as "total empleados"
from employees
group by department_id

desc departments

DEPARTMENT_NAME

Select DEPARTMENT_NAME as "Departamento", count(*) as "total empleados"
from employees, departments
group by DEPARTMENT_NAME

Select DEPARTMENT_NAME as "Departamento", count(*) as "total empleados"
from employees e, departments d
where e.department_id = d.department_id
group by DEPARTMENT_NAME


Select DEPARTMENT_NAME, count(Employee_id)
from employees e, departments d
where e.department_id = d.department_id
having count (Employee_id)>2
group by department_name
order by department_name


Select department_id, count(*) as "total empleados"
from employees
group by department_id
order by department_id

Para relajarse :D

                     

Taller 01 (LIKE, COUNT, ORDER BY, TABLA DUAL)

Aqui podran ver y estudiar el taller Nº1, podran apreciar las preguntas y las respuestas para cada una de ellas


Link para ver y descargar el .Docx -------> Taller Nº1

PROGRAMACIÓN DE BASES DE DATOS (OCA DEVELOP)

Bienvenidos al 2º semestre 2011