SQL ALGEBRA RELACIONAL

Práctica 3:

Lenguaje SQL

1ª Parte: Manipulación de

Bases de Datos

Silvia Abrahão - DSIC

curso 2010/2011

Objetivos

• Presentar la sintaxis del lenguaje SQL (sólo del Lenguaje de Manipulación).

• Ver algunos ejemplos sencillos para clarificar la semántica del SQL.

• Presentar las bases de datos CICLISMO, MÚSICA y BIBLIOTECA.

• Realizar de menor a mayor complejidad consultas SQL sobre dichas bases de datos.

Se presentan las instrucciones que se pueden ejecutar desde un intérprete de SQL, lo que se denomina SQL interactivo.

SQL es un lenguaje muy expresivo y, en general, permite muchas formas de expresar las mismas órdenes.

Las cuatro instrucciones que componen el lenguaje de manipulación de datos son las siguientes:

  SELECT: permite la declaración de consultas para la recuperación de información de una o más tablas de una base de datos.

  INSERT: realiza la inserción de una o varias filas sobre una tabla.

  DELETE: permite efectuar el borrado de una o varias filas de una tabla.

  UPDATE: realiza una modificación de los valores de una o más columnas de una o varias filas de una tabla.

Lenguaje de Manipulación del SQL

1.1. Consultas: instrucción SELECT

SELECT [ALL | DISTINCT] comalista_item_seleccionado | * FROM comalista_referencia_tabla

[WHERE expresión_condicional] [GROUP BY comalista_referencia_col] [HAVING expresión_condicional] [ORDER BY comalista_referencia_col]

  comalista_item_seleccionado: información a obtener de la base de datos.   FROM comalista_referencia_tabla: especifica de qué tablas se obtiene la

información buscada.

  WHERE expresión_condicional: expresa una condición que deben cumplir las filas de la consulta resultante.

  GROUP BY comalista_referencia_col: permite formar consultas agrupadas para extraer información global sobre los grupos formados.

1.1.1. Condiciones en consultas simples

SELECT [ALL | DISTINCT] comalista_ítem_seleccionado | * FROM tabla

[WHERE expresión_condicional] [ORDER BY comalista_referencia_col]

  ALL : Permite la aparición de filas idénticas (valor por defecto).   DISTINCT: No permite la aparición de filas idénticas.

  La expresión_condicional está formada por un conjunto de predicados combinados con las conectivas lógicas AND, OR y NOT.

  Los predicados utilizados permiten comparar columnas: predicados de comparación: =, <>, >, <, >=, <=.

predicado LIKE: permite comparar una tira de caracteres con un patrón. predicado BETWEEN: permite comprobar si un escalar está en un rango. predicado IN: permite comprobar si el valor está dentro de un conjunto. predicado IS NULL: permite comprobar si el valor es nulo.

EQUIPO(nom_eq: d_eq, director: d_dir) Clave Primaria: {nom_eq}

CICLISTA(dorsal: d_dor, nombre: d_nom, edad: d_edad, nom_eq: d_eq)) Clave Primaria: {dorsal}

CAj: {nom_eq} hace referencia a EQUIPO VNN: {nom_eq}

ETAPA(nºetapa: d_nº, km: d_km, salida: d_sal, llegada: d_lleg, dorsal: d_dor) Clave Primaria: {nºetapa}

CAj: {dorsal} hace referencia a CICLISTA

PUERTO(nombre:d_nom,altura:d_alt,categoría:d_cat, nºetapa:d_nº,dorsal: d_dor) Clave Primaria: {nombre}

CAj: {nºetapa} hace referencia a ETAPA CAj: {dorsal} hace referencia a CICLISTA VNN: {nºetapa}

MAILLOT(código: d_código, tipo: d_tipo, premio: d_pre, color: d_col) Clave Primaria: {código}

LLEVAR(dorsal: entero, nºetapa: d_nº, código: d_código) Clave Primaria: {nºetapa, código}

CAj: {nºetapa} hace referencia a ETAPA CAj: {dorsal} hace referencia a CICLISTA CAj: {código} hace referencia a MAILLOT VNN: {dorsal}

netapa km salida llegada dorsal Etapa nomeq director Equipo nompuerto altura categoria pendiente netapa dorsal Puerto dorsal nombre edad nomeq Ciclista dorsal netapa codigo Llevar codigo tipo premio color Maillot

Ciclismo

Esquema de Prácticas.

EJEMPLO: Obtener elnombre y la alturade todos lospuertos

de 1ª categoría.

1. ¿En qué tablas se encuentra la información? 2. ¿Qué condición deben cumplir las filas resultantes? 3. ¿Que información queremos visualizar?

4. ¿Queremos ordenar el resultado por alguna columna?

SELECT

nombre, altura

FROM

Puerto

EJEMPLO: Obtener el nombre y la edad de todos los ciclistas.

SELECT nombre, edad FROM Ciclista;

EJEMPLO: Obtener el nombre de los ciclistas cuya edad está entre 20 y 30 años.

SELECTnombreFROMCiclista

WHEREedadBETWEEN20AND30;

