Subconsultas anidadas y vinculadas en SQL, predicado EXISTS. Usando el operador EXISTS Consultas usando la función existe

DONDE EXISTE

Se comprueba la subconsulta para detectar la presencia de una o más filas. Si al menos una fila coincide con la consulta, se devuelve el valor booleano VERDADERO. Cuando se especifica la palabra clave NOT opcional, se devuelve el valor booleano VERDADERO si la subconsulta no devuelve ninguna fila coincidente.

subconsulta

Según la subconsulta completamente formada, se recupera el conjunto de datos resultante.

Reglas generales

El operador EXISTS prueba la existencia de una o más filas en una subconsulta de una consulta principal.

SELECCIONAR * DE trabajos DONDE NO EXISTE (SELECCIONAR * DE empleado DONDE jobs.job_id=employye.job_id);

Este ejemplo verifica la subconsulta de registros usando la palabra clave NOT adicional. El siguiente ejemplo busca registros específicos en una subconsulta para recuperar el conjunto de resultados principal.

SELECCIONE au_lname DE los autores DONDE EXISTE (SELECCIONE * DE los editores DONDE autores.ciudad=publishers.city);

Esta consulta devuelve los apellidos de los autores (au_lname) que viven en la misma ciudad que los editores. Tenga en cuenta que puede utilizar un asterisco en la subconsulta porque la subconsulta debe devolver solo un registro con el valor booleano VERDADERO. En tales casos, las columnas no importan. El punto clave es la existencia de la cuerda.

En muchas consultas, el operador EXISTS realiza la misma función que ANY. El operador EXISTS suele ser más eficaz cuando se utiliza con consultas correlacionadas.

El operador EXISTS es semánticamente equivalente al operador ANY.

Una subconsulta en una declaración EXISTS normalmente realiza uno de dos tipos de búsquedas. La primera opción es utilizar un comodín: un asterisco (por ejemplo, SELECCIONAR * DESDE...), en cuyo caso no recuperará ninguna columna o valor específico. El asterisco aquí significa "cualquier columna". La segunda opción es seleccionar solo una columna específica en la subconsulta (por ejemplo, SELECT aujd FROM). Algunas plataformas individuales permiten subconsultas en varias columnas (por ejemplo, SELECT aujd, aujname FROM...). Sin embargo, esta característica es poco común y debe evitarse en el código que debe migrarse a otras plataformas.

Diferencias entre plataformas

Todas las plataformas admiten el operador EXISTS en la forma que describimos anteriormente.

“Antes era más fácil”, pensé mientras me sentaba a optimizar la siguiente consulta en SQL. estudio de gestión. Cuando escribí en MySQL, todo era realmente más simple: funciona o no. O se ralentiza o no. Explicar resolvió todos mis problemas, no se necesitaba nada más. Ahora tengo un entorno poderoso para desarrollar, depurar y optimizar consultas y procedimientos/funciones, y todo este desorden solo crea más problemas en mi opinión. ¿Y por qué todos? Porque el optimizador de consultas incorporado es malo. Si en MySQL y PostgreSQL escribo

Seleccione * de a, b, c donde a.id = b.id, b.id = c.id

y cada una de las tabletas tendrá al menos 5k líneas; todo se congelará. ¡Y gracias a Dios! Porque de lo contrario, el desarrollador, en el mejor de los casos, desarrolla pereza para escribir correctamente y, en el peor, ¡no comprende en absoluto lo que está haciendo! Después de todo, la misma consulta en MSSQL funcionará de manera similar

Seleccione * de una unión b en a.id = b.id unión c en b.id = c.id

El optimizador incorporado revisará la solicitud redundante y todo estará bien.

También decidirá por sí mismo qué es mejor hacer: existir o unirse y mucho más. Y todo funcionará de la forma más óptima posible.

Sólo hay un PERO. En un momento, el optimizador tropezará consulta compleja y pasa, y luego tienes un gran problema. Y puede que no lo consigas de inmediato, sino cuando el peso de las mesas alcance una masa crítica.

Así que aquí vamos al punto del artículo. existe y hay operaciones muy pesadas. En realidad, esta es una subconsulta separada. para cada líneas de resultados. Y si también hay anidación, generalmente se apagan las luces. Todo estará bien cuando se devuelvan 1, 10, 50 líneas. No sentirá la diferencia y quizás unirse sea aún más lento. Pero cuando se retira el 500, empiezan los problemas. 500 subconsultas dentro de una solicitud es algo serio.

Aunque desde el punto de vista de la comprensión humana, in y exist son mejores, desde el punto de vista del costo de tiempo para las consultas que devuelven más de 50 filas, no son aceptables.

Es necesario hacer una reserva de que, naturalmente, si disminuye en alguna parte, en alguna parte debe llegar. Sí, unirse requiere más memoria, porque mantener toda la tabla de valores a la vez y operar con ella es más costoso que ejecutar subconsultas para cada fila, lo que libera memoria rápidamente. Debe observar específicamente la solicitud y medir si el uso de memoria adicional por razones de tiempo será crítico o no.

Daré ejemplos de analogías completas. En términos generales, todavía no me he encontrado con consultas de tal grado de complejidad que no puedan expandirse en una cascada de combinaciones. Puede que sea necesario un día, pero todo se puede revelar.

Seleccione * de a donde a.id en (seleccione id de b) seleccione * de a donde existe (seleccione top 1 1 de b donde b.id = a.id) seleccione * de una unión b en a.id = b. id seleccione * de a donde a.id no está en (seleccione id de b) seleccione * de a donde no existe (seleccione top 1 1 de b donde b.id = a.id) seleccione * de a izquierda unir b en a. id = b.id donde b.id es nulo

Repito: el optimizador MSSQL optimiza estos ejemplos para rendimiento máximo y nunca habrá gente estúpida con peticiones tan simples.

Consideremos ahora un ejemplo de una consulta real que tuvo que reescribirse porque simplemente se congeló en algunas muestras (la estructura está muy simplificada y los conceptos han sido reemplazados, no hay que temer que la estructura de la base de datos no sea óptima). ).

Debe extraer todos los "productos" duplicados en diferentes cuentas, centrándose en los parámetros del producto, su grupo y el grupo principal, si lo hay.

Seleccione d.PRODUCT_ID de PRODUCT s, PRODUCT_GROUP sg left join M_PG_DEPENDENCY sd on (sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID), PRODUCT d, PRODUCT_GROUP dg left join M_PG_DEPENDENCY dd on (dg.PRODUCT_GROUP_ID = dd .M_PG_DEPENDENCY_CHILD_ID) donde s.PRODUCT_GROUP_ID=sg .PRODUCT_GROUP_ID y d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID y sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC y sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME y s.PRODUCT_NAME=d.PRODUCT_NAME y s.PRODUCT_TYPE=d.PRODUCT_TYPE y s.PRODUCT_IS_SECURE=d.PRODUCT_ ES_SEGURO y s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT y dg.PRODUCT_GROUP_IS_TMPL=0 y ((sd.M_PG_DEPENDENCY_CHILD_ID es nulo y dd.M_PG_DEPENDENCY_CHILD_ID es nulo) o existe (seleccione 1 de PRODUCT_GROUP sg1, PRODUCT_GROUP dg1 donde sd.M_PG_DEPENDENCY_PAR ENT_ID = sg1.PRODUCT_GROUP_ID y dd .M_PG_DEPENDENCY_PARENT_ID = dg1.PRODUCT_GROUP_ID y sg1.PRODUCT_GROUP_PERSPEC=dg1.PRODUCT_GROUP_PERSPEC y sg1.PRODUCT_GROUP_NAME=dg1.PRODUCT_GROUP_NAME y))

Entonces este es el caso cuando el optimizador se rindió. Y para cada línea se ejecutó una existe pesada, lo que eliminó la base de datos.

