Curso de DevOps: Automatización I. Ciclos de entrega de IT automatizados

Vamos a describir el ciclo automatizado de entrega de IT y su impacto en la gestión y el monitoreo de IT. Las herramientas de supervisión y gestión de IT automatizan tareas repetitivas específicamente para profesionales de IT. El ciclo de entrega de IT comienza con la planificación, donde debemos planificar cuáles son todas las actividades comerciales o actividades de soporte que deben automatizarse. La planificación nos brinda una manera de aprovisionar y configurar. Lo que nos da la oportunidad de identificar el componente de la aplicación, el de la infraestructura y el del servicio que se pueden aprovisionar y configurar para admitir la automatización.

El tercer paso es recopilar datos operativos, porque eso nos ayudará a probar nuestra estrategia de automatización. Una vez que hayamos recopilado datos operativos de su implementación, podemos utilizar esos datos para el seguimiento. Y el monitoreo nos permite el análisis e información adecuados que el equipo de operaciones puede utilizar para improvisar aún más la automatización.

Así es como se puede implementar IT automatizada. A continuación, intentaremos comprender los impactos de la automatización de IT en la gestión.

 

Hay impactos positivos, también hay impactos negativos, esto depende de varios factores diferentes. Veremos son los impactos críticos de la automatización de IT en la gestión. En primer lugar, se basa en herramientas de software para definir el alcance de la automatización. Ese es uno de los impactos de la automatización de IT en la gestión, donde se depende mucho de las herramientas. En segundo lugar, reemplaza una serie de acciones entre los administradores y el entorno de IT, eliminando todas las tareas manuales que deben realizarse. El tercer impacto es que el sistema se vuelve capaz de desencadenar acciones de gestión automatizadas dependiendo de ciertas condiciones configuradas. En cuarto lugar, no requiere intervención manual para la conmutación por error si ha adoptado soporte de conmutación por error automatizado. También debemos comprender que la automatización de IT permite una gestión automatizada de la escala. Que se basa en condiciones y eventos prescritos, que pueden iniciar actividades de ampliación o reducción de escala. Y, por último, permite activadores automatizados de limpieza basados en reglas. Donde podemos definir varias reglas y esas reglas invocarán acciones para administrar los recursos para optimizarlos.

Es necesario entender el papel de los ingenieros de automatización en nuestra empresa. El ingeniero de automatización es un rol que garantiza que se facilite la creación de prácticas de automatización.

También se encargan de monitorear o controlar la producción y la entrega general, que pueden automatizarse. Son responsables de documentar todas las especificaciones de diseño relevantes para la automatización. Y aparte de eso, también desempeñan un papel importante a la hora de respaldar y ofrecer la automatización. El ingeniero de automatización también desempeña un papel esencial en la identificación de la causa raíz que les ayuda a solucionar errores de automatización. Como ingenieros de soporte, debemos tener una visión de administración para comprender el ciclo de vida del error. Siempre que se identifiquemos un nuevo error, como ingeniero de soporte lo escribiremos como un nuevo error. Después, debemos asignarlo al ingeniero de soporte que se supone debe manejarlo.

 

Y el estado de ese error ahora cambia de error nuevo a error asignado. Una vez asignado el error, pude tomar dos estados diferentes: el primero rechazado y el segundo abierto. Se rechaza porque al ingeniero de soporte al que se le asignó ese error en particular ya se le asignó anteriormente el mismo error, lo que significa que está duplicado. Otro motivo de rechazo es que se aplace porque depende de algún otro error. Además de rechazado, tiene los estados abierto y reabierto. El ingeniero de soporte revisa el error abierto, le aplica las correcciones correctas y cambia el estado a solucionado. Hecho esto, se prueba, y si todo está bien, se cierra. Pero sí, después de solucionarlo, si el impacto aún persiste, pasa a un estado llamado reapertura. Así es como podemos ajustar los estados de del error según nuestras necesidades. Para derivar el ciclo de vida de gestión de errores adecuado para nuestra aplicación o empresa.

 

Otros conceptos de SQL

 Renombrar las columnas, utilizando AS

Podemos cambiar el nombre a las columnas. Así, la llamada a un id se muestra siempre como id en el resultado. Podemos cambiarle el nombre:

SELECT  empleado.id AS id_persona,  departamento.id AS id_departamento FROM empleado JOIN departamento  ON empleado.id = departamento.id_pertenece;

Después del nombre de la columna, por ejemplo, empleado.id, utilizamos la nueva palabra clave AS y ponemos el nuevo nombre después de ella (id_persona). Podemos repetir este proceso con todas las columnas.

El nuevo nombre solo es un alias, lo que significa que es temporal y no cambia el nombre real de la columna en la base de datos. Solo influye en cómo la columna se muestra en el resultado de dicha consulta. Esta técnica se suele utilizar cuando hay varias columnas con el mismo nombre que proceden de diferentes tablas. Normalmente, cuando SQL muestra las columnas en el resultado, no hay información sobre las tablas de las que forman parte cada una de ellas.

En nuestro ejemplo, teníamos dos columnas id, así que, las renombramos como id_persona e id_departamento, respectivamente. Si ahora vemos las columnas en el resultado, sabremos a qué tabla pertenece cada columna.

Filtrar tablas combinadas

Vamos a ver cómo filtrar aún más los resultados:

SELECT  empleado.id,  departamento.nombre FROM empleado JOIN departamento   ON empleado.id =  departamento.id_pertenece WHERE empleado.edad < 25;

La cláusula WHERE sirve para filtrar las filas en las que el propietario sea menor de 25 años. Es necesario asegurarse de que se incluye el nombre de la tabla en la condición (empleado.edad).

Ordenar filas con ORDER BY

La secuencia en la que aparecen las filas es arbitraria, y cada base de datos puede comportarse de forma diferente. Incluso se puede ejecutar la misma instrucción de SQL varias veces y obtener un orden diferente cada vez, salvo que se pida a la base de datos que ordene las filas, obviamente.

SELECT * FROM pedidos ORDER BY id_cliente;

SELECT * FROM pedidos WHERE id_cliente = 100 ORDER BY importe_total;

SELECT * FROM empleados WHERE fecha = 2011 ORDER BY salario;

SELECT * FROM pedidos ORDER BY importe_total ASC;

También podemos invertir el orden y hacer que los valores más altos aparezcan primero.

SELECT * FROM pedidos ORDER BY importe_total DESC;

SELECT * FROM empleados ORDER BY apellido DESC;

Ordenar en base a varias columnas

Los resultados se pueden ordenar en base a más de una columna y, cada una de ellas, puede estar ordenada de forma diferente:

SELECT * FROM pedidos ORDER BY id_cliente ASC, importe_total DESC;

Como se puede ver, los resultados se ordenarán, primero, en orden ascendente (los valores más bajos primero) según la columna id_cliente y, luego, para cada id_cliente, los pedidos se ordenarán en orden descendente (los valores más grandes primero) según la columna importe_total.

Resultados duplicados

De forma predeterminada, la base de datos devuelve todas las filas que coinciden con los criterios especificados. Esto es lo que normalmente queremos, pero hay casos en los que podemos querer cambiar este comportamiento.

Imagina la siguiente situación: queremos obtener los números de cliente de todos los clientes que alguna vez hayan hecho un pedido. Podríamos utilizar la siguiente query:

SELECT id_cliente FROM pedidos;

¿Cuál es el problema del código en este caso?

Hay muchas filas con el mismo id_cliente, por lo que cada cliente aparece muchas veces en los resultados.

En nuestro ejemplo de pedidos, si un cliente hubiera hecho muchos pedidos, el ID de ese cliente se mostraría muchas veces en los resultados. Para ello hacemos:

SELECT DISTINCT id_cliente FROM pedidos;

Antes del nombre de la columna, hemos añadido la palabra DISTINCT. Ahora, la base de datos eliminará los duplicados y solo mostrará los valores únicos. Cada id_cliente aparecerá solo una vez.

Seleccionar valores únicos de varias columnas

También se puede utilizar DISTINCT en un grupo de columnas. Observa el siguiente código:

SELECT DISTINCT  id_cliente,   fecha_de_pedido FROM pedidos;

Un cliente puede hacer muchos pedidos cada día, pero si solo queremos saber en qué días cada cliente hizo al menos un pedido, la consulta anterior nos dará el resultado que deseamos.

COUNT Contar las filas

Es posible calcular las estadísticas de varias filas. Esta operación se llama agregación.

SELECT COUNT(*) FROM empleados;

