POSTGRESQL - Samuel Ferrara

Vista previa del material en texto

Facultad de Ingeniería-UNJu 
EJEMPLO DE CÁTEDRA - BASE DE DATOS – 2º CUATRIMESTRE 2016 
Observaciones: El total cobrado por cada trabajo se calcula como la suma de todos los (PrecioHora por HorasTrabajadasTotales). 
1) Suponiendo que las tablas ESPECIALIZACION Y RUBRO ya fueron creadas, cree las demás tablas en el orden que corresponda incorporando la/s clave/s foránea/s en la/s tabla/s respectivas. 
CREATE TABLE empleado (Cuil integer PRIMARY KEY,NomEmpl varchar(40), CodEspecial integer, FOREIGN KEY (CodEspecial) REFERENCES Especializacion(CodEspecial)); 
CREATE TABLE empleador (Cuit integer PRIMARY KEY,NomEmpresa varchar(40), CodRubro integer, FOREIGN KEY (CodRubro) REFERENCES Rubro(CodRubro)); 
CREATE TABLE trabajo (CodTrabajo integer PRIMARY KEY,Descripcion varchar(40), Tipo varchar(10),Cuil integer, Cuit integer, FOREIGN KEY (Cuil) REFERENCES Empleado(Cuil), FOREIGN KEY (Cuit) REFERENCES Empleador(Cuit)); 
CREATE TABLE TrabajoFecha (CodTrabajo integer PRIMARY KEY,FechaInicio date, FechaFin date,HsTrabajadasTotales integer, CodTrabajo integer, FOREIGN KEY (CodTrabajo) REFERENCES Trabajo(CodTrabajo)); 
2) Muestre la cantidad de trabajos que realizó cada empleado. 
SELECT cuil, COUNT(*) FROM trabajo GROUP BY cuil; 
3) Indicar el importe total pagado en cada trabajo. 
Utilizando pareo de tablas 
SELECT trabajo.codtrabajo, SUM(preciohora*hstrabajadastotales) FROM trabajo, trabajofecha, empleado, especialización WHERE trabajo.codtrabajo = trabajofecha.codtrabajo AND trabajo.cuil = empleado.cuil AND empleado.codespecial = especializacion.codespecial GROUP BY trabajo.codtrabajo 
Utilizando alias de tablas 
SELECT t.codtrabajo, SUM(preciohora*hstrabajadastotales) FROM trabajo t, trabajofecha tf, empleado e, especializacion es WHERE t.codtrabajo = tf.codtrabajo AND t.cuil = e.cuil AND e.codespecial = es.codespecial GROUP BY t.codtrabajo 
Cátedra: Base de Datos – 2016 Ejemplo de Cátedra PostgreSQL 1 
Facultad de Ingeniería-UNJu 
Utilizando inner join 
SELECT trabajo.codtrabajo, SUM(preciohora*hstrabajadastotales) FROM trabajo INNER JOIN trabajofecha ON trabajo.codtrabajo = trabajofecha.codtrabajo INNER JOIN empleado ON trabajo.cuil = empleado.cuil INNER JOIN especializacion ON empleado.codespecial = especializacion.codespecial GROUP BY trabajo.codtrabajo; 
4) Usando subconsultas muestre los trabajos en que trabajaron empleados cuya especialización es electricista 
SELECT * FROM trabajo WHERE cuil IN (select cuil FROM empleado WHERE codespecial = (SELECT codespecial FROM especialización WHERE nomespecial ='Electricista')); 
5) Usando subconsultas muestre los trabajos que corresponden al nombre de rubro “Seguridad”. 
SELECT * FROM trabajo WHERE cuit IN (SELECT cuit FROM empleador WHERE codrubro = (SELECT codrubro FROM rubro WHERE nomrubro = 'Informatica')); 
6) Obtener la suma total de hs. Trabajadas por empleado. 
Utilizando inner join 
SELECT trabajo.cuil, SUM(hstrabajadastotales) FROM trabajo INNER JOIN trabajofecha ON trabajo.codtrabajo = trabajofecha.codtrabajo GROUP BY trabajo.cuil 
Utilizando pareo de tablas 
SELECT trabajo.cuil, SUM(hstrabajadastotales) FROM trabajo where trabajo.codtrabajo = trabajoxfecha.codtrabajo GROUP BY trabajo.cuil 
7) Muestre el Nombre del Empleado, el Nombre del Empleador para todos los trabajos de tipo “Urgente”. 
SELECT NomEmpl, NomEmpresa FROM Empleado, Trabajo, Empleador WHERE Empleado.Cuil = Trabajo.Cuil AND Trabajo.Cuit = Empleador.Cuit AND Tipo = “Urgente” 
8) Realice un procedimiento al cual se le pase como parámetro el Cuil del Empleado y obtenga en una consulta el total general cobrado por el mismo. 
CREATE OR REPLACE FUNCTION punto8 (integer) RETURNS integer AS ‘ DECLARE 
 total integer; 
BEGIN 
 total := 0; 
total := (select sum(preciohora*hstrabajadastotales) from Especializacion, Empleado, Trabajo, Trabajosxfecha where especializacion.Codespecial = empleado.codespecial and empleado.cuil = trabajo.cuil and trabajo.codtrabajo = trabajosxfecha.codtrabajo); 
 return total 
END; 
‘ language ‘plpgsql’; 
9) Cree un trigger que en el caso de querer ingresar un empleado con nombre Juan inserte un nuevo registro en la tabla especializacion con codigo 111, nombre de especialidad "soldador" y Precio por hora 200. 
Cátedra: Base de Datos – 2016 Ejemplo de Cátedra PostgreSQL 2 
Facultad de Ingeniería-UNJu 
CREATE OR REPLACE FUNCTION ejemploTrigger() RETURNS trigger AS ' BEGIN 
 if new.nombreEmpleado = 'Juan' then 
 insert into especializacion(CodEspecial, NomEspecial, PrecioHora) 
 values (111, 'soldador', 200); 
 end if; 
 return null; 
END; 
'language 'plpgsql'; 
 
CREATE TRIGGER t_punto10 BEFORE INSERT ON empleado 
FOR EACH ROW 
EXECUTE PROCEDURE ejemploTrigger(); 
10) Realice una función que reciba como parámetros fechainicio y fechafinal, cargue el cursor con los datos de trabajosxfecha correspondientes a esas dos fechas, lea el cursor y grabe dichos datos en la tabla “trabajoespecial”, suponga que esta tabla ya fue creada con anterioridad. 
CREATE OR REPLACE FUNCTION punto10 (DATE,DATE) RETURNS void AS $body$ 
declare 
 desde alias for $1; 
 hasta alias for $2; 
 fila cursor for select * from trabajoxfecha 
 where fechainicio >= desde fechafinal <= hasta; 
 contenido trabajoxfecha%ROWTYPE; 
BEGIN 
 open fila; 
 loop 
 fetch fila into contenido; 
 if NOT FOUND then 
 exit; 
 end if; 
insert into trabajoespecial(codtrabajo, codtrabaj, fechainicio, fechafinal, 
hstrabajadastotales) 
 values(contenido.codtrabajo, contenido.codtrabaj, contenido.fechainicio, contenido.fechafinal, contenido.hstrabajadastotales); 
 end loop; 
 close fila; 
END; 
$body$ 
LANGUAGE 'plpgsql';
Cátedra: Base de Datos – 2016 Ejemplo de Cátedra PostgreSQL 3