(*) El predicado BETWEEN es equivalente a una condición con comparaciones de la siguiente forma:

exp between exp₁ and exp₂ ≡ (exp >= exp1) and (exp <=

exp₂)

EJEMPLO: Obtener el número de las etapas donde el nombre de la ciudad de llegada tenga por segunda letra una “O” o donde el nombre de la ciudad de salida lleve dos o más ‘A’s.

SELECT netapa FROM Etapa

WHERE llegada LIKE ‘_O%’ OR salida LIKE ‘%A%A%’;

EJEMPLO: Obtener el nombre de los puertos de 1ª, 2ª o 3ª categoría. SELECT nompuerto FROM Puerto

WHERE categoría IN ( 1, 2, 3 ) ;

(*) También el predicado IN es derivado y la expresión equivalente es: exp in (exp₁, exp₂, …, expn) ≡ (exp=exp1) or (exp=exp2) or…or (exp=expn)

EJEMPLO: Obtener todos los datos de aquellos ciclistas de los que se desconocía su edad.

SELECT * FROM Ciclista

COMPARACIÓN DE VALORES NULOS

Las comparaciones entre cualquier valor y NULL resultan en indefinido. Ejemplo:

select *

from T

where atrib

₁

> atrib

₂

Si en una fila se diera el caso que atrib1 = 50 y atrib2 fuera nulo, el resultado de la comparación sería indefinido y por tanto dicha fila no se incluiría en la selección.

Ejemplo de consulta incorrecta (error de sintaxis)

SELECT nomeq

FROM Equipo

WHERE director = null

La consulta correcta sería

SELECT nomeq

FROM Equipo

MÁS EJEMPLOS DE COMPARACIONES

Uso de operadores aritméticos: + (suma), - (diferencia), * (producto), / (división), etc.

EJEMPLO: Obtener de los maillots el tipo y el premio en dólares (supongamos que está en pesetas) ($1 = 150 ptas.) de aquellos maillots cuyo premio supere los 100 dólares.

SELECT tipo, premio / 150 FROM Maillot WHERE premio / 150 > 100;

Uso de LIKE

EJEMPLO: Obtener el nombre y la edad de los ciclistas que pertenezcan a equipos cuyo nombre contenga la cadena “100%”.

SELECT nombre, edad FROM Ciclista WHERE nomeq LIKE ‘%100\%%’ ESCAPE ‘\’

Se ha utilizado ‘\’ para indicar que el carácter comodín tiene su valor ‘%’

CONSULTAS DE VALORES AGREGADOS

La sintaxis de una referencia a una función agregada es la siguiente:

{ avg | max | min | sum | count } ( [all | distinct] expresión_escalar ) | count(*)

  Las funciones agregadas no se pueden anidar.

  Para las funciones SUM y AVG los argumentos deben ser numéricos.   DISTINCT indica que los valores redundantes sean eliminados antes de que

se realice el cálculo correspondiente.

  La función especial COUNT(*), en la que no está permitido incluir DISTINCT ni ALL, da como resultado el cardinal del conjunto de filas de la selección.

  Los cálculos se realizan después de la selección y aplicar las condiciones.   Los valores nulos son eliminados antes de realizar los cálculos (incl. count).   Si el número de filas de la selección es 0, la función COUNT devuelve el valor

FUNCIONES AGREGADAS EN CONSULTAS NO

AGRUPADAS

EJEMPLO:

SELECT ‘Núm. de ciclistas =’, COUNT(*), ‘Media Edad =’, AVG(edad)

FROM Ciclista

WHERE nomeq = ‘Banesto’;

En consultas no agrupadas, la selección sólo podrá incluir referencias a funciones agregadas o literales ya que las funciones van a devolver un único valor.

EJEMPLO INCORRECTO:

SELECT nombre, AVG(edad) FROM Ciclista

WHERE nomeq = ‘ONCE’;

Ejercicios:

Práctica 3: El lenguaje SQL (1

a

_Parte)

CONSULTAS SIMPLES SOBRE VARIAS TABLAS

Cuando la información que se desea obtener de la base de datos se encuentra almacenada en más de una tabla se hace indispensable el declarar una consulta que manipule estas tablas.

EJEMPLO: Obtener pares de números de etapas y nombres de puertos ganados por el mismo ciclista.

1. ¿En qué tablas se encuentra la información?

FROM Etapa, Puerto

2. ¿Qué condición deben cumplir las filas resultantes?

WHERE etapa.dorsal = puerto.dorsal;

3. ¿Qué información queremos visualizar?

SELECT etapa.netapa, nompuerto

En esta expresión es obligatorio que la referencia a la columna dorsal de Etapa y Puerto sea calificada con el nombre de la tabla, si no es ambigua. ==>

SELECT etapa.netapa, nompuerto FROM Etapa, Puerto

Ejemplo: SELECT * FROM T1, T2 WHERE T1.n = T2.n

n

a1 b1 a2 b2 a3 b3

T1

c1 d1 c2 b2

T2

n

X X X √  X X

T1 x T2

n n

