SQL

Al gestionar bases de datos el modelo de datos más utilizado es el modelo relacional y para comunicarse con el gestor se puede utilizar el lenguaje SQL

NO es un lenguaje de programación, sino un lenguaje de gestión de bases de datos que presenta las siguientes características:

• LDD (Lenguaje de definición de datos)

  El LDD de SQL proporciona comandos para la definición de esquemas de relación, borrado de relaciones y modificación de los esquemas de relación

• LMD (Lenguaje interactivo de manipulación de datos)

  El LMD de SQL incluye un lenguaje de consultas basado tanto en el álgebra relacional como en el cálculo relacional de tuplas. También incluye comandos para insertar, borrar y modificar tuplas

• Integridad

  El LDD incluye comandos para especificar restricciones de integridad que deben cumplir los datos almacenados en la base de datos. Las actualizaciones que violan las restricciones de integridad se rechazan

• Definición de vistas

  El LDD de SQL incluye comandos para la definición de vistas

• Control de transacciones

  SQL incluye comandos para especificar el comienzo y el final de transacciones

• SQL incorporado y SQL dinámico

  SQL incorporado y SQL dinámico define cómo se pueden incorporar las instrucciones de SQL en lenguajes de programación de propósito general como C, C++, Java, PL/L, Cobol, Pascal o Fortran, entre otros

• Autorización
  El LDD de SQL incluye comandos para especificar los derechos de acceso a las relaciones y a las vistas

Evolución del SQL

IBM desarrolló la versión original de SQL, denominado Sequel, como parte del proyecto System R a principios de 1970 por el grupo de San José, California

El lenguaje Sequel ha evolucionado desde entonces y su nombre ha pasado a ser SQL (lenguaje estructurado de consultas, Structured Query Language)

Hoy en día, muchos lenguajes de programación son compatibles con el lenguaje SQL y se ha establecido como el lenguaje estándar para el uso de bases de datos relacionales

1981, IBM lanzó el primer programa comercial basado en SQL

1986, ANSI (Instituto nacional americano de normalización, American National Standards Institute) publicó una norma SQL, denominada SQL-86 o SQL1 y fue confirmada por ISO (Organización internacional de normalización, International Standards Organization) en 1987

1989, ANSI publicó una extensión de la norma para SQL denominada SQL-89

1992, apareció una nueva versión de la norma denominada SQL-92 o SQL2

1999, apareció una nueva versión denominada SQL:1999 o SQL2000. En la cual se agregaron expresiones regulares, consultas recursivas (para relaciones jerárquicas), triggers y algunas características orientadas a objetos

2003, apareció una nueva versión denominada SQL:2003. En la cual se agregaron algunas características de XML, cambios en las funciones, estandarización del objeto sequence y de las columnas autonuméricas

2005, apareció una nueva versión denominada SQL:2005 y se corresponde con la norma ISO/IEC 9075-14:2005. Definiendo cómo SQL puede utilizarse conjuntamente con XML:

• Importar y guardar datos XML en una base de datos SQL, manipulándolos dentro de la base de datos y publicando el XML y los datos SQL convencionales en forma XML
• Facilidades que permiten a las aplicaciones integrar dentro de su código SQL el uso de XQuery, lenguaje de consulta XML publicado por el W3C (World Wide Web Consortium) para acceso concurrente a datos ordinarios SQL y documentos XML

2008, apareció una nueva versión denominada SQL:2008. En la cual se agregó el uso de la cláusula ORDER BY fuera de las definiciones de los cursores. Incluyendo disparadores del tipo INSTEAD OF. Y la inclusión de la sentencia TRUNCATE

2011, apareció una nueva versión denominada SQL:2011. En la cual se agregaron los datos temporales (PERIOD FOR) y mejoras en las funciones de ventana y de la cláusula FETCH

2016, apareció una nueva versión denominada SQL:2016. En la cual se agregó la búsqueda mediante patrones, funciones de tabla polimórficas y compatibilidad con los ficheros JSON

Lenguaje de definición de datos (LDD)

El lenguaje de definición de datos (LDD), es el que se encarga de la modificación de la estructura de los objetos de la base de datos

Incluye órdenes para modificar, borrar o definir las relaciones (tablas) en las que se almacenan los datos de la base de datos

Existen cuatro operaciones básicas:

• CREATE
• ALTER
• DROP
• TRUNCATE

Definición de los datos

El conjunto de relaciones de cada base de datos debe especificarse en el sistema en términos de un lenguaje de definición de datos (LDD)

El LDD de SQL no sólo permite la especificación de un conjunto de relaciones, sino también de la información relativa a esas relaciones, incluyendo:

• El esquema de cada relación
• El dominio de valores asociado a cada atributo
• Las restricciones de integridad
• El conjunto de índices que se deben mantener para cada relación
• La información de seguridad y de autorización de cada relación
• La estructura de almacenamiento físico de cada relación en el disco

Tipos básicos de dominios

La norma SQL soporta gran variedad de tipos de dominio predefinidos:

• CHAR(n)

  Una cadena de caracteres de longitud fija, con una longitud n especificada por el usuario. También se puede utilizar la palabra completa character

• VARCHAR(n)

  Una cadena de caracteres de longitud variable con una longitud máxima n especificada por el usuario. La forma completa, character varying, es equivalente

• INT

  Un entero (un subconjunto finito de los enteros dependiente del ordenador). La palabra completa, integer, es equivalente

• SMALLINT

  Un entero pequeño (un subconjunto dependiente del ordenador del tipo de dominio entero)

• NUMERIC(p, d)

  Un número en coma fija, cuya precisión la especifica el usuario. El número está formado por p dígitos (más el signo) y de esos p dígitos, d pertenece a la parte decimal

• DOUBLE

  Un número en coma flotante y números en coma flotante de doble precisión, con precisión dependiente del ordenador

• FLOAT(n)

  Un número en coma flotante cuya precisión es, al menos, de n dígitos

• DATE

  Dato de tipo fecha, con una precisión de hasta segundo

Definición básica de esquemas

CREATE TABLE

Las relaciones se definen mediante el comando CREATE TABLE:

\text{CREATE TABLE r} (\\ A_1 D_1, \cdots, A_n D_n,\\ \{rest-integridad_1\}, \cdots, \{rest-integridad_k\} )

Donde r es el nombre de relación, cada A_i es el nombre de un atributo del esquema de la relación r y D_i es el tipo de dominio de los valores del dominio del atributo A_i

Hay varias restricciónes de integridad válidas

Una de las más usadas es la de clave primaria (primary key), que adopta la siguiente forma:

\text{primary key}(A_{j1}, \cdots, A_{jm})

La especificación de clave primaria determina los atributos A_{j1}, \cdots, A_{jm} forman la clave primaria de la relación

Los atributos de la clave primaria tienen que ser no nulos y únicos; es decir, ninguna tupla puede tener un valor nulo para un atributo de la clave primaria y ningún par de tuplas de la relación puede ser igual en todos los atributos de la clave primaria

Aunque la especificación de clave primaria es opcional, suele ser una buena idea especificar una clave primaria para cada relación

CREATE TABLE cuenta(
numero_cuenta char(10),
nombre_sucursal char(15),
saldo numeric(12, 2),
primary key(numero_cuenta) )

En el ejemplo se ha creado una relación llamada cuenta en la cual se han definido los atributos:

numero_cuenta, que puede guardar una palabra de hasta 10 carácteres
nombre_sucursal, que puede guardar una palabra de hasta 15 carácteres
saldo, que puede guardar un número decimal con 12 enteros y 2 decimales
Y con la restricción sobre numero_cuenta como clave primaria de la relación

Si una tupla recién insertada o recién modificada de una relación contiene valores nulos para cualquiera de los atributos que forman parte de la clave primaria, o si tienen el mismo valor que otra tupla de la relación, SQL notificará el error e impedirá la actualización

CREATE TABLE cliente(
numero_cuenta char(10),
nombre_cliente char(15),
primary key(numero_cuenta) )

En el ejemplo se ha creado otra relación llamada cliente en la cual se han definido los atributos:

numero_cuenta, que puede guardar una palabra de hasta 10 carácteres
nombre_cliente, que puede guardar una palabra de hasta 15 carácteres
Y con la restricción sobre numero_cuenta como clave primaria de la relación

Como puede verse podemos crear más de una relación

DROP TABLE

Para eliminar una relación de la base de datos se utiliza el comando DROP TABLE

Este comando elimina de la base de datos toda la información sobre la relación

DROP TABLE cuenta

En el ejemplo se ha elimina de la base de datos toda la información sobre la relación cuenta

Este comando es más drástico que DELETE, que sólo borra sus tuplas manteniendo el esquema de la relación

Sin embargo, DROP, si se elimina cuenta, no se podrá volver a insertar ninguna tupla en dicha relación, a menos que se vuelva a crear con el comando CREATE TABLE

ALTER TABLE

El comando ALTER TABLE se utiliza para añadir atributos a una relación existente

Como valor del nuevo atributo se asigná a todas las tuplas de la relación el valor nulo

ALTER TABLE cuenta ADD
ano DATETIME

En el ejemplo se ha añadido un nuevo atributo

Donde cuenta es el nombre de la relación existente, ano es el nombre del atributo que se desea añadir y DATETIME es el dominio del atributo añadido

También se pueden eliminar atributos usando ALTER TABLE

ALTER TABLE cuenta DROP
ano

En el ejemplo se ha eliminado el atributo que añadimos antes

Donde cuenta es el nombre de la relación existente y ano es el nombre del atributo que se desea eliminar

En este caso no es necesario declarar el dominio del atributo a borrar

Muchos sistemas de bases de datos no permiten el borrado de atributos, aunque sí que permiten el borrado de tablas completas

TRUNCATE

Este comando solo se aplica a tablas y su función es borrar el contenido completo de la relación especificada

La ventaja sobre el comando DELETE, es que si se quiere borrar todo el contenido de la relación, es mucho más rápido, especialmente si la relación es muy grande

La desventaja es que TRUNCATE sólo sirve cuando se quiere eliminar absolutamente todas las tuplas, ya que no se permite la cláusula WHERE

Si bien, en un principio, esta sentencia parecería ser LMD (Lenguaje de Manipulación de Datos), es en realidad una LDD, ya que internamente, el comando TRUNCATE borra la tabla y la vuelve a crear y no ejecuta ninguna transacción

TRUNCATE TABLE cuenta

En el ejemplo hemos borrado todo el contenido de la relación llamada cuenta

Lenguaje interactivo de manipulación de datos (LMD)

El lenguaje de manipulación de datos (LMD) es el que se encarga de llevar a cabo las tareas de consulta o manipulación de los datos, organizados por el modelo de datos adecuado

Incluye órdenes para consultar, modificar, borrar o definir las tuplas (filas) que son almacenadas en las relaciones (tablas) de la base de datos

Existen cuatro operaciones básicas:

• SELECT
• INSERT
• UPDATE
• DELETE

Definición básica de esquemas

Operación alias

SQL proporciona una operación para renombrar mediante un alias tanto las relaciones como los atributos

Tiene la siguiente sintaxis: nombre_original AS alias

La cláusula AS es opcional aunque si la escribimos resultará más claro porque nos informa que estamos definiendo un alias

Situaciones en las que es útil usar un alias:

• Donde tenemos dos relaciones en la cláusula FROM que tienen el mismo nombre para sus atributos

  En estas situaciones se puede producir ambigüedad y podremos usar la operación alias para desambigüar los atributos y que quede claro a qué relación pertenecen

• Cuando tenemos una operación calculada que genera un atributo en la cláusula SELECT

• Cuando tenemos una operación calculada que genera un atributo despues de aplicar una operación de agregación

Para acceder a sus atributos se debe usar el alias seguido de un punto y el nombre del atributo

SELECT

Se puede utilizar el comando SELECT para consultar los datos almacenados en una relación de la base de datos

Devolverá un conjunto de tuplas de esa relación

La forma más habitual de consulta será:

\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1, r_2, \cdots, r_m\\ \text{WHERE} P

Donde cada A_i representa a un atributo, cada r_i una relación y P es un predicado

Por lo que una consulta SQL tendrá la siguiente expresión de álgebra relacional equivalente:

\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p(r_1 \Theta r_2 \Theta \cdots \Theta r_m) )

Si se omite la clausula WHERE, el predicado P es siempre cierto

Pero a diferencia de la expresión del álgebra relacional, el resultado de una consulta SQL puede contener varias tuplas repetidas

Cuando introducimos más de una relación en la claúsula FROM, SQL formará un producto cartesiano de todas las relaciones incluidas

Después se lleva a cabo la selección del álgebra relacional utilizando el predicado de la cláusula WHERE y despues proyecta el resultado sobre los atributos de la cláusula SELECT

Cláusula SELECT

SELECT DISTINCT numero_cuenta, nombre_sucursal, saldo
FROM cuenta

En el ejemplo se ha resuelto la consulta obtener todas las cuentas de la relación cuenta con los atributos numero_cuenta, nombre_sucursal, saldo

En este caso, al usar DISTINCT estamos forzando a que se eliminen los repetidos

SELECT ALL numero_cuenta, nombre_sucursal, saldo
FROM cuenta

En el ejemplo se ha resuelto la consulta obtener todas las cuentas de la relación cuenta con los atributos numero_cuenta, nombre_sucursal, saldo

En este caso, al usar ALL estamos forzando a que NO se eliminen los repetidos

SELECT *
FROM cuenta

En el ejemplo se ha sustituido el comando ALL por el símbolo * y se ha prescindido de los atributos, ya que el resultado es equivalente

SELECT numero_cuenta, nombre_sucursal, saldo * 1500
FROM cuenta

En el ejemplo se ha resuelto la consulta obtener todas las cuentas de la relación cuenta con los atributos numero_cuenta, nombre_sucursal, con un incremento de 1500 en el saldo

Como puede apreciarse, también podemos utilizar expresiones aritméticas que operen sobre los atributos

SELECT numero_cuenta, nombre_sucursal, saldo * 1500 incremento
FROM cuenta

En el ejemplo se ha creado un atributo calculado (que no pertenece a la relación) a partir de esas operaciones, asignándole un nombre o alias a esa operación, en este caso se llama incremento

Cláusula FROM

La cláusula FROM define por sí misma un producto cartesiano de las relaciones que aparecen en la consulta

Dado que la reunión natural se define en términos de un producto cartesiano, una selección y una proyección, resulta relativamente sencillo escribir una expresión SQL para la reunión natural

Tenemos que en álgebra relacional podemos escribirla como:

\prod_{A_1, \cdots, A_2, \cdots, A_n} (r_1 \Theta r_2 \Theta \cdots \Theta r_m)

SELECT c.numero_cuenta, c.saldo
FROM cuenta c, cliente cl
WHERE c.numero_cuenta = cl.numero_cuenta AND c.nombre_sucursal = 'Navacerrada'

El ejemplo resuelve la consulta de obtener todos los clientes que tienen cuenta en la sucursal de ‘Navacerrada‘

Para ello se han introducido las relaciones cuenta (se le ha asignado el alias c) y cliente (se le ha asignado el alias cl)

Sólo con la cláusula FROM no podríamos resolver la consulta

Ya que el producto cartesiano se realiza sobre cada una de las posibles parejas que pueden formarse con la relación cuenta y cliente

Dándonos resultados repetidos y también todos aquellos que no fueran de la sucursal de ‘Navacerrada‘

Por eso se ha añadido la condición que relaciona ambas relaciones, el atributo numero_cuenta

Después de añadirlo, tendremos sólo los valores que pertenecen a la relación, aunque puede seguir habiendo repetidos

Y la igualdad para la atributo nombre_sucursal con el valor ‘Navacerrada‘

Cláusula JOIN

SELECT c.numero_cuenta, c.saldo
FROM cuenta c
JOIN cliente cl ON c.numero_cuenta = cl.numero_cuenta
WHERE c.nombre_sucursal = 'Navacerrada'

En el ejemplo se ha usado la cláusula JOIN que nos devolverá un resultado equivalente

La cláusula JOIN permite realizar operaciones de reunión, las cuales toman dos relaciones y devuelven como resultado otra relación

