EJEMPLO DE CATEDRA - BASE DE DATOS

Prévia do material em texto

Facultad de Ingeniería-UNJu 
Cátedra: Base de Datos – 2016 Ejemplo de Cátedra PostgreSQL 1 
EJEMPLO DE CÁTEDRA - BASE DE DATOS – 2º CUATRIMESTR E 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 RUBR O ya fueron creadas, cree las 
demás tablas en el orden que corresponda incorpora ndo 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 
 
 
 
Facultad de Ingeniería-UNJu 
Cátedra: Base de Datos – 2016 Ejemplo de Cátedra PostgreSQL 2 
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 cor responden 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 empl eado. 
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 Em pleador 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 po r 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 ingresa r un empleado con nombre Juan inserte 
un nuevo registro en la tabla especializacion con c odigo 111, nombre de especialidad 
"soldador" y Precio por hora 200. 
 
 
 
 
 
 
 
Facultad de Ingeniería-UNJu 
Cátedra: Base de Datos – 2016 Ejemplo de Cátedra PostgreSQL 3 
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ám etros fechainicio y fechafinal, cargue 
el cursor con los datos de trabajosxfecha correspon dientes a esas dos fechas, lea el 
cursor y grabe dichos datos en la tabla “trabajoesp ecial”, 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';