Bases De Datos I. Tema Vi. Lenguaje De Consulta Estructurado

Tema VI. Lenguaje de Consulta

Estructurado SQL

::Universidad del Mar::

::Campus Puerto Escondido ::

Bases de Datos I MTI Remedios Fabián Velasco Ver. 1.1 Cuarto Semestre Licenciatura en Informática

Temario

6 Lenguaje de consulta estructurado (SQL). 6.1 Introducción.

6.1.1 ¿Qué es el SQL? 6.1.2 Características del lenguaje. 6.1.3 Cómo interpretar un diagrama sintáctico.

6.2 Consultas simples.

6.2.1 Sintaxis de la SELECT (para consultas simples). 6.2.2 La tabla origen (cláusula FROM). 6.2.3 Selección de columnas. 6.2.4 Ordenación de las filas (ORDER BY). 6.2.5 Selección de filas. 6.2.6 Las cláusulas DISTINCT / ALL. 6.2.7 La cláusula TOP. 6.2.8 La cláusula WHERE. 6.2.9 Condiciones de selección. 6.2.10 Expresiones válidas. 6.2.11 Operadores lógicos. 6.2.12 Caracteres comodines.

6.3 Las consultas multitabla.

6.3.1 La unión de tablas. 6.3.2 La composición de tablas. 6.3.3 El operador UNION. 6.3.4 El producto cartesiano. 6.3.5 El INNER JOIN. 6.3.6 El LEFT/RIGHT JOIN.

6.4 Las consultas de resumen.

6.4.1 Las funciones de columna. 6.4.2 Selección en el origen de datos. 6.4.3 Origen múltiple. 6.4.4 La cláusula GROUP BY. Universidad del Mar 07/2008 Bases de Datos I 6.4.5 La cláusula HAVING.

MTI Remedios Fabián Velasco

Temario 6.5 Las subconsultas. 6.5.1 Definiciones. 6.5.2 Referencias externas. 6.5.3 Anidar subconsultas. 6.5.4 Subconsulta en la lista de selección. 6.5.5 Subconsulta en la cláusula FROM. 6.5.6 Subconsulta en las cláusulas WHERE y HAVING. 6.5.7 Condiciones de selección con subconsultas. 6.6 Actualización de datos. 6.6.1 Insertar una fila INSERT INTO VALUES. 6.6.2 Insertar varias filas INSERT INTO SELECT. 6.6.3 Insertar filas en una tabla nueva SELECT INTO. 6.6.4 Modificar el contenido de las filas UPDATE. 6.6.5 Borrar fila DELETE. 6.6.6 Conceptos básicos de integridad referencial. 6.7 Tablas de referencias cruzadas. 6.7.1 La sentencia TRANSFORM. 6.7.2 Las columnas dinámicas. 6.7.3 Las columnas fijas. 6.8 El DDL (lenguaje de definición de datos). 6.8.1 La sentencia CREATE TABLE. 6.8.2 La sentencia ALTER TABLE. 6.8.3 La sentencia DROP TABLE. 6.8.4 La sentencia CREATE INDEX. 6.8.5 La sentencia DROP INDEX. 6.8.6 Integridad referencial. 6.8.7 Los índices. 6.8.8 Tipos de datos. Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.1 Introducción. 1974 D. Chamberlin Laboratorio San José de IBM Structured English Query Language o SEQUEL. 1976 SEQUEL/2 versión mejorada renombrada a SQL (sicuel) por razones legales