a1 b1 c1 d1 a1 b1 c2 b2 a2 b2 c1 d1 a2 b2 c2 b2 a3 b3 c1 d1 a3 b3 c2 b2

Cont. Consulta en varias tablas

  Cuando se va a trabajar con una tabla para hacer consulta entre

diferentes tuplas de ella, entonces se utilizan las variables de recorrido

[tabla | variable_recorrido].columna

Es una instancia de la tabla. Es virtual

  Por tanto, permiten dar un nombre alternativo a la misma tabla dentro

de una consulta. La manera de declarar una variable de recorrido es:

EJEMPLO: Obtener el nombre de los ciclistas compañeros de equipo de ‘Miguel Induráin’ que sean más jóvenes que él.

1. ¿En qué tablas se encuentra la información?

FROMCiclista Pero, como se requiere comparar con tuplas de la misma tabla, entonces se necesita tener varias imágenes de ella

2. ¿Qué condición deben cumplir las filas resultantes?

WHERE C2.nombre=‘Miguel Induráin’ AND C1.nomeq = C2.nomeq AND C1.edad < C2.edad;

3. ¿Qué información queremos visualizar?

SELECT DISTINCT C1.nombre

SELECT DISTINCT C1.nombre FROM Ciclista C1, Ciclista C2 WHERE C2.nombre=‘Miguel Induráin’ AND C1.nomeq = C2.nomeq AND C1.edad < C2.edad;

==>

FROM Ciclista C1, Ciclista C2

USO DE CLAVES AJENAS EN

CONSULTAS DE VARIAS TABLAS

Si existen claves ajenas, lo normal es que se dé una igualdad entre la clave ajena y los atributos correspondientes de la tabla a la que se hace referencia.

EJEMPLO: Obtener los nombres de los ciclistas pertenecientes al equipo dirigido por ‘Álvaro Pino’.

S E L E C T C . n o m b r e F R O M C i c l i s t a C , E q u i p o E WHERE C.nomeq = E.nomeq AND E.director = ‘Álvaro Pino’;

EJEMPLO: Obtener pares nombre de ciclista, número de etapa, de tal forma que dicho ciclista haya ganado dicha etapa. Además la etapa debe superar los 150 km. de recorrido.

Ejercicios:

Práctica 3: El lenguaje SQL (1a Parte)

Hacer el bloque de

consultas sobre varias

tablas

de las bases de datos Ciclismo y Música

CONSULTAS COMPLEJAS: SUBCONSULTAS

Si la información que se está buscando está incluida en una tabla y la condición de búsqueda de esta información requiere acceder a otras tablas, entonces también se pueden utilizar las subconsultas para expresar este tipo de condiciones.

EJEMPLO: Obtener el nombre de los ciclistas compañeros de equipo de ‘Miguel Induráin’ que sean más jóvenes que él. (Es el mismo enunciado de antes)

WHERE C1.nomeq IN (SELECT C2.nomeq FROM Ciclista C2

WHERE C2.nombre=‘Miguel Induráin’)

AND C1.edad < (SELECT C2.edad FROM Ciclista C2 WHERE C2.nombre=‘Miguel Induráin’); Tablas que se requieren para el Select precedente

Se verá más adelante

EJEMPLO: Obtener los nombres de los ciclistas pertenecientes al equipo dirigido por ‘Álvaro Pino’.

Antes, se habían usado igualdades:

SELECT C.nombre FROM Ciclista C, Equipo E

WHERE C.nomeq = E.nomeq AND E.director = ‘Álvaro Pino’;

Usando subconsultas, sería:

SELECT C.nombre FROM Ciclista C WHERE

C.nomeq = (SELECT E.nomeq FROM Equipo E WHERE E.director = ‘Álvaro Pino’);

Esto es posible porque la información que se requiere, nombre del ciclista, no está en la tabla de la subconsulta (Equipo) y porque la subconsulta retorna un único valor.

Las subconsultas pueden aparecer en las condiciones de búsqueda, como argumentos de algunos predicados, tanto de la cláusula WHERE como de la HAVING.

Los predicados que pueden llevar como argumentos subconsultas son los siguientes:

•  predicados de comparación (=, <>, >, <, >=, <=).

•  IN: comprueba que un valor pertenece a una colección dada mediante una subconsulta.

•  predicados de comparación cuantificados (ANY y ALL): permitir comparar un valor con un conjunto de valores.

•  MATCH: comprueba si un valor es idéntico a algún valor de una colección.

•  EXISTS: equivalente al cuantificador existencial, comprueba si una subconsulta devuelve alguna fila.

Cada uno de los dos lados de un predicado de comparación debe ser una única tupla formada por el mismo número de columnas. Es decir:

(A1, A2, …, An) predicado_comparación (B1, B2, …, Bn)

Las subconsultas pueden ser argumentos, siempre y cuando devuelvan una única fila y el número de columnas coincida en número y tipocon el otro lado del predicado de comparación.

Llamaremos constructor_fila a una lista de atributos entre paréntesis o una subconsulta.

constructor_filapredicado_comparaciónconstructor_fila

