UT4. Realización de consultas

En esta unidad, estudiaremos la realización de consultas en una base de datos. Las consultas forman parte del DQL (Data Query Language). Para ello, utilizaremos la sentencia SELECT definiendo consultas que pueden ocupar una línea o varias decenas, que acceden a una única tabla (o vista), o a múltiples tablas combinadas mediante el uso de joins, e incluso que utiliza diversos esquemas dentro de la misma base de datos.

Consultas SQL

Para el desarrollo de esta unidad, utilizaremos la siguiente base de datos de ejemplo, que contiene información sobre una empresa.

Puedes descargar el script SQL para crear esta base de datos desde aquí.

Así pues, entremos en detalle en la sentencia SELECT ... FROM. Su sintaxis completa, con las opciones más frecuentes, para MySQL:

SELECT {* | [DISTINCT] {columna | expresión} [[AS] alias], ... }
FROM tabla
    [WHERE condición]
    [GROUP BY col1 [, col2] ...]
    [HAVING predicado grupo]
    [ORDER BY col-n| pos-n [ASC|DESC] , col-m| pos-m [ASC|DES]…]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}];

En todas las consultas SQL, se debe indicar al menos las cláusulas SELECT que indica las columnas a recuperar, y FROM que indica la tabla o tablas de las que se recuperan los datos. El resto de cláusulas son opcionales y se utilizan para filtrar, agrupar, ordenar y limitar los resultados obtenidos.

A la hora de escribir una consulta, la podemos hacer en una sola línea o en varias líneas, utilizando saltos de línea y sangrías para mejorar la legibilidad. Debemos recordar que la consulta no finaliza hasta que se encuentra el punto y coma (;).

Proyección

La proyección es la operación que nos permite seleccionar las columnas que queremos recuperar de una tabla. Para ello, utilizamos la cláusula SELECT seguida del nombre de las columnas que queremos recuperar, separadas por comas. Si queremos recuperar todas las columnas de una tabla, podemos utilizar el asterisco (*). Por ejemplo:

SELECT * FROM centro;

Si queremos recuperar solo algunas columnas, podemos especificarlas de la siguiente manera:

SELECT NomCen, DirCen FROM centro;

También podemos utilizar alias para las columnas, utilizando la palabra clave AS. Por ejemplo:

SELECT NomCen AS NombreCentro, DirCen AS DireccionCentro FROM centro;

Nota

El uso de alias no modifica el nombre real de la columna en la tabla, solo cambia el nombre que se muestra en el resultado de la consulta.

Duplicados

Por defecto, una consulta SQL puede devolver filas duplicadas si existen en la tabla. Si queremos eliminar los duplicados, podemos utilizar la palabra clave DISTINCT después de SELECT. Por ejemplo:

SELECT CodDep FROM empleado;

SELECT DISTINCT CodDep FROM empleado;

Operaciones aritméticas

En las consultas SQL, podemos realizar operaciones aritméticas sobre las columnas numéricas. Por ejemplo, podemos sumar, restar, multiplicar o dividir valores de columnas. Por ejemplo, en la siguiente consulta, obtendremos el nombre, salario y el salario incrementado en un 10% de todos los empleados:

SELECT NomEmp, SalEmp, SalEmp * 1.1 AS SalarioIncrementado FROM empleado;

Funciones

Las funciones son operaciones predefinidas que podemos utilizar en nuestras consultas para realizar cálculos o transformaciones sobre los datos. Las funciones facilitan la manipulación de datos y la obtención de resultados específicos ahorrando tiempo y esfuerzo. Al igual que en programación, las funciones en SQL reciben argumentos y devuelven un valor.

Debemos tener en cuenta, que las funciones no están estandarizadas en SQL, por lo que cada sistema gestor de bases de datos puede tener sus propias funciones y sintaxis. Es recomendable consultar la documentación del sistema gestor de bases de datos que estemos utilizando para conocer las funciones disponibles y su uso correcto.

Funciones de agregación

Las funciones de agregación son funciones que operan sobre un conjunto de valores y devuelven un único valor resumen. Estas funciones son útiles para realizar cálculos estadísticos y resúmenes de datos.

