Lenguaje SQL Avanzado

Unidad de Apoyo para el Aprendizaje

Iniciar

Introducción


Estás trabajando en una empresa y se te solicita un reporte con información compleja para que los directivos tomen ciertas decisiones…

¿Qué conocimientos de bases de datos requieres para elaborar los reportes?

El manejo de grandes volúmenes de información almacenada en bases de datos implica la inminente necesidad de tener un dominio avanzado del lenguaje SQL, lo cual te facilitará realizar consultas complejas que respondan a preguntas específicas para la toma de decisiones.

En este tema se identificarán los comandos asociados a los lenguajes DDL, DML y DCL, así como aspectos, notas y sintaxis relacionadas con los conceptos vistas, índices, secuencias en subconsultas y triggers.

Por último, se abordarán los conceptos de variables de tipo tablas y cursores, triggers, procesamiento de transacciones, producto cartesiano; consultas inner join, outer join, full outer join, self join; así como adición de campos calculados, cálculos numéricos, operadores lógicos y relacionales, uso de paréntesis, agrupamiento y funciones de agregación.



Productos

Diseño de bases de datos



El estudio de este tema te permitirá:

Identificar estructuras y conceptos avanzados del lenguaje SQL, mediante el uso de comandos para la definición, manipulación y control de datos, con el fin de conocer cómo se construyen consultas mayormente complejas durante el manejo y manipulación de datos e información de una base de datos.

Partes del lenguaje SQL


Data Definition Language (DDL)

Lenguaje de definición de datos. Es la parte de SQL que se emplea para generar las estructuras de los datos, creando tablas, índices, atributos de integridad referencial, en general, cualquier información que vaya a formar parte del catálogo o diccionario de la base de datos. Existen tres sentencias básicas en el sublenguaje DDL, las cuales son CREATE, ALTER y DROP.

Data Manipulation Language (DML)

Lenguaje de manipulación de datos. Se trata de la parte más conocida de SQL. Está formada por todas las sentencias que permiten seleccionar conjuntos de datos, eliminar información, actualizarla, filtrarla, agruparla.

En lo particular, señalaremos por el momento que existen cuatro sentencias básicas en el sublenguaje DML: SELECT, INSERT, UPDATE y DELETE, cuya funcionalidad es la siguiente.

•SELECT. Se utiliza para seleccionar campos y registros de la base de datos.
•INSERT. Se utiliza para agregar registros en una tabla de la base de datos.
•UPDATE. Se utiliza para modificar registros en una tabla de la base de datos.
•DELETE. Se utiliza para eliminar registros de una tabla base de datos.

Data Manipulation Language (DML)

Lenguaje de control de datos. Está formado por las sentencias de SQL destinadas a controlar el acceso a los datos, definiendo privilegios de los usuarios, administrar las transacciones. Las sentencias del sublenguaje DCL son COMMIT, ROLLBACK, GRANT y REVOKE.


Para el desarrollo de este tema, se toma como punto de referencia lo señalado por Rojas y Méndez (2016), por lo que presentamos algunas consideraciones de sintaxis, ejemplos de comandos y aspectos relacionados con los conceptos de creación de vistas, índices, secuencias en subconsultas y triggers, según se describe en la siguiente tabla.

: Esquema representando un modelo semántico


De manera complementaria, también nos referiremos a conceptos que serán de utilidad para construir sentencias SQL más complejas, como los siguientes.

Esquema representando un modelo Entidad-relación


Procesamiento de transacciones


Al igual que la inserción de datos, la modificación y eliminación son operaciones que se verán afectadas por las transacciones en curso que pudieran existir en el RDBMS, cualquier cambio que efectuemos se perderá si no terminamos ejecutando la sentencia COMMIT.

Ante operaciones potencialmente peligrosas como lo son la sustitución de datos por otros, o la eliminación de éstos, su inclusión en el contexto de una transacción puede evitarnos un serio disgusto. Por ello, se utilizan las sentencias COMMIT y ROLLBACK.

Tras iniciar una transacción, cualquier operación que efectuemos no será realmente confirmada hasta que no usemos la sentencia COMMIT. Esto significa que si de forma accidental, por un error, modificamos o eliminamos las filas inadecuadas, siempre podremos usar ROLLBACK para cancelar las operaciones que forman parte de la transacción.

Combinaciones (join)

Un join es una consulta que relaciona dos o más tablas para obtener un resultado que incluya datos de las tablas involucradas. Es posible efectuar combinaciones cruzadas entre tablas (cross join), combinaciones internas (inner join) y combinaciones externas (outer join). Veamos su descripción.



Cross join o producto cartesiano

Una combinación cruzada de tablas, o CROSS JOIN, consiste en cruzar cada una de las filas de la primera tabla con cada una de las filas de la segunda tabla, generando lo que se conoce como un producto cartesiano.

Inner join