IBM desarrolló prototipo basado en SEQUEL/2, denominado System R, el propósito era validar la factibilidad del sistema relacional. SQL es resultado del desarrollo de este proyecto (System R) Las raíces de SQL se encuentran en el lenguaje SQUARE (Specifying Queries As Relational Expressions, especificación de consultas como expresiones relacionales) anterior al proyecto System R. SQUARE fue diseñado como lenguaje de investigación para implementar el álgebra

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.1 Introducción. A finales de los 70’s Oracle Corporation desarrolló el SMBD Oracle, que fue la primera versión implementación comercial de un SMBD relacional basado en SQL. 1982 ANSI comenzó a trabajar en un lenguaje de bases de datos relacional (RDL, Relational Database Language). RDL fue abandonado en 1984 y el borrador del estándar adoptó una forma parecida a las implementaciones existentes de SQL). 1986 ANSI (American Nacional Standard Institute) definió un estándar para SQL que fue adoptado en 1987 como estándar internacional por ISO (International Organization for Standardization). Actualmente se utiliza en centenares de SMBD.

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.1.1. ¿Qué es SQL? Lenguaje estándar para las bases de datos relacionales El SQL (Structured Query Language), lenguaje de consulta estructurado, es un lenguaje que surgió de un proyecto de investigación de IBM para el acceso a bases de datos relacionales. Actualmente se ha convertido en un estándar  de lenguaje de bases de datos, y la mayoría de los sistemas de bases de datos lo soportan, desde sistemas para ordenadores personales, hasta grandes ordenadores. Definición del lenguaje SQL hecha por ISO. Lenguaje portable, debe ajustarse a un estándar reconocido SQL es un ejemplo de lenguaje orientado a la transformación, diseñado para usar relaciones con el fin de transformar los datos de entrada en las

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.1.1. ¿Qué es SQL? Como su nombre indica, el SQL nos permite realizar consultas a la base de datos. Pero SQL además realiza funciones de definición, control y gestión de la base de datos. Las sentencias SQL se clasifican según su finalidad dando origen a tres ‘lenguajes’ o mejor dicho sublenguajes:

DDL (Data Description Language), lenguaje de definición de datos, incluye órdenes para definir, modificar o borrar las tablas en las que se almacenan los datos y de las relaciones entre estas. (Es el que más varia de un sistema a otro) DCL (Data Control Language), lenguaje de control de datos, contiene elementos útiles para trabajar en un entorno multiusuario, en el que es importante la protección de los datos, la seguridad de las tablas y el establecimiento de restricciones en el acceso, así como elementos para coordinar la compartición de datos por parte de usuarios concurrentes, asegurando que no interfieren unos con otros. DML (Data Manipulation Language), lenguaje de manipulación de datos, nos permite recuperar los datos almacenados en la base de datos y también incluye órdenes para permitir al usuario actualizar la base de datos añadiendo nuevos datos, suprimiendo datos antiguos o modificando datos previamente almacenados.

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.1.1. ¿Qué es SQL? Idealmente debe permitir: CREAR la base de datos y las estructuras de relación Realizar TAREAS básicas DE GESTION DE DATOS, como la inserción, modificación y borrado de los datos de las relaciones. Realizar CONSULTAS tanto simples como complejas.

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.1.2. Características del lenguaje Sintaxis y estructura fácil Lenguaje no procedimental, SQL no requiere que se especifique el método de acceso a los datos. SQL es de formato libre. La estructura de los comandos esta compuesta por palabras inglesas normales, por ejemplo: CREATE TABLE, INSERT, SELECT. Por ejemplo: CREATE TABLE Personal (noPersonal VARCHAR(5) PRIMARY KEY, nombrePersonal VARCHAR(15), apellidoPersonal VARCHAR(15), salario DECIMAL(7,2); INSERT INTO Personal VALUES (‘SG16’, ‘Carlos’, ‘Ríos’, 6300); SELECT noPersonal, apellidoPersonal, salario FROM Personal WHERE salario > 5000; Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.1.2. Características del lenguaje Una sentencia SQL es como una frase (escrita en inglés) con la cual, decimos lo que queremos obtener y de donde obtenerlo. Todas las sentencias empiezan con un verbo (palabra reservada que indica la acción a realizar), seguido del resto de cláusulas, algunas obligatorias y otras opcionales que completan la frase. Todas las sentencias siguen una sintaxis para que se puedan ejecutar correctamente, para describir esa sintaxis utilizaremos un diagrama sintáctico.

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.1.3. Cómo interpretar un diagrama sintáctico Diagrama sintáctico: Representación desentencia sintaxis de las se Una válida cláusulas SQL construye siguiendo la línea a través del diagrama hasta el punto que marca el final. Las líneas se siguen de izquierda a derecha y de arriba abajo. Cuando se quiere alterar el orden normal  se indica con una flecha, por ejemplo: el uso de la coma (,) si existe más de un elemento a listar.

Las palabras que aparecen en mayúsculas son palabras reservadas se tienen que poner tal cual y no se pueden utilizar para otro fin, por ejemplo, en el diagrama de la figura tenemos las palabras reservadas SELECT, ALL, DISTINCT, FROM, WHERE. Las palabras en minúsculas son variables que el usuario deberá sustituir por un dato concreto. En el diagrama tenemos Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.1.3. Cómo interpretar un diagrama sintáctico ¿Cómo se interpretaría el diagrama sintáctico de la figura? Palabras reservadas Palabras opcionales

Campos o columnas

SELECT ALL nombre, apellidos Tabla

FROM alumnos

Condición

WHERE edad=19

Puedes terminar aquí o bien incluir la cláusula WHERE

Iniciar por la palabra SELECT, después puedes poner ALL o bien DISTINCT o nada, a continuación un nombre de columna (nombre), o varios separados por comas (nombre, apellidos), posteriormente la palabra FROM y una expresión-tabla (alumnos), y por último de forma opcional puedes incluir la cláusula WHERE con una condición-debúsqueda (edad=19).

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.1.3. Cómo interpretar un diagrama sintáctico Otros ejemplos correctos: SELECT ALL col1,col2,col3 FROM tabla SELECT col1,col2,col3 FROM tabla SELECT DISTINCT col1 FROM tabla SELECT col1,col2 FROM tabla WHERE col2 = 0

Todas estas sentencias se podrían escribir y no darían lugar a errores sintácticos. Cuando una palabra opcional está subrayada, indica que ese es el valor por defecto (el valor que se asume si no se pone nada). En el ejemplo anterior las dos primeras sentencias son equivalentes (en el diagrama ALL aparece subrayada). Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.2. Consultas Simples La estructura básica de una expresión SQL consiste en tres cláusulas: select, from y where. El resultado de una consulta SQL es también una relación. Considérese una consulta simple, usando la Sintaxis SELECT. Una consulta típica en SQL tiene la forma SELECT a1,a2,a3,aN FROM r1, r2, rN WHERE P

Cada ai representa un atributo, y cada ri una relación. P es un predicado.

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.2. Consultas Simples La cláusula SELECT corresponde con la operación de Proyección del algebra relacional. Se usa para obtener una relación de atributos deseados en el resultado de una consulta. La cláusula FROM se corresponde con la operación producto cartesiano del álgebra relacional. Genera una lista de relaciones que deben ser analizadas por la evaluación de la expresión. La cláusula WHERE se corresponde con el predicado selección del álgebra relacional. Es un predicado que engloba a los atributos de las relaciones que aparecen en la cláusula SQL FROM. Algebra Relacional Select a1,a2,a3,aN From r1, r2, rN Where P Bases de Datos I

Πa1,a2,a3,aN(ϭ P (r1 X r2 X rN))

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.2. Consultas Simples SELECT [DISTINCT] [ALL] {* | [expresionColumna AS nuevoNombre]] [,…] } FROM NombreTabla [alias] [,…] [WHERE condición] [GROUP BY listaColumnas] [HAVING condición] [ORDER BY listaColumnas]

SELECT Su propósito consiste en extraer y visualizar datos de una o más tablas de la base de datos. Especifica que columnas deben aparecer en la salida DISTINCT elimina duplicados AS Opción de renombramiento campo calculado o derivado

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.2. Consultas Simples FROM especifica la tabla o tablas que hay que usar WHERE filtra las filas de acuerdo con alguna condición GROUP BY forma grupos de filas que tengan el mismo valor de columna HAVING filtra los grupos de acuerdo con alguna condición ORDER BY especifica el orden de la salida

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.2.1 Sintaxis de la SELECT (para consultas simples). SELECT Su propósito consiste en extraer y visualizar datos de una o más tablas de la base de datos. Especifica que columnas deben aparecer en la salida: AS Opción de renombramiento campo calculado o derivado