En el caso que la subconsulta esté vacía, se convierte a una fila con valores nulos en todas las columnas.

Para poder comparar dos constructor_fila de más de una columna, existe una forma definida de realizar esta comparación para cada uno de los predicados de comparación (=, <>, >, <, <=, >=). Pero, en general se verán subconsultas de una única columna, como el ejemplo anterior.

PREDICADOS DE COMPARACIÓN (=, <>, >, <, >=, <=)

EJEMPLO: Obtener los nombres de los puertos cuya altura es mayor que la media de altura de los puertos de 2ª categoría.

==> FROM Puerto

==> WHERE altura > (SELECT AVG(altura) FROM Puerto WHERE categoria = ‘2’ );

1. ¿En qué tablas se encuentra la información?

Puerto

2. ¿Qué condición deben cumplir las filas resultantes?

altura > AVG(altura) de los Puertos de segunda categoría Es un valor - una fila

==> SELECT nompuerto

EJEMPLO: Obtener los nombres de los puertos cuya altura es mayor que la media de altura de los puertos de 2ª categoría.

==> FROM Puerto

==> WHERE altura > (SELECT AVG(altura) FROM Puerto WHERE categoria = ‘2’ );

1. ¿En qué tablas se encuentra la información?

Puerto

2. ¿Qué condición deben cumplir las filas resultantes? altura > avg(altura) de los Puertos de segunda categoría

3. ¿Qué información queremos visualizar? nompuerto

==> SELECT nompuerto FROM Puerto

WHERE altura > (SELECT AVG(altura) FROM Puerto WHERE categoria = ‘2’ );

Es un valor a la vez

SELECT nompuerto FROM Puerto

WHERE altura > (SELECT altura FROM Puerto WHERE categoria = ‘2’ ); ¿Qué hace el siguiente ejemplo?

¿Es correcto?

1 columna con

n

filas

==> No puede hacer la comparación

A la derecha de IN puede aparecer más de una fila y por eso se denomina expresión_tabla.

EJEMPLO: Obtener el nº de las etapas ganadas por ciclistas con edad superior a los 30 años.

SELECT netapa FROM Etapa

WHERE dorsal IN (SELECT dorsal FROM Ciclista WHERE edad > 30);

Predicado IN

Comprueba que un valor pertenece a una colección dada mediante una subconsulta

constructor_fila [not] IN(expresión_tabla)

También, con IN se pueden hacer Subconsultas Encadenadas:

EJEMPLO: Obtener el número de las etapas ganadas por ciclistas que pertenezcan a equipos cuyo director tenga un nombre que empiece por ‘A’.

SELECT netapa FROM Etapa