Normalmente puede utilizarse la cláusula FROM para realizar reuniones externas o para cualquier situación en la que se pueda utilizar una reunión

Para distinguir si la reunión es externa se usará la palabra clave OUTER y para la interna INNER

Ambas son opcionales, aunque deja más claro qué tipo de reunión estamos usando

Hay cuatro tipos de reunión:

• INNER JOIN

  Devuelve todas las tuplas cuando hay al menos una coincidencia en ambas relaciones

  SELECT c.numero_cuenta, c.saldo
  FROM cuenta c
  INNER JOIN cliente cl ON c.numero_cuenta = cl.numero_cuenta
  WHERE c.nombre_sucursal = 'Navacerrada'

  En el ejemplo se ha usado la cláusula INNER JOIN que nos devolverá un resultado equivalente

  Ya que como dijimos, la palabra clave INNER era opcional

  Sin embargo, ahora tenemos más claro que la reunión era interna

• LEFT OUTER JOIN

  Devuelve todas las tuplas de la relación de la izquierda, y las tuplas coincidentes de la relación de la derecha

  SELECT c.numero_cuenta, c.saldo
  FROM cuenta c
  LEFT JOIN cliente cl ON c.numero_cuenta = cl.numero_cuenta
  WHERE c.nombre_sucursal = 'Navacerrada'

  En el ejemplo se ha usado la cláusula LEFT JOIN que nos devolverá un resultado que ya no es equivalente, ya que aparecerán valores nulos

  Hemos prescindido de la palabra clave OUTER porque el resultado es equivalente

  En este caso LEFT JOIN nos mantiene todas las tuplas de la relación izquierda (cuenta)

  Las tuplas de la relación derecha (cliente) se mostrarán si hay una coincidencia con las de la izquierda

  Si existen valores en la relación izquierda pero no en la relación derecha, ésta mostrará null

• RIGHT OUTER JOIN

  Devuelve todas las tuplas de la relación de la derecha, y las tuplas coincidentes de la relación de la izquierda

  SELECT c.numero_cuenta, c.saldo
  FROM cuenta c
  RIGHT JOIN cliente cl ON c.numero_cuenta = cl.numero_cuenta
  WHERE c.nombre_sucursal = 'Navacerrada'

  En el ejemplo se ha usado la cláusula RIGHT JOIN que nos devolverá un resultado que ya no es equivalente, ya que aparecerán valores nulos

  Hemos prescindido de la palabra clave OUTER porque el resultado es equivalente

  En este caso RIGHT JOIN nos mantiene todas las tuplas de la relación derecha (cliente)

  Las tuplas de la relación izquierda (cuenta) se mostrarán si hay una coincidencia con las de la derecha

  Si existen valores en la relación derecha pero no en la relación izquierda, ésta mostrará null

• FULL OUTER JOIN

  Devuelve todas las tuplas de las dos relaciones, la izquierda y la derecha

  SELECT c.numero_cuenta, c.saldo
  FROM cuenta c
  FULL JOIN cliente cl ON c.numero_cuenta = cl.numero_cuenta
  WHERE c.nombre_sucursal = 'Navacerrada'

  En el ejemplo se ha usado la cláusula FULL JOIN que nos devolverá un resultado que ya no es equivalente, ya que aparecerán valores nulos

  Hemos prescindido de la palabra clave OUTER porque el resultado es equivalente

  En este caso FULL JOIN devuelve todas las filas de la tabla izquierda (cuenta) y de la tabla derecha (cliente)

  Combina el resultado de los LEFT JOIN y RIGHT JOIN

  Aparecerá null en cada una de las tablas alternativamente cuando no haya una coincidencia

El tipo de reunión define la manera de tratar las tuplas de cada relación que no casan con la tupla de la otra relación

Hay tres tipos de condiciones que se aplicarán a las reuniones:

• reunión NATURAL

  Se utiliza cuando los atributos por los cuales se reúnen las relaciones tienen el mismo nombre

  SELECT c.numero_cuenta, c.saldo
  FROM cuenta c
  NATURAL JOIN cliente cl
  WHERE c.nombre_sucursal = 'Navacerrada'

  En el ejemplo se ha usado la cláusula NATURAL JOIN que nos devolverá un resultado equivalente sin utilizar la condición ON

  Ya que el atributo numero_cuenta se llama igual en las relaciones cuenta y cliente

• condición ON

  Su uso es similar al de la cláusula WHERE, pero está especializado en comparar atributos de ambas relaciones

• condición USING

  Su uso es similar al de la reunión NATURAL, ya que se utiliza cuando los atributos por los cuales se reúnen las relaciones tienen el mismo nombre

  Sin embargo, sólo devolverá un resultado de la reunión, no todos como hace la reunión NATURAL

  SELECT c.numero_cuenta, c.saldo
  FROM cuenta c
  JOIN cliente cl USING(numero_cuenta)
  WHERE c.nombre_sucursal = 'Navacerrada'

  En el ejemplo se ha usado la condición USING con el el atributo numero_cuenta, que nos devolverá un resultado que ya no es equivalente

  Ya que sólo devolverá sólo la primera coincidencia de cada pareja de la relación, no todas

El uso de las condiciones de reunión es obligatorio en las reuniones externas, pero es opcional en las internas (si se omiten, se obtiene un producto cartesiano)

Cláusula WHERE

SELECT *
FROM cuenta
WHERE saldo < 100

En el ejemplo se ha resuelto la consulta obtener sólo las cuentas de la relación cuenta que cumplen la condición introducida, en este caso las que en su atributo saldo tienen un valor inferior a 100

Para conseguirlo hemos utilizado la cláusula WHERE, gracias a la cuál podemos filtrar la consulta al admitir una condición usando el operador lógico de comparación <

SELECT *
FROM cuenta
WHERE nombre_sucursal = 'Navacerrada' AND saldo < 100

En el ejemplo se ha resuelto la consulta obtener sólo las cuentas de la relación cuenta que cumplen la condición introducida, en este caso las que en su atributo nombre_sucursal tienen un valor igual a ‘Navacerrada‘ y su atributo saldo tienen un valor inferior a 100

Para conseguirlo se ha utilizado el conector lógico AND

Operador BETWEEN

SELECT *
FROM cuenta
WHERE nombre_sucursal = 'Navacerrada' AND saldo >= 100 AND saldo <= 1000

En el ejemplo se ha resuelto la consulta obtener sólo las cuentas de la relación cuenta que cumplen la condición introducida, en este caso las que en su atributo nombre_sucursal tienen un valor igual a ‘Navacerrada‘ y su atributo saldo tienen un valor entre 100 y 1000

SELECT *
FROM cuenta
WHERE nombre_sucursal = 'Navacerrada' AND saldo BETWEEN 100 AND 1000

En el ejemplo se ha utilizado una consulta equivalente, utilizando el operador de comparación BETWEEN, el cuál nos simplifica un poco las cláusulas WHERE

SELECT *
FROM cuenta
WHERE nombre_sucursal = 'Navacerrada' AND saldo NOT BETWEEN 100 AND 1000

En el ejemplo se ha utilizado una consulta negando el operador de comparación NOT BETWEEN, el cuál nos devolverá aquellos valores que estén fuera de ese rango

Operador LIKE

SELECT *
FROM cuenta
WHERE nombre_sucursal = 'Navacerrada'

En el ejemplo se ha resuelto la consulta obtener sólo las cuentas de la relación cuenta que cumplen la condición introducida, en este caso las que en su atributo nombre_sucursal tienen un valor igual a ‘Navacerrada‘

SELECT *
FROM cuenta
WHERE nombre_sucursal LIKE 'Navacerrada'

En el ejemplo se ha utilizado una consulta equivalente, utilizando el operador de comparación LIKE, el cuál nos simplifica un poco las cláusulas WHERE al permitirnos el uso de patrones con las cadenas de caracteres

Para la descripción de patrones podemos utilizar dos caracteres especiales:

• %

  El tanto por ciento coincide con cualquier subcadena de caracteres

• _

  El subrayado coincide con cualquier carácter

Estos patrones pueden ser combinados para obtener distintos tipos de consultas:

SELECT *
FROM cuenta
WHERE nombre_sucursal LIKE 'Nava%'

En el ejemplo se ha utilizado el operador de comparación LIKE utilizando el patrón % después del texto, el cuál nos devolverá aquellos valores que empiecen por la cadena ‘Nava‘

SELECT *
FROM cuenta
WHERE nombre_sucursal LIKE '%Nava%'

En el ejemplo se ha utilizado el operador de comparación LIKE utilizando el patrón % delante y después del texto, el cuál nos devolverá aquellos valores que incluyan como subcadena ‘Nava‘

SELECT *
FROM cuenta
WHERE nombre_sucursal LIKE '___'

En el ejemplo se ha utilizado el operador de comparación LIKE utilizando el patrón _ repetido tres veces, el cuál nos devolverá aquellos valores que tengan exactamente tres caracteres

SELECT *
FROM cuenta
WHERE nombre_sucursal LIKE '___%'

En el ejemplo se ha utilizado el operador de comparación LIKE utilizando el patrón _ repetido tres veces y el patrón %, el cuál nos devolverá aquellos valores que tengan al menos tres caracteres

SELECT *
FROM cuenta
WHERE nombre_sucursal LIKE 'Nava\%rrete' escape '\'

En el ejemplo se ha utilizado el operador de comparación LIKE utilizando el patrón % en conjunto con el carácter especial de escape ‘\‘, el cuál nos devolverá aquellos valores que contengan ‘Nava%rrete‘

El carácter de escape (que puede ser cualquiera que nosotros elijamos) se utiliza antes de los caracteres especiales de patrón para indicar que ese carácter se va a tratar como un carácter normal y no como un patrón

Se define utilizando la palabra clave escape

SELECT *
FROM cuenta
WHERE nombre_sucursal NOT LIKE 'Nava%

En el ejemplo se ha utilizado el operador de comparación NOT LIKE utilizando el patrón %, el cuál nos devolverá aquellos valores que NO contengan ‘Nava%rrete‘

De esta forma también podemos buscar discordancias, solamente negando el operador LIKE

Valores nulos

SQL permite el uso de valores nulos para indicar la ausencia de información sobre el valor de un atributo

Se puede utilizar la palabra clave especial NULL en un predicado para comprobar si un valor es nulo

SELECT *
FROM cuenta
WHERE nombre_sucursal IS NULL

En el ejemplo se ha resuelto la consulta obtener todas las cuentas cuyo nombre de sucursal NO haya sido rellenado

SELECT *
FROM cuenta
WHERE nombre_sucursal IS NOT NULL

En el ejemplo se ha resuelto la consulta obtener todas las cuentas cuyo nombre de sucursal haya sido rellenado

Como puede verse, también puede comprobarse la ausencia de valores nulos usando el operador lógico NOT

Dado que el predicado de las cláusulas WHERE puede incluir operaciones booleanas como AND, OR y NOT sobre los resultados de la comparaciones, los valores NULL amplían las operaciones booleanas

Cláusula ORDER BY

En ocasiones necesitamos devolver la información de las tuplas en cierto order

SQL permite ordenar los atributos utilizando la cláusula ORDER BY

SELECT *
FROM cuenta
ORDER BY nombre_sucursal ASC

En el ejemplo se ha utilizado el operador de comparación ASC con la cláusula ORDER BY para el atributo nombre_sucursal, el cuál nos devolverá los valores ordenados de forma ascendente (en order alfabético porque era un atributo alfanumérico) por el atributo nombre_sucursal

SELECT *
FROM cuenta
ORDER BY nombre_sucursal DESC

En el ejemplo se ha utilizado el operador de comparación DESC con la cláusula ORDER BY para el atributo nombre_sucursal, el cuál nos devolverá los valores ordenados de forma descendente (en order alfabético porque era un atributo alfanumérico) por el atributo nombre_sucursal

El tipo de ordenación devuelto por los operadores de comparación ASC y DESC dependerán del tipo del atributo, ya que no se ordena de igual manera un número, una cadena o una fecha

Si necesitamos ordenar más de un atributo, lo haremos separandolos por comas y seguido el operador de comparación (ASC ó DESC )

El método de ordenación por defecto es ASC, es opcional y podríamos prescindir de escribirlo

Operaciones sobre conjuntos

SQL permite realizar las operaciones sobre conjuntos del álgebra relacional unión \cup, intersección \cap y diferencia \neg

Para utilizarlas tendremos la única restricción de que los nombres de los atributos de las relaciones implicadas deben ser iguales

Operación UNION

La forma más habitual de consulta será:

\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1\\ \text{WHERE} P\\ \text{UNION}\\ \text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2\\ \text{WHERE} Q

Donde cada A_i representa a un atributo, cada r_i una relación y P, Q son predicados

Por lo que una consulta SQL que contiene una unión tendrá la siguiente expresión de álgebra relacional equivalente:

\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r_1) ) \cup \prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma q (r_2) )

Si se omite la clausula WHERE, el predicado P ó Q (depende cuál se omita), es siempre cierto

SELECT numero_cuenta
FROM cuenta
UNION
SELECT numero_cuenta
FROM cliente

En el ejemplo se ha resuelto la consulta de todos los números de cuenta que existen en el banco o que tienen un cliente asociado a ellas

A diferencia de la cláusa SELECT la operación UNION elimina los valores repetidos

Por esta razón es buena idea utilizar la operación UNION para crear claves, ya que con SELECT no obtendríamos claves únicas (permite repetidos)

SELECT numero_cuenta
FROM cuenta
UNION ALL
SELECT numero_cuenta
FROM cliente

En el ejemplo se ha resuelto la consulta de todos los números de cuenta que existen en el banco o que tienen un cliente asociado a ellas

Al utilizar el operador ALL en conjunto con la operación UNION, estamos permitiendo que se comporte como un SELECT, ya que admite repetidos

Operación INTERSECT

La forma más habitual de consulta será:

\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1\\ \text{WHERE} P\\ \text{INTERSECT}\\ \text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2\\ \text{WHERE} Q

Donde cada A_i representa a un atributo, cada r_i una relación y P, Q son predicados

Por lo que una consulta SQL que contiene una intersección tendrá la siguiente expresión de álgebra relacional equivalente:

\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r_1) ) \cap \prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma q (r_2) )

Si se omite la clausula WHERE, el predicado P ó Q (depende cuál se omita), es siempre cierto

SELECT numero_cuenta
FROM cuenta
INTERSECT
SELECT numero_cuenta
FROM cliente

En el ejemplo se ha resuelto la consulta de todos los números de cuenta que existen en el banco y que tienen un cliente asociado a ellas

La operación INTERSECT elimina los valores repetidos y sólo devuelve aquellos valores que tienen coincidencia en la relación cuenta y en cliente a la vez, no sólo en uno de ellos

SELECT numero_cuenta
FROM cuenta
INTERSECT ALL
SELECT numero_cuenta
FROM cliente

En el ejemplo se ha resuelto la consulta de todos los números de cuenta que existen en el banco y que tienen un cliente asociado a ellas

Al utilizar el operador ALL en conjunto con la operación INTERSECT, estamos permitiendo que se comporte como un SELECT, ya que admite repetidos

Operación EXCEPT

La forma más habitual de consulta será:

\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1\\ \text{WHERE} P\\ \text{EXCEPT}\\ \text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2\\ \text{WHERE} Q

Donde cada A_i representa a un atributo, cada r_i una relación y P, Q son predicados

Por lo que una consulta SQL que contiene una diferencia tendrá la siguiente expresión de álgebra relacional equivalente:

\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r_1) ) \cap \neg (\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma q (r_2) ) )

Si se omite la clausula WHERE, el predicado P ó Q (depende cuál se omita), es siempre cierto

SELECT numero_cuenta
FROM cuenta
EXCEPT
SELECT numero_cuenta
FROM cliente

En el ejemplo se ha resuelto la consulta de todos los números de cuenta que existen en el banco y que NO tienen un cliente asociado a ellas

La operación EXCEPT elimina los valores repetidos y sólo devuelve aquellos valores que tienen coincidencia en la relación cuenta pero no coinciden con cliente

