Aplicando SQL Parte 4

Comenzaremos con un set de 20 nuevos ejercicios para continuar nuestro tutorial de bases de datos (ver tercera parte).

Para comenzar esta parte tendremos que crear una nueva Base de datos a la cual le llamaremos empresa la que a su vez tendrá 2 tablas relacionadas empleados y departamentos.

CREATE DATABASE `empresa`;

USE `empresa`; (para proceder a crear las tablas solo si estamos en modo consola)

CREATE TABLE `departamentos` (
`CODIGODEP` INTEGER(11) ,
`NOMBREDEP` VARCHAR(100) ,
`PRESUPUESTO` INTEGER(11) ,
PRIMARY KEY (`CODIGODEP`))
ENGINE=InnoDB


CREATE TABLE `empleados` (
`RUT` VARCHAR(10) ,
`NOMBRE` VARCHAR(100) ,
`APELLIDO` VARCHAR(200) ,
`DEPARTAMENTO` INTEGER(11) ,
PRIMARY KEY (`RUT`),
) ENGINE=InnoDB

ALTER TABLE EMPLEADOS ADD FOREIGN KEY (`DEPARTAMENTO`) REFERENCES `departamentos` (`CODIGODEP`) ON DELETE CASCADE ON UPDATE CASCADE


Ahora insertaremos registros en la tablas creadas

INSERT INTO `departamentos` (`CODIGODEP`, `NOMBREDEP`, `PRESUPUESTO`) VALUES
(1,'Informatica',1000000),
(2,'Personal',9000000),
(13,'Seguridad',500000),
(56,'Recursos Humanos',5000000),
(77,'Administracion',2000000);
COMMIT;

INSERT INTO `empleados` (`RUT`, `NOMBRE`, `APELLIDO`, `DEPARTAMENTO`) VALUES
('10333444-1','Sofia','quintana',13),
('10352097-6','Maria','Contreras',77),
('11111111-1','Pedro','Tapia',2),
('12052097-0','Jorge','Lara',1),
('13456789-2','Maria','Albornoz',13),
('16152094-2','Jose','Perez',1),
('21345678-7','Manuel','Rodriguez',56),
('23567876-4','Ignacio','Carrera',2),
('6435456-6','Pedro','Asnar',56),
('7654231-9','Eliana','Soto',13);
COMMIT;

(Todos los datos son inventados y no poseen relación con personas de la vida real xD)

Ejercicios:

1. Obtener los apellidos de los empleados
Sentencia SQL:
SELECT APELLIDO FROM EMPLEADOS;
Resultado de la consulta:
2. Obtener los apellidos de los empleados sin repeticiones
Nota tendremos que crear un nuevo trabajador con un apellido repetido en este caso sera Perez para ejecutar esta consulta (esto debido a que hasta el momento no tenemos ningún apellido repetido)
INSERT INTO empleados (RUT, NOMBRE, APELLIDO, DEPARTAMENTO) VALUES ('13987345-9', 'Miguel', 'Perez', 13);

Sentencia SQL:
SELECT APELLIDO FROM EMPLEADOS GROUP BY APELLIDO;
Resultado de la consulta: (como vemos solo aparece un Perez, esto gracias al Group By)

3. Obtener todos los datos de los empleados que se apellidan Perez
Sentencia SQL:
SELECT * FROM EMPLEADOS WHERE APELLIDO = 'Perez';
Resultado de la consulta:

4. Obtener todos los datos de los empleados que se apellidan Perez y los que se apellidan Contreras
Sentencia SQL:
SELECT * FROM EMPLEADOS WHERE APELLIDO = 'Perez' or APELLIDO = 'Contreras';
Resultado de la consulta:
5. Obtener todos los datos de los empleados que trabajan en el departamento 1
Sentencia SQL:
SELECT RUT, NOMBRE, APELLIDO, DEPARTAMENTO FROM EMPLEADOS
INNER JOIN DEPARTAMENTOS
ON DEPARTAMENTO = CODIGODEP WHERE DEPARTAMENTO = 1;
Resultado de la consulta:


6. Obtener todos los datos de los empleados que trabajan para el departamento 1 y para el departamento 13

Nota Importante: Como el rut es una clave primaria no podemos repetir un trabajador en otro departamento ya que nos dará un error de llave primaria, la solución seria incluir un campo rut en la tabla departamento y con ello tendriamos mas o menos solucionado esto, pero como no lo hice al comienzo y para no crear confuciones de nuevas relaciones agregare un campo llamado departamento 2 en la tabla empleados.
ALTER TABLE EMPLEADOS ADD DEPARTAMENTO2 INTEGER DEFAULT NULL;

