Ing. sistemas computacionalesMateria: Taller de Base de Datos

CONSULTAS

MODELO EMPLEADOS-PROYECTOS-HIJOSElaboro:Karla Barrios Alejo

Profesora:Yanet Evangelista AlcocerGrupo: V5Horario: de 12:00 p.m. a 1:00p.m

EJERCICIOS DE LA UNIDAD 3Documentar las siguientes consultas, mostrando los resultados obtenidos (insertar datos requeridos, asegurando que se obtengan resultados en las consultas para verificar su funcionamiento).MODELO EMPLEADOS-PROYECTOS-HIJOS.1. Crear la tabla Empleados_con_hijos (sin datos), con los campos ID_EMPLEADO, NO_HIJOS. Insertar a partir de una subconsulta el nmero de hijos a cada empleado en la tabla Empleados_con_hijos.

CREATE TABLE EMPHIJOS(dni varchar(8) primary key,numhijos int, Constraint fk_dniemphijos foreign key(dni) references empleados(dni));

Insert into Emphijos Select dni, count(dni) from hijos group by dni;

SELECT * FROM EMPHIJOS;

2. Actualizar el salario de los empleados en un 15 %.

UPDATE empleados SET sueldo=sueldo+ (sueldo*.15);

3. Actualizar el salario de los empleados en un 20% adicional a aquellos empleados que tengan un salario inferior al salario promedio.

Update empleados set sueldo =sueldo*1.2 where sueldo =2);

5. Encontrar a los empleados (nombre) que trabajan en el departamento de Compras. A)Select e.nombre from empleados E where numD = (select numdept from departamentos D where nombredept =compras);

B)Select nombre from empleados as e,departamentos d where e.numDept=d.numdept and d.nombredept=compras;

6. Encontrar a los empleados que estn en el departamento de compras y en el departamento de ventas.

A) Select e.nombre from empleados E where numD = (select numdept from departamentos D where nombredept =compras) OR numD=(select numdept from departamentos D where nombredept =ventas);

B) Select e.nombre,d.nombreDept from empleados e,departamentos d where d.numDept=e.numD and d.nombreDept IN (compras,ventas);

7. Obtener la informacin de los distintos salarios de los empleados.

SELECT distinct sueldo SALARIOS, nombre NOMBRES from empleados;

8. Encontrar a los empleados que ganan entre 10 y 100 unidades monetarias, ordenados por salario ascendentemente.SELECT nombre from empleados where sueldo between 1000 and 2000 order by sueldo asc;

select nombre,sueldo from empleados where sueldo>1000 and sueldo10, ordenados por apellido.Select nombre as NOMBRE_EMPLEADOS, apellido1 as APELLIDO_PAT ,apellido2 as APELLIDO_MAT from empleados where numD=1 AND sueldo>10 ORDER BY apellido1;

13. Nombre y apellidos de los empleados que trabajan en el departamento 1 o 2. SELECT nombre as NOMBRE_EMPLEADO, apellido1 AS APELLIDO_PAT, apellido2 AS APELLIDO_MAT FROM empleados WHERE numD IN (1,2);

14. Nombre de los empleados con al menos dos hijos ordenados por nombre.