Seleccione d.PRODUCT_ID de PRODUCT s únase a PRODUCT d en s.PRODUCT_TYPE=d.PRODUCT_TYPE y s.PRODUCT_NAME=d.PRODUCT_NAME y s.PRODUCT_IS_SECURE=d.PRODUCT_IS_SECURE y s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT únase a PRODUCT_GROUP sg en s.PRODUCT_GROUP_ID= sg.PRODUCT_GROUP_ID se une a PRODUCT_GROUP dg en d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID y sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME y sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC se une a M_PG_DEPEND ENCY sd en sg.PRODUCT_GROUP_ID = sd.M _PG_DEPENDENCY_CHILD_ID dejó de unirse a M_PG_DEPENDENCY dd en dg. PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_CHILD_ID unión izquierda PRODUCT_GROUP sgp en sgp.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_PARENT_ID unión izquierda PRODUCT_GROUP dgp en dgp.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_PARENT_ID y sgp.PRODUC T_GRO UP_NAME = dgp.PRODUCT_GROUP_NAME y isnull(sgp.PRODUCT_GROUP_IS_TMPL, 0) = isnull( dgp.PRODUCT_GROUP_IS_TMPL, 0) donde (sd.M_PG_DEPENDENCY_CHILD_ID es nulo y dd.M_PG_DEPENDENCY_CHILD_ID es nulo) o (sgp.PRODUCT_GROUP_NAME no es nulo y dgp.PRODUCT_GROUP_NAME no es nulo)

Después de estas transformaciones, el rendimiento de la vista aumentó exponencialmente con la cantidad de productos encontrados. O mejor dicho, el tiempo de búsqueda permaneció prácticamente independiente del número de coincidencias y siempre fue muy reducido. Como debería ser.

Este es un claro ejemplo de cómo confiar en el optimizador MSSQL puede resultar una broma cruel. No confíes en él, no seas perezoso, únete manualmente, piensa cada vez qué es mejor en una situación determinada: existe, está o únete.

El predicado del lenguaje SQL EXISTS realiza una tarea lógica. EN consultas SQL este predicado se usa en expresiones de la forma

EXISTE (SELECCIONAR * DE TABLE_NAME...).

Esta expresión devuelve verdadero cuando la consulta encuentra una o más filas que coinciden con la condición y devuelve falso cuando no se encuentra ninguna fila.

Para NO EXISTE es al revés. Expresión

NO EXISTE (SELECCIONAR * DE TABLE_NAME...)

Devuelve verdadero cuando no se encuentra ninguna fila en la consulta y falso cuando se encuentra al menos una fila.

Las consultas más simples con el predicado SQL EXISTS

En los ejemplos trabajamos con la base de datos de la biblioteca y sus tablas “Libro en uso” (BOOKINUSE) y “Usuario” (USER). Por ahora, sólo necesitamos la tabla “Libro en uso” (BOOKINUSE).

Autor	Título	Año pub	Inv_No	ID_usuario
Tolstoi	Guerra y paz	2005	28	65
Chéjov	El huerto de cerezos	2000	17	31
Chéjov	Historias seleccionadas	2011	19	120
Chéjov	El huerto de cerezos	1991	5	65
Ilf y Petrov	Las doce sillas	1985	3	31
Mayakovsky	Poemas	1983	2	120
Chirivía	Doctor Zhivago	2006	69	120
Tolstoi	Domingo	2006	77	47
Tolstoi	Ana Karénina	1989	7	205
Pushkin	la hija del capitan	2004	25	47
Gógol	Obras de teatro	2007	81	47
Chéjov	Historias seleccionadas	1987	4	205
Chirivía	Favoritos	2000	137	18

Ejemplo 1. Determine las identificaciones de los usuarios a quienes se les entregaron los libros de Tolstoi y a quienes también se les entregaron los libros de Chéjov. La consulta externa selecciona datos sobre los usuarios a quienes se les entregaron los libros de Tolstoi, y el predicado EXISTS especifica una condición adicional que se verifica en la consulta interna: los usuarios a quienes se les entregaron los libros de Chéjov. Una condición adicional en la solicitud interna es que los ID de usuario de las solicitudes externa e interna coincidan: User_ID=tols_user.user_id. La solicitud será la siguiente:

Esta consulta devolverá el siguiente resultado:

Diferencias entre predicados EXISTS e IN

A primera vista, las consultas con el predicado EXISTS pueden tener la impresión de que son idénticas. predicado EN. Esto está mal. Aunque son muy similares. El predicado IN busca valores del rango especificado en su argumento y, si existen dichos valores, se seleccionan todas las filas correspondientes a este rango. El resultado del predicado EXISTS es una respuesta "sí" o "no" a la pregunta de si existe algún valor correspondiente a los especificados en el argumento. Además, el predicado IN va precedido del nombre de la columna por la que buscar filas que coincidan con los valores del rango. Veamos un ejemplo que muestra la diferencia entre el predicado EXISTS y el predicado IN, y el problema resuelto usando el predicado IN.

Ejemplo 4. Determine los ID de los usuarios a quienes se les han emitido libros los autores cuyos libros se han emitido al usuario con ID 31. La solicitud será la siguiente:

ID_usuario

120

65

205

Una consulta interna (después de IN) selecciona autores: Chéjov; Ilf y Petrov. La consulta externa selecciona todos los usuarios a quienes estos autores les han publicado libros. Vemos que, a diferencia del predicado EXISTS, el predicado IN va precedido del nombre de la columna, en este caso Autor.

Consultas con predicado EXISTS y condiciones adicionales

Si, además del predicado EXISTS en la consulta, aplica al menos una condición adicional, por ejemplo, especificada usando Funciones agregadas, entonces dichas consultas pueden servir para un análisis de datos simple. Demostremos esto con el siguiente ejemplo.

Ejemplo 5. Determine las identificaciones de los usuarios a quienes Pasternak les ha emitido al menos un libro y a quienes les han emitido más de 2 libros. Escribimos la siguiente consulta, en la que la primera condición está especificada por el predicado EXISTS con una consulta anidada, y la segunda condición con el operador HAVING siempre debe venir después de la consulta anidada:

Resultado de la solicitud:

ID_usuario

120

Como puede verse en la tabla BOOKINUSE, el libro de Pasternak también se entregó al usuario con ID 18, pero solo se le entregó un libro y no está incluido en la muestra. Si vuelve a aplicar la función CONTAR a una consulta similar, pero esta vez para contar las filas seleccionadas (practique esto usted mismo), puede obtener información sobre cuántos usuarios que leen los libros de Pasternak también leen libros de otros autores. Esto ya proviene del campo del análisis de datos.

Consultas con predicado EXISTS en dos tablas

Las consultas con el predicado EXISTS pueden recuperar datos de más de una tabla. Muchos problemas se pueden resolver con el mismo resultado usando UNIRSE al operador, pero en algunos casos el uso de EXISTS le permite crear una consulta menos engorrosa. Es preferible utilizar EXISTS en los casos en que la tabla resultante contendrá columnas de una sola tabla.

En el siguiente ejemplo, de la misma base de datos, además de la tabla BOOKINUSE, también necesitarás una tabla USER.

El resultado de la consulta será la siguiente tabla:

Autor

Chéjov

Mayakovsky

Chirivía

Al igual que con el uso del operador JOIN, en los casos en que haya más de una tabla, debe usar alias de tabla para verificar que los valores de las claves que conectan las tablas coincidan. En nuestro ejemplo, los alias de las tablas son bk y us, y la clave que conecta las tablas es User_ID.

Predicado EXISTE en uniones de más de dos tablas

Ahora veremos con más detalle por qué es preferible usar EXISTS en los casos en que la tabla resultante contendrá columnas de una sola tabla.

Trabajamos con la base de datos "Real Estate". La tabla de ofertas contiene datos sobre ofertas. Para nuestras tareas, la columna Tipo con datos sobre el tipo de transacción (venta o arrendamiento) será importante en esta tabla. La tabla Objeto contiene datos sobre objetos. En esta tabla, necesitaremos los valores de las columnas Rooms (número de habitaciones) y LogBalc, que contienen datos sobre la presencia de una logia o balcón en formato booleano: 1 (sí) o 0 (no). Las tablas Cliente, Administrador y Propietario contienen datos sobre clientes, administradores de empresas y propietarios, respectivamente. En estas tablas, FName y LName son el nombre y el apellido, respectivamente.