El valor por defecto de los campos es Null (esto es distinto a cero y a vacio) .
Ahora Asignare trabajadores a otro departamento de la empresa. (Los que estan en el departamento 1 los llevare al 13 tambien)
UPDATE EMPLEADOS SET DEPARTAMENTO2 = 13 WHERE departamento = 1;

Y ahora a ejecutar la sentencia solicitada en el ejercicio
Sentencia SQL:
SELECT RUT, NOMBRE, APELLIDO, DEPARTAMENTO, DEPARTAMENTO2 FROM EMPLEADOS
WHERE DEPARTAMENTO = 1 OR DEPARTAMENTO = 13
AND DEPARTAMENTO2 = 1 OR DEPARTAMENTO2 = 13;

Nota: He preguntado 1 ó 13 en ambos campos para filtrar correctamete la consulta.
Resultado de la consulta:

7. Obtener todos los datos de los empleados cuyo apellido comience con P.
Sentencia SQL:
SELECT * FROM EMPLEADOS WHERE APELLIDO LIKE 'P%';
Nota:LIKE es otra palabra clave que se utiliza en la cláusula WHERE. Básicamente, LIKE le permite hacer una búsqueda basada en un patrón en vez de especificar exactamente lo que se desea (como en IN) o determinar un rango (como en BETWEEN).
En este caso LIKE 'P%'; entregará todos los apellidos que comienzan con P
Resultado de la consulta:

8. Obtener el presupuesto total de todos los departamentos.
Sentencia SQL:
SELECT NOMBREDEP, PRESUPUESTO FROM DEPARTAMENTOS;
Resultado de la consulta:


9. Obtener el número de empleados de cada departamento.
Sentencia SQL:
SELECT COUNT(DEPARTAMENTO), NOMBREDEP
FROM EMPLEADOS INNER JOIN DEPARTAMENTOS
ON DEPARTAMENTO = CODIGODEP
OR DEPARTAMENTO2 = CODIGODEP GROUP BY NOMBREDEP;

NOTA: En esta consulta he debido considerar que hay empleados que trabajan en 2 departamentos a la vez por ello realice el count a departamento pero a su vez en el inner Join
ingrese el departamento2
Resultado de la consulta:



10. Obtener un listado completo de cada empleado incluyendo por cada empleado los datos de syu departamento.
Sentencia SQL:
SELECT NOMBREDEP, NOMBRE, APELLIDO, RUT
FROM DEPARTAMENTOS INNER JOIN EMPLEADOS
ON CODIGODEP = DEPARTAMENTO
OR CODIGODEP = DEPARTAMENTO2 ORDER BY NOMBREDEP;

NOTA: En esta consulta para lo mi lo mas optimo fue hacer el select a departamento y el enner join a empleados puesto que como ya vengo mencionando tengo empleados en 2 departamentos a la vez (es un ejemplo mas realista):


11. Obtener un listado completo de cada empleado incluyendo por cada empleado los nombres y apellidos junto al nombre y presupuesto de su departament0
Sentencia SQL:
SELECT NOMBRE, APELLIDO, RUT, NOMBREDEP, PRESUPUESTO FROM DEPARTAMENTOS INNER JOIN EMPLEADOS ON CODIGODEP = DEPARTAMENTO OR CODIGODEP = DEPARTAMENTO2 ORDER BY NOMBREDEP;

Nota: Los usuarios que pertenecen a dos departamentos aparecen con su presupuesto en ambos departamentos).


12. Obtener un los nombres y apellidos de empleados que trabajen en departamentos con un presupuesto mayor a $500000
Sentencia SQL:
SELECT NOMBRE, APELLIDO FROM DEPARTAMENTOS INNER JOIN EMPLEADOS ON CODIGODEP = DEPARTAMENTO OR CODIGODEP = DEPARTAMENTO2 WHERE PRESUPUESTO > 500000 ORDER BY NOMBREDEP;

13. Obtener un los datos de los departamentos cuyo presupuesto es mayor al precio medio de todos los departamentos.
Sentencia SQL:
SELECT NOMBREDEP, PRESUPUESTO FROM DEPARTAMENTOS WHERE PRESUPUESTO > ( SELECT AVG(PRESUPUESTO) FROM DEPARTAMENTOS);


14. Obtener unicamente los nombres de los departamentos que poseen más de 2 empleados.
Sentencia SQL:
SELECT NOMBREDEP
FROM EMPLEADOS INNER JOIN DEPARTAMENTOS
ON DEPARTAMENTO = CODIGODEP
OR DEPARTAMENTO2 = CODIGODEP
GROUP BY NOMBREDEP HAVING COUNT(DEPARTAMENTO) > 2;

En lo personal considero que esta consulta es mas completa indicando además cuantos empleados posee el departamento mencionado.

