Fundamentos de Bases de Datos PDF

CAPÍTULO 4 SQL L os lenguajes formales descritos en el Capítulo 3 proporcionan una notación concisa para la representación de consultas. Sin embargo, los sistemas de bases de datos comerciales...

CAPÍTULO 4 SQL L os lenguajes formales descritos en el Capítulo 3 proporcionan una notación concisa para la representación de consultas. Sin embargo, los sistemas de bases de datos comerciales necesitan un lenguaje de consultas cómodo para el usuario. En este capítulo se estudia el lenguaje comercial de mayor influencia, SQL. SQL usa una combinación de álgebra relacio- nal y construcciones del cálculo relacional. Aunque el lenguaje SQL se considere un lenguaje de consultas, contiene muchas otras capa- cidades además de la consulta en bases de datos. Incluye características para definir la estruc- tura de los datos, para la modificación de los datos en la base de datos y para la especificación de restricciones de seguridad. No se pretende proporcionar un manual de usuario completo para SQL. Por el contrario, se presentan las construcciones y conceptos fundamentales de SQL. Las distintas implementa- ciones de SQL pueden diferenciarse en detalles, o pueden admitir sólo un subconjunto del len- guaje completo. 4.1. INTRODUCCIÓN IBM desarrolló la versión original en su Laboratorio de características de SQL:1999 y se proporciona un estu- Investigación de San José (San José Research Center, dio más detallado en el Capítulo 9. Muchos sistemas de actualmente Centro de Investigación de Almadén, Alma- bases de datos soportan algunas de las nuevas cons- dén Research Center). IBM implementó el lenguaje, tructoras de SQL:1999, aunque ningún sistema de bases originalmente denominado Sequel, como parte del pro- datos actual soporta todas las nuevas constructoras. Tam- yecto System R, a principios de 1970. El lenguaje Sequel bién hay ser consciente de que algunos sistemas de bases ha evolucionado desde entonces y su nombre ha pasa- de datos ni siquiera soportan todas las características de do a ser SQL (Structured Query Language, Lenguaje SQL-92 y de que muchas bases de datos proporcionan estructurado de consultas). Actualmente, numerosos características no estándar que no se tratan aquí. productos son compatibles con el lenguaje SQL. SQL El lenguaje SQL tiene varios componentes: se ha establecido como el lenguaje estándar de bases de datos relacionales. Lenguaje de definición de datos (LDD). El LDD En 1986, ANSI (American National Standards Ins- de SQL proporciona órdenes para la definición de titute, Instituto Nacional Americano de Normalización) esquemas de relación, borrado de relaciones, cre- e ISO (International Standards Organization, Organi- ación de índices y modificación de esquemas de zación Internacional de Normalización), publicaron una relación. norma SQL, denominada SQL-86. En 1987, IBM publi- Lenguaje interactivo de manipulación de datos có su propia norma de SQL corporativo, Interfaz de (LMD). El LMD de SQL incluye un lenguaje de bases de datos para arquitecturas de aplicación a siste- consultas, basado tanto en el álgebra relacional mas (Systems Application Architecture Database Inter- como en el cálculo relacional de tuplas. Incluye face, SAA-SQL). En 1989 se publicó una norma exten- también órdenes para insertar, borrar y modificar dida para SQL denominada SQL-89 y actualmente los tuplas de la base de datos. sistemas de bases de datos son normalmente compati- Definición de vistas. El LDD de SQL incluye bles al menos con las características de SQL-89. La órdenes para la definición de vistas. siguiente versión de la norma fue SQL-92 y la versión más reciente es SQL:1999. Las notas bibliográficas pro- Control de transacciones. SQL incluye órdenes porcionan referencias a esas normas. para la especificación del comienzo y final de tran- En este apartado se presenta una visión general de sacciones. SQL basada en la norma SQL-92 ampliamente imple- SQL incorporado y SQL dinámico. SQL diná- mentada. La norma SQL:1999 es un superconjunto de mico e incorporado define cómo se pueden incor- la norma SQL-92; en este capítulo se tratan algunas porar las instrucciones SQL en lenguajes de pro- 87 FUNDAMENTOS DE BASES DE DATOS gramación de propósito general, tales como C, Esquema-sucursal = (nombre-sucursal, C++, Java, PL/I, Cobol, Pascal y Fortran. ciudad-sucursal, activo) Integridad. El LDD de SQL incluye órdenes para Esquema-cliente = (nombre-cliente, calle-cliente, la especificación de las restricciones de integridad ciudad-cliente) que deben satisfacer los datos almacenados en la Esquema-préstamo = (número-préstamo, base de datos. Las actualizaciones que violen las nombre-sucursal, importe) restricciones de integridad se rechazan. Esquema-prestatario = (nombre-cliente, número-préstamo) Autorización. El LDD de SQL incluye órdenes Esquema-cuenta = (número-cuenta, nombre- para especificar derechos de acceso para las rela- sucursal, saldo) ciones y vistas. Esquema-impositor = (nombre-cliente, En este capítulo se estudia el LMD y las caracterís- número-cuenta) ticas básicas del LDD de SQL. También se describe bre- vemente SQL incorporado y dinámico, incluyendo las Nótese que en este capítulo, como en el resto del tex- normas ODBC y JDBC para la interacción con una base to, se usan nombres separados por guiones para los de datos desde programas escritos en lenguajes C y Java. esquemas, relaciones y atributos para facilitar su lectu- Las características de SQL que dan soporte a la integri- ra. Sin embargo, en los sistemas SQL actuales, los guio- dad y autorización se describen en el Capítulo 6, mien- nes no son partes válidas de un nombre (se tratan como tras que el Capítulo 9 esboza las extensiones orientadas el operador menos). Una forma simple de traducir los a objeto de SQL. nombres que se usan aquí a nombres SQL válidos es Los ejemplos de este capítulo y posteriores se basa- reemplazar todos los guiones por el símbolo de subra- rán en una empresa bancaria, con los siguientes esque- yado («_»). Por ejemplo, se usa nombre_sucursal en mas de relación: lugar de nombre-sucursal. 4.2. ESTRUCTURA BÁSICA Una base de datos relacional consiste en un conjunto de select A1, A2,…, An relaciones, a cada una de las cuales se le asigna un nom- from r1, r2,…, rm bre único. Cada relación tiene una estructura similar a where P la presentada en el Capítulo 3. SQL permite el uso de valores nulos para indicar que el valor o bien es desco- Cada Ai representa un atributo, y cada ri una relación. nocido, o no existe. Se fijan criterios que permiten al P es un predicado. La consulta es equivalente a la expre- usuario especificar a qué atributos no se puede asignar sión del álgebra relacional valor nulo, como estudiaremos en el Apartado 4.11. La estructura básica de una expresión SQL consiste Π A1, A2,…, An (σP (r1 × r2 × … × rm )) en tres cláusulas: select, from y where. Si se omite la cláusula where, el predicado P es cierto. La cláusula select corresponde a la operación pro- Sin embargo, con diferencia a la expresión del álgebra yección del álgebra relacional. Se usa para listar los relacional, el resultado de la consulta SQL puede con- atributos deseados del resultado de una consulta. tener varias copias de algunas tuplas; este aspecto se analizará de nuevo en el Apartado 4.2.8. La cláusula from corresponde a la operación pro- SQL forma el producto cartesiano de las relaciones ducto cartesiano del álgebra relacional. Lista las incluidas en la cláusula from, lleva a cabo la selección relaciones que deben ser analizadas en la evalua- del álgebra relacional usando el predicado de la cláu- ción de la expresión. sula where y entonces proyecta el resultado sobre los La cláusula where corresponde al predicado selec- atributos de la cláusula select. En la práctica, SQL pue- ción del álgebra relacional. Es un predicado que de convertir la expresión en una forma equivalente que engloba a los atributos de las relaciones que apa- puede ser procesada más eficientemente. Las cuestio- recen en la cláusula from. nes relativas a la eficiencia se analizan en los Capítulos 13 y 14. Un hecho histórico desafortunado es que el término select tiene un significado diferente en SQL que en el álge- 4.2.1. La cláusula select bra relacional. A continuación se resaltan las diferentes interpretaciones, a fin de minimizar la posible confusión. El resultado de una consulta SQL es, por supuesto, una Una consulta típica en SQL tiene la forma relación. Considérese una consulta simple, usando el 88  CAPÍTULO 4 SQL ejemplo bancario, «Obtener los números de todas las devolverá una relación que es igual que la relación prés- sucursales en la relación préstamo»: tamo, salvo que el atributo importe está multiplicado por 100. select nombre-sucursal SQL también proporciona tipos de datos especia- from préstamo les, tales como varias formas del tipo fecha y permite varias funciones aritméticas para operar sobre esos El resultado es una relación consistente en el único atri- tipos. buto nombre-sucursal. Los lenguajes formales de consulta están basados en la noción matemática de que una relación es un con- 4.2.2. La cláusula where junto. Así, nunca aparecen tuplas duplicadas en las rela- A continuación se ilustra con un ejemplo el uso de la ciones. En la práctica, la eliminación de duplicados con- cláusula where en SQL. Considérese la consulta «Obte- sume tiempo. Sin embargo, SQL (como la mayoría de ner todos los números de préstamo para préstamos los lenguajes de consulta comerciales) permite dupli- hechos en la sucursal con nombre Navacerrada, en los cados en las relaciones, así como en el resultado de las que el importe sea superior a 1.200 €». Esta consulta expresiones SQL. Así, la consulta anterior listará cada puede escribirse en SQL como nombre-sucursal una vez por cada tupla en la que apa- rece en la relación préstamo. select número-préstamo En aquellos casos donde se quiera forzar la elimina- from préstamo ción de duplicados, se insertará la palabra clave distinct where nombre-sucursal = ‘Navacerrada’ and después de select. Por lo tanto, se puede reescribir la importe > 1200 consulta anterior como SQL usa las conectivas lógicas and, or y not (en select distinct nombre-sucursal lugar de los símbolos matemáticos ∧, ∨ y ¬) en la cláu- from préstamo sula where. Los operandos de las conectivas lógicas si se desean eliminar los duplicados. pueden ser expresiones que contengan los operadores Es importante resaltar que SQL permite usar la pala- de comparación =, = y. SQL permite usar bra clave all para especificar explícitamente que no se los operadores de comparación para comparar cadenas eliminan los duplicados: y expresiones aritméticas, así como tipos especiales, tales como el tipo fecha. select all nombre-sucursal SQL incluye un operador de comparación between from préstamo para simplificar las cláusulas where que especifica que un valor sea menor o igual que un valor y mayor o igual Como de manera predeterminada se realiza la reten- que otro valor. Si se desea obtener el número de prés- ción de duplicados, de ahora en adelante no se usará la tamo de aquellos préstamos por importes entre 90.000 € palabra clave all en los ejemplos. Para asegurar la elimi- y 100.000 €, se puede usar la comparación between nación de duplicados en el resultado de los ejemplos de para escribir consultas, se usará la cláusula distinct siempre que sea necesario. En la mayoría de las consultas donde no se uti- select número-préstamo liza distinct, el número exacto de copias duplicadas de from préstamo cada tupla que resultan de la consulta no es importante. where importe between 90000 and 100000 Sin embargo, el número es importante en ciertas aplica- ciones; este aspecto se volverá a tratar en el Apartado 4.2.8. en lugar de El símbolo asterisco «*» se puede usar para denotar «todos los atributos». Así, el uso de préstamo.* en la select número-préstamo cláusula select anterior indicaría que todos los atribu- from préstamo tos de préstamo serían seleccionados. Una cláusula where importe = 90000 select de la forma select * indica que se deben selec- cionar todos los atributos de todas las relaciones que De forma análoga, se puede usar el operador de com- aparecen en la cláusula from. paración not between. La cláusula select puede contener también expre- siones aritméticas que contengan los operadores, +, –, 4.2.3. La cláusula from * y / operando sobre constantes o atributos de la tuplas. Por ejemplo, la consulta Finalmente, se estudia el uso de la cláusula from. La cláusula from define por sí misma un producto carte- select nombre-sucursal, número-préstamo, siano de las relaciones que aparecen en la cláusula. importe * 100 Escribir una expresión SQL para la reunión natural es from préstamo una tarea relativamente fácil, puesto que la reunión natu- 89 FUNDAMENTOS DE BASES DE DATOS ral se define en términos de un producto cartesiano, una select distinct nombre-cliente, prestatario.número- selección y una proyección. préstamo, importe La expresión del álgebra relacional se escribe como from prestatario, préstamo sigue: where prestatario.número-préstamo = préstamo.número-préstamo Πnombre-cliente, número-préstamo,importe ( prestatario préstamo) El resultado de esta consulta es una relación con los atri- para la consulta «Para todos los clientes que tienen un butos siguientes: préstamo en el banco, obtener los nombres, números de préstamo e importes». Esta consulta puede escribirse en nombre-cliente, número-préstamo, importe. SQL como Los nombres de los atributos en el resultado se derivan select nombre-cliente, prestatario.número-préstamo, de los nombres de los atributos de la relación que apa- importe rece en la cláusula from. from prestatario, préstamo Sin embargo, no se pueden derivar siempre los nom- where prestatario.número-préstamo bres de este modo. En primer lugar, dos relaciones que = préstamo.número-préstamo aparecen en la cláusula from pueden tener atributos con el mismo nombre, en cuyo caso, un nombre de atribu- Nótese que SQL usa la notación nombre-relación.nom- to se duplica en el resultado. En segundo lugar, si se bre-atributo, como lo hace el álgebra relacional, para incluye una expresión aritmética en la cláusula select, evitar ambigüedad en los casos en que un atributo apa- los atributos resultantes no tienen el mismo nombre. rece en el esquema de más de una relación. También se Y en tercer lugar, incluso si un nombre de atributo se podría haber escrito prestatario.nombre-cliente en lugar puede derivar de las relaciones base, como en el ejem- de nombre-cliente, en la cláusula select. Sin embargo, plo anterior, se puede querer cambiar el nombre del atri- como el atributo nombre-cliente aparece sólo en una de buto en el resultado. Para todo ello, SQL proporciona las relaciones de la cláusula from, no existe ambigüe- una forma de renombrar los atributos de una relación dad al escribir nombre-cliente. resultado. Se puede extender la consulta anterior y considerar Por ejemplo, si se quisiera renombrar el atributo un caso más complicado en el que se pide además qué número-préstamo, asociándole el nombre de id-présta- clientes poseen un préstamo en la sucursal Navacerra- mo, se podría reescribir la consulta anterior del siguien- da: «Obtener los nombres, números de préstamo e te modo importes de todos los clientes que tienen un préstamo en la sucursal Navacerrada». Para escribir esta consul- select nombre-cliente, prestatario.número-préstamo ta será necesario establecer dos restricciones en la cláu- as id-préstamo, importe sula where, relacionadas con la conectiva lógica and: from prestatario, préstamo where prestatario.número-préstamo = select nombre-cliente, prestatario.número-préstamo, préstamo.número-préstamo importe from prestatario, préstamo 4.2.5. Variables tupla where prestatario.número-préstamo = préstamo.número-préstamo and La cláusula as es particularmente útil en la definición nombre-sucursal= ‘Navacerrada’ del concepto de variables tupla, como se hace en el cálculo relacional de tuplas. Una variable tupla en SQL SQL-92 incluye extensiones para llevar a cabo reu- se debe asociar con una relación concreta. Las variables niones naturales y reuniones externas en la cláusula tupla se definen en la cláusula from mediante el uso de from. Esto se estudiará en el Apartado 4.10. la cláusula as. Como ejemplo, a continuación se rees- cribe la consulta «Obtener los nombres y números de 4.2.4. La operación renombramiento préstamo de todos los clientes que tienen un préstamo en el banco» como sigue SQL proporciona un mecanismo para renombrar tanto relaciones como atributos. Para ello utiliza la cláusula select nombre-cliente, T.número-préstamo, S.importe as, que tiene la forma siguiente: from prestatario as T, préstamo as S where T.número-préstamo = S.número-préstamo nombre-antiguo as nombre-nuevo Nótese que se define la variable tupla en la cláusula la cláusula as puede aparecer tanto en select como en from, colocándola después del nombre de la relación a from. la cual está asociada y detrás de la palabra clave as (la Considérese de nuevo la consulta anterior: palabra clave as es opcional). Al escribir expresiones 90  CAPÍTULO 4 SQL de la forma nombre-relación.nombre-atributo, el nom- ‘_ _ _’ encaja con cualquier cadena de tres carac- bre de la relación es, en efecto, una variable tupla defi- teres. nida implícitamente. ‘_ _ _%’ encaja con cualquier cadena de al menos Las variables tupla son de gran utilidad para com- tres caracteres. parar dos tuplas de la misma relación. Hay que recor- dar que, en los casos de este tipo, se puede usar la ope- Los patrones se expresan en SQL utilizando el opera- ración renombramiento del álgebra relacional. Si se dor de comparación like. Considérese la consulta desea formular la consulta «Obtener los nombres de siguiente: «Obtener los nombres de todos los clientes todas las sucursales que poseen un activo mayor que al cuyas calles contengan la subcadena ‘Mayor’». Esta menos una sucursal situada en Barcelona», se puede consulta se podría escribir como sigue escribir la siguiente expresión SQL select nombre-cliente select distinct T.nombre-sucursal from cliente from sucursal as T, sucursal as S where calle-cliente like ‘%Mayor%’ where T.activo > S.activo and S.ciudad-sucursal = ‘Barcelona’ Para que los patrones puedan contener los caracteres especiales patrón (esto es, % y _ ), SQL permite la espe- Obsérvese que no se puede utilizar la notación sucur- cificación de un carácter de escape. El carácter de esca- sal.activo, puesto que no estaría claro a qué aparición pe se utiliza inmediatamente antes de un carácter espe- de sucursal se refiere. cial patrón para indicar que ese carácter especial va a SQL permite usar la notación (v1, v2, …, vn) para desig- ser tratado como un carácter normal. El carácter de esca- nar una tupla de aridad n que contiene los valores v1, v2, pe para una comparación like se define utilizando la …, vn. Los operadores de comparación se pueden utili- palabra clave escape. Para ilustrar esto, considérense zar sobre tuplas, y el orden se define lexicográficamen- los siguientes patrones, los cuales utilizan una barra te. Por ejemplo (a1, a2) ≤ (b1, b2) es cierto si (a1 < b1) o invertida (\) como carácter de escape: si se cumple que (a1 = b1) ∧ (a2 ≤ b2); análogamente, dos tuplas son iguales si lo son todos sus atributos. like ‘ab\%cd%’ escape ‘\’ encaja con todas las cadenas que empiecen por ab%cd. 4.2.6. Operaciones sobre cadenas like ‘ab\\cd%’ escape ‘\’ encaja con todas las cade- nas que empiecen por ab\cd. SQL especifica las cadenas encerrándolas entre comi- llas simple, como ‘Navacerrada’, como se vio anterior- SQL permite buscar discordancias en lugar de concor- mente. Un carácter comilla que sea parte de una cade- dancias utilizando el operador de comparación not like. na se puede especificar usando dos caracteres comilla; SQL también proporciona una variedad de funcio- por ejemplo, la cadena «El carácter ‘ se puede ver en nes que operan sobre cadenas de caracteres, tales como esta cadena» se puede especificar como ‘El carácter ‘’ la concatenación (usando «||»), la extracción de subca- se puede ver en esta cadena’. denas, el cálculo de la longitud de las cadenas, la con- La operación más usada sobre cadenas es el encaje versión a mayúsculas y minúsculas, etc. SQL:1999 tam- de patrones, para el que se usa el operador like. Para la bién ofrece una operación similar to que proporciona descripción de patrones se utilizan los dos caracteres un encaje de patrones más potente que la operación like; especiales siguientes: la sintaxis para especificar patrones es similar a la usa- da en Unix para expresiones regulares. Tanto por ciento (%): El carácter % encaja con cualquier subcadena. 4.2.7. Orden en la presentación de las tuplas Subrayado (_): El carácter _ encaja con cualquier carácter. SQL ofrece al usuario cierto control sobre el orden en el cual se presentan las tuplas de una relación. La cláu- Los patrones son muy sensibles, esto es, los carac- sula order by hace que las tuplas resultantes de una con- teres en mayúsculas no encajan con los caracteres en sulta se presenten en un cierto orden. Para listar en orden minúscula, o viceversa. Para ilustrar el encaje de patro- alfabético todos los clientes que tienen un préstamo en nes, considérense los siguientes ejemplos: la sucursal Navacerrada se escribirá: select distinct nombre_cliente ‘Nava%’ encaja con cualquier cadena que empie- from prestatario, préstamo ce con «Nava». where prestatario.número-préstamo ‘%cer%’ encaja con cualquier cadena que conten- = préstamo.número-préstamo and ga «cer» como subcadena, por ejemplo ‘Navace- nombre-sucursal = ‘Navacerrada’ rrada’, ‘Cáceres’ y ‘Becerril’. order by nombre-cliente 91 FUNDAMENTOS DE BASES DE DATOS De manera predeterminada la cláusula order by lista 1. Si existen c1 copias de la tupla t1 en r1, y t1 satis- los elementos en orden ascendente. Para especificar el face la selección σθ , entonces hay c1 copias de t1 tipo de ordenación se puede incluir la cláusula desc para en σθ (r1). orden descendente o asc para orden ascendente. Ade- 2. Para cada copia de la tupla t1 en r1, hay una copia más, se puede ordenar con respecto a más de un atri- de la tupla ΠA(t1) en ΠA(r1), donde ΠA(t1) denota buto. Si se desea listar la relación préstamo en orden la proyección de la tupla única t1. descendente para importe. Si varios préstamos tienen el mismo importe, se ordenan ascendentemente según el 3. Si existen c1 copias de la tupla t1 en r1 y c2 copias número de préstamo. Esta consulta en SQL se escribe de la tupla t2 en r2, entonces hay c1 * c2 copias de del modo siguiente: la tupla t1.t2 en r1 × r2. Por ejemplo, supóngase que las relaciones r1 con select * esquema (A, B) y r2 con esquema (C) son los multi- from préstamo conjuntos siguientes: order by importe desc, número-préstamo asc r1 = {(1,a), (2,a)} r2 = {(2), (3), (3)} Para ejecutar una consulta que contiene la cláusula order by, SQL tiene que llevar a cabo una ordenación. Entonces, ΠB(r1 ) sería {(a), (a)}, mientras que ΠB (r1 ) Como ordenar un gran número de tuplas puede ser cos- × r2 sería toso, es conveniente ordenar sólo cuando sea estricta- mente necesario. {(a,2), (a,2), (a,3), (a,3), (a,3), (a,3)} Se puede ahora definir cuántas copias de cada tupla 4.2.8. Duplicados aparecen en el resultado de una consulta SQL. Una con- La utilización de relaciones con duplicados se ha mos- sulta SQL de la forma trado útil en diversas situaciones. SQL no sólo define formalmente las tuplas que están en el resultado de una select A1, A2, …, An consulta, sino también el número de copias de cada una from r1, r2, …, rm de esas tuplas que aparece en el resultado. La semán- where P tica de duplicados de una consulta SQL se puede defi- es equivalente a la expresión del álgebra relacional nir utilizando versiones de los operadores relacionales para multiconjuntos. A continuación se definen las ver- ΠA , A , …, A (σP (r1 × r2 × … × rm)) 1 2 n siones multiconjunto de varios de los operadores del álgebra relacional. Dadas las relaciones multiconjunto usando las versiones multiconjunto de los operadores r 1 y r 2, relacionales σ, Π y ×. 4.3. OPERACIONES SOBRE CONJUNTOS Las operaciones de SQL-92 union, intersect y except y el conjunto de todos los clientes que tienen un prés- operan sobre relaciones y corresponden a las operacio- tamo en el banco, que puede obtenerse con: nes del álgebra relacional ∪, ∩ y –. Al igual que la unión, intersección y diferencia de conjuntos en el álge- select nombre-cliente bra relacional, las relaciones que participan en las ope- from prestatario raciones han de ser compatibles; esto es, deben tener el mismo conjunto de atributos. A partir de ahora, las letras i y p se utilizarán para hacer A continuación se demuestra cómo se pueden for- referencia a las relaciones obtenidas como resultado de mular en SQL varias de las consultas de ejemplo con- las dos consultas anteriores. sideradas en el Capítulo 3 utilizando consultas que inclu- yen las operaciones union, intersect y except de dos 4.3.1. La operacion unión conjuntos. Los dos conjuntos utilizados serán: el con- juntos de todos los clientes que tienen una cuenta en el Para encontrar todos los clientes que poseen un présta- banco, que puede obtenerse con: mo, una cuenta o las dos cosas en el banco, se escribirá: select nombre-cliente (select nombre-cliente from impositor from impositor) 92  CAPÍTULO 4 SQL union intersect all (select nombre-cliente (select nombre-cliente from prestatario) from prestatario) A diferencia de la cláusula select, la operación union El número de tuplas duplicadas en el resultado es igual (unión) elimina duplicados automáticamente. Así, en la al mínimo número de duplicados que aparecen en i y p. consulta anterior, si un cliente —por ejemplo, Santos— Así, si Santos tuviese tres cuentas y dos préstamos en tiene varias cuentas o préstamos (o ambas cosas) en el el banco, entonces en el resultado de la consulta apare- banco, entonces Santos aparecerá sólo una vez en el cerían dos tuplas con el nombre de Santos. resultado. Para conservar los duplicados, se utilizará union all en lugar de union: 4.3.3. La operación excepto (select nombre-cliente Para encontrar todos los clientes que tienen cuenta pero from impositor) no tienen ningún préstamo en el banco se escribirá: union all (select nombre-cliente (select distinct nombre-cliente from prestatario) from impositor) except El número de tuplas duplicadas en el resultado es igual (select distinct nombre-cliente al número total de duplicados que aparecen en i y p. Así, from prestatario) si Santos tuviese tres cuentas y dos préstamos en el ban- co, entonces en el resultado aparecerían cinco tuplas La operacion except (excepto) elimina duplicados auto- con el nombre de Santos. máticamente. Así, en la consulta anterior, una tupla con el nombre de Santos aparecerá en el resultado (exacta- 4.3.2. La operación intersección mente una vez), sólo si Santos tiene una cuenta en el banco, pero no tiene ningún préstamo en el mismo. Para encontrar todos los clientes que tienen tanto un Para conservar los duplicados, se utilizará except all préstamo como una cuenta en el banco, se escribirá: en lugar de except: (select distinct nombre-cliente (select nombre-cliente from impositor) from impositor) intersect except all (select distinct nombre-cliente (select nombre-cliente from prestatario) from prestatario) La operacion intersect (intersección) elimina duplica- dos automáticamente. Así, en la consulta anterior, si un El número de copias duplicadas de una tupla en el resul- cliente —por ejemplo, Santos— tiene varias cuentas o tado es igual al número de copias duplicadas de dicha tupla préstamos (o ambas cosas) en el banco, entonces San- en i menos el número de copias duplicadas de la misma tos aparecerá solo una vez en el resultado. tupla en p, siempre que la diferencia sea positiva. Así, si Para conservar los duplicados se utilizará intersect Santos tuviese tres cuentas y un préstamo en el banco, all en lugar de intersect: entonces en el resultado aparecerían dos tuplas con el nom- bre de Santos. Si, por el contrario, dicho cliente tuviese (select nombre-cliente dos cuentas y tres préstamos en el banco, no habrá nin- from impositor) guna tupla con el nombre de Santos en el resultado. 4.4. FUNCIONES DE AGREGACIÓN Las funciones de agregación son funciones que toman Máximo: max una colección (un conjunto o multiconjunto) de valores Total: sum como entrada y producen un único valor como salida. Cuenta: count SQL proporciona cinco funciones de agregación pri- mitivas: La entrada a sum y avg debe ser una colección de números, pero los otros operadores pueden operar sobre Media: avg colecciones de datos de tipo no numérico, tales como Mínimo: min las cadenas. 93 FUNDAMENTOS DE BASES DE DATOS Como ejemplo, considérese la consulta «Obtener la sula having de SQL. Los predicados de la cláusula having media de saldos de las cuentas de la sucursal Navacerra- se aplican después de la formación de grupos, de modo da». Esta consulta se puede formular del modo siguiente: que se pueden usar las funciones de agregación. Esta con- sulta se expresa en SQL del modo siguiente: select avg (saldo) from cuenta select nombre-sucursal, avg (saldo) where nombre-sucursal = ‘Navacerrada’ from cuenta group by nombre-sucursal El resultado de esta consulta será una relación con un úni- having avg (saldo) > 1200 co atributo, que contendrá una única fila con un valor numérico correspondiente al saldo medio de la sucursal A veces se desea tratar la relación entera como un Navacerrada. Opcionalmente se puede dar un nombre al único grupo. En casos de este tipo no se usa la cláusu- atributo resultado de la relación, usando la cláusula as. la group by. Considérese la consulta «Obtener el sal- Existen situaciones en las cuales sería deseable apli- do medio de todas las cuentas». Esta consulta se for- car las funciones de agregación no sólo a un único con- mulará del modo siguiente: junto de tuplas sino también a un grupo de conjuntos de tuplas; esto se especifica en SQL usando la cláusula select avg (saldo) group by. El atributo o atributos especificados en la from cuenta cláusula group by se usan para formar grupos. Las tuplas con el mismo valor en todos los atributos especificados Con mucha frecuencia se usa la función de agrega- en la cláusula group by se colocan en un grupo. ción count para contar el número de tuplas de una rela- Como ejemplo, considérese la consulta «Obtener el ción. La notación para esta función en SQL es count saldo medio de las cuentas de cada sucursal». (*). Así, para encontrar el número de tuplas de la rela- Dicha consulta se formulará del modo siguiente ción cliente, se escribirá select nombre-sucursal, avg (saldo) select count (*) from cuenta from cliente group by nombre-sucursal La conservación de duplicados es importante al calcu- SQL no permite el uso de distinct con count (*). Sí lar una media. Supóngase que los saldos de las cuentas se permite, sin embargo, el uso de distinct con max y en la (pequeña) sucursal de nombre «Galapagar» son min, incluso cuando el resultado no cambia. Se puede 1.000 €, 3.000 €, 2.000 € y 1.000 €. El saldo medio usar la palabra clave all en lugar de distinct para espe- es 7.000/4 =1.750 €. Si se eliminasen duplicados se cificar la retención de duplicados, pero como all se espe- obtendría un resultado erróneo (6.000/3 = 2.000 €). cifica de manera predeterminada, no es necesario incluir Hay casos en los que se deben eliminar los duplica- dicha cláusula. dos antes de calcular una función de agregación. Para Si en una misma consulta aparece una cláusula whe- eliminar duplicados se utiliza la palabra clave distinct re y una cláusula having, se aplica primero el predica- en la expresión de agregación. Como ejemplo considé- do de la cláusula where. Las tuplas que satisfagan el rese la consulta «Obtener el número de impositores de predicado de la cláusula where se colocan en grupos cada sucursal». En este caso un impositor sólo se debe según la cláusula group by. La cláusula having, si exis- contar una vez, sin tener en cuenta el número de cuen- te, se aplica entonces a cada grupo; los grupos que no tas que el impositor pueda tener. La consulta se formu- satisfagan el predicado de la cláusula having se elimi- lará del modo siguiente: nan. La cláusula select utiliza los grupos restantes para generar las tuplas resultado de la consulta. select nombre-sucursal, count (distinct nombre- Para ilustrar el uso de la cláusula where y la cláu- cliente) sula having dentro de la misma consulta considérese el from impositor, cuenta ejemplo «Obtener el saldo medio de cada cliente que where impositor.número-cuenta = cuenta.número- vive en Madrid y tiene como mínimo tres cuentas». cuenta group by nombre-sucursal select impositor.nombre-cliente, avg (saldo) from impositor, cuenta, cliente A veces es más útil establecer una condición que se where impositor.número-cuenta aplique a los grupos que una que se aplique a las tuplas. = cuenta.número-cuenta and Por ejemplo, podemos estar interesados sólo en aquellas impositor.nombre-cliente sucursales donde el saldo medio de cuentas es superior a = cliente.nombre-cliente and 1.200 €. Esta condición no es aplicable a una única tupla; ciudad-cliente = ‘Madrid’ se aplica a cada grupo construido por la cláusula group group by impositor.nombre-cliente by. Para expresar este tipo de consultas se utiliza la cláu- having count (distinct impositor.número-cuenta) >= 3 94  CAPÍTULO 4 SQL 4.5. VALORES NULOS SQL permite el uso de valores nulos para indicar la para contener (proyecciones de) tuplas en R1 × … × Rn ausencia de información sobre el valor de un atributo. para las que el predicado P se evalúa a cierto. Si el pre- En un predicado se puede usar la palabra clave espe- dicado se evalúa a falso o desconocido para una tupla cial null para comprobar si un valor es nulo. Así, para de R1 × … × Rn (la proyección de) la tupla no se añade encontrar todos los números de préstamo que aparecen al resultado. en la relación préstamo con valores nulos para impor- SQL también permite determinar si el resultado de te se escribe una comparación es desconocido en lugar de cierto o falso usando las cláusulas is unknown (es desconoci- select número-préstamo do) e is not unknown (no es desconocido) from préstamo La existencia de valores nulos también complica el where importe is null procesamiento de los operadores de agregación. Supón- gase que algunas tuplas en la relación préstamo tienen El predicado is not null pregunta por la ausencia de un valor nulo para el atributo importe. Considérese en ese valor nulo. caso la siguiente consulta, que calcula el total de todas El uso de un valor nulo en las operaciones aritméti- las cantidades prestadas: cas y de comparación causa varias complicaciones. En el Apartado 3.3.4 se vio cómo se manejan los valores select sum (importe) nulos en el álgebra relacional. Ahora se describe cómo from préstamo maneja SQL los valores nulos. El resultado de una expresión aritmética (incluyen- Los valores que van a ser sumados en la consulta ante- do por ejemplo +,–,* o /) es nulo si cualquiera de los rior incluyen valores nulos, puesto que algunas tuplas valores de entrada es nulo. SQL trata como desconoci- tienen valor nulo para el atributo importe. En lugar de do el resultado de cualquier comparación que implique decir que la suma total es nula, la norma SQL estable- un valor nulo (aparte de is null e is not null). ce que el operador sum debería ignorar los valores nulos Dado que el predicado en una cláusula where pue- de su entrada. de incluir operaciones booleanas tales como and, or y En general, las funciones de agregación tratan los not sobre los resultados de las comparaciones, las defi- valores nulos según la regla siguiente: todas las funcio- niciones de estas operaciones se extienden para mane- nes de agregación excepto count(*) ignoran los valores jar el valor desconocido, como se describe en el Apar- nulos de la colección de datos de entrada. Como resul- tado 3.3.4. tado de ignorar los valores nulos, la colección de valo- res de entrada puede resultar vacía. El cálculo de count and: el resultado de cierto and desconocido es des- de una colección vacía se define como 0 y todas las demás conocido, falso and desconocido es falso, mientras operaciones de agregación devuelven un valor nulo cuan- que desconocido and desconocido es desconocido. do se aplican sobre una colección de datos vacía. El efec- or: el resultado de cierto or desconocido es cier- to de los valores nulos en algunas de las construcciones to, falso or desconocido es desconocido, mientras más complicadas de SQL puede ser más sutil. que desconocido or desconocido es desconocido. En SQL:1999 se introdujo un tipo de datos boolean, que puede tomar los valores cierto, falso y desconoci- SQL define el resultado de una instrucción SQL de la do. Las funciones de agregación some (algún) y every forma (cada), que significan exactamente lo que se espera de ellas, se pueden aplicar a una colección de valores boo- select … from R1, …., Rn where P leanos. 4.6. SUBCONSULTAS ANIDADAS SQL proporciona un mecanismo para las subconsultas 4.6.1. Pertenencia a conjuntos anidadas. Una subconsulta es una expresión select-from- where que se anida dentro de otra consulta. Un uso SQL utiliza el cálculo relacional para las operaciones común de subconsultas es llevar a cabo comprobacio- que permiten comprobar la pertenencia de una tupla a nes sobre pertenencia a conjuntos, comparación de con- una relación. La conectiva in comprueba la pertenen- juntos y cardinalidad de conjuntos. Estos usos se estu- cia a un conjunto, donde el conjunto es la colección de diarán en los apartados siguientes. valores resultado de una cláusula select. La conectiva 95 FUNDAMENTOS DE BASES DE DATOS not in comprueba la no pertenencia a un conjunto. que tienen un préstamo en el banco, pero no tienen una Como ejemplo considérese de nuevo la consulta cuenta en el banco, se puede escribir «Encontrar todos los clientes que tienen tanto un prés- tamo como una cuenta en el banco». Anteriormente select distinct nombre-cliente escribimos esta consulta como la intersección de dos from prestatario conjuntos: el conjunto de los impositores del banco y where nombre-cliente not in (select nombre-cliente el conjunto de los prestatarios del banco. Sin embargo, from impositor) existe un enfoque alternativo consistente en encontrar todos los tenedores de cuentas en el banco que son Los operadores in y not in también se pueden usar miembros del conjunto de prestatarios. Claramente, sobre conjuntos enumerados. La consulta siguiente esta formulación genera el mismo resultado que la ante- selecciona los nombres de los clientes que tienen un rior, pero obliga a formular la consulta usando la conec- préstamo en el banco y cuyos nombres no son ni «San- tiva in de SQL. A continuación, se van a obtener todos tos» ni «Gómez». los tenedores de cuentas formulando así la siguiente subconsulta: select distinct nombre-cliente from prestatario (select nombre-cliente where nombre-cliente not in (‘Santos’, ‘Gómez’) from impositor) 4.6.2. Comparación de conjuntos A continuación es necesario encontrar aquellos clientes que son prestatarios del banco y que aparecen en la lis- Considérese la consulta «Obtener los nombres de todas ta de tenedores de cuenta, obtenida como resultado de las sucursales que poseen un activo mayor que al menos la subconsulta anterior. Esto se consigue anidando la una sucursal situada en Barcelona». En el Apartado 4.2.5 subconsulta en un select más externo. La consulta resul- se formulaba esta consulta del modo siguiente: tante es la siguiente: select distinct T.nombre-sucursal select distinct nombre-cliente from sucursal as T, sucursal as S from prestatario where T.activo > S.activo and where nombre-cliente in (select nombre-cliente S.ciudad-sucursal = ‘Barcelona’ from impositor) SQL ofrece, sin embargo, un estilo alternativo de for- Este ejemplo muestra que es posible escribir la mis- mular la consulta anterior. La expresión: «mayor que al ma consulta de diversas formas en SQL. Esta flexibili- menos una» se representa en SQL por > some. Esta dad es de gran utilidad, puesto que permite al usuario constructora permite reescribir la consulta en una for- pensar en una consulta del modo que le parezca más ma más parecida a la formulación de la consulta en len- natural. Más adelante se verá que existe una gran can- guaje natural. tidad de redundancia en SQL. En el ejemplo anterior se comprobaba la pertenen- select nombre-sucursal cia a un conjunto en una relación de un solo atributo. from sucursal También es posible comprobar la pertenencia a un con- where activo > some (select activo junto en una relación cualquiera. Así, se puede formu- from sucursal lar la consulta «Listar los clientes que tienen tanto una where ciudad-sucursal cuenta como un préstamo en la sucursal Navacerrada» = ‘Barcelona’) de un modo distinto al visto anteriormente: La subconsulta select distinct nombre-cliente from prestatario, préstamo (select activo where prestatario.número-préstamo = from sucursal préstamo.número-préstamo and where ciudad-sucursal = ‘Barcelona’) nombre-sucursal = ‘Navacerrada’ and (nombre-sucursal, nombre-cliente) in genera el conjunto de todos los valores de activo para (select nombre-sucursal, nombre-cliente todas las sucursales sitas en Barcelona. La comparación from impositor, cuenta > some, en la cláusula where de la cláusula select más where impositor.número-cuenta externa, es cierta si el valor del atributo activo de la tupla = cuenta. número-cuenta) es mayor que al menos un miembro del conjunto de todos los valores de activo de las sucursales de Barcelona. A continuación, se ilustra el uso de la constructora SQL también permite realizar las comparaciones < not in. Por ejemplo, para encontrar todos los clientes some, = some, = some y some. Como ejer- 96  CAPÍTULO 4 SQL cicio, se puede verificar que = some es idéntico a in, mien- Utilizando la constructora not exists se puede com- tras que all corresponde a la expresión not exists considérese otra vez la consulta «Obtener «superior a todas». Utilizando esta constructora la con- todos los clientes que tienen una cuenta en todas las sulta se podría formular del modo siguiente: sucursales de Barcelona». Será necesario comprobar para cada cliente si el conjunto de todas las sucursales select nombre-sucursal en las que dicho cliente tiene cuenta contiene al con- from sucursal junto de todas las sucursales de Barcelona. Utilizando where activo > all (select activo el operador except se puede formular la consulta del from sucursal modo siguiente: where ciudad-sucursal = ‘Barcelona’) select distinct S.nombre-cliente from impositor as S Al igual que con some, SQL también permite utilizar where not exists ((select nombre-sucursal las comparaciones < all, = all, = all y all. from sucursal Como ejercicio se puede verificar que = all (select avg (saldo) la subconsulta from cuenta group by nombre-sucursal) (select R.nombre-sucursal from impositor as T, cuenta as R 4.6.3. Comprobación de relaciones vacías where T.número-cuenta = R.número-cuenta and S.nombre-cliente = T.nombre-cliente ) SQL incluye la posibilidad de comprobar si una sub- consulta no produce ninguna tupla como resultado. La obtiene todas las sucursales en las cuales el cliente constructora exists devuelve el valor cierto si la sub- S.nombre-cliente tiene una cuenta. Por último, el select consulta argumento no es vacía. más externo toma cada cliente y comprueba si el con- Usando la constructora exists se puede formular la junto de todas las sucursales en las que dicho cliente consulta «Obtener los clientes que tienen tanto una cuen- tiene cuenta, contiene al conjunto de todas las sucursa- ta como un préstamo en el banco» de otra nueva forma: les de Barcelona. En consultas que contengan subconsultas se aplica select nombre-cliente una regla de visibilidad para las variables tupla. En una where exists (select * subconsulta, sólo se pueden usar variables tupla que from impositor estén definidas en la propia subconsulta o en cualquier where impositor.nombre-cliente = consulta que contenga a dicha subconsulta. Si una varia- prestatario.nombre-cliente) ble tupla está definida tanto localmente (en una sub- 97 FUNDAMENTOS DE BASES DE DATOS consulta) como globalmente (en una consulta que con- La existencia de tuplas duplicadas en una subconsulta tenga a la subconsulta) se aplica la definición local. Esta se puede comprobar utilizando la constructora not uni- regla es análoga a la utilizada para las variables en los que. Para ilustrar esta constructora considérese la con- lenguajes de programación. sulta «Obtener todos los clientes que tienen al menos dos cuentas en la sucursal Navacerrada», que se puede 4.6.4. Comprobación de tuplas duplicadas formular del modo siguiente: SQL incluye la posibilidad de comprobar si una sub- select distinct T.nombre-cliente consulta produce como resultado tuplas duplicadas. La from impositor as T constructora unique devuelve el valor cierto si la sub- where not unique (select R.nombre-cliente consulta que se le pasa como argumento no produce from cuenta, impositor as R tuplas duplicadas. Usando la constructora unique se where T.nombre-cliente puede formular la consulta «Obtener todos los clientes = R.nombre-cliente and que tienen sólo una cuenta en la sucursal de nombre R.número-cuenta = Navacerrada» del siguiente modo: cuenta.número-cuenta and cuenta.número-sucursal select T.nombre-cliente = ‘Navacerrada’) from impositor as T where unique (select R.nombre-cliente Formalmente, la comprobación hecha por la cons- from cuenta, impositor as R tructora unique sobre una relación debería fallar si y where T.nombre-cliente sólo si en la relación existieran dos tuplas t1 y t2 tales = R.nombre-cliente and que t1 = t2. Como la comprobación t1 = t2 sólo falla si R.número-cuenta cualquier campo de t1 o de t2 es nulo, entonces es posi- = cuenta.número-cuenta and ble que el resultado de unique sea cierto incluso si exis- cuenta.nombre-sucursal ten varias copias de una tupla, siempre que al menos = ‘Navacerrada’) uno de los atributos de la tupla sea nulo. 4.7. VISTAS Una vista en SQL se define utilizando la orden create where prestatario.número-préstamo view. Para definir una vista se le debe dar un nombre y = préstamo.número-préstamo) se debe construir la consulta que genere dicha vista. La forma de la orden create view es la siguiente: Los nombres de los atributos de una vista se pueden indicar explícitamente de la forma siguiente: create view v as create view total-préstamos-sucursal donde puede ser cualquier con- (nombre-sucursal, total-préstamos) as sulta válida. El nombre de la vista se representa por v. select nombre-sucursal, sum (importe) Nótese que la notación usada para la definición de una from préstamo vista en el álgebra relacional (véase Capítulo 3) se basa group by nombre-sucursal en esta de SQL. Como ejemplo considérese la vista consistente en los La vista anterior contiene para cada sucursal la suma de nombres de sucursales y los nombres de los clientes que los importes de todos los préstamos de esa sucursal. tienen una cuenta o un préstamo en esa sucursal. Si se Como la expresión sum (importe) no tiene nombre, el denomina esta vista como todos-los-clientes se defini- nombre del atributo se especifica explícitamente en la rá del modo siguiente: definición de la vista. Los nombres de vistas pueden aparecer en cual- create view todos-los-clientes as quier lugar en el que pudiera aparecer un nombre de (select nombre-sucursal, nombre-cliente relación. Usando la vista todos-los-clientes, se pue- from impositor, cuenta den listar todos los clientes de la sucursal Navacerra- where impositor.número-cuenta da, escribiendo = cuenta.número-cuenta) union select nombre-cliente (select nombre-sucursal, nombre-cliente from todos-los-clientes from prestatario, préstamo where nombre-sucursal = ‘Navacerrada’ 98  CAPÍTULO 4 SQL 4.8. CONSULTAS COMPLEJAS Las consultas complejas son a menudo difíciles o select max(saldo-total) imposibles de escribir como un único bloque SQL o from (select nombre-sucursal, sum(saldo) una unión, intersección o diferencia de bloques SQL from cuenta (un bloque SQL consiste en una única instrucción group by nombre-sucursal) as select from where, posiblemente con cláusulas group total-sucursal(nombre-sucursal, by y having). Aquí se estudian dos formas de com- saldo-total) poner varios bloques SQL para expresar una consul- ta compleja: las relaciones derivadas y la cláusula 4.8.2. La cláusula with with. Las consultas complicadas son mucho más fáciles de 4.8.1. Relaciones derivadas formular y de entender si se descomponen en vistas más simples y después se combinan, al igual que se SQL permite el uso de una expresión de subconsulta en estructuran los programas, descomponiendo sus tareas la cláusula from. Si se usa una expresión de este tipo en procedimientos. Sin embargo, son distintas a la defi- se debe dar un nombre a la relación resultado y se pue- nición de procedimientos en cuanto a que una cláusu- den renombrar los atributos usando la cláusula as. Por la create view crea una definición de vista en la base ejemplo, considérese la subconsulta de datos y esa definición de vista permanece en la base de datos hasta que se ejecuta una orden drop view nom- (select nombre-sucursal, avg (saldo) bre-vista. from cuenta La cláusula with proporciona una forma de definir group by nombre-sucursal) una vista temporal cuya definición está disponible sólo as media-sucursal (nombre-sucursal, saldo-medio) para la consulta en la que aparece esta cláusula. Consi- dérese la siguiente consulta, que selecciona cuentas con Esta subconsulta produce una relación consistente en el saldo máximo; si hay muchas cuentas con el mismo los nombres de todas las sucursales y sus correspon- saldo máximo, todas ellas se seleccionan. dientes saldos de cuenta medios. El resultado de la sub- consulta recibe el nombre de media-sucursal y contie- with saldo-máximo(valor) as ne los atributos nombre-sucursal y saldo-medio. select max (saldo) Para ilustrar el uso de una expresión de subconsul- from cuenta ta en la cláusula from considérese la consulta «Obte- select número-cuenta ner el saldo medio de las cuentas de aquellas sucursa- from cuenta, saldo-máximo les donde dicho saldo medio sea superior a 1.200 €». where cuenta.saldo = saldo-máximo.valor En el Apartado 4.4 se formulaba esta consulta utili- zando la cláusula having. Ahora se puede reescribir La cláusula with introducida en SQL:1999 se incluye dicha consulta sin usar esta cláusula de la siguiente actualmente sólo en algunas bases de datos. forma: Se podría haber escrito la consulta anterior usando una subconsulta anidada tanto en la cláusula from como select nombre-sucursal, saldo-medio en la where. Sin embargo, el uso de subconsultas ani- from (select nombre-sucursal, avg (saldo) dadas hace que la consulta sea más difícil de leer y from cuenta entender. La cláusula with hace que la lógica de la con- group by nombre-sucursal) sulta sea más clara; también permite usar una definición as resultado (nombre-sucursal, saldo-medio) de vista en varios lugares de una consulta. where saldo-medio > 1200 Por ejemplo, supóngase que se desea encontrar todas las sucursales donde el depósito de cuentas es mayor En esta formulación no es necesario el uso de la cláu- que la media del total de depósitos de cuentas en todas sula having puesto que la relación temporal resulta- las sucursales. Se puede escribir la consulta con la cláu- do se calcula en la cláusula from, y los atributos de sula with como se muestra a continuación. resultado se pueden usar directamente en la cláusula where. with total-sucursal(nombre-sucursal,valor) as Supóngase como otro ejemplo que se desea hallar select nombre-sucursal, sum(saldo) el máximo del total de saldos de todas las sucursales. from cuenta La cláusula having no sirve en este caso, pero se pue- group by nombre-sucursal de escribir fácilmente esta consulta usando una sub- with total-media-sucursal(valor) as consulta en la cláusula from, como se muestra a conti- select avg(sa valor ldo) nuación: from total-sucursal 99 FUNDAMENTOS DE BASES DE DATOS select nombre-sucursal Por supuesto, se puede crear una consulta equivalente from total-sucursal, total-media-sucursal sin la cláusula with, pero sería más complicada y difí- where total-sucursal.valor > = total-media- cil de entender. Como ejercicio, se puede escribir la con- sucursal.valor sulta equivalente. 4.9. MODIFICACIÓN DE LA BASE DE DATOS Hasta ahora nos hemos limitado a la extracción de infor- delete from cuenta mación de una base de datos. A continuación se mos- where nombre-sucursal in (select nombre-sucursal trará cómo añadir, eliminar o cambiar información uti- from sucursal lizando SQL. where ciudad-sucursal = ‘Navacerrada’) 4.9.1. Borrado El borrado anterior selecciona primero todas las sucur- Un borrado se expresa de igual modo que una consul- sales con sede en Navacerrada y a continuación borra ta. Se pueden borrar sólo tuplas completas, es decir, no todas las tuplas cuenta pertenecientes a esas sucursa- se pueden borrar valores de atributos concretos. Un les. borrado se expresa en SQL del modo siguiente: Nótese que, si bien sólo se pueden borrar tuplas de delete from r una sola relación cada vez, se puede utilizar cualquier where P número de relaciones en una expresión select-from- where anidada en la cláusula where de un delete. La donde P representa un predicado y r representa una rela- orden delete puede contener un select anidado que use ción. La declaración delete selecciona primero todas una relación de la cual se van a borrar tuplas. Por ejem- las tuplas t en r para las que P (t) es cierto y a conti- plo, para borrar todas las cuentas cuyos saldos sean infe- nuación las borra de r. La cláusula where se puede omi- riores a la media del banco se puede escribir: tir, en cuyo caso se borran todas las tuplas de r. Hay que señalar que una orden delete opera sólo delete from cuenta sobre una relación. Si se desea borrar tuplas de varias where saldo < (select avg (saldo) relaciones, se deberá utilizar una orden delete por cada from cuenta) relación. El predicado de la cláusula where puede ser tan complicado como el where de cualquier cláusula La orden delete comprueba primero cada tupla de la select, o tan simple como una cláusula where vacía. La relación cuenta para comprobar si la cuenta tiene un sal- consulta do inferior a la media del banco. A continuación se borran todas las tuplas que no cumplan la condición delete from préstamo anterior, es decir, las que representan una cuenta con un saldo menor que la media. Es importante realizar todas borra todas las tuplas de la relación préstamo (los sis- las comprobaciones antes de llevar a cabo ningún borra- temas bien diseñados requerirán una confirmación del do (si se borrasen algunas tuplas antes de que otras fue- usuario antes de ejecutar una consulta tan devastadora). ran comprobadas, el saldo medio podría haber cambia- A continuación se muestran una serie de ejemplos do y el resultado final del borrado dependería del orden de borrados en SQL. en que las tuplas fueran procesadas). Borrar todas las cuentas de la sucursal Navacerrada. 4.9.2. Inserción delete from cuenta where nombre-sucursal = ‘Navacerrada’ Para insertar datos en una relación, o bien se especifica la tupla que se desea insertar o se formula una consul- Borrar todos los préstamos en los que la cantidad esté ta cuyo resultado sea el conjunto de tuplas que se de- comprendida entre 1.300 € y 1.500 €. sean insertar. Obviamente, los valores de los atributos de la tuplas que se inserten deben pertenecer al domi- delete from préstamo nio de los atributos. De igual modo, las tuplas inserta- where importe between 1300 and 1500 das deberán ser de la aridad correcta. La instrucción insert más sencilla corresponde a la Borrar las cuentas de todas las sucursales de Nava- de inserción de una tupla. Supongamos que se desea cerrada. insertar en la base de datos el hecho de que hay una 100  CAPÍTULO 4 SQL cuenta C-9732 en la sucursal Navacerrada y que dicha Es importante que la evaluación de la instrucción cuenta tiene un saldo de 1.200 €. La inserción se pue- select finalice completamente antes de llevar a cabo nin- de formular del modo siguiente: guna inserción. Si se realizase alguna inserción antes de que finalizase la evaluación de la instrucción select, insert into cuenta una consulta del tipo: values (‘C-9732’, ‘Navacerrada’, 1200) insert into cuenta En este ejemplo los valores se especifican en el mismo select * orden en que los atributos se listan en el esquema de from cuenta relación. Para beneficio de los usuarios, que pueden no recordar el orden de los atributos, SQL permite que los podría insertar un número infinito de tuplas. La prime- atributos se especifiquen en la cláusula insert. Así, el ra tupla de la relación cuenta se insertaría de nuevo en siguiente ejemplo tiene una función idéntica al anterior: cuenta, creando así una segunda copia de la tupla. Como esta segunda copia ya sería parte de cuenta, la instruc- insert into cuenta (nombre-sucursal, número- ción select podría seleccionarla, insertando así una ter- cuenta, saldo) cera copia en la relación cuenta. Esta tercera copia values (‘Navacerrada’, ‘C-9732’, 1200) podría ser seleccionada a continuación por el select e insertar una cuarta copia y así infinitamente. Evaluan- insert into cuenta (número-cuenta, nombre- do completamente toda la instrucción select antes de sucursal, saldo) realizar ninguna inserción se evitan este tipo de pro- values (‘C-9732’, ‘Navacerrada’, 1200) blemas. Por ahora, en el estudio de la instrucción insert sólo Generalmente se desea insertar las tuplas que resul- se han considerado ejemplos en los que se especificaba tan de una consulta. Por ejemplo, si a todos los clientes un valor para cada atributo de las tuplas insertadas. tenedores de préstamos en la sucursal Navacerrada se Como se estudió en el Capítulo 3, es posible indicar sólo les quisiera regalar, como gratificación, una cuenta de valores para algunos de los atributos del esquema. ahorro con 200 € por cada cuenta de préstamo que tie- A cada uno de los atributos restantes, se les asignará un nen, se podría escribir: valor nulo, que se denota por null. Como ejemplo con- sidérese la consulta: insert into cuenta select nombre-sucursal, número-préstamo, 200 insert into cuenta from préstamo values (‘C-401’, null, 1200) where nombre-sucursal = ‘Navacerrada’ en la que se sabe que la cuenta C-401 tiene un saldo de En lugar de especificar una tupla, como se hizo en los 1.200 €, pero no se conoce el nombre de la sucursal. primeros ejemplos de este apartado, se utiliza una ins- Considérese ahora la consulta trucción select para especificar un conjunto de tuplas. La instrucción select se evalúa primero, produciendo select número-cuenta un conjunto de tuplas que a continuación se insertan en from cuenta la relación cuenta. Cada tupla tiene un nombre-sucur- where nombre-sucursal = ‘Navacerrada’ sal (Navacerrada), un número-préstamo (que sirve como número de cuenta para la nueva cuenta) y un saldo ini- Como el nombre de la sucursal de la cuenta C-401 es cial de la cuenta (200 €). desconocido, no se puede determinar si es igual a «Nava- Además es necesario añadir tuplas a la relación impo- cerrada». sitor; para hacer esto, se escribirá: Se puede prohibir la inserción de valores nulos utili- zando el LDD de SQL, que se estudia en el Apartado 4.11. insert into impositor select nombre-cliente, número-préstamo 4.9.3. Actualizaciones from prestatario, préstamo where prestatario.número-préstamo En determinadas situaciones puede ser deseable cam- = préstamo.número-préstamo and biar un valor dentro de una tupla, sin cambiar todos los nombre-sucursal = ‘Navacerrada’ valores de la misma. Para este tipo de situaciones se uti- liza la instrucción update. Al igual que ocurre con insert Esta consulta inserta en la relación impositor una y delete, se pueden elegir las tuplas que van a ser actua- tupla (nombre-cliente, número-préstamo) por cada lizadas mediante una consulta. nombre-cliente que posea un préstamo en la sucursal Por ejemplo, si hubiera que realizar el pago de inte- Navacerrada, con número de préstamo número-prés- reses anuales y todos los saldos se incrementasen en un tamo. 5 %, habría que formular la siguiente actualización: 101 FUNDAMENTOS DE BASES DE DATOS update cuenta La forma general de la instrucción case es la siguien- set saldo = saldo * 1.05 te: Esta actualización se aplica una vez a cada tupla de la case relación cuenta. when pred1 then result1 Si se paga el interés sólo a las cuentas con un saldo when pred2 then result2 de 1.000 € o superior, se puede escribir … when predn then resultn update cuenta else result0 set saldo = saldo * 1.05 end where saldo >= 1000 La operación devuelve resulti, donde i es el primero de En general, la cláusula where de la instrucción upda- result1, result2, …,resultn que se satisface; si ninguno de te puede contener cualquier constructor legar en la cláu- ellos se satisface, la operación devuelve result0. Las ins- sula where de una instrucción select (incluyendo ins- trucciones case se pueden usar en cualquier lugar don- trucciones select anidadas). Como con insert y delete, de se espere un valor. un select anidado en una instrucción update puede refe- renciar la relación que se esté actualizando. Como antes, 4.9.4. Actualización de vistas SQL primero comprueba todas las tuplas de la relación para determinar las que se deberían actualizar y después La anomalía de la actualización de vistas estudiada en realiza la actualización. Por ejemplo, se puede escribir el Capítulo 3 también se produce en SQL. Como ejem- «Pagar un interés del 5% a las cuentas cuyo saldo sea plo considérese la siguiente definición de vista: mayor que la media» como sigue: create view préstamo-sucursal as update cuenta select nombre-sucursal, número-préstamo set saldo = saldo * 1.05 from préstamo where (saldo > select avg(saldo) from cuenta) Como SQL permite que el nombre de una vista aparez- ca en cualquier lugar en el que pueda aparecer el nom- Si se supone que las cuentas con saldos superiores a bre de una relación, se puede formular: 10.000 € reciben un 6% de interés, mientras que las insert into préstamo-sucursal demás un 5%, se deberán escribir dos instrucciones de values (‘Navacerrada’, ‘P-307’) actualización: SQL representa esta inserción mediante una inserción update cuenta en la relación préstamo, puesto que préstamo es la rela- set saldo = saldo * 1.06 ción real a partir de la cual se construye la vista prés- where saldo > 10000 tamo-sucursal. Por lo tanto, debería especificarse un valor para el atributo importe. Este valor es un valor update cuenta nulo. De este modo, la inserción anterior es equivalen- set saldo = saldo * 1.05 te a la inserción de la tupla where saldo = 0)) En este ejemplo se utiliza la cláusula check para simu- lar un tipo enumerado especificando que nivel-estudios create table impositor (nombre-cliente char (20), debe ser «Graduado», «Licenciado» o «Doctorado». En número-cuenta char (10), el Capítulo 6 se considerarán condiciones check más primary key (nombre-cliente, número-cuenta)) generales, así como clases de restricciones denomina- FIGURA 4.8. Definición de datos en SQL para parte de la base das restricciones de integridad. de datos del banco. Una relación inicialmente está vacía. Se pueden uti- lizar instrucciones de inserción para introducir datos en la misma. Muchas bases de datos relacionales tienen datos bancaria. En el mundo real, muchas personas tie- utilidades de carga para la introducción de un conjunto nen el mismo nombre por lo que nombre-cliente no sería inicial de tuplas en una relación. una clave primaria de cliente; probablemente se usaría Para borrar una relación de una base de datos SQL, un id-cliente como clave primaria. Se usa nombre-clien- se utiliza la orden drop table. Dicha orden borra de la te como clave primaria para mantener el esquema de la base de datos toda la información sobre la relación eli- base de datos simple y pequeño. minada. La instrucción Si como resultado de una inserción o modificación, una tupla toma valores nulos para cualquiera de los atri- drop table r butos que forman parte de la clave primaria, o si tiene el mismo valor que otra tupla de la relación para éstos, tiene una repercusión más drástica que SQL notifica el error y la actualización no se lleva a delete from r cabo. De forma análoga ocurre lo mismo si falla la con- dición check de una tupla. La última conserva la relación r, pero borra todas sus De manera predeterminada, null es un valor válido tuplas. La primera, no sólo borra todas las tuplas de la para cualquier atributo en SQL, a menos que se especi- relación r, sino también borra su esquema. Después de fique con not null. Un atributo se puede declarar para que r se elimine no se puede insertar ninguna tupla en que no sea nulo de la forma siguiente. dicha relación, a menos que su esquema se vuelva a número-cuenta char(10) not null crear utilizando la instrucción create table. En SQL-92, la instrucción alter table se utiliza para SQL también soporta una restricción de integridad añadir atributos a una relación existente. La sintaxis de la instrucción es la siguiente: unique (Aj1, Aj2,…,Ajm) alter table r add A D La especificación unique indica que los atributos Aj1, Aj2,…,Ajm forman una clave candidata; es decir, no pue- donde r es el nombre de una relación existente, A es el de haber dos tuplas en la relación con todos los atribu- nombre del atributo que se desea añadir y D es el domi- tos que forman la clave candidata iguales. Sin embar- nio del atributo A. Se pueden eliminar atributos de una go, se permite que los atributos que forman la clave relación utilizando la orden candidata sean nulos, a menos que se hayan declarado como not null. Recuérdese que un valor nulo no es igual alter table r drop A a ningún otro valor. El tratamiento de los valores nulos aquí es el mismo que para la constructora unique defi- donde r es el nombre de una relación existente y A es el nida en el Apartado 4.6.4. nombre de un atributo de la relación. Muchos sistemas Un uso habitual de la cláusula check es el de asegu- de bases de datos no permiten el borrado de atributos, rar que los valores de los atributos satisfacen unas con- aunque sí permiten el borrado de una tabla completa. 108  CAPÍTULO 4 SQL 4.12. SQL INCORPORADO SQL proporciona un lenguaje de consultas declarati- raciones escritas en el lenguaje anfitrión y por llamadas vo muy potente. La formulación de consultas en SQL a procedimientos que permiten la ejecución del acceso es normalmente mucho más sencilla que la formula- a la base de datos. Tras esta operación, el programa resul- ción de las mismas en un lenguaje de programación de tado se compila con el compilador del lenguaje anfi- propósito general. Sin embargo, el acceso a una base trión. Para identificar las consultas de SQL incorpora- de datos desde un lenguaje de programación de pro- do, se utiliza la instrucción EXEC SQL, que tiene la pósito general se hace necesario al menos por dos razo- siguiente forma: nes: EXEC SQL 1. No todas las consultas pueden expresarse en SQL, END-EXEC ya que SQL no dispone del poder expresivo de un lenguaje de propósito general. Así, existen con- La sintaxis exacta de las consultas en SQL incorpo- sultas que se pueden expresar en lenguajes como rado depende del lenguaje dentro del que se utilicen. Pascal, C, Cobol o Fortran y que no se pueden Por ejemplo, cuando se utilizan instrucciones de SQL expresar en SQL. Para formular consultas de este dentro de un programa en C, se debe utilizar un punto tipo, podemos utilizar SQL dentro de un lengua- y coma en lugar de END-EXEC. La incorporación de je más potente. SQL en Java (denominada SQLJ) usa la sintaxis SQL está diseñado de tal forma que las con- sultas formuladas puedan optimizarse automá- # SQL { }; ticamente y ejecutarse de manera eficiente (al pro- porcionar toda la potencia de un lenguaje de En el programa se incluye la instrucción SQL programación, la optimización automática es extre- INCLUDE para identificar el lugar donde el preproce- madamente difícil. sador debe insertar las variables especiales que se usan para la comunicación entre el programa y el sistema de 2. Las acciones no declarativas (como la impre- base de datos. Las variables del lenguaje anfitrión se sión de un informe, la interacción con un usua- pueden utilizar en las instrucciones de SQL incorpora- rio o el envío de los resultados de una consulta do, pero se precederán por dos puntos (:) para distin- a una interfaz gráfica) no se pueden llevar a cabo guirlas de las variables de SQL. desde el propio SQL. Normalmente las aplica- Las instrucciones de SQL incorporado son similares ciones tienen varios componentes y la consulta en cuanto a la sintaxis a las instrucciones SQL que se o actualización de datos es uno de ellos; los han descrito en este capítulo. Sin embargo, hay varias demás componentes se escriben en lenguajes de diferencias que se indican a continuación. programación de alto nivel. En el caso de una Para formular una consulta relacional se usa la ins- aplicación integrada, los programas escritos en trucción declare cursor. El resultado de la consulta no el lenguaje de programación deben tener la capa- se calcula aún. En lugar de esto, el programa debe usar cidad de acceder a la base de datos. las órdenes open y fetch (que se analizarán más adelante en este apartado) para obtener las tuplas resultado. La norma SQL define la utilización de SQL dentro Considerando el esquema bancario que se ha utili- de varios lenguajes de programación, tales como C, zado como ejemplo en este capítulo, supóngase que se Cobol, Pascal, Java, PL/I y Fortran. Un lenguaje en tiene una variable del lenguaje anfitrión importe y que el cual se introducen consultas SQL se denomina se desea encontrar los nombres y ciudades de residen- lenguaje anfitrión y las estructuras SQL que se admi- cia de aquellos clientes que superan esa cantidad en ten en el lenguaje anfitrión constituyen SQL incorpo- alguna de sus cuentas. Se puede escribir esta consulta rado. del modo siguiente: Los programas escritos en el lenguaje anfitrión pue- den usar sintaxis SQL para acceder y actualizar datos EXEC SQL almacenados en la base de datos. Esta forma incorpora- declare c cursor for da de SQL amplía aún más la capacidad de los progra- select nombre-cliente, ciudad-cliente madores de manipular la base de datos. En SQL incor- from impositor, clien