SELECT numero_cuenta
FROM cuenta
EXCEPT ALL
SELECT numero_cuenta
FROM cliente

En el ejemplo se ha resuelto la consulta de todos los números de cuenta que existen en el banco y que NO tienen un cliente asociado a ellas

Al utilizar el operador ALL en conjunto con la operación EXCEPT, estamos permitiendo que se comporte como un SELECT, ya que admite repetidos

Operación IN

SQL permite comprobar la pertenencia de las tuplas a una relación que es equivalente a comprobar la pertenencia a un conjunto del álgebra relacional

La forma más habitual de consulta será:

\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1\\ \text{WHERE} P\\ \text{IN}\\ (\text{SELECT} _1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2\\ \text{WHERE} Q)

Donde cada A_i representa a un atributo, cada r_i una relación y P, Q son predicados

Por lo que una consulta SQL que contiene una pertenencia tendrá la siguiente expresión de álgebra relacional equivalente:

\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r_1) ) \in \prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma q (r_2) )

Si se omite la clausula WHERE, el predicado P ó Q (depende cuál se omita), es siempre cierto

SELECT numero_cuenta
FROM cliente
WHERE numero_cuenta IN (SELECT numero_cuenta
                        FROM cuenta
                        WHERE nombre_sucursal = 'Navacerrada')

El ejemplo resuelve la consulta de obtener todos los clientes que tienen cuenta en la sucursal de ‘Navacerrada‘

Anteriormente la resolvimos utilizando la cláusula JOIN

Pero como puede comprobarse, también podemos resolverla utilizando la teoría de conjuntos

La pertenencia nos devolverá los valores y en caso de no haber ninguno, mostrará null

SELECT numero_cuenta
FROM cliente
WHERE numero_cuenta NOT IN (SELECT numero_cuenta
                        FROM cuenta
                        WHERE nombre_sucursal = 'Navacerrada')

El ejemplo resuelve la consulta de obtener todos los clientes que NO tienen cuenta en la sucursal de ‘Navacerrada‘

Al negar la pertenencia nos devolverá el resto de valores y en caso de no haber ninguno, mostrará null

SELECT numero_cuenta
FROM cuenta
WHERE nombre_sucursal IN ('Logroño', 'Navacerrada')

El ejemplo resuelve la consulta de obtener todos los clientes que tienen cuenta en la sucursal de ‘Logroño‘ ó ‘Navacerrada‘

Como puede apreciarse, la pertenencia o la no pertenencia, aparte de una subconsulta como en los ejemplos anteriores, también admite conjuntos enumerados

Siempre que esos conjuntos enumerados sean del mismo dominio que el atributo del que se quiere comprobar la pertenencia

Operadores de comparación

SQL ofrece la posibilidad de comparar conjuntos utilizando subconsultas

Operador SOME

La forma más habitual de consulta será:

\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1\\ \text{WHERE} P\\ \text{SOME}\\ (\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2\\ \text{WHERE} Q)

Donde cada A_i representa a un atributo, cada r_i una relación y P, Q son predicados

Por lo que una consulta SQL que contiene una pertenencia tendrá la siguiente expresión de álgebra relacional equivalente:

\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r_1) ) \approx \prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma q (r_2) )

Si se omite la clausula WHERE, el predicado P ó Q (depende cuál se omita), es siempre cierto

SELECT numero_cuenta
FROM cliente
WHERE numero_cuenta = SOME (SELECT numero_cuenta
                        FROM cuenta
                        WHERE nombre_sucursal = 'Navacerrada')

El ejemplo resuelve la consulta de obtener algún cliente que tiene cuenta en la sucursal de ‘Navacerrada‘

Anteriormente la resolvimos utilizando la operación IN

Pero como puede comprobarse, también podemos resolverla utilizando una comparación de conjuntos

En este caso utilizando el símbolo = delante del operador SOME para denotar que son iguales

SELECT numero_cuenta
FROM cliente
WHERE numero_cuenta <> SOME (SELECT numero_cuenta
                        FROM cuenta
                        WHERE nombre_sucursal = 'Navacerrada')

El ejemplo resuelve la consulta de obtener algún cliente que NO tiene cuenta en la sucursal de ‘Navacerrada‘

Aunque podría parecer que es equivalente al ejemplo anterior con NOT INT, el resultado puede no ser igual

También podemos utilizar el resto de operadores lógicos de comparación

Operador ANY

Puede utilizarse también el operador ANY ya que es equivalente a SOME

Las primeras versiones de SQL sólo admitían el operador ANY, pero se añadió SOME para que se asemejase más al lenguaje natural, ya que any creaba ambigüedad en la lengua inglesa

La forma más habitual de consulta será:

\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1\\ \text{WHERE} P\\ \text{ANY}\\ (\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2\\ \text{WHERE} Q)

Donde cada A_i representa a un atributo, cada r_i una relación y P, Q son predicados

Por lo que una consulta SQL que contiene una pertenencia tendrá la siguiente expresión de álgebra relacional equivalente:

\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r_1) ) \approx \prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma q (r_2) )

Si se omite la clausula WHERE, el predicado P ó Q (depende cuál se omita), es siempre cierto

También podemos utilizar el resto de operadores lógicos de comparación

SELECT numero_cuenta
FROM cliente
WHERE numero_cuenta = ANY (SELECT numero_cuenta
                        FROM cuenta
                        WHERE nombre_sucursal = 'Navacerrada')

El ejemplo resuelve la consulta de obtener algún cliente que tiene cuenta en la sucursal de ‘Navacerrada‘

Anteriormente la resolvimos utilizando la operación SOME

Como puede verse es totalmente equivalente y sólo lo usaríamos por compatibilidad con versiones antiguas de SQL

Operador ALL

La forma más habitual de consulta será:

\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1\\ \text{WHERE} P\\ \text{ALL}\\ (\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2\\ \text{WHERE} Q)

Donde cada A_i representa a un atributo, cada r_i una relación y P, Q son predicados

Por lo que una consulta SQL que contiene una pertenencia tendrá la siguiente expresión de álgebra relacional equivalente:

\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r_1) ) \forall \prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma q (r_2) )

Si se omite la clausula WHERE, el predicado P ó Q (depende cuál se omita), es siempre cierto

También podemos utilizar el resto de operadores lógicos de comparación

SELECT numero_cuenta
FROM cliente
WHERE numero_cuenta > ALL (SELECT numero_cuenta
                        FROM cuenta
                        WHERE nombre_sucursal = 'Navacerrada')

El ejemplo resuelve la consulta de obtener todos los clientes que tienen cuenta en la sucursales después de ‘Navacerrada‘ (alfabéticamente)

En realidad al utilizar el símbolo > delante del operador ALL estamos denotando que buscamos todos los valores que tengan una ordenación alfabética superior a ‘Navacerrada‘

Control de repetidos

SQL permite controlar si una subconsulta tiene tuplas repetidas en su resultado

La forma más habitual de consulta será:

\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1\\ \text{WHERE UNIQUE}\\ (\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2, \cdots, r_n\\ \text{WHERE} P)

Donde cada A_i representa a un atributo, cada r_i una relación y P es predicado

Por lo que una consulta SQL que contiene una pertenencia tendrá la siguiente expresión de álgebra relacional equivalente:

\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma \exists \prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r_2, \cdots, r_n) ) (r_1) )

Si se omite la clausula WHERE, el predicado P, es siempre cierto

Formalmente la evaluación de UNIQUE sobre una relación se define como falsa si y sólo si la relación contiene dos tuplas r_i y r_k tales que r_i = r_k

Como la comprobación r_i = r_k es falsa si algún atributo de r_i ó r_k es nulo, es posible que el resultado de UNIQUE sea cierto aunque haya varios duplicados de una misma tupla

Siempre que al menos uno de los atributos de la tupla sea nulo

SELECT cl.numero_cuenta
FROM cliente cl
WHERE UNIQUE (SELECT c.numero_cuenta
              FROM cuenta c 
              WHERE cl.numero_cuenta = c.numero_cuenta AND
                    c.nombre_sucursal = 'Navacerrada')

