Prueba+evaluación+bloque+1

code format="sql" /* Vamos a trabajar con una base de datos para generar las nóminas de una administración. Tendremos las tablas que se muestran en el script, donde se indican con comentarios su significado y el de su atributos.

DROP TABLE EX_EMPLEADO CASCADE CONSTRAINTS; DROP TABLE EX_DEPARTAMENTO CASCADE CONSTRAINTS; DROP TABLE EX_NOMINA CASCADE CONSTRAINTS; DROP TABLE EX_COMPLEMENTO CASCADE CONSTRAINTS; DROP TABLE EX_GRUPO_NIVEL CASCADE CONSTRAINTS; DROP TABLE EX_COMP_EMP CASCADE CONSTRAINTS;

-- Compelementos que puede cobrar un empleado CREATE TABLE EX_COMPLEMENTO ( COD_COMPLEMENTO NUMBER(2) PRIMARY KEY, NOMBRE VARCHAR2(40 CHAR), IMPORTE NUMBER(6,2) );

INSERT INTO EX_COMPLEMENTO VALUES (1,'Destino dificultoso',200); INSERT INTO EX_COMPLEMENTO VALUES (2,'General',60.2); INSERT INTO EX_COMPLEMENTO VALUES (3,'Productividad',90); INSERT INTO EX_COMPLEMENTO VALUES (4,'Dirección',1700); INSERT INTO EX_COMPLEMENTO VALUES (5,'Jefatura',600);

-- Departamentos en la empresa CREATE TABLE EX_DEPARTAMENTO ( COD_DEPARTAMENTO NUMBER(2) NOT NULL ENABLE, NOMBRE VARCHAR2(20 BYTE), COMPLEMENTO NUMBER(6,2), CONSTRAINT EX_DEPARTAMENTO_PK PRIMARY KEY (COD_DEPARTAMENTO) );

INSERT INTO EX_DEPARTAMENTO VALUES (1,'Dirección',100); INSERT INTO EX_DEPARTAMENTO VALUES (2,'Producción',40.43);

-- Empleados en la empresa CREATE TABLE EX_EMPLEADO ( DNI NUMBER(9,0), NOMBRE VARCHAR2(20 BYTE), APELLIDOS VARCHAR2(40 BYTE), DOMICILIO VARCHAR2(100 BYTE), DEPARTAMENTO NUMBER(2), GRUPO CHAR(1), NIVEL NUMBER(2), CONSTRAINT EX_EMPLEADO_PK PRIMARY KEY (DNI) );

INSERT INTO EX_EMPLEADO VALUES (11111111,'Nombre1','Ape1','Dom1',1,'A',29); INSERT INTO EX_EMPLEADO VALUES (22222222,'Nombre2','Ape2','Dom2',1,'C',19); INSERT INTO EX_EMPLEADO VALUES (33333333,'Nombre3','Ape3','Dom3',2,'B',24); INSERT INTO EX_EMPLEADO VALUES (44444444,'Nombre4','Ape4','Dom4',2,'D',18);

-- Complementos del empleado CREATE TABLE EX_COMP_EMP ( EMPLEADO NUMBER(9,0), COMPLEMENTO VARCHAR2(20 BYTE), CONSTRAINT EX_COMP_EMP_PK PRIMARY KEY (EMPLEADO,COMPLEMENTO) );

INSERT INTO EX_COMP_EMP VALUES (11111111,4); INSERT INTO EX_COMP_EMP VALUES (22222222,1); INSERT INTO EX_COMP_EMP VALUES (22222222,3); INSERT INTO EX_COMP_EMP VALUES (33333333,5); INSERT INTO EX_COMP_EMP VALUES (44444444,3); INSERT INTO EX_COMP_EMP VALUES (11111111,2); INSERT INTO EX_COMP_EMP VALUES (22222222,2); INSERT INTO EX_COMP_EMP VALUES (33333333,2); INSERT INTO EX_COMP_EMP VALUES (44444444,2);

-- Grupos y niveles que puede tener los empleados. Determina el sueldo base, por -- ejemplo, un empleado A-24 cobrará 1023 €, y un B-22 cobrará 989 € CREATE TABLE EX_GRUPO_NIVEL ( COD_GRUPO CHAR(1) NOT NULL ENABLE, -- Grupo, puede ser 'A', 'B', ...'D' COD_NIVEL NUMBER(2) NOT NULL ENABLE, -- Nivel, puede ser de 12 a 30 SUELDO_BASE NUMBER(6,2), -- El sueldo base que corresponde a un grupo y nivel determinado CONSTRAINT EX_CUERPO_NIVEL_PK PRIMARY KEY (COD_GRUPO, COD_NIVEL) );

INSERT INTO EX_GRUPO_NIVEL VALUES ('A',29,1578.87); INSERT INTO EX_GRUPO_NIVEL VALUES ('C',19,965.23); INSERT INTO EX_GRUPO_NIVEL VALUES ('B',24,1187.00); INSERT INTO EX_GRUPO_NIVEL VALUES ('D',18,798.27);

-- Datos de la nómina. Existirá una fila por cada empleado y més, para indicar -- lo que ha cobrado el empleado ese més. CREATE TABLE EX_NOMINA ( EMPLEADO NUMBER(9,0), -- Empleado AÑO NUMBER(4,0), -- Año de la nómina (2009, 2010, ...) MES VARCHAR2(20 BYTE), -- Mes de la nómina (Enero, Febrero, etc.) INGRESOS_BRUTOS NUMBER(6,2), -- Ingresos brutos RETENCIONES NUMBER(6,2), -- Retención, en euros del IRPF SEG_SOC NUMBER(6,2), -- Aportación a la seguridad social INGRESOS_NETO NUMBER (6,2), -- Ingresos netos (lo que se ingresa al empleado) CONSTRAINT EX_NOMINA_PK PRIMARY KEY (EMPLEADO,AÑO,MES), CONSTRAINT EX_NOMINA_EMP_FK FOREIGN KEY (EMPLEADO) REFERENCES EX_EMPLEADO );

INSERT INTO EX_NOMINA (EMPLEADO,AÑO,MES,INGRESOS_BRUTOS,RETENCIONES,SEG_SOC,INGRESOS_NETO) VALUES (11111111,2011,'Enero',3439.07,584.64,103.17,2751.26); INSERT INTO EX_NOMINA (EMPLEADO,AÑO,MES,INGRESOS_BRUTOS,RETENCIONES,SEG_SOC,INGRESOS_NETO) VALUES (22222222,2011,'Enero',1415.43,240.62,42.46,1132.34); INSERT INTO EX_NOMINA (EMPLEADO,AÑO,MES,INGRESOS_BRUTOS,RETENCIONES,SEG_SOC,INGRESOS_NETO) VALUES (33333333,2011,'Enero',1887.63,320.9,56.63,1510.1); INSERT INTO EX_NOMINA (EMPLEADO,AÑO,MES,INGRESOS_BRUTOS,RETENCIONES,SEG_SOC,INGRESOS_NETO) VALUES (44444444,2011,'Enero',988.9,168.11,29.67,791.12);

alter table ex_empleado add constraint FK_EMP_DPTO foreign key (departamento) references EX_DEPARTAMENTO; alter table ex_empleado add constraint FK_emp_grupo_nivel foreign key (grupo,nivel) references ex_grupo_nivel;

create or replace view EX_COMPLEMENTOS_TOTALES as select empleado dni, sum(c.importe) complementos from ex_complemento c,ex_comp_emp ce where c.cod_complemento=ce.complemento group by ce.empleado;

insert into ex_nomina(empleado,año,mes,ingresos_brutos,retenciones,seg_soc,ingresos_neto) select e.dni, to_char(sysdate,'YYYY'), to_char(sysdate,'Month'), gn.sueldo_base+d.complemento+ ct.complementos, (gn.sueldo_base+d.complemento+ ct.complementos)*0.17, (gn.sueldo_base+d.complemento+ ct.complementos)*0.03, (gn.sueldo_base+d.complemento+ ct.complementos)*0.8 from ex_empleado e, ex_departamento d,ex_grupo_nivel gn, ex_complementos_totales ct where e.departamento= d.cod_departamento and e.grupo= gn.cod_grupo and e.nivel=gn.cod_nivel and e.dni= ct.dni;

-- EJERCICIOS

-- a. Realiza una consulta que muestre las filas de la tabla nóminas con algún error de cálculo.

-- b. Muestra el número de complementos y su suma, para aquellos empleados que no pertenezcan al --   departamento de 'Dirección' (es decir, todo lo que cuestan los complementos que se pagan --    a los empleados que no son de 'Dirección'.

-- c. Muestra los grupos y niveles que no están asignados a ningún empleado del departamento de 'Dirección'.

-- d. Muestra los datos del número de empleados que cobran el complemento por 'Productividad'.

-- e. Obtener un listado de los empleados que cobran el complemento de Jefatura, y que en sus nóminas pasadas --   no cobraron un sueldo bruto de menos de 1000 euros.

code

code format="sql" -- a. Realiza una consulta que muestre las filas de la tabla nóminas con algún error de cálculo. select * from ex_nomina where (ingresos_brutos- retenciones- seg_soc) <> ingresos_neto;

-- b. Muestra el número de complementos y su suma, para aquellos empleados que no pertenezcan al --   departamento de 'Dirección' (es decir, todo lo que cuestan los complementos que se pagan --    a los empleados que no son de 'Dirección'. select count(ce.empleado),sum(c.importe) from ex_comp_emp ce,ex_complemento c where ce.complemento= c.cod_complemento  and ce.empleado not in (select dni from ex_empleado where ex_empleado.departamento = (select cod_departamento                                                          from ex_departamento d                                                           where d.nombre='Dirección') );

-- c. Muestra los grupos y niveles que no están asignados a ningún empleado del departamento de 'Dirección'. select cod_grupo, cod_nivel from ex_grupo_nivel gn where (cod_grupo, cod_nivel) not in (select (cod_grupo, cod_nivel)                                    from ex_empleado e,ex_departamento d                                     where e.departamento= d.cod_departamento and                                           d.nombre='Dirección');

-- d. Muestra los datos del número de empleados que cobran el complemento por 'Productividad'. select count(*) from ex_complemento,ex_comp_emp where ex_complemento.cod_complemento=ex_comp_emp.complemento and nombre='Productividad';

-- e. Obtener un listado de los empleados que cobran el complemento de Jefatura, y que en sus nóminas pasadas --   no cobraron un sueldo bruto de menos de 1000 euros. elect empleado from ex_complemento c,ex_comp_emp ce where c.cod_complemento=ce.complemento and nombre='Jefatura' and 1000 < all (select ingresos_brutos                                       from ex_nomina n                                        where n.empleado=ce.empleado);

-- b. Muestra el número de complementos y su suma, para aquellos empleados que no pertenezcan al --   departamento de 'Dirección'.

code