En lugar del asterisco (*), que significa "todo", ponemos la expresión COUNT(*).

COUNT(*) es una función. Las funciones de SQL siempre tienen un nombre seguido de un paréntesis. En los paréntesis, se puede poner la información que la función necesita para funcionar. Por ejemplo, COUNT() calcula el número de filas especificado en el paréntesis.

En este caso, utilizamos COUNT(*), que significa "contar todas las filas". Como resultado, solo obtendremos la cantidad total de filas que tiene la tabla empleados, en vez de su contenido.

Contar las filas e ignorar los valores NULL

Podemos pedir a la base de datos que cuente los valores de una columna específica, por ejemplo, de la siguiente manera:

SELECT COUNT(id_empleado) FROM empleados;

¿Cuál es la diferencia entre COUNT(*) y COUNT(id_empleado)? Pues, la primera opción cuenta todas las filas de la tabla y, la segunda, cuenta todas las filas en las que la columna id_cliente tiene un valor que no sea nulo. En otras palabras, si hay un NULL en la columna id_cliente, esa fila no se contará.

Contar los valores únicos de una columna

También podemos añadir la palabra clave DISTINCT a nuestra función COUNT() de la siguiente manera:

SELECT COUNT(DISTINCT id_cliente) AS clientes_unicos FROM pedidos;

Esta vez, contamos todas las filas que tienen valores distintos en la columna id_cliente. En otras palabras, esta instrucción nos dice cuántos clientes diferentes hicieron un pedido hasta el momento. Si un cliente realizó 5 pedidos, solo se contará una vez.

Obtener los valores mínimo y máximo

SELECT MIN(importe_total) FROM pedidos;

La función MIN(importe_total) devuelve el valor más pequeño de la columna importe_total. Así, podemos saber el pedido más barato de nuestra tabla.

También, se puede utilizar una función similar llamada MAX(), que devuelve el valor más alto de la columna especificada.

Obtener el promedio

SELECT AVG(importe_total) FROM pedidos WHERE id_cliente = 100;

La función AVG() devuelve el promedio de la columna especificada.

En el ejemplo de arriba se obtiene el promedio del pedido del cliente con id 100.

Obtener la suma

SELECT SUM(importe_total) FROM pedidos WHERE id_cliente = 100;

La instrucción anterior devolverá la suma total de los importes todos los pedidos realizados por el cliente con id 100.

 

 

Ejecutar y depurar un paquete de Oracle con PL/SQL Developer

Vamos a aprender a ejecutar paquetes de Oracle desde PL/SQL Developer, si aun así no nos sirve y queremos ver lo que hace por dentro, podemos depurarlo.

Ejecutar

Para ejecutar un paquete hay que compilarlo antes en el PL/SQL para que coja la información de compilación.  Desde la pestaña de la izquierda de objetos

Nos colocamos sobre  el objeto a depurar y con el botón derecho del ratón pulsamos Edit Spec & body

 

Esto nos abre una pantalla con el paquete de Oracle, es necesario compilarla ejecutando el triángulo verde para que pueda reconocerla luego el depurador.

 

Hecho esto abrimos una ventana de test

Y le ponemos la llamada al paquete

BEGIN

    PAQUETE.FUNCION

END

Si es una función hay que declarar una variable que tome lo que devuelve

declare

  resultado varchar2(20);

begin

resultado:= rd_funciones_clientes.F_DAME_FILTRO_FECHAS_OTIF('6-6-2023','9-6-2023','BRR',1);

end;

Es importante que elijamos Package bodies (si cogemos Package) solo muestra las definiciones y no entra en el código.   Ejecutamos pulsando el botón verde triangular

 

Para poner los puntos de ruptura hay que posicionarse en los números de línea y pulsar el botón derecho del ratón.

Para depurar paso a paso

Cerramos la test window que abrimos en el paso anterior y nos colocamos sobre la función del paquete a depurar que se abrió al pulsar sobre Edit Spec & body.

Con el botón derecho del ratón sobre los números de la izquierda

podemos colocar puntos de ruptura para que se pare la ejecución en esos puntos.

Comenzamos la ejecución pulsando sobre el triángulo verde. La línea actual se muestra en azul.

 

Vamos paso a paso pulsando sobre las flechas de la toolbar 

 