El tipo más habitual de combinación es aquél en el que interesa obtener las filas de dos o más tablas que tienen un contenido equivalente en una determinada columna, también se conoce como uniones internas al expresarse en SQL como INNER JOIN.

Outer Join

A veces puede interesarnos la inclusión de todas las filas de la primera tabla y sólo las concordantes de la segunda; solamente las concordantes de la primera y todas las de la segunda o, incluso, todas las filas de ambas tablas, pero distribuidas de acuerdo con la relación entre columnas que se haya indicado.

Si tuviéramos una consulta LEFT OUTER JOIN, se conservan todas las filas de la tabla de la izquierda, desechando las que no son coincidentes en la tabla de la derecha. Si cambiamos el LEFT JOIN por un RIGHT JOIN, incluiríamos entonces en los resultados todas las filas de la tabla contraria.

Full Outer Join

También existe la posibilidad de recuperar tanto todas las filas de una entidad denominada E1 como todas las filas de otra entidad denominada E2, relacionadas a través de una entidad intermedia que resuelve la cardinalidad muchos a muchos E1-2, sencillamente sustituyendo el primer RIGHT OUTER JOIN por un FULL OUTER JOIN. De esta manera, la primer subconsulta, que tiene las filas de E1, se combina con la segunda E1-2, la cual es consecuencia de las tablas E1 y E2, sin eliminar fila alguna.



Subconsultas (self join)

Hay casos en los que los valores que han de utilizarse como referencia en una expresión condicional, para obtener las filas que se ajustan a un criterio, no pueden facilitarse directamente como valores constantes, sino que se encuentran en otra tabla de la base de datos. En este caso, lo que se hace es incluir como parte del predicado una subconsulta.

También pueden introducirse subconsultas en el predicado de una cláusula WHERE, mediante los elementos IN, ALL, ANY, SOME y EXISTS.

Adición de campos calculados


Además de columnas existentes en las tablas referenciadas en la cláusula FROM, la sentencia SELECT también permite la creación de columnas, normalmente calculadas a partir de las ya existentes y su inclusión en el conjunto de resultados. Podemos adicionar una secuencia de caracteres, operaciones con números, fechas, así como trabajar con el resultado de funciones y ciertas columnas especiales con las que cuentan los RDBMS..

Cálculos numéricos

El contenido de una columna puede ser el resultado de una operación aritmética, con independencia que en ella intervengan o no otras columnas de la tabla. Los operadores a utilizar son los habituales: +, -, *, y /, para efectuar una suma, una resta, una multiplicación o una división.

Mediante estos operadores y asumiendo que tuviésemos tablas con datos numéricos, podríamos calcular un porcentaje del precio de un producto, por ejemplo, un impuesto o descuento; sumar datos de varias columnas para hallar un total.

En la expresión aritmética pueden utilizarse múltiples operadores y los niveles de paréntesis que sean necesarios para alcanzar el resultado deseado.

Tipos de operadores


Los operadores son símbolos que permiten realizar operaciones relacionales o lógicas.

Operadores relacionales

Existen distintos tipos de condiciones de búsqueda, si bien el más simple —y seguramente de uso más frecuente— es aquel en el que se establece una determinada relación entre una columna de datos y un valor concreto, algo que se expresaría de la siguiente manera:

WHERE [Campo] operador valor

El operador será uno de los enumerados en la siguiente tabla, permitiendo buscar coincidencias entre el contenido de la columna y el valor, todos aquellos casos en el que el contenido de la columna puede considerarse menor que el valor de referencia, etcétera.



Operador Relación
= Igualdad entre contenido de la columna y valor
<> Desigualdad entre contenido de la columna y valor
<= Contenido de la columna menor o igual al valor
< Contenido de la columna menor que el valor
>= Contenido de la columna mayor o igual al valor
> Contenido de la columna mayor que el valor

Tabla. Operadores de relación de SQL

A la hora de utilizar una condición es necesario tener en cuenta que la columna, cuyo contenido va a evaluarse y el valor de referencia, deben de ser, necesariamente, del mismo tipo.

Operadores lógicos

En ocasiones, puede ser interesante usar más de una condición a la hora de filtrar las filas de una tabla, por ejemplo, para comprobar que se dan ciertas condiciones en dos o más columnas, de la siguiente manera:

WHERE [CampoDisponible] = ‘S’ OperadorLogico [CampoAutor] = ‘X’

Las dos expresiones no pueden disponerse, sin más, una detrás de la otra, sino que es necesario unirlas con uno de los operadores lógicos con que cuenta SQL. Según el operador elegido, el resultado final será TRUE, si ambas expresiones son ciertas en el caso de AND, o si lo es al menos una de ellas, al utilizar OR.

Otro operador lógico de que dispone SQL es NOT, que sirve no para unir dos expresiones, sino para invertir el sentido de la siguiente expresión:



arrojas=> select codigo, titulo from libros where disponible = "S" and not autor =
"Charte, Francisco";

Con esta sentencia obtenemos la lista de los libros disponibles que no son de un cierto autor cuyo nombre indicamos.

Expresiones con paréntesis


Al usar los operadores lógicos para componer una condición formada por varias expresiones, puede darse el caso de que el orden de evaluación predeterminado no genere los resultados esperados en un principio.

Un ejemplo se muestra en la siguiente consulta.



WHERE disponible = "S" AND
autor = "autor1" OR autor =


En este caso, el resultado no es el esperado. Sería una lista con los libros de autor1 que se encuentren disponibles y todos los libros de autor2, estén o no disponibles. Esto es así porque el operador AND une las dos primeras expresiones.

El orden de evaluación puede modificarse fácilmente mediante el uso de paréntesis, como haríamos en cualquier expresión matemática.

WHERE disponible = ‘S’ AND (autor = ‘autor1’ OR autor = autor2’)

Con esta condición sí generaríamos la tabla de resultados requerida, con los libros que están disponibles y pertenecen a uno u otro autor.



Agrupamiento

La cláusula GROUP BY para agrupar una serie de filas en subconjuntos, según el contenido de una o más de sus columnas, o bien, de una expresión obtenida a partir de ellas, emplearemos la cláusula GROUP BY. Ésta aparecerá tras SELECT, FROM y WHERE, es decir, partimos de una consulta que extraería una serie de filas y después usamos GROUP BY para dividirlas en grupos.

Tras GROUP BY, al igual que después de ORDER BY, dispondremos del nombre de la columna o columnas por las que se agrupará, o bien, la expresión o cálculo que servirá como base de la agrupación. A diferencia de ORDER BY, sin embargo, GROUP BY no admite que se utilicen los alias de columnas ni tampoco los indicadores de posición relativa.

Funciones de agregación

La cláusula GROUP BY no encuentra su mayor sentido hasta que no se conozcan las funciones de agregación, aquellas que nos permitan operar sobre las columnas de cada grupo de filas para efectuar algún tipo de totalización. En la siguiente tabla se indica el nombre y finalidad de cada una de estas funciones.

Función Operación que efectúa
COUNT Cuenta el número de filas que contienen un valor no nulo en la columna indicada y lo devuelve como resultado.
AVG Halla el valor medio de los valores existentes en la columna indicada y lo devuelve como resultado.
MIN Devuelve el mínimo valor existente en la columna indicada.
MAX Devuelve el máximo valor existente en la columna indicada.
SUM Calcula la suma de los valores contenidos en la columna indicada y lo devuelve como resultado.

Todas estas funciones deben ir seguidas de unos paréntesis entre los cuales se indicará el nombre del campo, sobre la que se llevará a cabo la operación descrita.



Actividad 1. Sentencias SQL

Los comandos que forman parte del Data Control Language y los conceptos SQL se requieren para la ceración de vistas e índices. En la siguiente actividad se verificará que tengas claridad con los comandos.

Actividad 2. Funciones de agregación

Las funciones de agregación son aquellas que nos permiten operar sobre las columnas de cada grupo de filas para efectuar algún tipo de totalización.

Autoevaluación. Sentencias SQL

Identificar el tipo de unión que se utiliza en la definición de la consulta SQL y las transacciones, permitirá obtener la información requerida. Por ello, de las sentencias que a continuación se presentan, deberás completar con la sentencia SQL correcta.

Fuentes de información

Básicas

Bibliografía

Kendall, K. E. y Kendall, J. E. (2005). Análisis y diseño de sistemas (6.ª ed.). México: Prentice Hall.

Coronel, C., Morris, S. y Rob, P. (2011). Bases de datos. Diseño, implementación y administración. (J. H. Romo, trad.). México: Cengage Learning.

Documentos electrónicos

Blázquez, M. (2014, 20 de febrero). Fundamentos y Diseño de Bases de Datos [Mensaje en blog]. Consultado el 30 de octubre de 2017 de http://ccdoc-basesdedatos.blogspot.mx/2013/02/modelo-entidad-relacion-er.html

Rojas, A. y Méndez, C. (2016). Desarrollo de aplicaciones en manejadores de bases de datos relacionales. Apunte electrónico [Versión electrónica]. México: SUAYED-UNAM-FCA. Consultado el 07 de noviembre de 2017 de http://fcasua.contad.unam.mx/apuntes/interiores/docs/20172/informatica/5/apunte/LI_1547_18116_A_Desarrollo_aplicaciones_BD_v1.pdf

Complementarias

Documentos electrónicos

Mendelzon, A. (2000). Introducción a las bases de datos relacionales. México: Pearson Educación.

Connolly, T. M. y Begg, C. (2005). Sistemas de bases de datos (4.ª ed.). México: Pearson Educación-Addison Wesley.

Cómo citar

Texto correspondiente a esta sección.