El ejemplo resuelve la consulta de obtener todos los clientes que tienen a lo sumo una cuenta en la sucursal de ‘Navacerrada‘

Para ello se han introducido las relaciones cuenta (se le ha asignado el alias c) y cliente (se le ha asignado el alias cl)

Sólo con la cláusula FROM no podríamos resolver la consulta

Podríamos haber usado la cláusula JOIN, aunque puede seguir habiendo repetidos

Por eso se ha añadido el operador UNIQUE que relaciona ambas relaciones con el atributo numero_cuenta y la igualdad para la atributo nombre_sucursal con el valor ‘Navacerrada‘

Después de añadirlos, tendremos sólo los valores que pertenecen a la relación

SELECT cl.numero_cuenta
FROM cliente cl
WHERE NOT UNIQUE (SELECT c.numero_cuenta
              FROM cuenta c 
              WHERE cl.numero_cuenta = c.numero_cuenta AND
                    c.nombre_sucursal = 'Navacerrada')

El ejemplo resuelve la consulta de obtener todos los clientes que tienen más de una cuenta en la sucursal de ‘Navacerrada‘

Como puede verse, la negación de UNIQUE nos permite comprobar si hay más de un repetido

Funciones de agregación

Las funciones de agregación son funciones que toman una colección (un conjunto o multiconjunto) de valores como entrada y devuelven un solo valor

SQL permite cinco funciones de agregación:

• AVG

  Permite realizar la media aritmética de la colección, la cuál debe ser numérica

  Si hay valores nulos en la entrada, se ignoran

  SELECT AVG(saldo) media
  FROM cuenta

  En el ejemplo se ha resuelto la consulta obtener la media aritmética del saldo de todas las cuentas

  Se ha creado un atributo calculado (que no pertenece a la relación) a partir de esas operaciones, asignándole un nombre o alias a esa operación, en este caso se llama media

• MIN

  Permite determinar cuál es valor mínimo de la colección, la cuál no es obligatorio que sea numérica

  Si hay valores nulos en la entrada, se ignoran

  SELECT MIN(saldo) menor
  FROM cuenta

  En el ejemplo se ha resuelto la consulta obtener el saldo más bajo de todas las cuentas

  Se ha creado un atributo calculado (que no pertenece a la relación) a partir de esas operaciones, asignándole un nombre o alias a esa operación, en este caso se llama menor

• MAX

  Permite determinar cuál es valor máximo de la colección, la cuál no es obligatorio que sea numérica

  Hay que tener en cuenta que NO admite DISTINCT pero si ALL, pero como es el valor por defecto, puede omitirse

  Si hay valores nulos en la entrada, se ignoran

  SELECT MAX(saldo) mayor
  FROM cuenta

  En el ejemplo se ha resuelto la consulta obtener el saldo más alto de todas las cuentas

  Se ha creado un atributo calculado (que no pertenece a la relación) a partir de esas operaciones, asignándole un nombre o alias a esa operación, en este caso se llama mayor

• SUM

  Permite realizar la suma de la colección, la cuál debe ser numérica

  Si hay valores nulos en la entrada, se ignoran

  SELECT SUM(saldo) liquido
  FROM cuenta

  En el ejemplo se ha resuelto la consulta obtener el saldo total de todas las cuentas

  Se ha creado un atributo calculado (que no pertenece a la relación) a partir de esas operaciones, asignándole un nombre o alias a esa operación, en este caso se llama liquido

• COUNT

  Permite contar el número de elementos que compone la colección, la cuál no es obligatorio que sea numérica

  Si hay valores nulos en la entrada, se toman como 0

  SELECT COUNT(saldo) total
  FROM cuenta

  En el ejemplo se ha resuelto la consulta obtener cuantas cuentas hay en total

  Se ha creado un atributo calculado (que no pertenece a la relación) a partir de esas operaciones, asignándole un nombre o alias a esa operación, en este caso se llama total

Cláusula GROUP BY

Existen circunstancias en las cuales sería deseable aplicar las funciones de agregación no sólo a un único conjunto de tuplas sino también a un grupo de conjuntos de tuplas

En SQL podemos especificarlo mediante la cláusula GROUP BY

La forma más habitual de consulta será:

\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r\\ \text{WHERE} P\\ \text{GROUP BY} A_1, \cdots, A_2, \cdots, A_n\\ \text{HAVING} Q

Donde cada A_i representa a un atributo, cada r_i una relación y P, Q son predicados

Por lo que una consulta SQL que contiene una agrupación tendrá la siguiente expresión de álgebra relacional equivalente:

\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma q (\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r) ) )

Si se omiten las clausulas WHERE ó HAVING, el predicado P ó Q (depende cuál se omita), es siempre cierto

SELECT nombre_sucursal, COUNT(saldo) total
FROM cuenta
GROUP BY nombre_sucursal, saldo

En el ejemplo se ha resuelto la consulta obtener cuantas cuentas hay en total de cada sucursal

Se ha creado un atributo calculado (que no pertenece a la relación) a partir de esas operaciones, asignándole un nombre o alias a esa operación, en este caso se llama total

Para que la operación de agrupación pueda realizarse sin errores debemos utilizar el mismo orden de los atributos tanto en la cláusula SELECT como en GROUP BY

En el caso de que no vayan a intervenir en la agrupación, podremos omitir esos atributos colocándolos a la derecha de los que sí serán usados

Cláusula HAVING

En ocasiones necesitamos que una condición se aplique a los grupos en vez de a las tuplas

Para expresar este tipo de consultas utilizamos la cláusula HAVING

SELECT nombre_sucursal, COUNT(saldo) total
FROM cuenta
GROUP BY nombre_sucursal, saldo
HAVING nombre_sucursal LIKE 'Navacerrada'

En el ejemplo se ha resuelto la consulta obtener cuantas cuentas hay en total de la sucursal de ‘Navacerrada‘

Se ha creado un atributo calculado (que no pertenece a la relación) a partir de esas operaciones, asignándole un nombre o alias a esa operación, en este caso se llama total

Se ha aplicado la condición sobre el atributo nombre_sucursal con el valor ‘Navacerrada‘ utilizando la cláusula HAVING para aplicarla sobre la agrupación y no sobre la relación

En el caso de que tendríamos una cláusula WHERE y una HAVING:

1. SQL aplicará primero el predicado de la cláusula WHERE
2. El resultado se dividirá en los grupos que satisfagan el predicado de la cláusula HAVING

SELECT nombre_sucursal, COUNT(saldo) total
FROM cuenta
GROUP BY nombre_sucursal, saldo
HAVING nombre_sucursal LIKE 'Navacerrada' AND COUNT(saldo) > 100

En el ejemplo se ha resuelto la consulta obtener cuantas cuentas hay en total de la sucursal de ‘Navacerrada‘ y que tengan un saldo mayor de 100

Se ha creado un atributo calculado (que no pertenece a la relación) a partir de esas operaciones, asignándole un nombre o alias a esa operación, en este caso se llama total

Se ha aplicado la condición sobre el atributo nombre_sucursal con el valor ‘Navacerrada‘

Adicionalmente se ha aplicado la condición sobre el atributo saldo aplicándole la operación de agrupación COUNT con la condición de que sean mayores que 100

Ambas han sido aplicacadas sobre la cláusula HAVING

Como puede verse, pueden aplicarse sin problemas las operaciones de agregación dentro de la cláusula HAVING aunque también aparezcan en la cláusula SELECT

INSERT

Las relaciones recién creadas están inicialmente vacías

Se puede utilizar el comando INSERT para añadir datos a la relación

La forma más habitual de consulta de inserción será:

\text{INSERT INTO r}\\ \text{VALUES} (A_1, A_2, \cdots, A_n)

Donde cada A_i representa a un atributo, r la relación y cada P_i es un predicado

INSERT INTO cuenta
VALUES ('C-9732', 'Navacerrada', 1200)

En el ejemplo se ha resuelto la consulta para añadir la cuenta C-9732 en la sucursal de ‘Navacerrada‘ con un saldo de 1200€

Los valores se especifican en el orden en que se relacionan los atributos correspondientes en el esquema de la relación

INSERT INTO cuenta (saldo, numero_cuenta, nombre_sucursal)
VALUES (1200, 'C-9732', 'Navacerrada')