Si no pasa por la función definida posiblemente se nos ha olvidado compilarla (ver más arriba)

Podemos ver el valor que van tomando las variables escribiendo su nombre en la ventana inferior

 

Para acceder a ella ejecutando paso a paso desde la carga del interfaz podemos poner un punto de ruptura en la función a depurar.  

Ojo, si no para es porque no entra en la función y hay que revisar la condiciones de llamada a la función

Si no cumple estas condiciones, nunca llama a la función definida y no podremos depurarla.

Clausula UNION en SQL

Esta clausula sirve para combinar los resultados de dos o más consultas.

SELECT * FROM jefe WHERE departamento = 'Ventas'  UNION SELECT * FROM empleado WHERE departamento = 'Ventas';

Como se puede ver, primero seleccionamos todos los jefes del departamento de ventas y luego, utilizamos la palabra clave UNION para añadir todos los empleados del departamento de ventas.

¿Podríamos dividir esta instrucción en dos consultas distintas? Sí. Pero, utilizando una UNION, obtenemos, juntos, todos los resultados de la primera tabla más todos los resultados de la segunda tabla.

Requisitos para usar UNION. Para que los resultados puedan combinarse en una sola tabla, las dos tablas deben tener el mismo número de columnas. También debemos recordar que sus columnas respectivas deben tener almacenado el mismo tipo de dato (número o texto).

Si, por ejemplo, en la columna lugar, una de tus tablas tiene almacenados números (1, 2, 3) y, la otra, tiene textos ("primero", "segundo", "tercero"), no funcionará.

Clausula UNION ALL

De forma predeterminada, UNION elimina las filas duplicadas. Por suerte, esto se puede cambiar. Solo hay que escribir UNION ALL en lugar de UNION en la consulta

SELECT * FROM jefe WHERE departamento = 'Ventas'  UNION ALL SELECT * FROM empleado WHERE departamento = 'Ventas';

... para obtener todas las filas, aunque sean iguales. Es decir haya empleados que aparezcan como jefe y como empleado.

Clausula INTERSECT

SELECT * FROM jefe WHERE departamento = 'Ventas'  INTERSECT SELECT * FROM empleado WHERE departamento = 'Ventas';

En lugar de UNION (o UNION ALL), escribimos INTERSECT. ¿Cuál es la diferencia?

UNION devuelve todos los resultados de la primera consulta más los resultados de la segunda. INTERSECT, en cambio, solo muestra las filas que están en AMBAS tablas.

En este caso, obtendríamos los empleados que a su vez son jefes del departamento de Ventas.

Los requisitos aquí siguen siendo los mismos: el número de columnas de ambas tablas debe ser el mismo y los tipos de valores (número o texto) deben coincidir.

Clausula EXCEPT

SELECT * FROM jefe WHERE departamento = 'Ventas'  EXCEPT SELECT * FROM empleado WHERE departamento = 'Ventas';

Muestra todos los resultados de la primera tabla (la anterior a EXCEPT), excepto los resultados que también aparecen en la segunda tabla (la posterior a EXCEPT).

En nuestro ejemplo, veremos todas los empleados, MENOS los jefes  aparezcan también en la tabla de empleados.

 

Curso de DevOps. Automatización IT y Tareas IT

Vamos a describir el concepto de automatización de IT, junto con la tarea crítica de automatización de TI que aporta valor a los sistemas empresariales. 

Hay diferentes niveles de gestión en las empresas. Necesitamos asegurarnos de que somos capaces de automatizar diversas tareas habilitadas por las IT para garantizar la precisión y también acelerar los procesos de la empresa. Para hacer eso, dependiendo del nivel de gestión, tenemos que crear una mesa de servicio e intentar automatizarla. Vamos a intentar comprender cuál es el papel de la mesa de servicio cuando se trata de la automatización de varios servicios diferentes. 

Prestación de servicios

Como podemos ver en la imagen superior, se ha dividido en dos partes diferenciadas. En primer lugar está la prestación de servicios, que necesita automatizar la gestión de la capacidad y la gestión financiera. La gestión de la continuidad del servicio con el fin de garantizar que haya un mínimo o ningún fallo. La gestión del nivel de servicio para cuidar de todos y cada uno de los servicios. Y por último, la gestión de la disponibilidad para garantizar que los servicios estén siempre disponibles. 