SELECT salario/12 AS salarioMes FROM personal;

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.2.2 La tabla origen (cláusula FROM). FROM especifica la tabla o tablas que hay que usar From r1, r2, rN = (r1 X r2 X rN)

SQL forma el producto cartesiano de las relaciones incluidas en la cláusula FROM SELECT {* | [expresionColumna AS nuevoNombre]] [,…] } FROM NombreTabla [alias] [,…] Selecciona los nombres del producto cartesiano: alumnos X profesores SELECT a.nombre, b.nombre FROM alumnos a, profesores b

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.2.3 Selección de columnas. Extracción de todas las filas: SELECT * FROM nomTabla;

Extraer una serie de columnas específicas de todas las filas: SELECT nomAlumno, edadAlumno FROM estudiante;

Lista con valores no duplicados: SELECT DISTINCT nomAlumno FROM estudiante;

Campos calculados: SELECT COUNT nomAlumno AS numAlumnos FROM estudiante; Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.2.4 Ordenación de las filas (ORDER BY). ORDER BY especifica el orden de la salida Ordenación ASC ascendente o DESC descendente SELECT noPersonal, nomPersonal, apellidoPersonal, salario FROM Personal ORDER BY salario DESC;

La cláusula anterior ordenará los números, nombres, apellidos y salario del personal de acuerdo al campo salario, iniciando con el mayor y terminando con el menor. Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.2.5 Selección de filas. WHERE se utiliza cuando se requiera restringir las filas que hay que extraer, por ejemplo: Extraer todos los nombres de la tabla alumnos (sin restricción) SELECT nombres FROM alumnos; Extraer todos los nombres de la tabla alumnos cuya edad sea mayor a 18 años (con restricción) SELECT nombres FROM alumnos WHERE edad >= 18; Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.2.6 Las cláusulas DISTINCT / ALL. SQL forma el producto cartesiano de las relaciones incluidas en la cláusula FROM, lleva a cabo la selección del álgebra relacional usando el predicado de la cláusula WHERE y entonces proyecta el resultado sobre los atributos de la cláusula SELECT. Los lenguajes formales de consulta están basados en la noción matemática de que una relación es un conjunto. Así, nunca aparecen tuplas duplicadas en las relaciones. En la práctica, la eliminación de duplicados consume tiempo. SELECT [DISTINCT] [ALL] {* | [expresionColumna AS nuevoNombre]] [,…] } FROM NombreTabla [alias] [,…];

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.2.6 Las cláusulas DISTINCT / ALL. SQL permite duplicados en las relaciones, así como en el resultado. En aquellos casos donde se quiera forzar la eliminación de duplicados, se insertará la palabra reservada DISTINCT después de SELECT. DISTINCT elimina duplicados. SELECT DISTINCT nomSucursal FROM préstamo

SQL permite usar la palabra clave ALL para especificar explícitamente que no se eliminan los duplicados ALL especifica duplicados (default) SELECT ALL nomSucursal FROM préstamo Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.2.7 La cláusula TOP. La cláusula TOP permite sacar las n primeras filas de la tabla origen. No elige entre valores iguales. Siempre se guía por una columna de ordenación, la que aparece en la cláusula ORDER BY o en su defecto la clave principal de la tabla. Por ejemplo, para saber los dos empleados más antiguos de la empresa. SELECT TOP 2 numemp, nombre FROM empleado ORDER BY contrato;

Lista el código y nombre de los empleados ordenándolos por fecha de contrato, sacando únicamente los dos primeros (serán los dos más antiguos). SELECT TOP 10 PERCENT nombre FROM empleado ORDER BY contrato

Bases de Datos I