Ejemplo 7. Identifique clientes que hayan comprado o alquilado propiedades que no tienen logia o balcón. Escribimos la siguiente consulta, en la que el predicado EXISTS especifica un acceso al resultado de unir dos tablas:

Dado que las columnas se seleccionan de la tabla Cliente mediante el operador de asterisco, se mostrarán todas las columnas de esta tabla, que tendrá tantas filas como clientes que coincidan con la condición especificada por el predicado EXISTS. No necesitamos generar ninguna columna de las tablas a cuya combinación accede la subconsulta. Por lo tanto, para ahorrar tiempo a la máquina, sólo se recupera una columna. Para hacer esto, se escribe una unidad después de la palabra SELECT. La misma técnica se utiliza en las consultas de los siguientes ejemplos.

Escriba usted mismo una consulta SQL con el predicado EXISTS y luego mire la solución

Seguimos escribiendo consultas SQL junto con el predicado EXISTS.

Ejemplo 9. Determinar los propietarios de los objetos que fueron arrendados. Escribimos la siguiente consulta, en la que el predicado EXISTS también especifica un acceso al resultado de unir dos tablas:

Como en el ejemplo anterior, se devolverán todos los campos de la tabla a la que accede la consulta externa.

Ejemplo 10. Determine el número de propietarios cuyas propiedades estaban a cargo del administrador Savelyev. Escribimos una consulta en la que la consulta externa accede a una combinación de tres tablas y el predicado EXISTS especifica el acceso a una sola tabla:

Todas las consultas se comparan con una base de datos existente. ¡Uso exitoso!

Bases de datos relacionales y lenguaje SQL

Academia Estatal de Economía y Gestión de Novosibirsk

PRÁCTICAS DE LABORATORIO SOBRE DISCIPLINA

"BASE DE DATOS"

Trabajo de laboratorio No. 7.

"Lenguaje de las bases datos SQL: comandos de manipulación de datos»

NOVOSIBIRSK 2000

SQL es una abreviatura de lenguaje de consulta estructurado. Del nombre del lenguaje se desprende que su objetivo principal es generar consultas para obtener información de una base de datos. Los comandos para recuperar datos forman la base del lenguaje de manipulación de datos DML, una parte integral del lenguaje SQL. Sin embargo, DML consta de algo más que comandos para recuperar datos de una base de datos. También hay comandos para modificación de datos, gestión de datos y otros.

El trabajo de laboratorio examina las herramientas básicas del lenguaje DML. En curso trabajo de laboratorio Nos apegaremos al estándar SQL2.

Debido a que SQL es un lenguaje extenso, consideraremos sólo los comandos básicos. En laboratorios posteriores se tratan varias herramientas SQL específicas.

Para realizar trabajos de laboratorio se requieren conocimientos de los conceptos básicos del modelo de datos relacionales, los conceptos básicos de álgebra relacional y cálculo relacional y los principios de trabajo con el DBMS de MS SQL Server.

Como resultado de completar el trabajo de laboratorio, dominará los métodos de manipulación de datos utilizando comandos del lenguaje SQL, considere el dialecto del lenguaje implementado en el DBMS de MS SQL Server.

INTRODUCCIÓN

SQL contiene una amplia gama de capacidades de manipulación de datos, tanto para crear consultas como para actualizar la base de datos. Estas capacidades dependen únicamente de la estructura lógica de la base de datos, no de su estructura física, que es consistente con los requisitos del modelo relacional.

La estructura original de la sintaxis SQL estaba (o al menos parecía estar) basada en el cálculo relacional de Codd. La única operación admitida en álgebra relacional fue la unión.

Además de la sintaxis similar al cálculo relacional desarrollada en el estándar anterior, SQL2 implementa directamente las operaciones unión, intersección, diferencia y unión. Las operaciones de selección, proyecto y producto fueron (y continúan siendo) respaldadas casi directamente, mientras que las operaciones de división y asignación reciben respaldo de una forma más engorrosa.

Primero describiremos el lenguaje de consulta SQL y luego sus operaciones de entrada y modificación de datos. Las operaciones de modificación de datos se describirán en último lugar, ya que su estructura depende en cierta medida de la estructura del lenguaje de consulta.

consultas simples

Para nosotros petición sencilla Habrá una consulta que accede solo a una tabla en la base de datos. Las consultas simples nos ayudarán a ilustrar la estructura básica de SQL.

Solicitud sencilla. Una consulta que accede solo a una tabla de base de datos.

Pedido:¿Quiénes trabajan como yeseros?

WHERE SKILL_TYPE = "Yesero"

Resultado:

G.Rickover

Esta consulta ilustra los tres más comunes. frases SQL: SELECCIONAR, DESDE y DÓNDE. Aunque en nuestro ejemplo los colocamos en líneas diferentes, todos pueden aparecer en la misma línea. También se les puede aplicar sangría diferente y las palabras dentro de las frases se pueden separar mediante una cantidad arbitraria de espacios. Veamos las características de cada frase.

Seleccionar. La cláusula SELECT enumera las columnas que deberían aparecer en la tabla resultante. Siempre son columnas de alguna tabla relacional. En nuestro ejemplo, la tabla resultante consta de una columna (NOMBRE), pero en general puede contener varias columnas; también puede contener valores calculados o constantes. Daremos ejemplos de cada una de estas opciones. Si la tabla resultante debe contener más de una columna, todas las columnas requeridas se enumeran después SELECCIONAR comandos separado por comas. Por ejemplo, la frase SELECT WORKER_ID, NAME dará como resultado una tabla que consta de las columnas WORKER_ID y NAME.

Cláusula SELECT. Especifica las columnas de la tabla resultante.

De. La cláusula FROM especifica una o más tablas a las que accede la consulta. Todas las columnas enumeradas en las cláusulas SELECT y WHERE deben existir en una de las tablas enumeradas en el comando FROM. En SQL2, estas tablas se pueden definir directamente en el esquema como tablas base o vistas de datos, o pueden ser tablas sin nombre resultantes de consultas SQL. En el último caso, la solicitud se proporciona explícitamente en el comando FROM.

La frase DESDE. Especifica las tablas existentes a las que accede la consulta.

Dónde. La cláusula WHERE contiene una condición. sobre cuya base se seleccionan las filas de la(s) tabla(s). En nuestro ejemplo, la condición es que la columna SKILL_TYPE debe contener la constante "Yesero" encerrada entre apóstrofos, como siempre se hace con las constantes de texto en SQL. La cláusula WHERE es el comando SQL más volátil; puede contener muchas condiciones diferentes. Gran parte de nuestra discusión se dedicará a ilustrar las diversas construcciones permitidas en el comando WHERE.

Dónde cláusula. Especifica la condición en función de las filas que se seleccionan de las tablas especificadas.

El sistema procesa la consulta SQL anterior en el siguiente orden: DESDE, DÓNDE, SELECCIONAR. Es decir, las filas de la tabla especificadas en el comando FROM se colocan en el área de trabajo para su procesamiento. Luego, la cláusula WHERE se aplica a cada fila en secuencia. Todas las filas que no cumplan la condición WHERE quedan excluidas de la consideración. Luego, las filas que satisfacen la condición WHERE son procesadas por la instrucción SELECT. En nuestro ejemplo, se selecciona NOMBRE de cada una de esas filas y todos los valores seleccionados se generan como resultados de la consulta.

Pedido: Proporcionar toda la información sobre edificios de oficinas.

DONDE TIPO = "Oficina"

Resultado:

EDIFICIO IDADDRESSTYPEQLTY NIVELESTADO

312 Elm St., 123 Oficina 2 2

Calle Berezovaya, 210. 1011 Oficina Z 1

Calle Osinovaya, 111. 1213 Oficina 4 1

Un asterisco (*) en un comando SELECT significa "fila completa". Esta es una abreviatura conveniente que usaremos con frecuencia.

Pedido:¿Cuál es el salario semanal de cada electricista?

SELECCIONE NOMBRE, "Salario semanal = ", 40 * HRLY_RATE

WHERE SKILL_TYPE = "Electricista"

Resultado:

M. Faraday Salario semanal = 500,00

H.Columbus Salario semanal = 620.00

