lOMoARcPSD|23579216
Ejercicos sql 2 practicas
Calculabilidad y Complejidad de Algoritmos (Universidad Central del Ecuador)
Studocu no está patrocinado ni avalado por ningún colegio o universidad.
Descargado por Manuel Yore Hermosilla (myore@[Link])
lOMoARcPSD|23579216
2 Gestión de empleados
1.2.1 Modelo entidad/relación
1.2.2 Base de datos para MySQL
DROP DATABASE IF EXISTS empleados;
CREATE DATABASE empleados CHARACTER SET utf8mb4;
USE empleados;
CREATE TABLE departamento (
codigo INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
nombre VARCHAR(100) NOT NULL,
presupuesto DOUBLE UNSIGNED NOT NULL,
gastos DOUBLE UNSIGNED NOT NULL
);
CREATE TABLE empleado (
codigo INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
nif VARCHAR(9) NOT NULL UNIQUE,
nombre VARCHAR(100) NOT NULL,
apellido1 VARCHAR(100) NOT NULL,
apellido2 VARCHAR(100),
codigo_departamento INT UNSIGNED,
FOREIGN KEY (codigo_departamento) REFERENCES departamento(codigo)
);
INSERT INTO departamento VALUES(1, 'Desarrollo', 120000, 6000);
INSERT INTO departamento VALUES(2, 'Sistemas', 150000, 21000);
INSERT INTO departamento VALUES(3, 'Recursos Humanos', 280000, 25000);
INSERT INTO departamento VALUES(4, 'Contabilidad', 110000, 3000);
INSERT INTO departamento VALUES(5, 'I+D', 375000, 380000);
INSERT INTO departamento VALUES(6, 'Proyectos', 0, 0);
INSERT INTO departamento VALUES(7, 'Publicidad', 0, 1000);
INSERT INTO empleado VALUES(1, '32481596F', 'Aarón', 'Rivero', 'Gómez', 1);
Descargado por Manuel Yore Hermosilla (myore@[Link])
lOMoARcPSD|23579216
INSERT INTO empleado VALUES(2, 'Y5575632D', 'Adela', 'Salas', 'Díaz', 2);
INSERT INTO empleado VALUES(3, 'R6970642B', 'Adolfo', 'Rubio', 'Flores', 3);
INSERT INTO empleado VALUES(4, '77705545E', 'Adrián', 'Suárez', NULL, 4);
INSERT INTO empleado VALUES(5, '17087203C', 'Marcos', 'Loyola', 'Méndez', 5);
INSERT INTO empleado VALUES(6, '38382980M', 'María', 'Santana', 'Moreno', 1);
INSERT INTO empleado VALUES(7, '80576669X', 'Pilar', 'Ruiz', NULL, 2);
INSERT INTO empleado VALUES(8, '71651431Z', 'Pepe', 'Ruiz', 'Santana', 3);
INSERT INTO empleado VALUES(9, '56399183D', 'Juan', 'Gómez', 'López', 2);
INSERT INTO empleado VALUES(10, '46384486H', 'Diego','Flores', 'Salas', 5);
INSERT INTO empleado VALUES(11, '67389283A', 'Marta','Herrera', 'Gil', 1);
INSERT INTO empleado VALUES(12, '41234836R', 'Irene','Salas', 'Flores', NULL);
INSERT INTO empleado VALUES(13, '82635162B', 'Juan Antonio','Sáez', 'Guerrero',
NULL);
1.2.3 Consultas sobre una tabla
1. Lista el primer apellido de todos los empleados.
SELECT apellido1 FROM empleados;
2. Lista el primer apellido de los empleados eliminando los apellidos que estén
repetidos.
SELECT DISTIC (apellido1) FROM empleado.
3. Lista todas las columnas de la tabla empleado.
SELECT * FROM empleado;
4. Lista el nombre y los apellidos de todos los empleados.
SELECT nombre, apellido1,apellido2 FROM empleado;
Descargado por Manuel Yore Hermosilla (myore@[Link])
lOMoARcPSD|23579216
5. Lista el código de los departamentos de los empleados que aparecen en la
tabla empleado.
SELECT código_departamento FROM empleados;
6. Lista el código de los departamentos de los empleados que aparecen en la
tabla empleado, eliminando los códigos que aparecen repetidos.
SELECT DISTIC(código_departamento) FROM empleado;
7. Lista el nombre y apellidos de los empleados en una única columna.
SELECT CONCAT(nombre,“”,apellido1, “”,Coalesce(apellido2,"")) FROM
empleado;
8. Lista el nombre y apellidos de los empleados en una única columna, convirtiendo
todos los caracteres en mayúscula.
SELECT UCASE(CONCAT(nombre,“”,apellido1, “”,Coalesce(apellido2,"")))
FROM empleado;
9. Lista el nombre y apellidos de los empleados en una única columna, convirtiendo
todos los caracteres en minúscula.
SELECT LCASE(CONCAT(nombre,“”,apellido1, “”,Coalesce(apellido2,"")))
FROM empleado;
Descargado por Manuel Yore Hermosilla (myore@[Link])
lOMoARcPSD|23579216
10. Lista el código de los empleados junto al nif, pero el nif deberá aparecer en dos
columnas, una mostrará únicamente los dígitos del nif y la otra la letra.
SELECT codigo,left(nif,8), right(nif,1) FROM empleados
11. Lista el nombre de cada departamento y el valor del presupuesto actual del que
dispone. Para calcular este dato tendrá que restar al valor del presupuesto inicial
(columna presupuesto) los gastos que se han generado (columna gastos). Tenga en
cuenta que en algunos casos pueden existir valores negativos. Utilice un alias
apropiado para la nueva columna columna que está calculando.
SELECT nombre, presupuesto-(gastos) AS presupuesto_actual FROM
departamento;
12. Lista el nombre de los departamentos y el valor del presupuesto actual ordenado de
forma ascendente.
SELECT nombre, presupuesto-(gastos) AS presupuesto_actual FROM
departamento ORDER BY Presupuesto_actual ASC;
13. Lista el nombre de todos los departamentos ordenados de forma ascendente.
SELECT nombre FROM departamento ORDER BY nombre ASC;
14. Lista el nombre de todos los departamentos ordenados de forma desscendente.
SELECT nombre FROM departamento ORDER BY nombre DESC;
Descargado por Manuel Yore Hermosilla (myore@[Link])
lOMoARcPSD|23579216
15. Lista los apellidos y el nombre de todos los empleados, ordenados de forma
alfabética tendiendo en cuenta en primer lugar sus apellidos y luego su nombre.
SELECT apellido1, apellido2, nombre FROM empleado ORDER BY apellido1
asc, COALESCE(apellido2,"") asc, nombre asc;
16. Devuelve una lista con el nombre y el presupuesto, de los 3 departamentos que
tienen mayor presupuesto.
SELECT nombre, presupuesto FROM departamento ORDER BY presupuesto
DESC LIMIT 3;
17. Devuelve una lista con el nombre y el presupuesto, de los 3 departamentos que
tienen menor presupuesto.
SELECT nombre, presupuesto FROM departamento ORDER BY presupuesto asc
LIMIT 3
18. Devuelve una lista con el nombre y el gasto, de los 2 departamentos que tienen
mayor gasto.
SELECT nombre, gastos FROM departamento ORDER BY gastos desc LIMIT 2;
19. Devuelve una lista con el nombre y el gasto, de los 2 departamentos que tienen
menor gasto.
SELECT nombre, gastos FROM departamento ORDER BY gastos asc LIMIT 2;
Descargado por Manuel Yore Hermosilla (myore@[Link])
lOMoARcPSD|23579216
20. Devuelve una lista con 5 filas a partir de la tercera fila de la tabla empleado. La
tercera fila se debe incluir en la respuesta. La respuesta debe incluir todas las
columnas de la tabla empleado.
SELECT * FROM empleado LIMIT 2,5;
21. Devuelve una lista con el nombre de los departamentos y el presupuesto, de aquellos
que tienen un presupuesto mayor o igual a 150000 euros.
SELECT nombre, presupuesto FROM departamento WHERE presupuesto>=
150000
22. Devuelve una lista con el nombre de los departamentos y el gasto, de aquellos que
tienen menos de 5000 euros de gastos.
SELECT nombre, presupuesto FROM departamento WHERE presupuesto < 5000
23. Devuelve una lista con el nombre de los departamentos y el presupesto, de aquellos
que tienen un presupuesto entre 100000 y 200000 euros. Sin utilizar el
operador BETWEEN.
SELECT nombre, presupuesto FROM departamento WHERE presupuesto
>100000 AND presupuesto <200000
Descargado por Manuel Yore Hermosilla (myore@[Link])
lOMoARcPSD|23579216
24. Devuelve una lista con el nombre de los departamentos que no tienen un
presupuesto entre 100000 y 200000 euros. Sin utilizar el operador BETWEEN.
SELECT nombre FROM departamento WHERE NOT (presupuesto >100000
AND presupuesto <200000)
25. Devuelve una lista con el nombre de los departamentos que tienen un presupuesto
entre 100000 y 200000 euros. Utilizando el operador BETWEEN.
SELCT nombre, presupuesto FROM departamento WHERE presupuesto
BETWEEN 100000 AND 200000 ;
26. Devuelve una lista con el nombre de los departamentos que no tienen un
presupuesto entre 100000 y 200000 euros. Utilizando el operador BETWEEN.
SELCT nombre, presupuesto FROM departamento WHERE NOT (presupuesto
BETWEEN 100000 AND 200000 );
27. Devuelve una lista con el nombre de los departamentos, gastos y presupuesto, de
quellos departamentos donde los gastos sean mayores que el presupuesto del que
disponen.
SELECT nombre, gastos, presupuesto FROM departamento WHERE gasto>
presupuesto
Descargado por Manuel Yore Hermosilla (myore@[Link])
lOMoARcPSD|23579216
28. Devuelve una lista con el nombre de los departamentos, gastos y presupuesto, de
aquellos departamentos donde los gastos sean menores que el presupuesto del que
disponen.
SELECT nombre, gastos, presupuesto FROM departamento WHERE gasto< presupuesto
29. Devuelve una lista con el nombre de los departamentos, gastos y presupuesto, de
aquellos departamentos donde los gastos sean iguales al presupuesto del que
disponen.
SELECT nombre, gastos, presupuesto FROM departamento WHERE gasto=
presupuesto
30. Lista todos los datos de los empleados cuyo segundo apellido sea NULL.
SELECT * FROM empleado WHERE apellido2 IS NULL
31. Lista todos los datos de los empleados cuyo segundo apellido no sea NULL.
SELECT * FROM empleado WHERE apellido2 IS NOT NULL
32. Lista todos los datos de los empleados cuyo segundo apellido sea López.
SELECT * FROM empleadoWHERE lcase(apellido2)= “lopez”
33. Lista todos los datos de los empleados cuyo segundo apellido sea Díaz o Moreno. Sin
utilizar el operador IN.
Descargado por Manuel Yore Hermosilla (myore@[Link])
lOMoARcPSD|23579216
SELECT * FROM empleadoWHERE lcase(apellido2)= “diaz” or
lcase(apellido2)= “moreno”;
34. Lista todos los datos de los empleados cuyo segundo apellido sea Díaz o Moreno.
Utilizando el operador IN.
SELECT * FROM empleado WHERE lcase(apellido2) in (“diaz”, “moreno”);
35. Lista los nombres, apellidos y nif de los empleados que trabajan en el
departamento 3.
SELECT nombre, apellido1, apellido2, nif FROM empleado WHERE
código_departamento=3;
36. Lista los nombres, apellidos y nif de los empleados que trabajan en los
departamentos 2, 4 o 5.
1.2.4 Consultas multitabla (Composición interna)
Resuelva todas las consultas utilizando la sintaxis de SQL1 y SQL2.
1. Devuelve un listado con los empleados y los datos de los departamentos donde
trabaja cada uno.
2. Devuelve un listado con los empleados y los datos de los departamentos donde
trabaja cada uno. Ordena el resultado, en primer lugar por el nombre del
Descargado por Manuel Yore Hermosilla (myore@[Link])
lOMoARcPSD|23579216
departamento (en orden alfabético) y en segundo lugar por los apellidos y el nombre
de los empleados.
3. Devuelve un listado con el código y el nombre del departamento, solamente de
aquellos departamentos que tienen empleados.
4. Devuelve un listado con el código, el nombre del departamento y el valor del
presupuesto actual del que dispone, solamente de aquellos departamentos que tienen
empleados. El valor del presupuesto actual lo puede calcular restando al valor del
presupuesto inicial (columna presupuesto) el valor de los gastos que ha generado
(columna gastos).
5. Devuelve el nombre del departamento donde trabaja el empleado que tiene el
nif 38382980M.
6. Devuelve el nombre del departamento donde trabaja el empleado Pepe Ruiz
Santana.
7. Devuelve un listado con los datos de los empleados que trabajan en el departamento
de I+D. Ordena el resultado alfabéticamente.
8. Devuelve un listado con los datos de los empleados que trabajan en el departamento
de Sistemas, Contabilidad o I+D. Ordena el resultado alfabéticamente.
9. Devuelve una lista con el nombre de los empleados que tienen los departamentos
que no tienen un presupuesto entre 100000 y 200000 euros.
Descargado por Manuel Yore Hermosilla (myore@[Link])
lOMoARcPSD|23579216
10. Devuelve un listado con el nombre de los departamentos donde existe algún
empleado cuyo segundo apellido sea NULL. Tenga en cuenta que no debe mostrar
nombres de departamentos que estén repetidos.
1.2.5 Consultas multitabla (Composición externa)
Resuelva todas las consultas utilizando las cláusulas LEFT JOIN y RIGHT JOIN.
1. Devuelve un listado con todos los empleados junto con los datos de los
departamentos donde trabajan. Este listado también debe incluir los empleados que
no tienen ningún departamento asociado.
2. Devuelve un listado donde sólo aparezcan aquellos empleados que no tienen ningún
departamento asociado.
3. Devuelve un listado donde sólo aparezcan aquellos departamentos que no tienen
ningún empleado asociado.
4. Devuelve un listado con todos los empleados junto con los datos de los
departamentos donde trabajan. El listado debe incluir los empleados que no tienen
ningún departamento asociado y los departamentos que no tienen ningún empleado
asociado. Ordene el listado alfabéticamente por el nombre del departamento.
5. Devuelve un listado con los empleados que no tienen ningún departamento asociado
y los departamentos que no tienen ningún empleado asociado. Ordene el listado
alfabéticamente por el nombre del departamento.
Descargado por Manuel Yore Hermosilla (myore@[Link])
lOMoARcPSD|23579216
1.2.6 Consultas resumen
1. Calcula la suma del presupuesto de todos los departamentos.
2. Calcula la media del presupuesto de todos los departamentos.
3. Calcula el valor mínimo del presupuesto de todos los departamentos.
4. Calcula el nombre del departamento y el presupuesto que tiene asignado, del
departamento con menor presupuesto.
5. Calcula el valor máximo del presupuesto de todos los departamentos.
6. Calcula el nombre del departamento y el presupuesto que tiene asignado, del
departamento con mayor presupuesto.
7. Calcula el número total de empleados que hay en la tabla empleado.
8. Calcula el número de empleados que no tienen NULL en su segundo apellido.
9. Calcula el número de empleados que hay en cada departamento. Tienes que
devolver dos columnas, una con el nombre del departamento y otra con el número
de empleados que tiene asignados.
10. Calcula el nombre de los departamentos que tienen más de 2 empleados. El
resultado debe tener dos columnas, una con el nombre del departamento y otra con
el número de empleados que tiene asignados.
Descargado por Manuel Yore Hermosilla (myore@[Link])
lOMoARcPSD|23579216
11. Calcula el número de empleados que trabajan en cada uno de los departamentos. El
resultado de esta consulta también tiene que incluir aquellos departamentos que no
tienen ningún empleado asociado.
12. Calcula el número de empleados que trabajan en cada unos de los departamentos
que tienen un presupuesto mayor a 200000 euros.
1.2.7 Subconsultas
[Link] Con operadores básicos de comparación
1. Devuelve un listado con todos los empleados que tiene el departamento de Sistemas.
(Sin utilizar INNER JOIN).
2. Devuelve el nombre del departamento con mayor presupuesto y la cantidad que
tiene asignada.
3. Devuelve el nombre del departamento con menor presupuesto y la cantidad que
tiene asignada.
[Link] Subconsultas con ALL y ANY
4. Devuelve el nombre del departamento con mayor presupuesto y la cantidad que
tiene asignada. Sin hacer uso de MAX, ORDER BY ni LIMIT.
5. Devuelve el nombre del departamento con menor presupuesto y la cantidad que
tiene asignada. Sin hacer uso de MIN, ORDER BY ni LIMIT.
Descargado por Manuel Yore Hermosilla (myore@[Link])
lOMoARcPSD|23579216
6. Devuelve los nombres de los departamentos que tienen empleados asociados.
(Utilizando ALL o ANY).
7. Devuelve los nombres de los departamentos que no tienen empleados asociados.
(Utilizando ALL o ANY).
[Link] Subconsultas con IN y NOT IN
8. Devuelve los nombres de los departamentos que tienen empleados asociados.
(Utilizando IN o NOT IN).
9. Devuelve los nombres de los departamentos que no tienen empleados asociados.
(Utilizando IN o NOT IN).
[Link] Subconsultas con EXISTS y NOT EXISTS
10. Devuelve los nombres de los departamentos que tienen empleados asociados.
(Utilizando EXISTS o NOT EXISTS).
11. Devuelve los nombres de los departamentos que tienen empleados asociados.
(Utilizando EXISTS o NOT EXISTS).
Descargado por Manuel Yore Hermosilla (myore@[Link])