WHERE dorsal IN (SELECT dorsal FROM Ciclista WHERE nomeq IN (SELECT nomeq FROM Equipo

•  El predicado de comparación cuantificado con ALL se evalúa a cierto si lo es para todas las filas de la expresión de tabla (si la tabla está vacía también se evalúa a cierto).

•  El predicado de comparación cuantificado con ANY o SOME

se evalúa a cierto si lo es para alguna fila de la expresión de tabla (si la tabla está vacía se evalúa a falso).

(*) el predicado

IN

es idéntico al predicado de comparación cuantificado = ANY.

Predicados de comparación cuantificados (ALL, ANY)

Permiten comparar un valor con un conjunto de valores.

constructor_fila predicado_comparación{all | any | some}

(expresión_tabla)

EJEMPLO: Obtener el nombre de los puertos y de los ciclistas que los hayan ganado que tengan la mayor pendiente.

SELECT P.nompuerto, C.nombre FROM Puerto P, Ciclista C

WHERE P.dorsal = C.dorsal AND

P.pendiente >= ALL (SELECT P1.pendiente FROM Puerto P1 )

EJEMPLO: Obtenerel nombre de los puertos y de los ciclistas que los hayan ganado, cumpliendo que el puerto no sea el que tenga la menor pendiente.

SELECT P.nompuerto, C.nombre FROM Puerto P, Ciclista C

WHERE P.dorsal = C.dorsal AND P.pendiente > ANY (SELECT P1.pendiente FROM Puerto P1 )

(*) Cualquier ANY se puede convertir en un ALL cambiando la condición a su condición negada y añadiendo un NOT.

• El predicado EXISTS se evalúa a cierto si la expresión SELECT

devuelve al menos una fila.

• En general, IN y EXISTS son intercambiables y se pueden eliminar haciendo consultas a múltiples tablas e igualando por claves ajenas.

Predicado EXISTS

EXISTS (expresión_tabla)

EJEMPLO: Obtener el nombre de aquellos ciclistas que han llevado un maillot de un premio menor de 120 euros.

SELECT C.nombre FROM Ciclista C, Llevar L WHERE C.dorsal = L.dorsal AND

EXISTS (SELECT * FROM Maillot M

WHERE M.premio < 120 AND M.codigo = L.codigo)

O bien:

SELECT C.nombre FROM Ciclista C, Llevar L WHERE C.dorsal = L.dorsal AND L.codigo IN (SELECT M.codigo

FROM Maillot M

EJEMPLO: Obtener el nombre de los ciclistas que no han ganado etapas.

SELECT nombre FROM Ciclista

WHERE NOT EXISTS (SELECT * FROM Etapa

WHERE Etapa.dorsal = Ciclista.dorsal);

WHERE EXISTS (SELECT * FROM …)

equivale a: WHERE 0 < (SELECT COUNT(*) FROM …)

WHERE NOTEXISTS (SELECT * FROM …)

equivale a: WHERE 0 = (SELECT COUNT(*) FROM …)

Ejercicios:

Práctica 3: El lenguaje SQL (1a Parte)

SELECTP.nombre

FROMProfesor P

WHERE NOT EXISTS (SELECT * FROM Asignatura A

WHERE NOT EXISTS (SELECT * FROM Docencia D

WHERE D.cod_pro=P.cod_pro ∧

D.cod_asg=A.cod_asg))

Obtener el nombre de los profesores que imparten todas las asignaturas.

Usode EXISTS para cuantificación universal (NO HAY EN SQL)

∀

X F(X)

≡



∃

X



F(X)

El lenguaje SQL

Problemas con la cuantificación universal

Obtener el nombre de los profesores que imparten todas las asignaturas de su departamento de mas de 6 créditos.

¿qué pasa si en el departamento de un profesor no hay asignaturas de

El lenguaje SQL

{PX.nombre| Profesor (PX) ∧

∀AX ((Asignatura (AX) ∧ AX.cod_dep= PX.cod_dep ∧ (AX.teoría+AX.prac)>6)

→

∃DX (Docencia (DX) ∧

DX.cod_pro = PX.cod_pro ∧ DX.cod_asg = AX.cod_asg) ) }

¿qué pasa si en el departamento de un profesor PX no hay asignaturas

de mas de 6 créditos?

FALSO para todo valor de AX

CIERTO

¡El profesor PX aparecería en el resultado de la consulta!

El lenguaje SQL

¡Si estos profesores no deben salir en la consulta, entonces se debe hacer un control para comprobar que en el departamento del profesor existe alguna asignatura de mas de seis créditos!.

{PX.nombre| Profesor (PX) ∧

∃AX (Asignatura (AX) ∧AX.cod_dep= PX.cod_dep ∧(AX.teoría+AX.prac)>6) ∧

∀AX ( (Asignatura (AX) ∧AX.cod_dep= PX.cod_dep ∧(AX.teoría+AX.prac)>6)

→

∃DX (Docencia (DX) ∧

SELECTPX.nombre FROMProfesor PX

WHERE EXISTS (SELECT *

FROM Asignatura AX

WHERE AX.cod_dep=PX.cod_dep AND (AX.teoría+AX.prac)>6) AND

NOT EXISTS (SELECT * FROM Asignatura AX

WHERE AX.cod_dep= PX.cod_dep AND (AX.teoría+AX.prac)>6

AND

NOT EXISTS (SELECT * FROM Docencia DX WHEREDX.cod_pro=PX.cod_pro AND DX.cod_asg=AX.cod_asg) ) )

SQL

El lenguaje SQL.

Usode EXISTS para cuantificación universal

∀

X F(X)

≡



∃

X



F(X)

Obtener el nombre del ciclista que ha ganado todas las etapas de más de 200 km.

C.nombre| Ciclista(C)∧∀ X

(Etapa(X)∧X.Km>200→C.dorsal=X.dorsal)

es equivalente a:

C.nombre| Ciclista(C)∧

Para poder expresar esta consulta en SQL se convertirá en: “Obtener el nombre del ciclista tal que no existe una etapa de más de 200 km. que él no haya ganado”

SELECT nombre FROM Ciclista C

WHERE NOT EXISTS (SELECT * FROM Etapa E WHERE km > 200 AND

C.dorsal <> E.dorsal );

¿Qué pasa si no hay etapas de más de 200 km?

¡¡¡SALDRÍAN TODOS LOS CICLISTAS!!!

Solución:

SELECT C.nombre FROM Ciclista C

WHERE NOT EXISTS (SELECT * FROM Etapa E WHERE E.km > 200 AND C.dorsal <>

E.dorsal ) AND EXISTS (SELECT * FROM ETAPA E2 WHERE E2.km > 200);

Ejercicios:

Práctica 3: El lenguaje SQL (1a Parte)

Hacer el bloque de consultas con cuantificación

universal de las BDs Ciclismo y Música

AUTOR(autor_id: tira(4), nombre: tira(35), nacionalidad: tira(20)) Clave Primaria: {autor_id}

LIBRO(id_lib: tira(10), titulo: tira(80), año: entero, num_obras: entero) Clave Primaria: {id_lib} VNN: {titulo}

TEMA(tematica: tira(20), descripcion: tira(50)) Clave Primaria: {tematica}

OBRA(cod_ob: entero, titulo: tira(80), año: d_cat, tematica: tira(20)) Clave Primaria: {cod_ob}

Clave Ajena: {tematica}→ TEMA VNN: {titulo}

Biblioteca

AMIGO(num: entero, nombre: tira(60), telefono: tira(10))

Clave Primaria: {num} VNN: {nombre}

PRESTAMO(num: entero, id_lib:tira(10)) Clave Primaria: {num,id_lib} Clave Ajena: {num} → AMIGO Clave Ajena: {id_lib} → LIBRO

ESTA_EN(cod_ob: entero, id_lib:tira(10)) Clave Primaria: {cod_ob,id_lib} Clave Ajena: {cod_ob} → OBRA Clave Ajena: {id_lib} → LIBRO

SELECT [ALL | DISTINCT] A_1i, A_2j,..., A_nk| *

FROM R1, R2, ..., Rn

[WHEREcondición]

[GROUP BY B₁, B₂,..., B_m] [HAVINGcondición]

Consultas Agrupadas

GROUP BY: define grupos de tuplas en el conjunto de tuplas seleccionadas por la condición WHERE. Los grupos se definen por la igualdad de valor en los atributos de agrupación (B₁, B₂,..., B_m).

HAVING: de los grupos definidos se seleccionan aquellos que cumplen la condición expresada.

Un grupo se puede entender como un conjunto de filas con el mismo valor para el conjunto de columnas por las que se agrupa (las incluidas en la cláusula GROUP BY).

Consultas Complejas

Relación Selección-Agrupamiento

EJEMPLO: Obtener el nombre de cada equipo y la edad media de los ciclistas de dicho equipo:

Nomeq Edad

Banesto 28 Banesto 22 ONCE 25

ONCE 30 Banesto 25 PDM 32

Kelme 28

Kelme 29

SELECT nomeq, AVG(edad)

FROM Ciclista

Las funciones agregadas en las consultas agrupadas funcionan de forma diferente que en las consultas normales, devolviendo un valor por cada grupo formado.

Nomeq Edad

Banesto 28 Banesto 22

ONCE 25 ONCE 30 Banesto 25

PDM 32

Kelme 28 Kelme 29

Un Valor

por Grupo

Entonces, para

Nomeq Edad Banesto 25 ONCE 27,5 PDM 32 Kelme 28,5

SELECT nomeq, AVG(edad)

FROM Ciclista

GROUP BY

nomeq;

EJEMPLO: Obtener el número total de profesores de cada departamento

Consultas Agrupadas

cod_pro nombre tel!fono cod_dep

JCC Juan C. Casamayor R!denas 7796 DSIC RFC Robert Fuster i Capilla 6789 MAT JBD Jos" V. Benlloch Dualde 5760 DISCA MAF Mar#a Alpuente Frasnedo 3560 DSIC CPG Cristina P"rez Guillot 7439 IDM JTM Jos" M. Torralba Mart#nez 4590 OEM IGP Ignacio Gil Pechu$n 3423 OEM DGT Daniel Gil Tom$s 5679 DISCA MCG Matilde Celma Gim"nez 7756 DSIC Profesor cod_dep DSIC 3 MAT 1 DISCA 2 IDM 1 OEM 2

SELECT cod_dep, COUNT (*)

FROM Profesor

GROUP BY cod_dep

Obtener el número total de profesores de los departamentos que tienen mas de 2 profesores.

cod_pro nombre tel!fono cod_dep JCC Juan C. Casamayor R!denas 7796 DSIC

RFC Robert Fuster i Capilla 6789 MAT

JBD Jos" V. Benlloch Dualde 5760 DISCA MAF Mar#a Alpuente Frasnedo 3560 DSIC CPG Cristina P"rez Guillot 7439 IDM JTM Jos" M. Torralba Mart#nez 4590 OEM

IGP Ignacio Gil Pechu$n 3423 OEM

DGT Daniel Gil Tom$s 5679 DISCA

MCG Matilde Celma Gim"nez 7756 DSIC

cod_dep

DSIC 3

SELECT cod_dep, COUNT (*)

FROM Profesor

GROUP BY cod_dep HAVING COUNT (*) > 2

SELECT nomeq, nombre, AVG(edad)

FROM Ciclista

GROUP BY nomeq;

La regla sintáctica que aplican los sistemas relacionales para asegurar el buen funcionamiento de las consultas agrupadas es la siguiente:

“En la selección de una consulta agrupada, sólo pueden aparecer referencias a columnas por las cuales se agrupa, referencias a funciones agregadas o literales”.

EJEMPLO INCORRECTO:

Si se incluye la cláusula

where,

la aplicación de esta cláusula se produce previamente a la agrupación.

SELECT nomeq, AVG(edad)

FROM Ciclista

WHERE edad > 25

GROUP BY nomeq;

GROUP y WHERE

4

1

Evaluación:

1) Se seleccionan n tuplas de las relaciones que cumplan la condición de la cláusula WHERE. 2) En el conjunto de tuplas seleccionadas se definen

grupos basados en el valor de los atributos de agrupación.

3) De los grupos definidos se seleccionan los que cumplen la condición de la cláusula HAVING.

La cláusula HAVING sólo puede ir en consultas agrupadas y es similar a WHERE, pero en un orden diferente:

1º) Condición WHERE (se usa para las filas) 2º) Agrupamiento y cálculo de valores agregados 3º) Condición HAVING (se usa para los grupos)