En el ejemplo se ha modificado el orden en que se relacionan los atributos correspondientes en el esquema de la relación y por eso los datos se han introducido de forma obligatoria, en el nuevo orden que hemos especificado

De esta forma podemos elegir nosotros en qué orden introducimos los datos

En el caso de no introducir un atributo, SQL tratará de introducir un valor por defecto o el valor nulo y si es la clave primaria, notificará el error e impedirá la actualización

INSERT INTO cliente
       SELECT numero_cuenta, 'Pedro Almodovar'
       FROM cuenta
       WHERE nombre_sucursal = 'Navacerrada'
       EXCEPT
       SELECT numero_cuenta
       FROM cliente

En el ejemplo se ha resuelto la consulta para añadir el cliente ‘Pedro Almodovar‘ para todos los números de cuenta que existen en la sucursal de ‘Navacerrada‘ y que NO tienen un cliente asociado a ellas

En lugar de especificar una tupla se ha usado una subconsulta ya que la cláusula SELECT nos permitirá especificar un conjunto de tuplas

La forma más habitual de subconsulta para la inserción será:

\text{INSERT INTO} r1\\ \text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2\\ \text{WHERE} P

Donde cada A_i representa a un atributo, r_i las relaciones y P es un predicado

Se evaluará en primer lugar la cláusula SELECT y se insertarán en la relación cliente las tuplas obtenidas de la subconsulta

Hemos de tener cuidado al utilizar subconsultas anidadas, ya que al abusar de ellas, podríamos crear un bucle infinito de subconsultas y la inserción se realizaría de forma infinita, quedando la base de datos bloqueada

Por suerte los sistemas gestores de bases de datos tienen mecanismos o herramientas para realizar inserciones masivas de forma controlada, evitando el bloqueo de la base de datos

UPDATE

Se puede utilizar el comando UPDATE para modificar los valores de un conjunto de tuplas existentes de una relación

La forma más habitual de consulta de actualización será:

\text{UPDATE r}\\ \text{SET} A_1 = P_1, A_2 = P_2, \cdots, A_n = P_n

Donde cada A_i representa a un atributo, r la relación y cada P_i es un predicado

UPDATE cuenta
SET saldo = 100

En el ejemplo se ha actualizado la relación llamada cuenta, en concreto al atributo saldo le hemos cambiado su valor a 100

El comando actualizará todas las tuplas de la relación cuenta

UPDATE cuenta
SET saldo = 100
WHERE numero_cuenta LIKE 'C-9732'

En el ejemplo se han actualizado sólo las tuplas que cumplen la condición introducida, en este caso las que en su atributo numero_cuenta tienen un valor igual a ‘C-9732‘

En lugar de especificar una tupla se ha usado una subconsulta ya que la cláusula SELECT nos permitirá especificar un conjunto de tuplas

Hemos de tener cuidado al utilizar subconsultas anidadas, ya que al abusar de ellas, podríamos crear un bucle infinito de subconsultas y la inserción se realizaría de forma infinita, quedando la base de datos bloqueada

Por suerte los sistemas gestores de bases de datos tienen mecanismos o herramientas para realizar inserciones masivas de forma controlada, evitando el bloqueo de la base de datos

Operador Case

El orden de dos instrucciones UPDATE es importante ya que si se cambia el orden de las instrucciones de actualización podemos obtener datos incoherentes

Para solucionar este problema tenemos el operador CASE

La forma más habitual de consulta de actualización será:

\text{UPDATE r}\\ \text{SET} A_1 = P_1, A_2 = P_2, \cdots, A_n = P_n

Donde cada A_i representa a un atributo, r la relación y cada P_i es un predicado el cual podemos sustituir por el siguiente predicado Q:

\text{CASE}\\ \text{WHEN} P_1 \text{THEN} r_1\\ \text{WHEN} P_2 \text{THEN} r_2\\ \cdots\\ \text{WHEN} P_n \text{THEN} r_n\\ \text{ELSE} r_0\\ \text{END}

Donde cada P_i cada predicado que puede satisfacerse, cada r_i el resultado que se usará en la actualización

En el caso de no cumplirse ninguno de los predicados, se aplicaría el resultado r₀

UPDATE cuenta
SET saldo = CASE
                WHEN saldo <= 100 THEN saldo * 0.05
                WHEN saldo = 500 THEN saldo * 0.07
                WHEN saldo > 1000 THEN saldo * 0.25
                ELSE saldo * 0.01
            END

En el ejemplo se ha resuelto la consulta de actualización para añadir un 0.05 al saldo cuando es menor o igual que 100 con una modificación del 0.05, cuando es igual que 500 con una modificación del 0.07, cuando es mayor que 1000 con una modificación del 0.25 y una modificación del 0.01 en cualquier otro caso

Se ha utilizado la cláusula CASE para resolverlo al igualarlo con el atributo saldo que era el valor a actualizar

DELETE

Se puede utilizar el comando DELETE para borrar tuplas de una relación

Y sólo de esa relación, ya que no es capaz de trabajar sobre varias

La forma más habitual de consulta de borrado será:

\text{DELETE FROM r}\\ \text{WHERE} P

Donde r representa la relación y cada P es un predicado

DELETE FROM cuenta

En el ejemplo se ha borrado la relación llamada cuenta

El comando borrará todas las tuplas de la relación cuenta

DELETE FROM cuenta
WHERE saldo < 100

En el ejemplo se han borrado sólo las tuplas que cumplen la condición introducida, en este caso las que en su atributo saldo tienen un valor inferior a 100

Hay que tener en cuenta que el comando DELETE comprueba primero cada tupla de la relación cuenta y luego borrará todas las tuplas que cumplen la condición

Es importante realizar todas las comprobaciones antes del borrado ya que el orden en que se procesan las tuplas puede variar la operación de borrado

Vistas

Las vistas se utilizan en situaciones en las que por motivos de seguridad o de rendimiento, queremos ocultar ciertos datos a los usuarios

SELECT numero_cuenta, nombre_sucursal, saldo
FROM cuenta
WHERE numero_cuenta LIKE 'C-9732'

En el ejemplo, un cliente necesita saber el número operación de un préstamo a su nombre, el ‘C-9732‘ y el nombre de la sucursal que lo ha tramitado, pero para esa operación no es necesario que vea el saldo o más datos relacionados

Con la consulta anterior resolvíamos lo que el cliente necesitaba, pero le dábamos más datos de los necesarios, pudiendo poner a la entidad bancaria en una situación que podría ser insegura

Las relaciones que no forman parte del modelo lógico pero son visibles a los usuarios mediante relaciones virtuales, se denominan vistas

Definición básica de vistas

CREATE VIEW

Las relaciones se definen mediante el comando CREATE VIEW:

\text{CREATE VIEW v} AS r

Donde r es una expresión de consulta y v es el nombre de la vista

CREATE VIEW todas_las_cuentas AS
SELECT numero_cuenta, nombre_sucursal
FROM cuenta

En el ejemplo se ha creado una vista que trata de resolver el problema del ejemplo anterior

Sin embargo, a pesar de que hemos mejorado la seguridad al no mostrar al usuario los saldos, no resuelve el problema, aún deberíamos hacer la subconsulta siguiente para resolverlo:

SELECT numero_cuenta, nombre_sucursal
FROM todas_las_cuentas
WHERE numero_cuenta LIKE 'C-9732'

En el ejemplo se ha tratado la vista como si fuera una relación más

Sin embargo, para el sistema gestor de bases de datos es una relación virtual por ser una vista

Al ser una relación virtual podemos hacer operaciones de consulta SELECT

Pero no podemos hacer operaciones de actualización como INSERT, UPDATE ó DELETE

CREATE VIEW prestamo(nombre_sucursal, total) AS
SELECT nombre_sucursal, COUNT(saldo) total
FROM cuenta
GROUP BY nombre_sucursal, saldo

En el ejemplo se ha creado una vista llamada prestamo(nombre_sucursal, total) que permite obtener los nombres de todas las sucursales y el saldo total que tienen

Como puede verse, es posible devolver cualquier atributo y trabajar normalmente con las consultas

