Drop table Proyecto cascade constraints;
Drop table Empleado cascade constraints;
Drop table Hora cascade constraints;
create table Proyecto (
codpro varchar2 (5),
nombre varchar2 (25),
area varchar2 (20),
oficina varchar2 (5)
);
alter table Proyecto
add constraint proyecto_pk primary key (codPro);
create table Empleado (
rut varchar2 (12),
nombre varchar2 (25),
fechnac date,
direccion varchar2 (35),
ciudad varchar2 (15),
telefono varchar2 (10)
);
alter table Empleado
add constraint empleado_pk primary key (rut);
create table Hora (
fecha date,
horas integer,
codpro varchar2(5),
rut varchar2 (12)
);
alter table Hora
add constraint Hora_fk_references_Proyecto foreign key (codPro)
references Proyecto (codPro);
alter table Hora
add constraint Hora_fk_references_empleado foreign key (rut)
references Empleado (rut);
/*
PROYECTO
101 WEB Ipaid Marketing 1
102 Plan Cuentas Contabilidad 2
103 Sala Clases Arquitectura 3
104 Capacitación RRHH 4
EMPLEADO
1-7 Luis Ponce 25-ene-1958 Cienfuegos 45 Santiago 5678654
2-8 Ana Zamora 30-mar-1960 null Santiago 6754345
3-5 Pedro Figueroa 1-ene-1970 Portales 890 Valparaíso null
4-6 Camila Espinoza 27-04-1980 cuevas 67 Rancagua null
HORA
2012-11-07 2 101 4-6
2012-11-07 4 101 1-7
2012-11-04 3 102 3-5
2012-12-03 6 103 3-5
*/
insert into Proyecto values ('101','WEB Ipaid','Marketing','1');
insert into Proyecto values ('102','Plan Cuentas','contabilidad','2');
insert into Proyecto values ('103','Sala Clases','Arquitectura','3');
insert into Proyecto values ('104','Capacitacion','RRHH','4');
insert into Empleado values ('1-7','Luis Ponce','25-ene-1958','cienfuegos 45','santiago','5678654');
insert into Empleado values ('2-8','Ana Zamora','30-mar-1960',null,'santiago','67543445');
insert into Empleado values ('3-5','Pedro Figueroa','01-ene-1970','portales 890','Valparaiso',null);
insert into Empleado values ('4-6','Camila Espinoza','27-04-1980','cuevas 67','Rancagua',null);
insert into Hora values (to_date('2012-11-07', 'yyyy-mm-dd'),2,'101','4-6');
insert into Hora values (to_date('2012-11-07', 'yyyy-mm-dd'),4,'101','1-7');
insert into Hora values (to_date('2012-11-04', 'yyyy-mm-dd'),3,'102','3-5');
insert into Hora values (to_date('2012-12-03', 'yyyy-mm-dd'),6,'103','3-5');
---------------------------------------------------------------------------------------
 Control N°2
a) Mostrar la cantidad de hora que hay registrada por dia
select fecha, sum(horas)
from hora
group by fecha
------------------------------------------------
b) Mostrar el promedio de la edad de los empleados
select trunc((sysdate - to_date('29-09-1975'))/365)
from dual;
select round(avg (trunc ((sysdate -fechnac))/365),1)
from empleado;
-------------------------------------------------------------
c) Mostrar el nombre de los empleados que no tienen registrados telefonos en la base de datos
select nombre
from empleado
where telefono is null;
NVL : Si el valor es nulo, pongale valor por ejemplo, si el valor es Nulo, hay que ponerlo en 0
---------------------------------------------------------------------------
d) Mostrar los nombres de los empleados que trabajaron en Noviembre del 2012
select nombre
from empleado e
join hora h on  e.rut=h.rut
where fecha between '1-11-2012' and  '30-11-2012' ;

---Usando natural Join
select nombre
from empleado natural join hora
where fecha between '1-11-2012' and  '30-11-2012' ;

---------------------------------------------------------------------------------
e) Mostrar el codigo y nombre de los proyectos cuya Area sea es Arquitectura o Marketing
select codpro, nombre
from proyecto
where lower (area) in ('arquitectura',  'marketing');
-----------------------------------------------
f) Crear una vista llamada VEmpCiudad que muestre la cantidad
de empleados por ciudad, incluyendo solamente las ciudades
de Santiago y Valparaiso
drop view VEmpCiudad;
create view VEmpCiudad as
select ciudad, count(*)as Total
from empleado
where lower(ciudad)in ('santiago', 'valparaiso')
group by ciudad;
desc VEmpCiudad
select * from VEmpCiudad;
select ciudad, count(*)
from empleado
where lower(ciudad)in ('santiago', 'valparaiso')
group by ciudad;
---Ahora seleccionando las ciudades con mas de un empleado
select ciudad, count(*) total
from empleado
group by ciudad
having count (*) >1;
--------------------------------------------------------------
Crear una vists VProyFigueroa que muestre los nombres de los proyectos en los que ha trabajado Pedro
Figueroa, eliminando los registros duplicados (use Join)
select p.nombre
from proyecto p
join hora h on p.codpro=h.codpro
join empleado e on e.rut=h.rut
where lower (e.nombre) = 'pedro figueroa';
---Con subSelect
select rut
from empleado
where lower (nombre) = 'pedro figueroa';
select nombre
from proyecto
where codpro in (select codpro
from hora
where rut=(select rut
from empleado
where lower (nombre) = 'pedro figueroa'));
select codpro
from hora
where rut=(select rut
from empleado
where lower (nombre) = 'pedro figueroa');
create or replace view xyz as
select nombre
from proyecto
where codpro in (select codpro
from hora
where rut=(select rut
from empleado
where lower (nombre) = 'pedro figueroa'));
select * from xyz;
---------------------------------
Crear la vista VEmpProy que muestre los nombres de los empleados y el nombre de los proyectos, que se
trabajaron el dia 2012-11-07.(Usando Where, sin usar Join)
select e.nombre, p.nombre
from empleado e, hora h,proyecto p
where e.rut=h.rut
and h.codpro=p.codpro
and fecha= to_date('2012-11-07', 'yyyy-mm-dd');
create or replace view VEmpProy as
select e.nombre "nombre empleado" , p.nombre "nombre proyecto"
from empleado e, hora h,proyecto p
where e.rut=h.rut
and h.codpro=p.codpro
and fecha= to_date('2012-11-07', 'yyyy-mm-dd');
desc VEmpProy;
select * from VEmpProy;
select "nombre empleado" from VEmpProy;
--------------------------------------------------
select *
from  user_views;