Soporte de servicios

La segunda parte de la automatización de IT, es para automatizar específicamente la mesa de servicio, y está relacionada con el soporte del servicio. El soporte de servicio entra en juego cuando hablamos de gestión de incidentes, donde siempre que hay un incidente, debemos descubrirlo y automatizar la respuesta a ese incidente en particular. 

La gestión de cambios, la gestión de configuración y la gestión de versiones. Todo esto es parte del soporte de servicio, que podemos planificar para automatizar, con el fin de construir una mesa de servicio sólida para nuestra empresa. 

Ahora centrémonos en la tarea de automatización crítica, que puede tener un impacto en la aplicación general.

Gestión del cambio

La primera tarea que debemos planificar para automatizar es la gestión del cambio. Siempre que hay un cambio en los escenarios empresariales, descubriremos que nuestra IT debe ajustarse de acuerdo con ese cambio en particular. Necesitamos evaluar el alcance de la automatización de ese cambio en particular. 

Gestión de la configuración

En segundo lugar está la gestión de la configuración, necesitamos evaluar con frecuencia la necesidad de gestión de la configuración y el cambio que debemos realizar en la configuración, debido al diferente estado de los servicios existentes. 

Gestión de la automatización

La tercera tarea de automatización de IT esencial o crítica está relacionada con el aprovisionamiento. Dependiendo de varias métricas diferentes, también podemos planificar la automatización del aprovisionamiento, para garantizar que siempre proporcionamos una infraestructura sólida y recursos óptimos para que se ejecuten las aplicaciones. 

También debemos planificar la automatización de las tareas de mantenimiento de rutina. Por ejemplo, borrar el caché, borrar el disco. La automatización de la copia de seguridad y la restauración, para que podamos adoptar una copia de seguridad. Siempre que haya un desencadenante que indique un fallo, deberíamos invocar automáticamente la restauración

Gestión de identidades y accesos

Hay otra tarea crítica de automatización de IT, se trata de la automatización de la gestión de identidades y accesos. Necesitamos asegurarnos de que somos capaces de automatizar los escenarios de gestión de identidades y accesos. 

Gestión de la recuperación ante desastres

La recuperación ante desastres es otra área donde podemos aplicar la automatización de IT. Pero sí, necesitamos identificar cuál es la causa crítica del desastre, dependiendo de las rutinas que podemos identificar para que se responsabilicen de la recuperación ante desastres. 

Gestión del movimiento de datos

Y finalmente, el movimiento de datos, cuando queremos tener un movimiento de datos incremental o completo, de un sistema a otro, y queremos programarlo, podemos seguir adelante con la automatización de IT. 

 Automatización de los procesos de negocio

El proceso de negocio es esencial porque nos proporciona lo que necesitamos saber para impulsarlo con nuestras IT. Las empresas pueden incluir funcionalidades tanto no relacionadas con IT como de IT. Necesitamos identificar qué papel puede desempeñar la automatización de procesos de negocio. Para empezar, la primera tarea siempre será el análisis, donde realizaremos un análisis adecuado, teniendo en cuenta el alcance de la automatización. El análisis posterior quedará claro con el alcance de la automatización, y que debe implementarse en la fase de implementación, como se muestra en el diagrama  inferior. 

Una vez finalizada la implementación, debemos compartir el conocimiento sobre la implementación actual con diferentes niveles de ingenieros de soporte, eso lo hacemos en la tercera fase de capacitación y soporte. Y, por último, es necesario integrar la implementación para garantizar que cualquier actividad o proceso comercial identificado pueda ser automatizado, integrándolo en la aplicación de la empresa. 

Derivación del valor de la automatización