Esta consulta ilustra el uso de constantes de caracteres (en nuestro ejemplo "Salario semanal = ") y cálculos en el comando SELECT. Dentro de la instrucción SELECT, puede realizar cálculos que utilizan columnas numéricas y constantes numéricas, así como operadores aritméticos estándar ( +, -, *, /), agrupados según sea necesario mediante paréntesis. También hemos incluido un nuevo comando ORDEN POR, que ordena el resultado de la consulta en orden alfanumérico ascendente según la columna especificada. Si desea ordenar los resultados en orden descendente, debe agregar DESC al comando. La cláusula ORDER BY puede ordenar los resultados por varias columnas, algunas en orden ascendente y otras en orden descendente. La columna de clave principal de la clasificación aparece en primer lugar.

Carácter constante. Una constante que consta de letras, números y caracteres "especiales".

Pedido:¿Quién tiene una tarifa por hora de $10 a $12?

DONDE HRLY_RATE > = 10 Y HRLY_RATE< - 12

Resultado:

IDENTIFICACIÓN DEL TRABAJADOR NOMBRE HRLY_RATE SKILL_TYPE SUPV_ID

Esta consulta ilustra algunas de las características adicionales de la declaración WHERE: operadores de comparación y el operador booleano AND. Seis operadores de comparación (=,<>(no es igual),<, >, <=, >=). Los operadores booleanos AND, OR y NOT se pueden utilizar para crear condiciones compuestas o para negar una condición. Los paréntesis se pueden utilizar para agrupar condiciones, como es común en los lenguajes de programación.

Operadores de comparación =,<>, <, >, <=, >=.

operaciones booleanas Y (Y), O (O) y NO (ÉL) .

También podría utilizar el operador BETWEEN (entre) para formular esta consulta:

DONDE HRLY_RATE ENTRE 10 Y 12

ENTRE se puede utilizar para comparar una cantidad con otras dos cantidades, la primera de las cuales es menor que la segunda, si la cantidad comparada puede ser igual a cada una de estas cantidades o cualquier valor intermedio.

Solicitud: Listado de yeseros, techadores y electricistas.

WHERE SKILL_TYPE IN ("Yesero", "Techador", "Electricista")

Resultado:

WORKER_ID NOMBRE HRLY_RATE SKILL_TYPE SUPV_ID

1412 K.Nemo 13.75 Yesero 1520

2920 R. Garrett 10.00 Techador 2920

1520 G. Rickover 11,75 Yesero 1520

Esta consulta explica el uso del operador de comparación IN (B). La condición WHERE se considera verdadera si el tipo de especialidad de la fila se encuentra dentro del conjunto especificado entre paréntesis, es decir, si el tipo de especialidad es yesero, techador o electricista. Veremos nuevamente el operador IN en las subconsultas.

Supongamos que no podemos recordar exactamente la ortografía de nuestra especialidad: “electricista” o “ingeniero electrónico” o algo más. Los caracteres comodín, que reemplazan cadenas de caracteres no definidas, facilitan la búsqueda de ortografías inexactas en una consulta.

Símbolos de patrón. Caracteres que reemplazan cadenas de caracteres no definidas.

Pedido: Enumere los empleados cuyo tipo de especialidad comience con "Elek".

DONDE SKILL_TYPE LIKE ("Elegir%")

Resultado:

ID DE TRABAJADOR NOMBRE HRLY_RATE SKILL_TYPE SUPV_ID

1235 M. Faraday 12.50 Electricista 1311

1311 H. Colón 15.50 Eléctrico 1311

SQL tiene dos caracteres comodín: % (porcentaje) y _ (guión bajo). El guión bajo reemplaza exactamente un carácter no definido. El porcentaje reemplaza un número arbitrario de caracteres, comenzando con cero. Cuando se utilizan caracteres comodín, se requiere un operador LIKE para comparar variables de caracteres con constantes. Otros ejemplos:

NOMBRE COMO "__Columbus"

NOMBRE COMO "__K%"

La condición del primer ejemplo es verdadera si NOMBRE consta de dos caracteres seguidos de "Colón". En la tabla TRABAJADOR, todos los nombres comienzan con una primera inicial y un punto. Por lo tanto, usando esta condición nosotros. Busquemos a todos los empleados con el apellido "Columbus". La condición del segundo ejemplo nos permite encontrar todos los empleados cuyos apellidos comiencen con la letra “K”.

Pedido: Encuentre todos los trabajos que comiencen dentro de las próximas dos semanas.

DONDE LA FECHA_INICIO ENTRE LA FECHA_ACTUAL Y

Resultado:(Supongamos que la fecha actual es FECHA ACTUAL = 10.10)

WORKER_ID BLDG_ID START_DATE NUM_DAYS

1235 312 10.10 5

1235 515 17.10 22

3231 111 10.10 8

1412 435 15.10 15

3231 312 24.10 20

1311 460 23.10 24

Esta consulta ilustra el uso del operador ENTRE con valores de fecha e intervalo. CURRENT_DATE es una función que siempre devuelve la fecha de hoy. Expresión

FECHA_ACTUAL + INTERVALO "14" DÍA

agrega un período de dos semanas a la fecha actual. Por lo tanto, se selecciona ASIGNACIÓN (suponiendo que hoy es 10/10) si el valor de su columna START_DATE está entre 10/10 y 24/10. De esto podemos ver que podemos agregar valores de intervalo a los campos de fecha. Además, podemos multiplicar los valores de los intervalos por valores enteros. Por ejemplo, supongamos que queremos saber qué número habrá en un determinado número de semanas (indicado por la variable NUM_WEEKS). Podemos hacerlo así:

FECHA_ACTUAL + INTERVALO "7" DÍA * NUM_SEMANAS

2. Consultas de varias tablas

La capacidad de relacionar elementos de datos a través de los límites de una sola tabla es importante para cualquier lenguaje de base de datos. En álgebra relacional, esta función la realiza la operación de unión. Aunque gran parte de SQL se basa directamente en el cálculo relacional, SQL conecta datos de diferentes tablas de forma similar a la operación de unión del álgebra relacional. Ahora mostraremos cómo se hace esto. Considere la solicitud:

Pedido:

Los datos necesarios para la respuesta se encuentran en dos tablas: TRABAJADOR y ASIGNACIÓN. La solución SQL requiere enumerar ambas tablas en el comando FROM y especificar un tipo especial de cláusula WHERE:

SELECCIONAR TIPO DE HABILIDAD

DEL TRABAJADOR, ASIGNACIÓN

DONDE TRABAJADOR.ID_TRABAJADOR = ASIGNACIÓN.ID_TRABAJADOR

Y EDIFICIO_ID = 435

¿Que está pasando aqui? Debemos considerar dos etapas en cómo el sistema procesa esta solicitud.

1. Como de costumbre, primero se procesa la cláusula FROM. Sin embargo, en este caso, debido a que el comando especifica dos tablas, el sistema crea un producto cartesiano de las filas de estas tablas. Esto significa que se crea (lógicamente) una tabla grande que consta de columnas de ambas tablas, con cada fila de una tabla emparejada con cada fila de la otra tabla. En nuestro ejemplo, debido a que la tabla TRABAJADOR tiene cinco columnas y la tabla ASIGNACIÓN tiene cuatro columnas, el producto cartesiano generado por el comando FROM tendrá nueve columnas. El número total de filas del producto cartesiano es igual a m * n, donde m es el número de filas de la tabla TRABAJADOR; y n es el número de filas de la tabla ASIGNACIÓN. Dado que la tabla TRABAJADOR tiene 7 filas y la tabla ASIGNACIÓN tiene 19 filas, el producto cartesiano contendrá 7x19 o 133 filas. Si el comando FROM enumera más de dos tablas, se crea un producto cartesiano de todas las tablas especificadas en el comando.

producto cartesiano. El resultado de unir cada fila de una tabla con cada una fila de otra tabla.

2. Después de crear la tabla relacional gigante, el sistema usa el comando WHERE como antes. Cada fila de la tabla creada por el comando FROM. se verifica para ver si se cumple la condición WHERE. Las filas que no cumplan la condición quedarán excluidas de la consideración. Luego, la cláusula SELECT se aplica a las filas restantes.