Lista el nombre de los empleados ordenándolos por fecha de contrato, mostrando únicamente un 10% del total de empleados. Como tenemos 10 empleados, sacará el primero, si fueran 100 empleados sacaría los 10 primeros. Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.2.8 La cláusula WHERE. WHERE filtra las filas de acuerdo con alguna condición Para restringir las filas que hay que extraer , se hace mediante la cláusula WHERE seguida de una condición de búsqueda que especifica las filas que hay que extraer. SELECT nombre FROM alumnos WHERE materia = ‘Bases de Datos I’; SELECT nombre FROM alumnos WHERE carrera IN (‘Informática’);

Bases de Datos I

SELECT nombre FROM empleados

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.2.9 Condiciones de selección. Condiciones basadas en: Comparación. Compara el valor de una expresión con el valor de otra Rango. Comprueba si el valor de una expresión cae dentro del rango específico de valores Pertenencia a conjunto. Comprueba si el valor de una expresión coincide con uno de lo valores de un cierto conjunto. Correspondencia de patrones. Comprueba si una cadena de caracteres se ajusta a un patrón específicado. Nulo. Comprueba si una columna tiene un valor nulo (desconocido). Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.2.9 Condiciones de selección. Rango. Comprueba si el valor de una expresión cae dentro del rango específico de valores Condiciona a una búsqueda de rango BETWEEN AND, NOT BETWEEN. Esta condición hace una búsqueda indicada en los puntos extremos del rango. La función NOT sirve para buscar a los que se encuentren fuera del rango especificado. Pertenencia a conjunto. Comprueba si el valor de una expresión coincide con uno de lo valores de un cierto conjunto. IN / NOT IN Comprueba si el valor de los datos se corresponde con uno de los valores especificados en una determinada lista. SELECT noPersonal, nomPersonal, apellidoPersonal, puestoPersonal Bases de Datos I

FROM Personal

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.2.9 Condiciones de selección. Correspondencia de patrones. Comprueba si una cadena de caracteres se ajusta a un patrón específicado. LIKE / NOT LIKE símbolos especiales para correspondencia de patrones campo LIKE ‘H%’ primer carácter debe ser H campo LIKE ‘H___’ de palabras de 4 posiciones el carácter uno es H campo LIKE ‘%e’ el ultimo carácter de una secuencia debe ser e, incluye ‘e’ campo LIKE ‘%Centro%’ cualquier cadena que incluya la palabra centro campo NOT LIKE ‘H%’ el primer carácter no debe ser H campo LIKE ‘15#%’ ESCAPE ‘#’

Nulo. Buscar valores que sean o no nulos Universidad Mar 07/2008 Búsqueda IS NULL / NOT IS del NULL

Bases de Datos I

MTI Remedios Fabián Velasco

6.2.10 Expresiones válidas. Las reglas para evaluar una expresión condicional son: Las expresiones se evalúan de izquierda a derecha Primero se evalúan subexpresiones contenidas entre corchetes o paréntesis NOT se evalúa antes que AND y OR AND se evalúa antes que OR Se recomienda utilizar paréntesis para eliminar cualquier posible ambigüedad. Pueden generarse predicados más complejos utilizando operadores lógicos NOT, AND, OR para mostrar un orden de evaluación. Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.2.11 Operadores lógicos. En SQL están disponibles los siguientes operadores simples de comparación: = igual <> ó != distinto < menor que > mayor que <= menor o igual que >= mayor o igual que

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.2.12 Caracteres comodines. % secuencia de 0 o más caracteres _ cualquier carácter individual ‘___%’ cualquier cadena con al menos tres caracteres ‘%cer%’ cualquier cadena que contenga cer

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.3 Las consultas multitabla. Las operaciones de union, intersect y except operan sobre relaciones y corresponden a las operaciones del álgebra relacional ⋃, ⋂ y –. Al igual que la unión, intersección y diferencia de conjuntos en el álgebra relacional, las relaciones que participan en las operaciones han de ser compatibles; esto es, deben tener el mismo conjunto de atributos.

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.3.1 La unión de tablas. Combina los resultados de dos o más consultas en un solo conjunto de resultados que incluye todas las filas que pertenecen a las consultas de la unión. La operación UNION es distinta de la utilización de combinaciones de columnas de dos tablas. A continuación se muestran las reglas básicas para combinar los conjuntos de resultados de dos consultas con UNION: El número y el orden de las columnas deben ser idénticos en todas las consultas. Los tipos de datos deben ser compatibles. Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.3.1 La unión de tablas. Esta operación se utiliza cuando tenemos dos tablas con las mismas columnas y queremos obtener una nueva tabla con las filas de la primera y las filas de la segunda. En este caso la tabla resultante tiene las mismas columnas que la primera tabla (que son las mismas que las de la segunda tabla).

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.3.2 La composición de tablas. La composición de tablas consiste en concatenar filas de una tabla con filas de otra. En este caso obtenemos una tabla con las columnas de la primera tabla unidas a las columnas de la segunda tabla, y las filas de la tabla resultante son concatenaciones de filas de la primera tabla con filas de la segunda tabla.