Select e.nombre, e.apellido1, count(from empleados where (select COUNT(*) from hijos where empleados.dni=dni)>=2 ORDER BY nombre;

SELECT e.nombre,e.apellido1,count(h.dni) NUMEROHIJOS from empleados e, hijos h where e.dni=h.dni group by e.dni having NUMEROHIJOS>=2 order by e.nombre;

15. Nombre y apellido de los empleados con algn hijo. SELECT nombre as NOMBRE_EMPLEADOS,apellido1 as APELLIDO FROM empleados WHERE EXISTS (SELECT * FROM hijos WHERE empleados.dni=hijos.dni);

16. Nombre y apellido de los empleados con al menos dos hijos.

Select nombre AS NOMBRE_EMPLEADOS, apellido1 AS APELLIDO_PATERNO from empleados where (select COUNT(*) from hijos where empleados.dni=dni)>=2;

17. Nombre y apellido de los empleados sin hijos ordenados por apellido ascendente y por nombre descendente.

Select nombre AS NOMBRE_DESCENDENTE,apellido1 AS APELLIDO_ASCENDENTE from empleados where NOT EXISTS (select * from hijos where empleados.dni=hijos.dni) ORDER BY apellido1,nombre DESC;

18. Nombre y apellido de jefes de departamento con al menos un hijo.SELECT nombre as NOMBRE_JEFE, apellido1 as APELLIDO FROM empleados WHERE dni IN (SELECT dniJefe FROM departamentos WHERE dniJefe IN (SELECT dni FROM hijos));

19. Mostrar el nombre de los hijos de cada empleado y su edad, ordenar la informacin por clave de empleado primero y luego por edad de los hijos.Select empleados.dni,hijos.nombre,(YEAR(CURDATE())-YEAR(fecha))-(RIGHT(CURDATE(),5)100 GROUP BY numD HAVING count(*)>3;

40. Obtener los nuevos salarios que resultara de sumar a los empleados del departamento 1 una gratificacin de 1000 unidades monetarias (slo consultar sin actualizar).

select sueldo+1000 SALARIO_GRATIFICACION from empleados where numD = 00000001;

41. Hacer una proyeccin de a cunto ascendera el monto total de aumentarle a todos los empleados del departamento 2; 1000 unidades monetarias.

Select numD,nombre,sueldo,sueldo+1000 AS ASCENDIO_A from empleados where numD=2;

42. Encontrar a los empleados que ganan menos de la mitad del promedio de todos los salarios, ordenados por salario ascendente.

Select nombre,dni,sueldo from empleados where sueldo 1000) OR (sueldo 0 and sueldo is not null && numD =3);

47. Obtener el nmero de horas que se trabaja en cada proyecto.Select numP ID_PROYECTO,numH NUMERO_DE_HORAS from trabajaen;

48. Obtener el nmero de proyectos que tiene cada departamento, ordenado por nombre de departamento.

SELECT D.nombreDept DEPARTAMENTO, count(*) AS NUMERO_PROYECTOS FROM departamentos D JOIN proyectos P ON D.numdept = P.numdept GROUP BY nombreDept UNION ALL SELECT nombreDept, 0 FROM departamentos D where not exists(select * from proyectos where numdept = D.numdept);

49. Obtener las comisiones y los nmeros de departamentos posibles de la empresa de manera que no se repitan.

SELECT distinct sueldo empleados, numD NO_DEPARTAMENTO from empleados;

50. Listar el nombre del empleado y nmero de proyectos que tiene asignado.

SELECT nombre, count(*) AS numProyectos FROM empleados E, trabajaen P where E.dni= P.dni GROUP BY nombre UNION ALL SELECT nombre, 0 FROM empleados E where not exists(select * from trabajaen where dni = E.dni);

51. Nombre del empleado, nombre del departamento y nmero de proyectos que tiene asignado.

Select empleados.nombre as NOMBRE_EMPLEADO,nombreDept ,COUNT(numP) AS NUMERO_DE_PROYECTOS from empleados,departamentos,proyectos where empleados.numD=departamentos.numDept AND proyectos.numDept=departamentos.numDept GROUP BY proyectos.numP;

52. Nmero de horas asignadas a cada proyecto considerando solo aquellos proyectos que tienen ms de 5 horas.

SELECT P.nombre as NOMBRE_PROYECTO, SUM(EP.numH) as HORAS_PROYECTO FROM proyectos P JOIN trabajaen EP ON P.numP = EP.numP where numH>5 GROUP BY nombre;

53. Nmero de proyectos por departamento.

SELECT D.nombreDept DEPARTAMENTO, count(*) AS NUMERO_PROYECTOS FROM departamentos D JOIN proyectos P ON D.numDept = P.numDept GROUP BY nombreDept UNION ALL SELECT nombreDept, 0 FROM departamentos D where not exists(select * from proyectos where numDept = D.numDept);

54. Nmero de horas promedio para cada proyecto.

SELECT P.nombre as NOMBRE_PROYECTO, AVG(EP.numH) as HORAS_PROYECTO FROM proyectos P JOIN trabajaen EP ON P.numP = EP.numP GROUP BY nombre;

55. Nmero de empleados por proyecto, considerando el nombre del empleado y el nombre del proyecto.

SELECT E.nombre as NOMBRE_EMP,P.nombre as NOMBRE_PROYEC, count(*) AS NUMERO_EMPLEADOS FROM empleados as E JOIN trabajaen as EP ON E.dni=EP.dni JOIN proyectos as P ON EP.numP=P.numP GROUP BY e.nombre;

56. Obtener para cada empleado el nmero de horas que trabaja en cada proyecto, incluir el nombre del proyecto y el nombre del departamento donde se desarrolla.Select proyectos.nombre AS NOMBRE_DE_PROYECTO,nombreDept ,numH AS HORAS_PROYECTO from proyectos,empleados,trabajaEn,departamentos where empleados.dni=trabajaEn.dni AND proyectos.numP=trabajaEn.numP AND proyectos.numDept=departamentos.numDept GROUP BY numH;

57. Borrar a los empleados que no tengan hijos.ANTES

DESPUESDELETE from EMPLEADOS where dni NOT IN (SELECT DISTINC hijos.dni FROM HIJOS);