Clase 15-04-13

drop table Empleado cascade constraints;
drop table Departamento cascade constraints;
drop table Asignacion cascade constraints;

CREATE TABLE Empleado(
  num_emp number,
  nombre varchar2(40) constraint non_nn NOT NULL,
  tarifa_hora varchar2(20) constraint tar_nn NOT NULL,
  profesion varchar2(40) constraint pro_nn NOT NULL,
  constraint num_emp_pk primary key(num_emp));

CREATE TABLE Departamento(
  numero number,
  nombre varchar2(40) constraint nondep_nn NOT NULL,
  constraint numero_pk primary key(numero));

CREATE TABLE Asignacion(
  num_emp number, constraint num_emp_fk foreign key(num_emp)
  references Empleado(num_emp),
  fecha_ini varchar2(20) constraint fecha_ini_nn NOT NULL,
  numerodep number, constraint numerodep_fk foreign key(numerodep)
  references Departamento(numero));

INSERT into Empleado VALUES(1235,'Marcia Pérez','7000','Contador');
INSERT into Empleado VALUES(1412,'Carlos garcía','6500','Analista de Sistemas');
INSERT into Empleado VALUES(2920,'Ricardo Gómez','5800','Analista de Sistemas');
INSERT into Empleado VALUES(3231,'Patricio Morán','6300','Ingeniero en Informática');
INSERT into Empleado VALUES(2331,'Paula Marín','9000','Ingeniero en Informática');
INSERT into Empleado VALUES(4531,'Ricardo Pardo','6300','Contador');
INSERT into Empleado VALUES(8931,'ana Mora','7300','Analista de Sistemas');
INSERT into Empleado VALUES(3267,'Paula Morán','9200','Ingeniero Comercial');

INSERT into departamento VALUES(101014,'Finanzas');
INSERT into departamento VALUES(110115,'Contabilidad');
INSERT into departamento VALUES(101514,'Informática');
INSERT into departamento VALUES(110215,'Desarrollo');

INSERT into Asignacion VALUES(1235,'20/03/2013',110115);
INSERT into Asignacion VALUES(2920,'18/01/2013',110215);
INSERT into Asignacion VALUES(1412,'17/02/2013',110215);
INSERT into Asignacion VALUES(3231,'20/03/2013',101514);
INSERT into Asignacion VALUES(2331,'25/02/2013',110215);
INSERT into Asignacion VALUES(4531,'18/01/2013',101014);
INSERT into Asignacion VALUES(8931,'17/01/2013',110215);
INSERT into Asignacion VALUES(3267,'20/01/2013',101014);
INSERT into Asignacion VALUES(2920,'25/03/2013',101514);


--Mostrar los nombre de los empleados que ingresaron en el 20-03-13
select nombre, profesion
from empleado e
join Asignacion asi on e.num_emp = asi.num_emp
where fecha_ini = '20/03/2013';

--
select *
from Empleado e
join Asignacion asi on e.num_emp = asi.num_emp
join Departamento d on asi.numerodep = d.numero
order by e.nombre;

--Mostra la cantidad de dias que llevan trabaja
select nombre, sum(trunc(sysdate - to_date(fecha_ini, 'dd-mm-yyyy')))
from empleado e
join asignacion asi on e.num_emp = asi.num_emp
where lower(nombre) in ('carlos garcía', 'ana mora')
group by nombre
order by nombre;

select profesion, count(*)
from empleado
group by profesion;

select count(*)
from empleado
where tarifa_hora < (select avg(tarifa_hora)from empleado);

select nombre, tarifa_hora, tarifa_hora - (select avg(tarifa_hora)from empleado)as diferencia
from empleado;

--en una sola instruccion select mostrar el max y min de las tarifa_hora de los empleados
select max(tarifa_hora) as "tarifa maxima", min(tarifa_hora) as "tarifa minima" from empleado;

--mostrar el nombre del empleado y nombre de departamento cuya fecha de inicio de asignacion esta entre 01/01/2013
--al 01/03/2013, ambas fechas inclucive el resultado debe salir en minusculas debe usar where.
select e.nombre, d.nombre, fecha_ini
from empleado e, asignacion asi, departamento d
where e.num_emp = asi.num_emp
and asi.numerodep = d.numero
and to_date (fecha_ini)between '01/01/2013' and '01/03/2013';

--mostrar el nombre del departamento de los empleados que no pertenece al departamento del desarrollo

select to_char(sysdate,'month')
from dual;

select INSTR('la clase es grande','es',1,2)
from dual;

select next_day('25-03-2013','lunes')
from dual;

select next_day(sysdate, 'martes')
from dual;


select Rpad('texto original',20,'*')
from dual;

select Lpad('texto original',20,'*')
from dual;

select Ltrim('texto original','itzel')
from dual;

select round(123.355,2)
from dual;

---------------------------------------------------------------------
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');

--Mostrar la cantidad de horas que hay que registrar por dia
select * from hora;

select fecha, sum(horas)
from hora
group by fecha;

--Mostrar el promedio de las edades de los empleados
select * from empleado;

select avg (trunc((sysdate - fechnac)/365))
from empleado;


select nombre, fechnac, trunc((sysdate - fechnac)/365)
from empleado;

--Mostrar los nombre d elos empleados que no tienen registrado
--telefonos en la base de datos

select *
from empleado
where telefono is null;

--Mostrar los nombres de los empleados que trabajaron
--en Noviembre del 2012

select nombre
from empleado e, hora h
where e.rut = h.rut
and fecha between '01-11-2012' and '30-11-2012';

select * from hora
where fecha between '01-11-2012' and '30-11-2012';

--Mostrar el codigo y nombre de los proyectos cuya area es
--Arquitecto o Marketing

select codpro, nombre
from proyecto
where area in ('Marketing','Arquitecto');

select codpro, nombre
from proyecto
where lower (area) in ('marketing','arquitecto');

--Crear una vista llamada VempCiudad que muestre la cantidad de los
--Empleados por ciudad, incluyendo solamente
--las ciudades de Santiago y Valparaiso

create view VempCiudad as
select upper (ciudad) ciudad, count(rut) cantidad
from Empleado
where lower (ciudad) in ('santiago', 'valparaiso')
group by ciudad;

select *from VempCiudad;

--Crear la vista VproyFigueroa que muestre los nombres
--de los proyectos en los que han trabajado Pedro Figueroa,
--eliminando los registros duplicados (Sin JOIN)

create view VproyFigueroa as
select distinct p.nombre
from empleado e, proyecto p, hora h
where h.codpro = p.codpro
and e.rut = h.rut
and lower(e.nombre) = 'pedro figueroa';


--Crear la vista VproyFigueroa que muestre los nombres
--de los empleados y el nombre de los proyectos, que se trabajaron el
--dia 2012-11-07 (Usando where, usar JOIN)

create view VEmpProy as
select p.nombre nombre_proy, e.nombre nombre_emp
from hora h
join proyecto p on p.codpro = h.codpro
join empleado e on e.rut = h.rut
where fecha = to_date('2012-11-07', 'yyyy-mm-dd');

select * from VEmpProy;