La cláusula WHERE de nuestra consulta contiene dos condiciones:

1. TRABAJADOR. TRABAJADOR_ID = ASIGNACIÓN.TRABAJADOR_ID

2. EDIFICIO_ID = 435

La primera de estas condiciones es la condición de unión. Tenga en cuenta que, dado que las tablas WORKER y ASSIGNMENT contienen una columna denominada WORKER_ID, su producto cartesiano contendrá dos columnas con ese nombre. Para diferenciarlos, anteponemos el nombre de la columna con el nombre de la tabla fuente, separado por un punto.

La primera condición significa que en cualquier fila seleccionada, el valor de la columna WORKER_ID de la tabla WORKER debe coincidir con el valor de la columna WORKER_ID de la tabla ASSIGNMENT. En realidad, estamos uniendo dos tablas por WORKER_ID. Todas las filas en las que los valores de estas dos columnas no sean iguales se excluyen de la tabla de productos. Sucede exactamente lo mismo cuando se realiza la operación de unión natural del álgebra relacional. (Sin embargo, todavía hay alguna diferencia con respecto a una unión natural: SQL no elimina automáticamente la columna WORKER_ID adicional). La unión completa de estas dos tablas con la condición adicional BLDG_ID = 435 se muestra en la Fig. 1. El uso del comando SELECT finalmente dará el siguiente resultado de la consulta:

TIPO DE HABILIDAD

Yesero

Techador

Electricista

Arroz. 1. Unirse a las tablas TRABAJADOR y ASIGNACIÓN

Ahora mostraremos cómo unir una tabla consigo misma en SQL.

Pedido: Enumere los empleados, indicando los nombres de sus jefes.

SELECCIONE A.NOMBRE_TRABAJADOR, B.NOMBRE_TRABAJADOR

DEL TRABAJADOR A, TRABAJADOR B

DONDE B.WORKER_ID = A.SUPV_ID

La cláusula FROM de este ejemplo crea dos "copias" de la tabla WORKER, dándoles los alias A y B. Un alias es un nombre alternativo dado a la tabla. Luego, las copias A y B de la tabla WORKER se unen mediante el comando WHERE según la condición de igualdad de WORKER_ID en B y SUPV_ID en A. Por lo tanto, cada fila de A se une a la fila B, que contiene información sobre el administrador de la fila A. (Figura 2).

Arroz. 2. Unir dos copias de la tabla WORKER

Al seleccionar dos nombres de empleados de cada línea, obtenemos la lista requerida:

A.NOMBREB.NOMBRE

M. Faraday H. Colón

K.Nemo G.Rickover R.Garrett R.Garrett

P. Mason P. Mason G. Rickover G. Rickover H. Columbus H. Columbus J. Barrister P. Mason

Apodo. Un nombre alternativo dado a la tabla.

A.WORKER_NAME representa al trabajador y B.WORKER_NAME representa al administrador. Tenga en cuenta que algunos trabajadores son sus propios gerentes, lo que se desprende de la igualdad WORKER_ID - SUPV_ID en sus líneas.

En SQL, puedes vincular más de dos tablas a la vez:

Pedido

SELECCIONAR NOMBRE_TRABAJADOR

DEL TRABAJADOR, ASIGNACIÓN, EDIFICIO

DONDE TRABAJADOR.TRABAJADOR_ID = ASIGNACIÓN.TRABAJADOR_ID Y ASIGNACIÓN.BLDG_ID = EDIFICIO.BLDG_ID Y

TIPO = "Oficina"

Resultado:

M. Faraday

G.Rickover

J. Abogado

Tenga en cuenta que si el nombre de una columna (por ejemplo, WORKER_ID o BLDG_ID) aparece en más de una tabla, para evitar ambigüedades debemos anteponer el nombre de la columna al nombre de la tabla original. Pero si el nombre de la columna aparece solo en una tabla, como TYPE en nuestro ejemplo, entonces no hay ambigüedad, por lo que no es necesario especificar el nombre de la tabla.

Los comandos SQL de esta consulta crean una tabla a partir de tres tablas de bases de datos relacionales. Las dos primeras tablas se unen mediante WORKER_ID, después de lo cual la tercera tabla se une mediante BLDG_ID a la tabla resultante. Condición

TIPO = "Oficina"

La cláusula WHERE hace que se excluyan todas las filas excepto las de edificios de oficinas. Esto cumple con los requisitos de la solicitud.

3. Subconsultas

Subconsulta. Consulta dentro de una consulta

Se puede colocar una subconsulta dentro de la cláusula WHERE de una consulta, ampliando así las capacidades de la cláusula WHERE. Veamos un ejemplo.

Pedido:¿Cuáles son las especialidades de los trabajadores asignados al edificio 435?

SELECCIONE SKTLL_TYPE

DEL TRABAJADOR DONDE WORKER_ID EN

(SELECCIONE TRABAJADOR_ID

DONDE BLDG_ID = 435)

Subconsulta en este ejemplo

(SELECCIONE TRABAJADOR_ID

DONDE BLDG_ID = 435)

Una consulta que contiene una subconsulta se llama solicitud externa o solicitud principal. La subconsulta da como resultado la creación del siguiente conjunto de ID de empleado:

IDENTIFICACIÓN DEL TRABAJADOR

Solicitud externa. La consulta principal, que contiene todas las subconsultas.

Este conjunto de ID reemplaza a una subconsulta en la consulta externa. A partir de este momento, la consulta externa se ejecuta utilizando el conjunto creado por la subconsulta. La consulta externa procesa cada fila de la tabla WORKER según la cláusula WHERE. Si el WORKER_ID de una fila se encuentra en el conjunto (IN) creado por la subconsulta, entonces el SKILL_TYPE de la fila se selecciona y se muestra en la tabla resultante:

TIPO DE HABILIDAD

Yesero

Techador

Electricista

Es muy importante que la cláusula SELECT de la subconsulta contenga WORKER_ID y solo WORKER_ID. De lo contrario, la cláusula WHERE de la consulta externa, que significa que WORKER_ID está en el conjunto de ID de trabajadores, no tendría significado.

Tenga en cuenta que una subconsulta se puede ejecutar lógicamente antes de que la consulta principal considere al menos una fila. En cierto sentido, una subconsulta es independiente de la consulta principal. Se puede ejecutar como una consulta completa. Decimos que dicha subconsulta no está correlacionada con la consulta principal. Como veremos en breve, las subconsultas se pueden correlacionar.

Subconsulta no correlacionada. Una subconsulta cuyo valor es independiente de cualquier consulta externa.

A continuación se muestra un ejemplo de una subconsulta dentro de una subconsulta.

Pedido: Enumere los empleados asignados a los edificios de oficinas.

Nuevamente miramos la consulta con la que examinamos la conexión.

SELECCIONAR TRABAJADOR_MAME

DONDE WORKER_ID EN

(SELECCIONE TRABAJADOR_ID

DONDE BLDG_ID EN

DONDE TIPO = "Oficina"))

Resultado:

M. Faraday

G.Rickover

J. Abogado

Tenga en cuenta que no es necesario anteponer los nombres de las columnas a los nombres de las tablas en ninguna parte, ya que cada subconsulta procesa una y solo una tabla, por lo que no pueden surgir ambigüedades.

La ejecución de la consulta se produce en orden de adentro hacia afuera. Es decir, primero se ejecuta la consulta más interna (o "más inferior"), luego se ejecuta la subconsulta que la contiene y luego la consulta externa.

Subconsultas correlacionadas. Todas las subconsultas analizadas anteriormente eran independientes de las consultas principales en las que se utilizaron. Por independiente queremos decir que las subconsultas se pueden ejecutar por sí solas como consultas completas. Pasamos ahora a considerar una clase de subconsultas cuyos resultados de ejecución pueden depender de la fila considerada por la consulta principal. Estas subconsultas se denominan subconsultas correlacionadas.

Subconsulta correlacionada. Una subconsulta cuyo resultado depende de la fila considerada por la consulta principal.

Pedido: Enumere los empleados cuyas tarifas por hora son más altas que las de sus gerentes.

SELECCIONAR NOMBRE_TRABAJADOR

DONDE A.HRLY_RATE >