COUNT

La función COUNT se utiliza para contar el número de filas que cumplen una determinada condición. Por ejemplo, para contar el número total de empleados en la tabla empleado, podemos utilizar la siguiente consulta:

SELECT COUNT(*) AS NumeroEmpleados FROM empleado;

También podemos contar el número de valores distintos en una columna específica utilizando COUNT(DISTINCT columna). Por ejemplo, para contar el número de departamentos distintos en la tabla empleado:

SELECT COUNT(DISTINCT CodDep) AS NumeroDepartamentos FROM empleado;

SUM

La función SUM se utiliza para calcular la suma total de los valores en una columna numérica. Por ejemplo, para calcular la suma total de los salarios de todos los empleados:

SELECT SUM(SalEmp) AS SumaSalarios FROM empleado;

AVG

La función AVG se utiliza para calcular el valor promedio de una columna numérica. Por ejemplo, para calcular el salario promedio de los empleados:

SELECT AVG(SalEmp) AS SalarioPromedio FROM empleado;

MIN y MAX

Las funciones MIN y MAX se utilizan para encontrar el valor mínimo y máximo en una columna numérica, respectivamente. Por ejemplo, para encontrar el salario mínimo y máximo de los empleados:

SELECT MIN(SalEmp) AS SalarioMinimo, MAX(SalEmp) AS SalarioMaximo FROM empleado;

Funciones de cadena

Las funciones de cadena se utilizan para manipular y transformar datos de tipo texto. Algunas funciones comunes incluyen:

CONCAT(cadena1, cadena2, ...): concatena varias cadenas de texto en una sola.
SUBSTRING(cadena, inicio, longitud): extrae una subcadena de una cadena de texto.
LENGTH(cadena): devuelve la longitud de una cadena de texto.
UPPER(cadena): convierte una cadena de texto a mayúsculas.
LOWER(cadena): convierte una cadena de texto a minúsculas.
TRIM(cadena): elimina los espacios en blanco al inicio y al final de una cadena de texto.

Algunos ejemplos de uso de funciones de cadena:

SELECT CONCAT(NomEmp, ' ', ApeEmp) AS NombreCompleto FROM empleado;

SELECT SUBSTRING(NomEmp, 1, 3) AS Iniciales FROM empleado;

SELECT UPPER(NomEmp) AS NombreMayusculas FROM empleado;

Funciones de fecha y hora

Las funciones de fecha y hora se utilizan para manipular y extraer información de datos de tipo fecha y hora. Algunas funciones comunes incluyen:

NOW(): devuelve la fecha y hora actuales.
CURDATE(): devuelve la fecha actual.
CURRENT_TIMESTAMP: devuelve la fecha y hora actuales (similar a NOW()).
DATE_ADD(fecha, INTERVAL valor unidad): añade un intervalo de tiempo a una fecha.
DATE_SUB(fecha, INTERVAL valor unidad): resta un intervalo de tiempo a una fecha.
YEAR(fecha), MONTH(fecha), DAY(fecha): extraen el año, mes o día de una fecha, respectivamente.
DATEDIFF(fecha1, fecha2): calcula la diferencia en días entre dos fechas.
TIMEDIFF(hora1, hora2): calcula la diferencia entre dos horas.
TIMESTAMPDIFF(unidad, fecha1, fecha2): calcula la diferencia entre dos fechas en la unidad especificada.
SECOND(fecha_hora), MINUTE(fecha_hora), HOUR(fecha_hora): extraen los segundos, minutos u horas de una fecha y hora, respectivamente.

Por ejemplo, para obtener la fecha y hora actuales:

select NOW(), CURRENT_TIMESTAMP, CURDATE();

Para añadir 7 días a la fecha actual:

SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY) AS FechaMasUnaSemana;

Para extraer el año de la fecha de contratación de los empleados:

SELECT YEAR(FecEmp) AS AnioContratacion FROM empleado;

Por último, para formatear una fecha en un formato específico, podemos utilizar la función DATE_FORMAT. Por ejemplo, para mostrar la fecha de contratación de los empleados en el formato 'DD-MM-YYYY':