En la cláusula HAVING sólo podrán aparecer directamente referencias a columnas por las cuales se agrupan o a funciones agregadas.

EJEMPLO: Obtener el nombre de cada equipo y la edad media de sus ciclistas con más de 25 años, de aquellos equipos con más de 3 corredores mayores de 25 años.

SELECTnomeq, AVG(edad)

WHEREedad > 25

GROUPBYnomeq

HAVINGCOUNT(dorsal) > 3;

FROMCiclista

SELECTC.nombre, COUNT(P.nompuerto)

FROMCiclista C, Puerto P

WHEREC.dorsal = P.dorsal

GROUPBYC.dorsal, C.nombre /* Agrupar siempre por CP */

HAVINGAVG (P.pendiente) >10;

Ejercicios:

Práctica 3: El lenguaje SQL (1a Parte)

Hacer el bloque de “consultas agrudapas” de las BDs Ciclismo y Música

Existen otras formas de combinar varias tablas en consultas y todas ellas, junto con las ya vistas, dan lugar a una “expresión de tabla”.

Existen, en definitiva, varias formas de combinar dos tablas en el lenguaje SQL:

→  Incluir varias tablas en la cláusula from.

→ Uso de subconsultas en las condiciones de las cláusulas

where o having.

→  Combinaciones conjuntistas de tablas: utilizando operadores de la teoría de conjuntos para combinar las tablas.