(SELECCIONE B.HRLY_RATE

DONDE B.WORKER_ID = A.SUPV_ID)

Resultado:

Los pasos lógicos para ejecutar esta solicitud son:

1. El sistema crea dos copias de la tabla TRABAJADOR: copia A y copia B. Según la forma en que las definimos, A se refiere al empleado, B se refiere al gerente.

2. Luego, el sistema considera cada fila A. Se selecciona una fila determinada si satisface la condición WHERE. Esta condición significa que se seleccionará una fila si su valor HRLY_RATE es mayor que el HRLY_RATE generado por la subconsulta.

3. La subconsulta selecciona el valor HRLY_RATE de la fila B, cuyo WORKER_ID es igual a SUPV_ID de la fila A, en este momento considerado por la solicitud principal. Este es el HRLY_RATE del gerente.

Tenga en cuenta que, dado que A.HRLY_RATE solo se puede comparar con un valor, la subconsulta debe devolver solo un valor. Este valor cambia dependiendo de qué fila A se esté considerando. Por tanto, la subconsulta se correlaciona con la consulta principal. Veremos más ejemplos de subconsultas correlacionadas más adelante, cuando estudiemos las funciones integradas.

Operadores EXISTE y NO EXISTE

Supongamos que queremos identificar trabajadores que no están asignados a trabajar en un determinado edificio. Superficialmente, parece que tal solicitud puede satisfacerse fácilmente simplemente negando la versión afirmativa de la solicitud. Supongamos, por ejemplo, que estamos interesados ​​en un edificio con BLDG_ID 435. Considere la solicitud:

SELECCIONAR TRABAJADOR_ID

DONDE BLDG_ID NO 435

Desafortunadamente, esta es una formulación incorrecta de la solución. La solicitud simplemente nos dará las identificaciones de los trabajadores que trabajan en otros edificios. Evidentemente, algunos de ellos también pueden asignarse al edificio 435.

Una solución correctamente formulada utiliza el operador NO EXISTE:

SELECCIONAR TRABAJADOR_ID

DONDE NO EXISTE

DONDE ASIGNACIÓN.WORKER_ID = TRABAJADOR.WORKER_ID Y

Resultado:

ID_TRABAJADOR

Los operadores EXISTE y NO EXISTE siempre se colocan antes de la subconsulta. EXISTS se evalúa como verdadero si el conjunto generado por la subconsulta no está vacío. Si el conjunto generado por la subconsulta está vacío, entonces EXISTS toma el valor "falso". El operador NO EXISTE, por supuesto, funciona exactamente al revés. Es verdadero si el resultado de la subconsulta está vacío y falso en caso contrario.

operador EXISTE. Devuelve verdadero si el conjunto de resultados no está vacío.

Operador NO EXISTE. Devuelve verdadero si el conjunto de resultados está vacío.

En este ejemplo utilizamos el operador NO EXISTE. La subconsulta selecciona todas las filas de la tabla ASSIGNMENT en las que WORKER_ID tiene el mismo valor que la fila considerada por la consulta principal y BLDG_ID es igual a 435. Si este conjunto está vacío, entonces la fila de trabajador considerada por la consulta principal es seleccionado, ya que esto significa que este empleado no trabaja en el edificio 435.

En la solución que proporcionamos, utilizamos una subconsulta correlacionada. Si usamos el operador IN en lugar de NOT EXISTS, podemos arreglárnoslas con una subconsulta no correlacionada:

SELECCIONAR TRABAJADOR_ID

DONDE WORKER_ID NO ESTÁ

(SELECCIONE TRABAJADOR_ID

DONDE BLDG_ID = 435)

Esta solución es más sencilla que la solución con el operador NO EXISTE. Surge una pregunta natural: ¿por qué necesitamos EXISTE y NO EXISTE en absoluto? La respuesta es que NO EXISTE es la única forma de resolver consultas que contienen la palabra "todos" en la condición. Estas consultas se resuelven en álgebra relacional mediante la operación de división y en cálculo relacional mediante el cuantificador universal. A continuación se muestra un ejemplo de una consulta con la palabra "cada" en su condición:

Pedido: Enumere los empleados asignados a cada edificio.

Esta pregunta se puede implementar en SQL usando dobles negaciones. Reformularemos la consulta para incluir un doble negativo:

Pedido: Enumere los empleados para quienes No hay un edificio al que no están asignados.

Destacamos el doble negativo. Es evidente que esta solicitud es lógicamente equivalente a la anterior.

Ahora queremos formular la solución en SQL. Para que la solución final sea más fácil de entender, primero damos una solución a un problema preliminar: el problema de identificar todos los edificios para los cuales un trabajador hipotético, "1234", No fijado.

(I) SELECCIONE BLDG_ID

DONDE NO EXISTE

ASIGNACIÓN.WORKER_ID = 1234)

Hemos marcado esta consulta (I) porque nos referiremos a ella más adelante. Si no hay ningún edificio que satisfaga esta solicitud, entonces se asigna el trabajador 1234 a cada edificio y, por lo tanto, satisface las condiciones de la solicitud original. Para obtener una solución a la consulta original, debemos generalizar la consulta (I) de un trabajador específico 1234 a la variable WORKER_ID y convertir esta consulta modificada en una subconsulta de la consulta más grande. Aquí está la solución:

(II) SELECCIONAR WORKER_ID

DONDE NO EXISTE

DONDE NO EXISTE

DONDE ASIGNACIÓN.BLDG_ID = EDIFICIO.BLDG_ID Y

ASIGNACIÓN.WORKER_ID = TRABAJADOR.WORKER_ID)

Resultado:

IDENTIFICACIÓN DEL TRABAJADOR

Tenga en cuenta que la subconsulta que comienza en la cuarta línea de la consulta (II) es idéntica a la consulta (I), con "1234" reemplazado por WORKER.WORKER_ID. La consulta (II) se puede leer de la siguiente manera:

Seleccione WORKER_ID de WORKER si no hay ningún edificio al que no esté asignado WORKER_ID.

Esto coincide con las condiciones de la solicitud original.

Vemos que el operador NO EXISTE se puede utilizar para formular aquellas consultas que requirieron una operación de división en álgebra relacional y un cuantificador universal en cálculo relacional. Desde el punto de vista de la facilidad de uso, el operador NOT EXISTS no ofrece ningún beneficio particular, lo que significa que las consultas SQL que usan NOT EXISTS dos veces no son más fáciles de entender que las soluciones de álgebra relacional con división o las soluciones de cálculo relacional con cuantificadores universales. Se necesitará más investigación para crear construcciones lingüísticas que permitan resolver dichas consultas de forma más natural.

Funciones integradas

Consideremos preguntas de este tipo:

¿Cuáles son las tarifas horarias máximas y mínimas? ¿Cuál es el número promedio de días que trabajan los empleados en el edificio 435? ¿Cuál es el número total de días asignados para los trabajos de enlucido del edificio 312? ¿Cuántas especialidades diferentes hay?

Responder a estas preguntas requiere funciones estadísticas que analicen muchas filas de una tabla y devuelvan un valor único. Hay cinco funciones de este tipo en SQL, llamadas funciones integradas o funciones de conjunto. Estas funciones son SUM (suma), AVG (promedio), COUNT (cantidad), MAX (máximo) y MIN (mínimo).

Función incorporada (función establecida). Una función estadística que opera en varias filas: SUM (suma), AVG (promedio), COUNT (cantidad), MAX (máximo), MIN (mínimo).

Pedido:¿Cuáles son las tarifas horarias máximas y mínimas?

SELECCIONE MAX(HRLY_RATE), MIN(HRLY_RATE)

Resultado: 17.40, 8.20

Funciones MÁXIMAS y MIN operan en una columna de la tabla. Seleccionan el valor máximo o mínimo, respectivamente, de esta columna. Nuestra formulación de consulta no contiene una cláusula WHERE. Para la mayoría de las consultas, este puede no ser el caso, como muestra nuestro siguiente ejemplo.

Pedido:¿Cuál es el número promedio de días que trabajan los empleados en el edificio 435?

SELECCIONAR PROMEDIO(NUM_DÍAS)

DONDE BLDG_ID =435