SELECT DATE_FORMAT(FecEmp, '%d-%m-%Y') AS FechaContratacionFormateada FROM empleado;

Podemos encontrar más funciones en la documentación oficial de MySQL date_format().

Otras funciones útiles

ROUND(numero, decimales): redondea un número al número especificado de decimales.
TRUNCATE(numero, decimales): trunca un número al número especificado de decimales sin redondear.

Ordenación y limitación de resultados

La cláusula ORDER BY se utiliza para ordenar los resultados de una consulta en función de una o más columnas. Podemos especificar el orden ascendente (ASC) o descendente (DESC) para cada columna. Por defecto, el orden es ascendente. Por ejemplo, para ordenar los empleados por salario de forma descendente:

SELECT NomEmp, SalEmp FROM empleado ORDER BY SalEmp DESC;

Podemos ordenar por múltiples columnas, especificando el orden para cada una. Por ejemplo, para ordenar los empleados primero por departamento y luego por salario dentro de cada departamento:

SELECT NomEmp, CodDep, SalEmp FROM empleado ORDER BY CodDep ASC, SalEmp DESC;

Rendimiento

Debemos tener en cuenta que el uso de la cláusula ORDER BY puede afectar al rendimiento de la consulta, especialmente si se ordenan grandes conjuntos de datos. Es recomendable utilizar índices en las columnas utilizadas para ordenar, para mejorar el rendimiento.

Ademas de usar el nombre de la columna, también podemos usar la posición de la columna en la lista de selección para ordenar los resultados. Por ejemplo, para ordenar los empleados por salario utilizando la posición de la columna:

SELECT NomEmp, CodDep, SalEmp FROM empleado ORDER BY 3 DESC;

La cláusula LIMIT se utiliza para limitar el número de filas devueltas por una consulta. Podemos especificar un número máximo de filas a devolver, o un rango de filas utilizando un desplazamiento (OFFSET). Por ejemplo, para obtener solo los primeros 5 empleados:

SELECT NomEmp, CodDep, SalEmp FROM empleado LIMIT 5;

Si queremos obtener un rango de filas que no empiece desde la primera fila, podemos utilizar OFFSET. Por ejemplo, para obtener 5 empleados a partir del sexto empleado:

SELECT NomEmp, CodDep, SalEmp FROM empleado LIMIT 5 OFFSET 5;

o podemos utilizar la sintaxis alternativa:

SELECT NomEmp, CodDep, SalEmp FROM empleado LIMIT 5, 5;

Filtrar

La cláusula WHERE se utiliza para filtrar las filas devueltas por una consulta en función de una condición específica. Solo las filas que cumplen la condición serán incluidas en el resultado. La sintaxis básica de la cláusula WHERE es la siguiente:

SELECT columnas
FROM tabla
WHERE condición;

Dentro de la condición, podemos utilizar operadores de comparación como =, <>, <, >, <=, >=, así como operadores lógicos como AND, OR y NOT para combinar múltiples condiciones. Además de los operadores de comparación y lógicos, también podemos utilizar operadores especiales como BETWEEN, IN, LIKE y IS NULL o utilizar funciones.

A continuacin, veremos algunos ejemplos de uso de la cláusula WHERE.

Rangos de valores

Podemos utilizar el operador BETWEEN para filtrar filas que se encuentran dentro de un rango de valores. Por ejemplo, para obtener los empleados con salarios entre 2000 y 3000:

SELECT NomEmp, SalEmp FROM empleado WHERE SalEmp BETWEEN 2000 AND 3000;

Evidentemente, también podemos utilizar los operadores de comparación para lograr el mismo resultado:

SELECT NomEmp, SalEmp FROM empleado WHERE SalEmp >= 2000 AND SalEmp <= 3000;

Fuera de un rango

Si queremos filtrar filas que están fuera de un rango de valores, podemos utilizar el operador NOT BETWEEN. Por ejemplo, para obtener los empleados con salarios fuera del rango de 2000 a 3000:

SELECT NomEmp, SalEmp FROM empleado WHERE SalEmp NOT BETWEEN 2000 AND 3000;

Conjuntos de valores

