Prueba+de+evaluación+2

prompt ================= Borrando tablas ================= DROP TABLE TRABAJO_REALIZADO cascade constraints; DROP TABLE DEPARTAMENTO cascade constraints; DROP TABLE EMPLEADO cascade constraints; DROP TABLE CATEGORIA cascade constraints; DROP TABLE PROYECTO cascade constraints;

prompt ================= Creando tablas ================= CREATE TABLE CATEGORIA ( cat char(2) constraint PK_CATEGORIA PRIMARY KEY, --categoría descrip varchar2(200), --descripción correspondiente a la categoría tarifa_horaria number(5,2) --euros por hora trabajada );

CREATE TABLE DEPARTAMENTO ( dept char(4) constraint PK_DEPARTAMENTO PRIMARY KEY, -- código del departamento nombre varchar2(100) NOT NULL, -- nombre del departamento ubicacion varchar2(100), -- edificio y planta director number(4) -- director del departamento );

CREATE TABLE EMPLEADO ( numero number(4) constraint PK_EMPLEADO PRIMARY KEY, -- número de empleado nombre varchar2(100) NOT NULL, --nombre del empleado dir varchar2(80), --dirección poblacion varchar2(40) NOT NULL, --población del empleado dept char(4) NOT NULL constraint FK_DEPT_EMPLEADO REFERENCES DEPARTAMENTO, --departamento donde trabaja jefe number(4) constraint FK_JEFE_EMPLEADO REFERENCES EMPLEADO, --número de su jefe cat char(2) NOT NULL constraint FK_CAT_EMPLEADO REFERENCES CATEGORIA -- categoría );

CREATE TABLE PROYECTO ( identif varchar2(4) constraint PK_PROYECTO primary key, --código del proyecto (clave primaria) nombre varchar2(100) not null, --nombre del proyecto (no admite nulos) presupuesto number(10) not null, --presupuesto del proyecto (no admite nulos) poblacion varchar(30) not null, --población donde se desarrolla el proyecto (no admite nulos) dept char(4) not null constraint FK_DEPT_PROYECTO references DEPARTAMENTO, --departamento donde se desarrolla el proyecto (no admite nulos) responsable number(4) constraint FK_EMPLEADO_PROYECTO references EMPLEADO --empleado responsable del proyecto (no admite nulos) );

CREATE TABLE TRABAJO_REALIZADO ( numero number(4) constraint FK_EMPLEADO references EMPLEADO, -- número del empleado que ha realizado el trabajo, identif varchar(4) constraint FK_PROYECTO references PROYECTO, -- código del proyecto realizado inicio date, -- cuando se comienza el trabajo fin date not null, -- cuando se finaliza el trabajo (no admite nulos) horas number(4) not null,-- número de horas de trabajo invertidas (no admite nulos) constraint PK_TRABAJO_REALIZADO primary key(numero,identif) );

prompt ...hecho.

prompt ================= Inserción de datos de ejemplo ================= INSERT INTO CATEGORIA VALUES ('DI','Dirección',350); INSERT INTO CATEGORIA VALUES ('DT','Diseñador técnico',110); INSERT INTO CATEGORIA VALUES ('E1','Ebanista primera',90); INSERT INTO CATEGORIA VALUES ('E2','Ebanista segunda',90);

INSERT INTO DEPARTAMENTO VALUES ('D100','Dirección','Edificio principal, planta segunda',NULL); INSERT INTO DEPARTAMENTO VALUES ('D101','Producción','Fábrica, planta primera',NULL); INSERT INTO DEPARTAMENTO VALUES ('D102','Control de calidad','Edificio principal, planta primera',NULL);

INSERT INTO EMPLEADO VALUES(1,'Empleado 1','Dirección empleado 1','Población 1','D100', NULL, 'DI'); INSERT INTO EMPLEADO VALUES(2,'Empleado 2','Dirección empleado 2','Población 2','D101', NULL, 'E1'); INSERT INTO EMPLEADO VALUES(3,'Empleado 3',NULL,'Población 2','D101', 1, 'E1'); INSERT INTO EMPLEADO VALUES(4,'Empleado 4','Dirección empleado 4','Población 2','D101', 1, 'E2'); INSERT INTO EMPLEADO VALUES(5,'Empleado 5','Dirección empleado 5','Población 1','D102', 1, 'DT');

INSERT INTO PROYECTO VALUES ('P001','Muebles Cocina Tipo 1',145000,'Población 2','D101',2); INSERT INTO PROYECTO VALUES ('P002','Muebles Cocina Tipo 2',115000,'Población 2','D101',2); INSERT INTO PROYECTO VALUES ('P003','Proyecto de Marketing',115000,'Población 2','D100',1);

insert into trabajo_realizado values ('1','P001','25/12/1990','27/01/2001',500); insert into trabajo_realizado values ('2','P002','03/02/2003','07/09/2004',700); insert into trabajo_realizado values ('3','P001','23/12/2009','15/02/2010',200); insert into trabajo_realizado values ('5','P003','21/07/2004','25/08/2006',900);

update departamento set director='0001' where dept='D100'; update departamento set director='0002' where dept='D101'; update departamento set director='0003' where dept='D102';

alter table departamento add constraint FK_DEPT_EMP foreign key(director) references empleado;

/* Crea las consultas SQL necesarias para responder a lss siguientes peticiones: a. Obtener el número y nombre de los empleados de los que no se conoce la dirección. (0.75) b. Obtener cuántos empleados tienen una categoría cuya descripción corresponde a 'Ebanista primera' y no tienen un jefe asignado. (0.75) c. Obtener el número y nombre de los empleados con mayor tarifa horaria. (1) d. Obtener el nombre de aquellos proyectos para los que han realizado trabajos empleados que viven en una población distinta a la del proyecto. (1.5) e. Suponiendo que únicamente los empleados trabajan una vez en cada proyecto, obtén los datos siguientes de cada proyecto: número de personas distintas que han trabajado en el proyecto, número total de horas trabajadas en el proyecto y la media de horas por persona (con un formato de salida y título de columnas apropiado). (1.5) f. Obtener el código del proyecto y la cantidad de empleados diferentes, con una tarifa horaria superior a los 100 euros. (1.5) g. Obtener la lista de proyectos que no tienen empleados con una tarifa horaria superior a los 100 euros. (1.5) h. Obtener el nombre de los empleados que han realizado todos los trabajos de los proyectos desarrollados por el departamento D100. (1.5)