Los tipos de composición de tablas son:

Bases de Datos I

El producto cartesiano El INNER JOIN El LEFT / RIGHT JOIN

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.3.3 El operador UNION. La unión de tablas. El operador UNION: R1 ⋃ R2 (SELECT * FROM r1) UNION (SELECT * FROM r2)

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.3.4 El producto cartesiano. La cláusula FROM define por sí misma un producto cartesiano de las relaciones que aparecen en la cláusula. Escribir una expresión SQL para la reunión natural es una tarea relativamente fácil, puesto que la reunión natural se define en términos de un producto cartesiano, una selección y una proyección. La expresión del álgebra relacional se escribe como sigue: ΠnomCliente, noPrestamo,importe (prestatario prestamo) para la consulta «Para todos los clientes que tienen un préstamo en el banco, obtener los nombres, números de préstamo e importes». Esta consulta puede escribirse en SQL como: SELECT nomCliente, prestatario.noPrestamo, importe FROM prestatario, prestamo WHERE prestatario.noPrestamo = prestamo.noPrestamo Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.3.5 El INNER JOIN. Además de proporcionar el mecanismo básico de producto cartesiano para reunir tuplas de relaciones, permitido en versiones anteriores, SQL también proporciona varios mecanismos para reunir relaciones, incluyendo reuniones condicionales y reuniones naturales, así como varias formas de reunión externa. Estas operaciones adicionales se usan a menudo como subconsultas dentro de la cláusula FROM. Ejemplo simple de reunión interna: prestamo inner join prestatario on prestamo.noPrestamo = prestatario.noPrestamo

La expresión calcula la reunión zeta de las relaciones préstamo y prestatario, donde la condición de reunión es: prestamo.noPrestamo = prestatario.noPrestamo

Los atributos del resultado son los atributos del lado izquierdo de la relación, seguidos por los del lado derecho de la misma.

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.3.6 El LEFT/RIGHT JOIN. El tipo de relación reunión externa por la derecha (right outer join) es simétrico al de reunión externa por la izquierda (left outer join). Las tuplas de la relación del lado derecho que no encajen con ninguna tupla de la relación del lado izquierdo se rellenan con valores nulos y se añaden al resultado de la reunión externa por la derecha. La siguiente expresión es un ejemplo de la combinación de la condición de reunión natural con el tipo de reunión externa por la derecha. prestamo natural right outer join

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.4 Las consultas de resumen. Una fila de una consulta de resumen corresponde a un resumen de varias filas de la tabla origen. El resultado de una consulta de resumen tienen una naturaleza distinta a las filas de las demás tablas resultantes de consultas, ya que corresponden a varias filas de la tabla origen. Las consultas de resumen introducen dos nuevas cláusulas a la sentencia SELECT, la cláusula GROUP BY y la cláusula HAVING, son cláusulas que sólo se pueden utilizar en una consulta de resumen, se tienen que escribir entre la cláusula WHERE y la cláusula ORDER BY y tienen la siguiente sintaxis:

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.4 Las consultas de resumen. Ejemplo: Primer tabla muestra el total de ventas por oficina y región. La segunda muestra el resumen de ventas por región

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.4 Las consultas de resumen. Encuentra todos los empleados que ganan más del salario promedio de todos los empleados de la empresa SELECT nombre_empleado FROM trabajo T WHERE salario > (SELECT AVG (salario) FROM Trabajo S WHERE T.nomEmpresa = S.nomEmpresa)