El primer beneficio que aporta la derivación de la automatización, es que elimina muchos trabajos. Aparte de eso, también nos brinda la capacidad de ampliar los procesos fácilmente. Mejora el rendimiento de los procesos y, por  tanto, tendremos una mayor velocidad de los procesos. La automatización también conduce a una mayor precisión en el trabajo, porque un script de automatización o mecanismo de automatización garantizará que primero se pruebe y luego se aplique.

 También ofrece una mejor participación del personal, debido a que al automatizar tareas  la plantilla se verá liberada de realizar tareas repetitivas. Y, por último, la estandarización automatizada de procesos, que ayuda a adoptar un estándar dentro de la organización para determinadas tareas, lo que aporta mucho valor. Y también acelera el desarrollo de aplicaciones. Finalmente, vamos a ver los que beneficios podemos obtener de la automatización. E principio tendremos siempre una mayor visibilidad de las métricas, genera responsabilidad. Proporciona suficiente espacio para mejorar la eficiencia operativa y se encarga de eliminar el error humano. Otro beneficio es que reduce el tiempo de respuesta, seguido de una mejora de la productividad, o mejora la productividad. Reduce el costo de operación, proporciona una mejor colaboración debido al conocimiento que se obtiene una vez que se automatizan las actividades. Y finalmente, proporciona o mejora una mayor satisfacción del cliente.

 

Subconsultas en SQL

Imagina que queremos encontrar alumnos que tengan la misma puntuación que un alumno concreto, p. ej. El alumno_tipo. Lo primero sería saber la puntuación del alumno_tipo. Lo podemos ver con la siguiente consulta:

SELECT puntuacion FROM alumnos WHERE nombre = 'alumno_tipo';

Luego, tendríamos que anotar el resultado de la consulta anterior en algún lugar y crear otra consulta como la siguiente:

SELECT nombre FROM alumnos WHERE puntuacion = 6;

Las subconsultas se crearon para ayudar con estos ejemplos. Son "consultas dentro de consultas" y siempre se ponen entre paréntesis. Echa un vistazo a la siguiente:

SELECT nombre FROM alumnos WHERE puntuacion = (  SELECT     puntuacion   FROM alumnos  WHERE nombre = 'alumno_tipo' );

La base de datos primero ejecutará la subconsulta (entre paréntesis), luego devolverá su resultado y lo pondrá en lugar de la subconsulta y, a continuación, ejecutará la consulta final.

En este ejemplo concreto, debemos escribir la subconsulta para que devuelva exactamente un valor (una columna de una fila), para que se pueda usar en la ecuación "puntuacion = X". No tendría mucho sentido poner una tabla entera.

Subconsultas con varios operadores lógicos

Las subconsultas también se pueden utilizar con otros operadores lógicos. Observa el siguiente ejemplo:

SELECT * FROM montana WHERE altura > (   SELECT altura   FROM montana   WHERE nombre = 'Mont Blanc' );

La consulta anterior devolverá todas las montañas que son más altas que el Mont Blanc. Como se puede ver, hemos utilizado el signo "mayor que" (>) junto con una subconsulta.

Funciones en subconsultas

Echa un vistazo a la siguiente consulta:

SELECT nombre FROM alumnos WHERE puntuacion <(SELECT AVG(puntuacion) FROM alumnos WHERE nombre = 'Alumno_tipo');

Ahora, nuestra consulta busca todos los alumnos con una puntuación inferior al promedio del alumno tipo. Como se puede ver, en la subconsulta utilizamos la función AVG() que nos da el valor promedio de una columna.

El operador IN

Hasta ahora, nuestras subconsultas solo devolvían valores únicos (como 6 o 16,28, por ejemplo).

SELECT *  FROM alumno WHERE puntuacion IN (3, 4, 5);

IN permite especificar varios valores en la cláusula WHERE, en lugar de uno solo.

En nuestro ejemplo, solo queremos mostrar alumnos que tengan una puntuación de 3 OR 4 OR 5. Eso es lo que significa IN (3,4,5).

Usar el operador IN con subconsultas

Para  utilizar el nuevo operador IN junto con subconsultas.  Hacemos lo siguiente:

SELECT precio FROM viaje WHERE id_ciudad IN (   SELECT id   FROM ciudad   WHERE poblacion < 2000000 );

En la subconsulta, buscamos los Id de todas las ciudades con una población inferior a 2 millones de habitantes. A continuación, utilizamos estos ID como valores para el operador IN.

Así, podemos obtener los precios de los viajes a las ciudades que tienen una población inferior a 2 millones de personas.

El operador ALL

SELECT * FROM pais WHERE superficie > ALL ( SELECT superficie   FROM ciudad );

Como se puede ver, tenemos el nuevo operador ALL a la derecha del operador lógico >. En este caso, > ALL significa "mayor que todos los valores del paréntesis".