Resultado: 12.33

Pedido:¿Cuál es el número total de días asignados para los trabajos de enlucido del edificio 312?

SELECCIONAR SUMA(NUM_DÍAS)

DE ASIGNACIÓN, TRABAJADOR

DONDE TRABAJADOR.ID_TRABAJADOR = ASIGNACIÓN.ID_TRABAJADOR Y

SKILL_TYPE = "Yesero" Y

Resultado: 27

La solución utiliza una unión entre las tablas ASSIGNMENT y WORKER. Esto es necesario porque SKILL_TYPE está en la tabla WORKER y BLDG_ID está en la tabla ASSIGNMENT.

Pedido:¿Cuántas especialidades diferentes hay?

SELECCIONAR RECUENTO (DISTINCT SKILL_TYPE)

Resultado: 4

Debido a que la misma especialidad puede aparecer en varias filas diferentes, debe utilizar la palabra clave DISTINCT en esta consulta para evitar que el sistema cuente el mismo tipo de especialidad más de una vez. El operador DISTINCT se puede utilizar con cualquiera de las funciones integradas, aunque por supuesto es redundante con las funciones MAX y MIN.

DISTINTO. Un operador que elimina líneas duplicadas.

Las funciones SUMA y AVG solo deben usarse con columnas numéricas. Se pueden utilizar otras funciones con datos tanto numéricos como de caracteres. Todas las funciones excepto COUNT se pueden utilizar con expresiones calculadas. Por ejemplo:

Pedido:¿Cuál es el salario semanal promedio?

SELECCIONAR PROMEDIO (40 * HRLY_RATE)

Resultado: 509.14

COUNT puede referirse a una fila completa en lugar de a una columna individual :

Pedido: ¿Cuántos edificios tienen nivel de calidad 3?

SELECCIONAR CUENTA (*)

DEL EDIFICIO DONDE

Resultado: 3

Como muestran todos estos ejemplos, si un comando SELECT contiene una función incorporada, entonces no puede aparecer nada más en ese comando SELECT. La única excepción a esta regla es la cláusula GROUP BY, que veremos ahora.

Cláusulas GROUP BY y HAVING

En la gestión, a menudo se requiere información estadística sobre cada grupo en muchos grupos. Por ejemplo, considere la siguiente consulta:

Pedido: Para cada gerente, averigüe la tarifa horaria máxima entre sus subordinados.

Para solucionar este problema, debemos dividir a los trabajadores en grupos según sus jefes. Luego determinaremos la oferta máxima dentro de cada grupo. En SQL esto se hace de esta manera:

GRUPO POR SUPV_ID

Resultado:

SUPV_IDMAX(TARIFA HRLY)

Al procesar esta consulta, el sistema primero divide las filas de la tabla TRABAJADOR en grupos usando la siguiente regla. Las filas se colocan en el mismo grupo si y sólo si tienen el mismo SUPV_ID. Luego, la cláusula SELECT se aplica a cada grupo. Dado que solo hay un valor SUPV_ID en este grupo, no hay incertidumbre de SUPV_ID en el grupo. Para cada grupo, la cláusula SELECT genera SUPV_ID y también calcula y genera el valor MAX(HRLY_RATE). El resultado se presenta arriba.

En un comando SELECT con funciones integradas, solo pueden aparecer aquellas columnas que están incluidas en la cláusula GROUP BY. Tenga en cuenta que SUPV_ID se puede utilizar en un comando SELECT porque está incluido en la cláusula GROUP BY.

Cláusula GRUPO POR. Indica que las filas deben dividirse en grupos con valores comunes de la(s) columna(s) especificada(s).

La cláusula GROUP BY le permite realizar ciertos cálculos complejos. Por ejemplo, es posible que queramos averiguar el promedio de estas ofertas máximas. Sin embargo, el cálculo con funciones integradas está limitado en el sentido de que no permite que las funciones integradas se utilicen dentro de otras funciones integradas. Entonces una expresión como

PROMEDIO(MÁXIMO(HRLY_RATE))

prohibido. La implementación de dicha solicitud constará de dos etapas. Primero debemos poner las ofertas máximas en una nueva tabla y, en un segundo paso, calcular su promedio.

Puede utilizar la cláusula WHERE con el comando GROUP BY:

Pedido: Para cada tipo de edificio infórmate nivel promedio calidad entre los edificios del estado 1.

SELECCIONAR TIPO, PROMEDIO(QLTY_LEVEL)

DONDE ESTADO = 1

Resultado:

TIPOAVG(QLTY_LEVEL)

Tienda 1

edificio residencial 3

La cláusula WHERE se ejecuta antes de la declaración GROUP BY. Por lo tanto, ningún grupo puede contener una fila que tenga un estado distinto de 1. Las filas de estado 1 se agrupan por valor TYPE y luego se aplica una cláusula SELECT a cada grupo.

TENER frase. Pone condiciones a los grupos.

También podemos aplicar condiciones a grupos creados por la cláusula GROUP BY. Esto se hace usando la frase TENER. Supongamos, por ejemplo, que decidimos hacer más específica una de las consultas anteriores:

Pedido: Para cada gerente que tenga más de un subordinado, averigüe la tarifa horaria máxima entre sus subordinados.

Podemos reflejar esta condición con el comando HAVING apropiado:

SELECCIONE SUPV_ID, MAX(HRLY_RATE)

DEL GRUPO DE TRABAJADORES POR SUPV_ID

TENER CUENTA (*) > 1

Resultado:

SUPV_ID MAX(HRLY_RATE)

La diferencia entre las cláusulas WHERE y HAVING es que WHERE se aplica a filas, mientras que HAVING se aplica a grupos.

Una consulta puede contener una cláusula WHERE y HAVING. En este caso, la cláusula WHERE se ejecuta primero porque se ejecuta antes de la agrupación. Por ejemplo, considere la siguiente modificación de la consulta anterior:

Pedido: Para cada tipo de edificio, averigüe el nivel medio de calidad entre los edificios de categoría 1. Considere únicamente aquellos tipos de edificios cuyo nivel máximo de calidad no supere el nivel 3.

SELECCIONAR TIPO, PROMEDIO (QLTY_JLEVEL)

DONDE ESTADO = 1

TENER MAX(QLTY_LEVEL)<= 3

Resultado:

TIPO PROMEDIO(QLTY_LEVEL)

Tienda 1

edificio residencial 3

Tenga en cuenta que a partir de la cláusula FROM, las cláusulas se ejecutan en orden y luego se aplica la cláusula SELECT. Por lo tanto, la cláusula WHERE se aplica a la tabla BUILDING y se eliminan todas las filas en las que STATUS es diferente de 1. Las filas restantes están agrupadas por TIPO; todas las filas con el mismo valor de TIPO terminan en el mismo grupo. Así, se crean varios grupos, uno para cada valor de TIPO. Luego se aplica la cláusula HAVING a cada grupo y se eliminan aquellos grupos cuyo valor máximo de nivel de calidad exceda 3. Finalmente, la cláusula SELECT se aplica a los grupos restantes.

7. Funciones y subconsultas integradas

Las funciones integradas solo se pueden utilizar en una cláusula SELECT o en un comando HAVING. Sin embargo, una cláusula SELECT que contiene una función en línea puede ser parte de una subconsulta. Veamos un ejemplo de dicha subconsulta:

Pedido:¿Qué trabajadores tienen una tarifa por hora superior a la media?

SELECCIONAR NOMBRE_TRABAJADOR

DONDE HRLY_RATE >