→ Concatenaciones de tablas: utilizando diferentes formas

variantes del operador concatenación del Álgebra Relacional.

COMBINACIONES DE TABLAS

√

 

El Lenguaje Estándar SQL

Operador Álgebra

Relacional SQL

Selección R Donde F SELECT ... FROM R WHERE F Proyección R [A_{i ,} A_{j ...,} A_k] SELECT A_{i ,} A_{j ...,} A_k FROM R Producto

Cartesiano

R₁ x R₂, ... x R_n SELECT ... FROM R₁, R₂, ..., R_n, o SELECT...FROM R₁CROSSJOIN

R₂, ..., CROSS JOIN R_n

Concatenación R1 R2 SELECT... FROM R1NATURALJOIN R2

Unión R1∪ R2 SELECT * FROM R1 UNION SELECT *

FROM R2

Diferencia R1 - R2 SELECT * FROM R1 EXCEPT SELECT

* FROM R2

Intersección R1∩ R2 SELECT * FROM R1 INTERSECT

SELECT * FROM R2

Corresponden a los operadores unión, intersección y

diferenciadel Álgebra Relacional. Dadas dos tablas A y B:

→  UNION: la tabla resultado tendrá las filas de A y B

→  INTERSECT: la tabla resultado tendrá las filas que se encuentren a la vez en A y en B.

→  EXCEPT: la tabla resultado tendrá las filas de A que no se encuentren en B.

Realiza la unión de las filas de las tablas provenientes de las dos expresiones.

Se permitirán o no duplicados según se incluya o no la opción ALL.

UNION

expresión_tablaunion [ALL]término_tabla

EJEMPLO: Obtener el nombre de todo el personal de la vuelta.

(SELECT nombre FROM Ciclista) UNION

(SELECT director FROM Equipo)

SELECTdirector FROM Departamento

UNION SELECTnombre

FROM Profesor

Ejemplo 2. Obtener el nombre de todo el personal (profesores y directores de departamento).

INTERSECT

expresión_tabla intersect término_tabla

Realiza la intersección de las filas de las tablas provenientes de las dos expresiones.

EJEMPLO: Obtener los nombres de las personas que son tanto ciclistas como directores de equipo .

(SELECT nombre FROM Ciclista) INTERSECT

(SELECT director FROM Equipo)

SELECT DISTINCT cod_dep

FROM Profesor

INTERSECT

SELECT DISTINCT cod_dep

FROM Asignatura

Ejemplo 2. Obtener los departamentos que tienen adscritas asignaturas y profesores.

EXCEPT

expresión_tabla except término_tabla

Realiza la diferencia de las filas de las tablas provenientes de las dos expresiones.

EJEMPLO: Obtener los nombres que aparecen en la tabla de ciclistas y no en la de directores.

(SELECT nombre FROM Ciclista) MINUS

(SELECT director FROM Equipo)

En Oracle es Minus

SELECT cod_dep

FROM Departamento

EXCEPT

SELECT DISTINCT cod_dep

FROM Asignatura

Ejemplo 2. Obtener los departamentos que no tienen adscritas asignaturas.

EXCEPT

SELECT [ALL | DISTINCT] A

_1i

,...,A

_2j

,...,A

_nk

| *

FROM R

₁

, R

₂

, ..., R

_n

[WHERE

condición

]

[GROUP BY B

₁

, B

₂

,..., B

_m

]

[HAVING

condición

]

Concatenación de tablas

concatenación de tablas

 concatenación interna: INNER JOIN  concatenación externa: OUTER JOIN