En este caso se ha devuelto el atributo nombre_sucursal y se ha utilizado la función de agregación COUNT despues de usar la cláusula GROUP BY con los atributos nombre_sucursal y saldo

CREATE VIEW cuentas_navacerrada AS
SELECT numero_cuenta, nombre_sucursal
FROM todas_las_cuentas
WHERE nombre_sucursal LIKE 'Navacerrada'

En el ejemplo se ha definido una vista a partir de otra para resolver la consulta que obtiene todos números de cuentas de la sucursal de ‘Navacerrada‘

Para ello hemos usado la vista llamada todas_las_cuentas

Ya que es tomado como una relación virtual para el sistema gestor de bases de datos por ser una vista

Hay que tener en cuenta que anidar vistas sobre si mismas puede producir un efecto recursivo y si están mal definidas bloquear la base de datos al caer en un bucle infinito

Actualización de vistas

Por norma general una vista no puede actualizarse, sin embargo un sistema gestor de bases de datos puede permitir hacerlo en casos concretos

Hay que consultar el manual del sistema gestor de bases de datos para saber en qué casos concretos lo permite y cómo lo resuelve

En el caso de que se permita su actualización, la vista debe cumplir:

• La cláusula FROM sólo tiene una relación
• La cláusula SELECT sólo contiene nombres de atributos de la relación, sin alias, valores agregados y sin la especificación DISTINCT
• Cualquier atributo que no aparece en la cláusula SELECT deberá ser definido como nulo
• La consulta no debe contener funciones de agregación (Cláusula GROUP BY, Cláusula HAVING)

DROP VIEW

Se puede utilizar el comando DROP VIEW para borrar una vista

Y sólo de esa vista, ya que no es capaz de trabajar sobre varias

La forma más habitual de consulta de borrado de vistas será:

\text{DROP VIEW v}

Donde v es el nombre de la vista

DROP VIEW todas_las_cuentas

En el ejemplo se ha borrado la vista llamada todas_las_cuentas

Cláusula WITH

La cláusula WITH fue introducida en la norma SQL:1999 y permite definir una vista temporal

Esta vista temporal sólo puede ser usada en la consulta en la cuál ha sido definida

La forma más habitual será:

\text{WITH} v \text{AS} r_0\newline\\ \text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_0, r_1, r_2, \cdots, r_m, r\\ \text{WHERE} P

Donde cada A_i representa a un atributo, cada r_i una relación (r₀ es una consulta), P es un predicado y v es el nombre de la vista temporal

WITH todas_las_cuentas AS
SELECT numero_cuenta, nombre_sucursal
FROM cuenta

SELECT numero_cuenta, nombre_sucursal
FROM todas_las_cuentas
WHERE numero_cuenta LIKE 'C-9732'

Este ejemplo es equivalente a la vista que vimos en otro ejemplo anterior

Solo que en esta ocasión no hemos usado una vista definida en la base de datos

Hemos usado una vista temporal definida con la cláusula WITH

Transacciones

Las transacciones son una secuencia de instrucciones de consulta o de actualización

La norma SQL especifica que una transacción comienza implícitamente cuando se ejecuta una instrucción SQL

Al finalizar la instrucción debe ejecutarse una de las siguientes instrucciones SQL:

• COMMIT WORK

  Valida las actualizaciones realizadas hasta ese momento y hace que pasen a ser permanentes en la base de datos

  Una vez completada, se iniciará de forma automática la siguiente transacción

  CREATE TABLE cuenta(
  numero_cuenta char(10),
  nombre_sucursal char(15),
  saldo numeric(12, 2),
  primary key(numero_cuenta) );
  
  INSERT INTO cuenta
  VALUES ('C-9732', 'Navacerrada', 1200);
  INSERT INTO cuenta
  VALUES ('C-9733', 'Navacerrada', 1100);
  INSERT INTO cuenta
  VALUES ('C-9734', 'Navacerrada', 1000);
  INSERT INTO cuenta
  VALUES ('C-9735', 'Navacerrada', 900);
  INSERT INTO cuenta
  VALUES ('C-9736', 'Navacerrada', 800);
  INSERT INTO cuenta
  VALUES ('C-9737', 'Navacerrada', 700);
  INSERT INTO cuenta
  VALUES ('C-9738', 'Navacerrada', 600);
  COMMIT;

  En el ejemplo hemos creado una relación llamada cuenta y la hemos poblado con 7 tuplas

  Las instrucciones de consulta o actualización están separadas unas de otras por el símbolo ; indicando a SQL que es una nueva instrucción

  Al terminar hemos ejecutado COMMIT para que los cambios se guarden en la base de datos

• ROLLBACK WORK

  Provoca la anulación de la transacción actual, es decir, anula todas las operaciones realizadas en la base de datos

  La base de datos vuelve al estado inicial, antes de la primera instrucción de la transacción

  CREATE TABLE cuenta(
  numero_cuenta char(10),
  nombre_sucursal char(15),
  saldo numeric(12, 2),
  primary key(numero_cuenta) );
  
  INSERT INTO cuenta
  VALUES ('C-9732', 'Navacerrada', 1200);
  INSERT INTO cuenta
  VALUES ('C-9733', 'Navacerrada', 1100);
  INSERT INTO cuenta
  VALUES ('C-9734', 'Navacerrada', 1000);
  INSERT INTO cuenta
  VALUES ('C-9735', 'Navacerrada', 900);
  INSERT INTO cuenta
  VALUES ('C-9736', 'Navacerrada', 800);
  INSERT INTO cuenta
  VALUES ('C-9737', 'Navacerrada', 700);
  INSERT INTO cuenta
  VALUES ('C-9738', 'Navacerrada', 600);
  ROLLBACK;

  En el ejemplo hemos creado una relación llamada cuenta y la hemos poblado con 7 tuplas

  Las instrucciones de consulta o actualización están separadas unas de otras por el símbolo ; indicando a SQL que es una nueva instrucción

  Al terminar hemos ejecutado ROLLBACK para que los cambios no se guardasen en la base de datos

La palabra clave WORK es opcional en ambas instrucciones, por eso hemos podido omitirla en los ejemplos

Utilizar ROLLBACK es útil cuando se detecta un error y es necesario volver a un estado estable de la base de datos

Una vez que se ha ejecutado COMMIT, no es posible realizar un ROLLBACK, ya que los cambios en los datos se hacen permanentes

Por eso, cuando los sistemas de bases de datos detectan un fallo (ya sea una caída de tensión, una caída del sistema, una mala conexión en la red), internamente realizan un ROLLBACK se deniega el uso del COMMIT, para evitar la pérdida de información

En el caso especial de la caída del sistema, el ROLLBACK se realizará al reinicio del sistema

A partir de la norma SQL:1999 se puede encerrar el contenido de una transacción con el siguiente formato:

\text{BEGIN}\\ S_1;\\ S_2;\\ \cdots;\\ S_n;\\ \text{END}

Donde S_i es una instrucción de consulta o una actualización

BEGIN
    CREATE TABLE cuenta(
    numero_cuenta char(10),
    nombre_sucursal char(15),
    saldo numeric(12, 2),
    primary key(numero_cuenta) );

    INSERT INTO cuenta
    VALUES ('C-9732', 'Navacerrada', 1200);
    INSERT INTO cuenta
    VALUES ('C-9733', 'Navacerrada', 1100);
    INSERT INTO cuenta
    VALUES ('C-9734', 'Navacerrada', 1000);
    INSERT INTO cuenta
    VALUES ('C-9735', 'Navacerrada', 900);
    INSERT INTO cuenta
    VALUES ('C-9736', 'Navacerrada', 800);
    INSERT INTO cuenta
    VALUES ('C-9737', 'Navacerrada', 700);
    INSERT INTO cuenta
    VALUES ('C-9738', 'Navacerrada', 600);
END
COMMIT;

El ejemplo es muy similar al mostrado anteriormente para el COMMIT

Sin embargo, en esta ocasión hemos encerrado la transacción con el formato de la norma SQL:1999

De esta forma podremos distinguir dónde empiezan y terminan distintas transacciones sin tener que buscar las instrucciones COMMIT ó ROLLBACK