Obtener la media de saldos de las cuentas de la sucursal Navacerrada SELECT AVG (saldo) FROM cuenta WHERE nomSucursal = ‘Navacerrada’

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.4.1 Las funciones de columna. En la lista de selección de una consulta de

resumen aparecen funciones de columna también denominadas funciones de dominio agregadas.

Una función de columna se aplica a una columna y obtiene un valor que resume el contenido de la columna. Tenemos las siguientes funciones de columna: Media: avg Mínimo: min Máximo: max Total: sum Cuenta: count

El argumento de la función indica con qué valores se tiene que operar, por eso expresión suele ser un nombre de columna, columna que contiene los valores a resumir, pero también

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.4.2 Selección en el origen de datos. Para eliminar del origen de datos algunas filas no requeridas, es necesario incluir la cláusula WHERE que ya conocemos después de la cláusula FROM. Ejemplo: Obtener el acumulado de ventas de los empleados de la oficina 12. SELECT SUM(ventas) FROM empleados WHERE oficina = 12; Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.4.3 Origen múltiple. Si los datos que se necesitan utilizar para obtener el resumen se encuentran en varias tablas, se forma el origen de datos adecuado en la cláusula FROM como si fuera una consulta multitabla normal. Ejemplo: Obtener el importe total de ventas de todos los empleados y el mayor objetivo de las oficinas asignadas a los empleados: SELECT SUM(empleados.ventas), MAX(objetivo) FROM empleados LEFT JOIN oficinas ON empleados.oficina=oficinas.oficina

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.4.4 La cláusula GROUP BY. Existen situaciones 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; esto se especifica en SQL usando la cláusula group by. El atributo o atributos especificados en la cláusula group by se usan para formar grupos. Las tuplas con el mismo valor en todos los atributos especificados en la cláusula group by se colocan en un grupo. Ejemplo, considérese la consulta «Obtener el saldo medio de las cuentas de cada sucursal». SELECT nomSucursal, AVG(saldo) FROM cuenta GROUP BY nomSucursal;

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.4.4 La cláusula GROUP BY. Una consulta con una cláusula GROUP BY se denomina consulta agrupada ya que agrupa los datos de la tabla origen y produce una única fila resumen por cada grupo formado. Ejemplo: obtener la suma de las ventas de

las oficinas agrupadas por región y ciudad: SELECT SUM(ventas) FROM oficinas GROUP BY region,ciudad;

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.4.5 La cláusula HAVING. A veces es más útil establecer una condición que se aplique a los grupos que una que se aplique a las tuplas. Por ejemplo, podemos estar interesados sólo en aquellas sucursales donde el saldo medio de cuentas es superior a $1,200. Esta condición no es aplicable a una única tupla; se aplica a cada grupo construido por la cláusula group by.

Para expresar este tipo de consultas se utiliza la cláusula having de SQL. Los predicados de la cláusula having se aplican después de la formación de grupos, de modo que se pueden usar las funciones de agregación. Esta consulta se expresa en SQL del modo siguiente:

Bases de Datos I

SELECT nomSucursal, avg (saldo) FROM cuenta GROUP BY nomSucursal Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

6.4.5 La cláusula HAVING. La cláusula HAVING nos permite seleccionar filas de la tabla resultante de una consulta de resumen. Ejemplo: Queremos saber las oficinas con un promedio de ventas de sus empleados mayor que 50,000.00 pesos. SELECT oficina FROM empleados GROUP BY oficina HAVING AVG(ventas) > 50000

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco

Bibliografía utilizada Libros: Fundamentos de Bases de Datos. Abraham Silberschatz, Henry F. Korth, S. Sudarshan Manual MySQL incorporado en la aplicación

Bases de Datos I

Universidad del Mar 07/2008

MTI Remedios Fabián Velasco