(SELECCIONE PROMEDIO(HRLY_RATE)

Resultado:

H. Colón

Tenga en cuenta que la subconsulta no está correlacionada con la consulta principal. La subconsulta devuelve exactamente un valor: la tarifa promedio por hora. La consulta principal selecciona a un trabajador solo si su tarifa es mayor que el promedio calculado.

Las consultas correlacionadas también pueden utilizar funciones integradas:

Consulta: ¿Qué empleado tiene una tarifa por hora superior a la tarifa por hora promedio entre los subordinados del mismo gerente?

En este caso, en lugar de calcular una tarifa promedio por hora para todos los trabajadores, debemos calcular la tarifa promedio para cada grupo de trabajadores que reportan al mismo gerente. Además, nuestro cálculo debe realizarse de nuevo para cada trabajador considerado por la consulta principal:

SELECCIONE A. NOMBRE_TRABAJADOR

SQL le permite anidar consultas entre sí. Normalmente, una subconsulta devuelve un valor único, que se comprueba para ver si el predicado es verdadero.

Tipos de términos de búsqueda:
. Comparación con el resultado de una subconsulta (=, >=)
. Comprobación de pertenencia a los resultados de una subconsulta (IN)
. Comprobación de existencia (EXISTE)
. Comparación múltiple (cuantitativa) (CUALQUIERA, TODOS)

Notas sobre consultas anidadas:
. Una subconsulta debe seleccionar solo una columna (excepto una subconsulta con un predicado EXISTS) y su tipo de datos de resultado debe coincidir con el tipo de datos del valor especificado en el predicado.
. En algunos casos, puede utilizar la palabra clave DISTINCT para asegurarse de que se devuelva un valor único.
. No puede incluir una cláusula ORDER BY o UNION en una subconsulta.
. La subconsulta puede ubicarse a la izquierda o a la derecha de la condición de búsqueda.
. Las subconsultas pueden utilizar funciones de agregación sin una cláusula GROUP BY, que devuelven automáticamente un valor especial para cualquier número de filas, un predicado IN especial y expresiones basadas en columnas.
. Siempre que sea posible, debe utilizar combinaciones de tablas JOIN en lugar de subconsultas.

Ejemplos de consultas anidadas:

SELECCIONAR * DE Pedidos DONDE SNum=(SELECCIONAR SNum DE Vendedores DONDE SName=’Motika’)
SELECCIONE * DE Pedidos DONDE SNum IN (SELECCIONE SNum DE Vendedores DONDE Ciudad=’Londres’)
SELECCIONAR * DE Órdenes DONDE SNum=(SELECCIONAR DISTINTO SNum DE Órdenes DONDE CNum=2001)
SELECCIONE * DE Órdenes DONDE Cantidad>(SELECCIONE PROMEDIO(Amt) DE Órdenes DONDE Fecha=04/10/1990)
SELECCIONE * DEL Cliente DONDE CNum=(SELECCIONE SNum+1000 DE Vendedores DONDE SName=’Serres’)

2) Subconsultas relacionadas

En SQL, puede crear subconsultas que hagan referencia a una tabla desde una consulta externa. En este caso, la subconsulta se ejecuta varias veces, una por cada fila de la tabla de la consulta externa. Por tanto, es importante que la subconsulta utilice el índice. Una subconsulta puede acceder a la misma tabla que una externa. Si la consulta externa devuelve una cantidad relativamente pequeña de filas, entonces la subconsulta vinculada será más rápida que la no vinculada. Si una subconsulta devuelve una pequeña cantidad de filas, la consulta relacionada será más lenta que la consulta no relacionada.

Ejemplos de subconsultas relacionadas:

SELECT * FROM SalesPeople Main WHERE 1(SELECT AVG(Amt) FROM Orders O2 WHERE O2.CNum=O1.CNum) //devuelve todos los pedidos cuyo valor excede el valor promedio del pedido para un cliente determinado

3) El predicado EXISTE

Forma sintáctica: EXISTE ()

El predicado toma una subconsulta como argumento y se evalúa como verdadero si la subconsulta tiene resultados y como falso en caso contrario. La subconsulta se ejecuta una vez y puede contener varias columnas, ya que sus valores no se verifican, pero simplemente se registra el resultado de la presencia de filas.

Notas sobre el predicado EXISTS:
. EXISTS es un predicado que devuelve VERDADERO o FALSO y puede usarse solo o con otras expresiones booleanas.
. EXISTS no puede utilizar funciones de agregación en su subconsulta.
. En subconsultas correlacionadas, el predicado EXISTS se ejecuta para cada fila de la tabla externa.
. Puede combinar el predicado EXISTS con combinaciones de tablas.

Ejemplos del predicado EXISTS:

SELECCIONAR * DEL Cliente DONDE EXISTE (SELECCIONAR * DEL Cliente DONDE Ciudad = 'San José'): devuelve todos los clientes si alguno de ellos vive en San José.
SELECCIONE DISTINCT SNum FROM Customer First WHERE NOT EXISTS (SELECT * FROM Customer Send WHERE Send.SNum=First.SNum AND Send.CNumFirst.CNum): devuelve los números de vendedores que atendieron solo a un cliente.
SELECCIONE DISTINTO F.SNum, SName, F.Ciudad DE SalesPeople F, Cliente S DONDE EXISTE (SELECCIONE * DEL Cliente T DONDE S.SNum=T.SNum AND S.CNumT.CNum AND F.SNum=S.SNum) – devuelve números, nombres y ciudades de residencia de todos los vendedores que atendieron a varios clientes.
SELECCIONE * DE Vendedores Primero DONDE EXISTE (SELECCIONE * DE Cliente Enviar DONDE Primer.SNum=Enviar.SNum Y 1

4) Predicados de comparación cuantitativa

Forma sintáctica: (=|>|=|) CUALQUIER|TODOS ()

Estos predicados utilizan una subconsulta como argumento; sin embargo, en comparación con el predicado EXISTS, se utilizan junto con predicados relacionales (=,>=). En este sentido, son similares al predicado IN, pero se usan sólo con subconsultas. El estándar permite utilizar la palabra clave SOME en lugar de ANY, pero no todos los DBMS la admiten.

Notas sobre predicados de comparación:
. El predicado ALL se evalúa como VERDADERO si cada valor seleccionado durante la ejecución de la subconsulta satisface la condición especificada en el predicado de la consulta externa. Se utiliza con mayor frecuencia con desigualdades.
. El predicado ANY se evalúa como VERDADERO si al menos un valor seleccionado durante la ejecución de la subconsulta satisface la condición especificada en el predicado de la consulta externa. Se utiliza con mayor frecuencia con desigualdades.
. Si la subconsulta no devuelve ninguna fila, TODO automáticamente toma el valor VERDADERO (se considera que se cumple la condición de comparación), y para CUALQUIER toma el valor FALSO.
. Si la comparación es VERDADERA sin filas y hay una o más filas con un valor NULL, ANY devuelve DESCONOCIDO.
. Si la comparación es FALSA sin filas y hay una o más filas con un valor NULL, entonces TODO devuelve DESCONOCIDO.

Ejemplos del predicado de comparación cuantitativa:

SELECCIONE * DE Vendedores DONDE Ciudad = CUALQUIER (SELECCIONE Ciudad DEL Cliente)
SELECCIONAR * DE Pedidos DONDE Cantidad TODOS (SELECCIONAR Calificación DEL Cliente DONDE Ciudad = 'Roma')

5) Predicado de unicidad

ÚNICO|DISTINTO ()

El predicado se utiliza para comprobar la unicidad (ausencia de duplicados) en los datos de salida de la subconsulta. Además, en el predicado UNIQUT, las cadenas con valores NULL se consideran únicas, y en el predicado DISTINCT, dos valores indefinidos se consideran iguales entre sí.

6) Predicado de coincidencia

FÓSFORO ()

El predicado MATCH prueba si el valor de una cadena de consulta coincidirá con el valor de cualquier cadena resultante de la subconsulta. Esta subconsulta se diferencia de los predicados IN AND ANY en que permite procesar coincidencias "parciales" (PARTICALES) que pueden ocurrir entre filas que tienen algunos valores NULL.

7) Consultas en la sección DESDE

De hecho, es legal utilizar una subconsulta siempre que se permita una referencia a una tabla.

SELECCIONE CName, Tot_Amt DEL Cliente, (SELECCIONE CNum, SUM(Amt) AS Tot_Amt FROM Orders GROUP BY CNum) DONDE Ciudad=’Londres’ AND Customer.CNum=Orders.CNum
//subconsulta devuelve la cantidad total de pedidos realizados por cada cliente desde Londres.

8) consultas recursivas

CON RECURSIVO
P1 COMO SELECCIONAR… DESDE… DONDE…
P2 COMO SELECCIONAR… DESDE… DONDE…