El operador IN nos permite filtrar filas que coinciden con un conjunto específico de valores. Por ejemplo, para obtener los empleados que pertenecen a los departamentos 10, 20 o 30:

SELECT NomEmp, CodDep FROM empleado WHERE CodDep IN (10, 20, 30);

Coincidencias parciales

El operador LIKE se utiliza para buscar patrones específicos en columnas de tipo texto. Podemos utilizar los comodines % (cualquier secuencia de caracteres) y _ (un solo carácter) para definir el patrón de búsqueda. Por ejemplo, para obtener los empleados cuyos nombres comienzan con 'A':

SELECT NomEmp FROM empleado WHERE NomEmp LIKE 'A%';

Por otro lado, para obtener los empleados cuyos nombres contienen la letra 'e' en cualquier posición:

SELECT NomEmp FROM empleado WHERE NomEmp LIKE '%e%';

Y usando el comodín _, para obtener los empleados cuyos nombres tienen una 'a' como segundo carácter:

SELECT NomEmp FROM empleado WHERE NomEmp LIKE '_a%';

Como vemos, el operador LIKE es muy útil para realizar búsquedas flexibles en columnas de texto.

Expresiones regulares

Algunos sistemas gestores de bases de datos, como MySQL, permiten utilizar expresiones regulares para realizar búsquedas más avanzadas en columnas de texto. En MySQL, podemos utilizar el operador REGEXP para este propósito. Por ejemplo, para obtener los empleados cuyos nombres comienzan con 'A' o 'E':

SELECT NomEmp FROM empleado WHERE NomEmp REGEXP '^(A|E)';

Valores nulos

Podemos utilizar el operador IS NULL para filtrar filas que tienen valores nulos en una columna específica. Por ejemplo, para obtener los empleados que no tienen asignado un departamento:

SELECT NomEmp FROM empleado WHERE CodDep IS NULL;

Para filtrar filas que no tienen valores nulos, podemos utilizar el operador IS NOT NULL. Por ejemplo, para obtener los empleados que tienen asignado un departamento:

SELECT NomEmp FROM empleado WHERE CodDep IS NOT NULL;

Operadores lógicos

Podemos combinar múltiples condiciones en la cláusula WHERE utilizando los operadores lógicos AND, OR y NOT. Por ejemplo, para obtener los empleados que pertenecen al departamento 10 y tienen un salario mayor a 2500:

SELECT NomEmp, SalEmp FROM empleado WHERE CodDep = 10 AND SalEmp > 2500;

Actividades

A4.1 Consultas básicas

Sobre la base de datos empresa, realiza las siguientes consultas básicas:

Lista el nombre de todos los departamentos que hay en la tabla departamento.
Lista los nombres y los presupuestos de todos los departamentos.
Lista todas las columnas de la tabla departamento.
Lista el nombre de los empleados junto con su salario.
Recupera todas las posibilidades de número de hijos que tienen los empleados (debes recuperar 0, 1 y 2).
Lista el nombre y el complemento familiar (= salario * nº hijos * 4 /100) de todos los empleados.
Lista el nombre de los departamentos, el presupuesto en euros y también en dólares estadounidenses (USD).
A partir de la consulta anterior, utiliza los siguientes alias para las columnas: nombre de departamento, euros, dólares.

A4.2 Funciones SQL

Sobre la base de datos empresa, y haciendo uso de funciones, realiza las siguientes consultas:

Lista los nombres y sus presupuestos de todos los departamentos de la tabla departamento, convirtiendo los nombres a mayúscula.
Lista los nombres y los salarios de todos los empleados, convirtiendo los nombres a minúsculas.
Lista el nombre de todos los empleados en una columna, y en otra columna recupera en mayúsculas los dos primeros caracteres del código del departamento.
Lista el código del empleado y del departamento de todos los empleados, así como un código formado por los datos de estos campos unidos por un _.
Lista los nombres, el salario y el salario incrementado un 12%, redondeando el salario incrementado.
Lista los nombres, el salario y el salario incrementado un 12%, truncando el salario incrementado con un único decimal.
Calcula el salario promedio de todos los empleados.
Calcula el salario máximo y mínimo de todos los empleados.
Calcula el número total de empleados.
Calcula el número de departamentos distintos en los que trabajan los empleados.