Como resultado, obtendremos todos los países cuya superficie sea mayor que las superficies de todas las ciudades. Liechtenstein, por ejemplo, es un país muy pequeño. Es mayor que algunas ciudades (como, por ejemplo, Lyon), pero no es mayor que todas las demás (Berlín, por ejemplo, es mayor), por lo que Liechtenstein no aparecerá en el resultado.

También se puede utilizar ALL con otros operadores lógicos, como, por ejemplo, = ALL, != ALL, < ALL, <= ALL y >= ALL.

Usar el operador ALL en subconsultas correlacionadas

SELECT * FROM alumnos mejor_alumno WHERE puntuacion >= ALL ( SELECT puntuacion   FROM alumnos otros_alumnos   WHERE mejor_alumno.id_clase = otros_alumnos.id_clase);

La consulta anterior busca todos los alumnos que han sacado la mayor puntuación de cada clase. La consulta solo devuelve los alumnos cuya puntuación es igual o superior a la de todos los alumnos de su clase.

El operador ANY

SELECT * FROM viaje WHERE precio < ANY (   SELECT precio   FROM viaje_de_senderismo   WHERE id_montana = 1 );

En el ejemplo anterior, queremos encontrar viajes a ciudades que sean más baratos que algún viaje de senderismo a la montaña con id 1 (Mont Blanc). Si encuentra un viaje urbano que sea más barato que cualquiera de los valores de los viajes al Mont Blanc, se mostrará en el resultado.

También se puede usar con otros operadores, como, por ejemplo, = ANY, != ANY, < ANY, <= ANY y >= ANY.

Usar el operador ANY en subconsultas correlacionadas

También se puede utilizar el operador ANY en las subconsultas correlacionadas.

SELECT * FROM viajes_america WHERE precio < ANY (   SELECT precio FROM viaje_asia   WHERE viaje_america.dias = viaje_asia.dias );

La consulta anterior compara los viajes a américa y los viajes a asia que duran el mismo número de días. Después, devuelve todos los viajes a asia que sean más baratos que cualquier viaje a una viaje a américa que dure los mismos días.

Consultas correlacionadas

Hasta ahora, solo hemos  visto subconsultas que eran independientes de la consulta principal. Se podría ejecutar solo la subconsulta y, luego, poner su resultado en la consulta principal.

Vamos a ver subconsultas que dependen de la consulta principal. Se llaman subconsultas correlacionadas.

SELECT * FROM país WHERE superficie <= ( SELECT MIN(superficie)   FROM ciudad

 WHERE ciudad.id_pais = pais.id );

Queremos obtener todos los países cuya superficie sea igual o menor que la superficie mínima de las ciudades de ese país. En otras palabras, si hay un país más pequeño que su ciudad más pequeña, se mostrará. ¿Por qué utilizaríamos una consulta de este tipo? Puede ser muy útil para comprobar si hay algún error en la base de datos. Si esta consulta devolviera algún registro, sabríamos que algo raro está pasando con nuestros datos.

¿Cuál es la novedad en esta consulta? Echa un vistazo a la cláusula WHERE de la subconsulta. utiliza pais.id. ¿A qué país se refiere? Obviamente, al país de la consulta principal. Éste es el secreto de las subconsultas correlacionadas; si se ejecutase solo la subconsulta, la base de datos dará error.

Pero, si se ejecutase la instrucción como una subconsulta y se utiliza por ejemplo el campo fecha que explore la tabla pais, la base de datos compararía todas las veces el pais.id de la subconsulta con el pais.id de ese momento de la fecha.

Recuerde la regla de oro: las subconsultas pueden utilizar tablas de la consulta principal, ¡pero la consulta principal no puede utilizar tablas de la subconsulta!

Usar alias para las tablas

Puede haber ejemplos en los que la misma tabla se utilice tanto en la consulta principal como en la subconsulta correlacionada.

SELECT * FROM ciudad ciudad_principal WHERE poblacion > (SELECT AVG(poblacion)

  FROM ciudad  media_ciudades WHERE media_ciudades.id_pais = ciudad_principal.id_pais);

En este ejemplo, queremos encontrar ciudades con una población superior a la media de la población de todas las ciudades del país en cuestión. El problema es que buscamos las ciudades en la cláusula principal y comprobamos el valor de la media de la población de las ciudades en la subconsulta. La misma tabla aparece dos veces, lo que no es bueno.