SELECT COUNT(*) As CANTIDAD, NOMBREDEP FROM EMPLEADOS INNER JOIN DEPARTAMENTOS ON DEPARTAMENTO = CODIGODEP OR DEPARTAMENTO2 = CODIGODEP GROUP BY NOMBREDEP HAVING COUNT(DEPARTAMENTO) > 2;


15. Añadir un nuevo departamento Calidad con presupuesto $40000 y código 11..
Sentencia SQL:
INSERT INTO DEPARTAMENTOS (CODIGODEP, NOMBREDEP, PRESUPUESTO) VALUES (11, 'Calidad', 40000);
Select * from departamentos;



16. Añadir un nuevo empleado al departamento calidad (Esther Vazquez rut 23456789-9).
Sentencia SQL:
INSERT INTO EMPLEADOS (RUT, NOMBRE, APELLIDO, DEPARTAMENTO)
VALUES (23456789-9, 'Esther', 'Vazquez', 11);


17. Aplicar un recorte presupuestario del 10% a todos los departamentos.
Sentencia SQL:
UPDATE DEPARTAMENTOS SET PRESUPUESTO = PRESUPUESTO * 0.9;

18. Reasignar los trabajadores del departamento 11 (Calidad) al departamento 1 (informática).
Sentencia SQL:
UPDATE EMPLEADOS SET DEPARTAMENTO = 1 WHERE DEPARTAMENTO =11;

19. Eliminar a todos los trabajadores del departamento 1 (informática).

Nota:Como ya he indicado hay empleados que están en 2 departamentos y se los elimino de informática también serán eliminados del departamento2 aunque este no sea informatica por lo que este ejercicio lo resolvere en 3 pasos.

Importante: Siempre se debe tener mucho cuidado en realizar un delete directo a la Base de datos ya que si no se asigna una condición Where serán eliminados todos los registros. ( No es exagerado realizar un backup antes de hacer un delete) recuerden que aquí no existe el comando deshacer....

1.- Primero eliminar todos los empledos que trabajen en el departamento 1 y que no posean un trabajo en un segundo departamento (por defecto el departamento2 debe se NULL)

Sentencia SQL:
DELETE FROM EMPLEADOS WHERE DEPARTAMENTO = 1 AND DEPARTAMENTO2 <=> NULL;

2.- Como yo se por las consultas anteriores que en la columna departamento 2 solo están asignados empleados al departamento 13 actualizare la información de manera de que los que están en informática (que por defecto también están el el dpto 13) serán asignados solo al departamento 13, pero como su primer departamento.

Sentencia SQL:

UPDATE EMPLEADOS SET DEPARTAMENTO =DEPARTAMENTO2 WHERE DEPARTAMENTO2 = 13;

3.- Ahora que ya dispongo de mi información actualizada procederé a eliminar la información del depratamento2 dejando el campo en NULL (recordemos que la misma información ya esta en la columna departamento y esto es solo para no dejar repetida la información en departamento2)

Sentencia SQL:
UPDATE EMPLEADOS SET DEPARTAMENTO2 = NULL WHERE DEPARTAMENTO2 = 13;

Veamos como quedo nuestra tabla select * from empleados;


20. Eliminar a todos los empleados que trabajen en un departamento con un presupuesto mayor a $2000000.
Sentencia SQL:

DELETE A FROM EMPLEADOS A INNER JOIN DEPARTAMENTOS ON DEPARTAMENTO = CODIGODEP WHERE PRESUPUESTO > 2000000;

Nota: Aquí he utilizado un inner join nuevamente para unir las tablas pero para que el delete se realice correctamente se debe asignar un alias a la tabla empleados en este caso mi alias es A ( si nos fijamos mi sentencia dice DELETE A FROM....).

Veamos como quedo nuestra información .... ahora solo deberiamos poseer empleados en departamentos con presupuestos de hasta $2000000

SELECT RUT, NOMBRE, APELLIDO, NOMBREDEP, DEPARTAMENTO, DEPARTAMENTO2 FROM EMPLEADOS INNER JOIN DEPARTAMENTOS ON DEPARTAMENTO = CODIGODEP;

21. Eliminar a todos los empleados.
Nota: esta sentencia es la mas fácil de todas, pero no se olviden realizar un backup antes ya que si sus jefe les dio la orden siempre cabe la posibilidad de que se arrepienta o de que necesitemos esa información.

Sentencia SQL:
DELETE FROM EMPLEADOS;

Vemos como quedo nuestra información (aunque ya lo se..... no habrá nada)

SELECT * FROM EMPLEADOS;


Ha sido todo por el momento.... cualquier consulta publicando sus comentarios.