Corresponden a variantes del operador concatenación del Álgebra Relacional.

•  Producto cartesiano – CROSS JOIN

•  Concatenación interna – NATURALJOIN

•  Concatenación externa – LEFT, RIGHT, FULL •  Concatenación unión – UNION JOIN

Producto Cartesiano

(CROSS JOIN)

referencia_tabla1

cross

join

referencia_tabla2

≡

SELECT *

from

referencia_tabla1,

referencia_tabla2

 La tabla resultado de la operación CROSS JOIN es

el producto cartesiano de las dos tablas operandos.

referencia_tabla1[natural] [inner]joinreferencia_tabla2

[onexpresión_condicional | using (comalista_columna) ]

Concatenación Interna

tabla1 jointabla2 onexpresión_condicional

≡

SELECT * FROMtabla1, tabla2

WHERE expresión_condicional

 Natural Join: se concatenan las tuplas de tabla1 y tabla2 que tienen el mismo valor en todos los atributos del mismo nombre

 Join...ON: combina una fila de cada operando cuando la condición expresada se evalúe a cierta.

SELECT PX.cod_pro, PX.nombre, COUNT(DX.cod_asg) FROM Profesor PX, Docencia DX

WHERE PX.cod_pro = DX.cod_pro GROUP BY cod_pro

SELECT cod_pro, nombre, COUNT (cod_asg) FROM Profesor NATURALJOIN Docencia GROUP BY cod_pro

Ejemplo- Natural Join

concatenación de Profesor y Docencia

 Natural Join: se concatenan las tuplas de Profesor y Docencia que tienen el mismo valor en los atributos del mismo nombre (cod_pro)

SELECT [ALL | DISTINCT] A1, A2,..., An| *

FROM

[WHERE condición] [GROUP BY B1, B2,..., Bm]

[HAVING condición]

se concatenan las tuplas de tabla1 y tabla2 que tienen el mismo valor en los atributos comunes C1, C2,..., Cn

(1) (2) (3) (4)

Es útil cuando no interesa que las relaciones se concatenen por todos los atributos del mismo nombre (NATURAL JOIN).

tabla1 JOINtabla2USING (C1, C2,..., Cn)

SELECT [ALL | DISTINCT] A1, A2,..., An| *

FROM

[WHERE condición2] [GROUP BY B1, B2,..., Bm]

[HAVING condición]

se concatenan las tuplas de tabla1 y tabla2 que cumplen condición1

(1) (2) (3) (4)

Es útil cuando:

  interesa concatenar tuplas de tabla1 y tabla2 por condiciones

distintas de la igualdad.

  los atributos por los que se desea concatenar no tienen el mismo

nombre en ambas relaciones.

tabla1 JOINtabla2 ON condición1

Concatenación JOIN...ON

Empleo del JOIN:

SELECT e.nomeq, e.director

FROM Equipo E JOIN Ciclista C ON E.nomeq=C.nomeq

Ejemplo: Obtener nombre y director de todos los equipos que tengan ciclistas.

Forma ya conocida:

SELECT e.nomeq, e.director FROM Equipo E, Ciclista C WHERE E.nomeq=C.nomeq

FULL, se muestran las tuplas no concatenadas de tabla1 y tabla2

referencia_tabla [natural]

{left [outer] | right [outer] |

full [outer] } JOIN referencia_tabla [on expresión_condicional | using

(comalista_columna) ]

Concatenación Externa

tabla1 leftjointabla2 on expresión_condicional

(Concat. interna de tabla1 y tabla2) union

(tuplas de la tabla1 que no están en la concatenación interna con valores nulos en el resto de columnas)

RIGHT≡LEFT con la diferencia de que las tuplas que se muestran son las de tabla2.

referencia_tabla unionjoin referencia_tabla

Concatenación Unión

Crea una tabla donde el esquema es la unión de los esquemas de las dos tablas, que pueden ser distintos.

tabla1

unionjoin

tabla2

tuplas de tabla1 con valores nulos en las columnas de tabla2 union

tuplas de tabla2 con valores nulos en las columnas de tabla1

EJERCICIO 33: Obtener nombre de todos los equipos indicando cuantos ciclistas tiene cada uno

1ª Opción (errónea) – 21 filas

SELECT e.nomeq, count(c.dorsal) FROM Equipo E, Ciclista C

WHERE E.nomeq=C.nomeq GROUP BY e.nomeq

2ªOpción Correcta – 22 filas

SELECT E.nomeq, count(c.dorsal)

FROM (EQUIPO E left join ciclista c on E.NOMEQ=C.NOMEQ) GROUP BY E.nomeq;

El equipo PDM con 0 ciclistas no

3ª Opción Correcta – 22 filas

SELECT e.nomeq, count(c.dorsal)

FROM Equipo E, Ciclista C WHERE E.nomeq=C.nomeq GROUP BY e.nomeq

EJERCICIO 33: Obtener nombre de todos los equipos indicando cuantos ciclistas tiene cada uno

Se pone (+) en la parte de la ICA de

la tabla de la que no se quieren

mantener las tuplas