Por eso, debemos utilizar alias para las tablas. En la subconsulta ponemos ... FROM ciudad media_ciudades ... y en la consulta principal ... FROM ciudad ciudad_principal. Como se puede ver, dimos nuevos nombres temporales a la tabla ciudad, y son diferentes para la consulta principal y para la subconsulta. El nombre temporal (el llamado alias) se pone después del nombre de la tabla, separado por un espacio. Recuerda, aquí no se usan comas.

Usar el operador IN con subconsultas correlacionadas

El operador IN nos permite especificar algunos valores en la cláusula WHERE, por lo que funciona un poco como el operador OR.

SELECT * FROM ciudad WHERE id_pais IN (SELECT id FROM país WHERE pais.poblacion < 40000);

Mostrará todas las ciudades de aquellos países que tengan una población total inferior a 40.000 habitantes.

El operador EXISTS

EXISTS es un operador que comprueba si alguna fila cumple la condición.

SELECT * FROM empleado WHERE EXISTS ( SELECT * FROM jefe WHERE id_empleado = empleado.id );

La consulta anterior solo mostrará la información de los empleados para los que existe al menos un empleado subordinado, del que es jefe el primero. No se mostrarán los empleados que no son jefes, (no tienen empleados subordinados). Si queremos lo contrario podemos poner NOT EXISTS

Usar subconsultas en la cláusula FROM

Las consultas también pueden utilizarse en otros lugares. Por ejemplo, en la cláusula FROM se puede utilizar una subconsulta en lugar de una tabla.

SELECT * FROM empleado, (SELECT  *   FROM jefe  WHERE subordinados < 10) AS jefe_grupo_pequeño WHERE jefe_grupo_pequeño.id =empleado.id_jefe;

La consulta anterior devuelve los jefes de los grupos pequeños. Pero, en nuestra base de datos no existe ninguna tabla llamada jefe_grupo_pequeño, así que... la creamos "sobre la marcha", utilizando una subconsulta en la cláusula FROM. Ya que tenemos que ponerle un nombre, utilizamos la palabra clave AS. Como resultado, la consulta muestra los empleados junto con sus jefes, siempre que estos tengan un grupo de menos de 10 subordinados.  Tenemos que poner la condición en la cláusula WHERE, porque, de lo contrario, cada empleado se mostraría junto con todos los jefes posibles.

Usar subconsultas en la cláusula FROM

SELECT  nombre,   dias,   precio FROM viaje, (     SELECT       *     FROM ciudad     WHERE puntuacion = 5) AS ciudad_bonita WHERE ciudad_bonita.id = viaje.id_ciudad;

La consulta anterior encuentra los viajes y sus respectivas ciudades para las ciudades que tienen una puntuación de 5. A continuación, muestra las columnas nombre, dias y precio para estas tablas. Cuando todas las columnas de las tablas tienen nombres diferentes, se pueden suprimir los nombres de las tablas (es decir, se puede escribir precio en lugar de viaje.precio, porque solo hay una columna llamada precio).

Obviamente, en esas consultas se pueden elegir solo algunas columnas. Estudia el siguiente ejemplo:

SELECT   nombre,   dias,   precio FROM viaje, ( SELECT       *     FROM ciudad     WHERE puntuacion = 5) AS ciudad_bonita WHERE ciudad_bonita.id = viaje.id_ciudad;

La consulta anterior encuentra los viajes y sus respectivas ciudades para las ciudades que tienen una puntuación de 5. A continuación, muestra las columnas nombre, dias y precio para estas tablas. Cuando todas las columnas de las tablas tienen nombres diferentes, se pueden suprimir los nombres de las tablas (es decir, se puede escribir precio en lugar de viaje.precio, porque solo hay una columna llamada precio).

Usar subconsultas en la cláusula SELECT

También se pueden usar subconsultas dentro de la lista de columnas de una cláusula SELECT. Para ello, es importante que la subconsulta devuelva exactamente una fila y una columna.

 SELECT   nombre,   (SELECT COUNT(*)   FROM viaje   WHERE id_ciudad = ciudad.id) AS numero_de_viajes FROM ciudad;

La consulta anterior devuelve el nombre de cada ciudad junto con el número de viajes que se realizan a la misma. Observa que utilizamos la función COUNT() para contar el número de viajes a cada ciudad.