A4.3 Ordenación y limitación de resultados

Sobre la base de datos empresa, realiza las siguientes consultas:

Listado del tipo de director, código, nombre y presupuesto de los departamentos ordenado por tipo de director (orden alfabético) y dentro de este criterio por presupuesto anual en orden ascendente.
Listado del nombre y salario de los empleados (con dos decimales y separadores de miles) ordenado por criterio descendente del salario.
Lista los tres departamentos ordenados por presupuesto anual y por el código del centro con menor presupuesto.
Recupera el nombre y salario del empleado que más cobra.
Lista los cinco empleados con menor salario.
Lista los cinco departamentos con mayor presupuesto anual.
Lista los empleados ordenados por código de departamento y dentro de este criterio por fecha de contratación (de más antiguo a más reciente).
Lista los empleados ordenados por número de hijos (de mayor a menor) y dentro de este criterio por salario (de menor a mayor).
Lista los departamentos ordenados por código de centro y dentro de este criterio por presupuesto anual (de mayor a menor).
Lista los centros ordenados por ciudad y dentro de este criterio por nombre de centro.

A4.4 Filtrado de resultados

Muestra el nombre y el salario de los empleados que trabajan en el departamento de "Producción Zona Sur" (cuyo código en la tabla es 'PROZS').
Lista el nombre y la dirección de los centros de trabajo que se encuentran en la ciudad de 'Cartagena'.
Obtén todos los datos de los empleados que tienen un salario superior a 5.000.000.
Muestra el nombre de los empleados que tienen al menos un hijo (NumHi > 0) y cuyo salario es menor de 4.000.000.
Lista los nombres de los departamentos que tienen un Tipo de Dirección (TiDir) igual a 'F' o que tienen un presupuesto anual (PreAnu) inferior a 10.000.000.
Encuentra los departamentos cuyo presupuesto anual oscila entre 15.000.000 y 30.000.000 (inclusive).
Muestra el nombre y la fecha de ingreso de los empleados que pertenecen a los departamentos de Dirección General ('DIRGE') o Ventas Zona Sur ('VENZS').
Busca a todos los empleados cuyo nombre empiece por la letra "M".
Lista los empleados que no tienen registrada una extensión telefónica (el campo ExTelEmp es nulo).
Muestra los empleados que ingresaron en la empresa antes del 1 de enero de 1990.

A4.5 Mezcla de conceptos

Muestra el nombre, fecha de ingreso y una "Ayuda Escolar" (Número de hijos * 300) de los empleados que tienen hijos y que ingresaron en la empresa antes de 1993. Ordena los resultados por salario de mayor a menor.
Lista el nombre del departamento, su presupuesto en euros y su presupuesto en dólares (multiplicando por 1.1 y redondeando). Filtra solo aquellos departamentos cuyo presupuesto anual esté entre 5.000.000 y 20.000.000. Ordena por presupuesto ascendente.
Lista el nombre en mayúsculas de los empleados que NO tienen extensión telefónica o cuyo nombre empieza por la letra 'A'.
Muestra el nombre y un código de empleado generado (ID + Nombre en minúsculas) de los empleados que pertenecen a los departamentos 'VENZS' o 'DIRGE' y que cobran más de 3.000.000.
Muestra el nombre del centro y su dirección para aquellos centros ubicados en 'Cartagena' o 'Murcia'.
Muestra el nombre y salario de los empleados del departamento de Producción Zona Sur ('PROZS') que ganan menos de 2.000.000 o más de 4.000.000. Ordena por salario.
Calcula cuántos empleados tienen más de un hijo.
Lista el nombre de los departamentos (recortado a los primeros 10 caracteres) y su presupuesto, para aquellos departamentos con tipo de dirección 'P' y presupuesto mayor a 10.000.000.
Muestra el nombre y las 3 primeras letras del nombre de los empleados que contienen la letra 'a' en su nombre y cuyo salario es superior a 3.000.000.
Muestra el nombre, salario y número de hijos de los 3 empleados que más cobran y que además tienen hijos.