Consultas Complejas: SELECCIÓN-AGRUPAMIENTO

„ 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). Obtener Obtener el el nombre nombre de de cada cada equipo equipo yy la la edad edad media media de de los ciclistas de dicho equipo: los ciclistas de dicho equipo:

select select nomeq, nomeq, AVG(edad) AVG(edad) from Ciclista from Ciclista group by nomeq ;;

Bases de Datos

Curso 2006/07

Nomeq Banesto ONCE PDM Banesto Kelme ONCE Kelme Banesto

Edad 22 25 32 25 28 30 29 28

DSIC-UPV

1

Consultas Complejas: SELECCIÓN-AGRUPAMIENTO

„ Las funciones agregadas en las consultas agrupadas funcionan de

forma diferente que en las consultas normales, devolviendo un valor por cada grupo formado. Nomeq Banesto Banesto Banesto ONCE ONCE PDM Kelme Kelme

Edad 22 25 28 25 30 32 29 28

Un Valor por Grupo

select select nomeq, nomeq, AVG(edad) AVG(edad) from Ciclista from Ciclista group group by by nomeq nomeq ;; Bases de Datos

Curso 2006/07

DSIC-UPV

2

1

Consultas Complejas: SELECCIÓN-AGRUPAMIENTO

„ Entonces para la consulta:

select select nomeq, nomeq, AVG(edad) AVG(edad) from Ciclista from Ciclista group group by by nomeq nomeq ;; La solución, es:

Bases de Datos

Nomeq Banesto ONCE PDM Kelme

Curso 2006/07

Edad 25 27,5 32 28,5

DSIC-UPV

3

Consultas Complejas: SELECCIÓN-AGRUPAMIENTO Ejemplo Ejemplo incorrecto: incorrecto:

select select nomeq, nomeq, nombre, nombre, AVG(edad) AVG(edad) from Ciclista from Ciclista group group by by nomeq nomeq ;; La La regla regla sintáctica sintáctica que que aplican aplican los los sistemas sistemas relacionales para asegurar el buen relacionales para asegurar el buen funcionamiento funcionamiento de de las las consultas consultas agrupadas agrupadas es es la la siguiente: siguiente: “en “en la la selección selección de de una una consulta consulta agrupada, agrupada, sólo sólo pueden pueden aparecer aparecer referencias referencias aa columnas columnas por por las las cuales cuales se se agrupa, agrupa, referencias referencias aa funciones funciones agregadas agregadas oo literales”. literales”.

Bases de Datos

Curso 2006/07

DSIC-UPV

4

2

Consultas Complejas: SELECCIÓN-AGRUPAMIENTO GROUP GROUP yy WHERE: WHERE: Si Si se se incluye incluye la la cláusula cláusula where, where, la la aplicación aplicación de de esta cláusula se produce previamente esta cláusula se produce previamente aa la la agrupación agrupación

4 1 2 3

Bases de Datos

select select nomeq, nomeq, AVG(edad) AVG(edad) from Ciclista from Ciclista where where edad>25 edad>25 group group by by nomeq nomeq ;;

Curso 2006/07

DSIC-UPV

5

Consultas Complejas: SELECCIÓN-AGRUPAMIENTO GROUP, GROUP, WHERE WHERE yy HAVING: HAVING: La La cláusula cláusula HAVING HAVING sólo sólo puede puede ir ir en en consultas consultas agrupadas agrupadas yy es es similar similar aa WHERE, WHERE, pero pero en en un un orden orden diferente: diferente: 1º) 1º) Condición Condición WHERE WHERE (se (se usa usa para para las las filas) filas) 2º) 2º) Agrupamiento Agrupamiento yy cálculo cálculo de de valores valores agregados agregados 3º) 3º) Condición Condición HAVING HAVING (se (se usa usa para para los los grupos) grupos) En En la la cláusula cláusula HAVING HAVING sólo sólo podrán podrán aparecer aparecer directamente referencias a columnas directamente referencias a columnas por por las las cuales se agrupan o a funciones agregadas. cuales se agrupan o a funciones agregadas. Bases de Datos

Curso 2006/07

DSIC-UPV

6

3

Consultas Complejas: SELECCIÓN-AGRUPAMIENTO Obtener Obtener el el nombre nombre de de cada cada equipo equipo yy la la edad edad media media de de sus ciclistas con más de 25 años, de aquellos sus ciclistas con más de 25 años, de aquellos equipos equipos con con más más de de 33 corredores corredores mayores mayores de de 25 25 años. años.

select select nomeq, nomeq, AVG(edad) AVG(edad) from Ciclista from Ciclista where where edad>25 edad>25 group group by by nomeq nomeq having COUNT(dorsal)>3; having COUNT(dorsal)>3;

Bases de Datos

Curso 2006/07

DSIC-UPV

7

Consultas Complejas: SELECCIÓN-AGRUPAMIENTO Obtener Obtener el el nombre nombre del del ciclista ciclista yy el el número número de de puertos puertos que ha ganado, siendo la media de la pendiente que ha ganado, siendo la media de la pendiente de de éstos éstos superior superior aa 10. 10.

select select C.nombre, C.nombre, COUNT(P.nompuerto) COUNT(P.nompuerto) from Ciclista C, Puerto from Ciclista C, Puerto PP where where C.dorsal=P.dorsal C.dorsal=P.dorsal group group by by dorsal, dorsal, nombre nombre Agrupar Agrupar siempre siempre por por CP CP having AVG(P.pendiente)>10; having AVG(P.pendiente)>10;

Bases de Datos

Curso 2006/07

DSIC-UPV

8

4

Trabajo a realizar Consultas Agrupadas y Generales (Ciclismo, Música y Biblioteca)

Bases de Datos

Curso 2006/07

DSIC-UPV

9

Combinaciones de Tablas Existen Existen varias varias formas formas de de combinar combinar dos dos tablas tablas en en el el lenguaje lenguaje SQL SQL dando dando lugar lugar aa una una “expresión “expresión de de tabla”: tabla”:

√

1º) 1º) Incluir Incluir varias varias tablas tablas en en la la cláusula cláusula FROM. FROM.

√

2º) 2º) Uso Uso de de subconsultas subconsultas en en las las condiciones condiciones de de las las cláusulas cláusulas WHERE WHERE oo HAVING. HAVING. 3º) 3º) Combinaciones Combinaciones conjuntistas conjuntistas de de tablas: tablas: utilizando operadores de la utilizando operadores de la teoría teoría de de conjuntos conjuntos para para combinar combinar las las tablas. tablas. 4º) 4º) Concatenaciones Concatenaciones de de tablas: tablas: utilizando utilizando diferentes formas variantes diferentes formas variantes del del operador operador concatenación del Álgebra Relacional. concatenación del Álgebra Relacional.

Bases de Datos

Curso 2006/07

DSIC-UPV

10

5

Combinaciones Conjuntistas de Tablas Corresponden Corresponden aa los los operadores operadores unión, unión, diferencia diferencia ee intersección del Álgebra Relacional: intersección del Álgebra Relacional: 1º) 1º) UNION UNION 2º) 2º) EXCEPT EXCEPT 3º) 3º) INTERSECT INTERSECT Permiten Permiten combinar combinar tablas tablas que que tengan tengan esquemas esquemas compatibles . compatibles.

Bases de Datos

Curso 2006/07

DSIC-UPV

11

Combinaciones Conjuntistas de Tablas UNIÓN: UNIÓN: expresión_tabla expresión_tabla union union [all] [all] término_tabla término_tabla Realiza Realiza la la unión unión de de las las filas filas de de las las tablas tablas provenientes de las dos expresiones. provenientes de las dos expresiones. Se Se permitirán permitirán oo no no duplicados duplicados según según se se incluya incluya oo no la opción all. no la opción all. Obtener Obtener el el nombre nombre de de todo todo el el personal personal de de la la vuelta: vuelta:

(select (select nombre nombre from from Ciclista) Ciclista) UNION UNION (select (select director director from from Equipo); Equipo); Bases de Datos

Curso 2006/07

DSIC-UPV

12

6

Combinaciones Conjuntistas de Tablas INTERSECCIÓN: INTERSECCIÓN: expresión_tabla expresión_tabla intersect intersect término_tabla término_tabla Realiza Realiza la la intersección intersección de de las las filas filas de de las las tablas provenientes de las dos expresiones. tablas provenientes de las dos expresiones. Obtener Obtener los los nombres nombres de de las las personas personas que que son son tanto tanto ciclistas como directores de equipo : ciclistas como directores de equipo :

(select (select nombre nombre from from Ciclista) Ciclista) INTERSECT INTERSECT (select (select director director from from Equipo); Equipo); Bases de Datos

Curso 2006/07

DSIC-UPV

13

Combinaciones Conjuntistas de Tablas DIFERENCIA: DIFERENCIA: expresión_tabla expresión_tabla except except término_tabla término_tabla

En Oracle es Minus Realiza Realiza la la diferencia diferencia de de las las filas filas de de las las tablas provenientes de las dos expresiones. tablas provenientes de las dos expresiones. Obtener Obtener los los nombres nombres que que aparecen aparecen en en la la tabla tabla de de ciclistas ciclistas yy no no en en la la de de directores: directores:

(select (select nombre nombre from from Ciclista) Ciclista) MINUS MINUS (select (select director director from from Equipo); Equipo); Bases de Datos

Curso 2006/07

DSIC-UPV

14

7

Concatenaciones de Tablas Corresponden Corresponden aa variantes variantes del del operador operador concatenación concatenación del Álgebra Relacional: del Álgebra Relacional:

1º) 1º) Producto Producto cartesiano cartesiano (cross (cross join) join) 2º) 2º) Concatenación Concatenación interna interna (natural (natural join) join) 3º) 3º) Concatenación Concatenación externa externa (Left, (Left, Right, Right, Full) Full) 4º) 4º) Concatenación Concatenación unión unión (union (union join) join)

Bases de Datos

Curso 2006/07

DSIC-UPV

15

Concatenaciones de Tablas Producto Producto Cartesiano Cartesiano (cross (cross join): join): tabla1 tabla1 CROSS CROSS JOIN JOIN tabla2 tabla2 Equivale Equivale a: a: SELECT SELECT ** FROM FROM tabla1, tabla1, tabla2 tabla2

Bases de Datos

Curso 2006/07

DSIC-UPV

16

8

Concatenaciones de Tablas Concatenación Concatenación Interna Interna (1): (1): referencia_tabla referencia_tabla [natural] [natural] join join [inner]referencia_tabla [inner]referencia_tabla [on [on expresión_condicional| expresión_condicional| using using (comalista_columna) (comalista_columna) ]]

tabla1 tabla1 JOIN JOIN tabla2 tabla2 ON ON expresión_condicional expresión_condicional Equivale Equivale a: a: Select Select ** FROM FROM tabla1, tabla1, tabla2 tabla2 WHERE expresión_condicional WHERE expresión_condicional

Bases de Datos

Curso 2006/07

DSIC-UPV

17

Concatenaciones de Tablas Concatenación Concatenación Interna Interna (2): (2): tabla1 tabla1 JOIN JOIN tabla2 tabla2 USING USING (c1, (c1, c2, c2, .., .., cn) cn) Equivale Equivale a: a: tabla1 tabla1 JOIN JOIN tabla2 tabla2 ON ON tabla1.c1 tabla1.c1 == tabla2.c1 tabla2.c1 AND tabla1.c2 = tabla2.c2 AND tabla1.c2 = tabla2.c2 AND AND …… AND AND tabla1.cn tabla1.cn == tabla2.cn tabla2.cn Equivale Equivale a: a: Select Select ** FROM FROM tabla1, tabla1, tabla2 tabla2 WHERE tabla1.c1 WHERE tabla1.c1 == tabla2.c1 tabla2.c1 AND AND tabla1.c2 tabla1.c2 == tabla2.c2 tabla2.c2 AND AND …… AND AND tabla1.cn tabla1.cn == tabla2.cn tabla2.cn Bases de Datos

Curso 2006/07

DSIC-UPV

18

9

Concatenaciones de Tablas Concatenación Concatenación Interna Interna (3): (3): tabla1 tabla1 NATURAL NATURAL JOIN JOIN tabla2 tabla2 Equivale Equivale a: a: tabla1 tabla1 join join tabla2 tabla2 using using (( c1, c1, c2, c2, ...., ...., cn) cn) Donde Donde tabla1 tabla1 yy tabla2 tabla2 tienen tienen nn atributos atributos Es Es de de

un un JOIN JOIN por por todos todos los los atributos atributos comunes comunes las tablas). las tablas).

Bases de Datos

Curso 2006/07

DSIC-UPV

19

Concatenaciones de Tablas Concatenación Concatenación Interna Interna (4): (4): Obtener Obtener el el dorsal dorsal yy nombre nombre de de los los ciclistas ciclistas que que han han llevado maillots: llevado maillots:

SELECT SELECT cl.dorsal, cl.dorsal, cl.nombre cl.nombre FROM FROM (Ciclista (Ciclista NATURAL NATURAL JOIN JOIN Llevar) Llevar) AS AS cl; cl; Equivale Equivale a: a: SELECT SELECT c.dorsal, c.dorsal, c.nombre c.nombre FROM Ciclista c, FROM Ciclista c, Llevar Llevar ll WHERE c.dorsal = l.dorsal; WHERE c.dorsal = l.dorsal; Bases de Datos

Curso 2006/07

DSIC-UPV

20

10

Concatenaciones de Tablas Concatenación Concatenación Externa Externa (1): (1): referencia_tabla referencia_tabla [natural] [natural] {left || {left [outer] [outer] right || right [outer] [outer] full [outer] } full [outer] } JOIN JOIN referencia_tabla referencia_tabla [on expresión_condicional [on expresión_condicional || using using (comalista_columna) (comalista_columna) ]]

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

Bases de Datos

Curso 2006/07

DSIC-UPV

21

Concatenaciones de Tablas Concatenación Concatenación Externa Externa (2): (2): tabla1 tabla1 LEFT LEFT JOIN JOIN tabla2 tabla2 ON ON expresión_condicional expresión_condicional (Concat. (Concat. interna interna de de tabla1 tabla1 yy tabla2) tabla2) union union (tuplas (tuplas de de la la tabla1 tabla1 que que no no están están en en la la concatenación interna con valores concatenación interna con valores nulos nulos en en el el resto resto de de columnas) columnas) RIGHT RIGHT equivale equivale que que las las tuplas tuplas tabla2. tabla2. Bases de Datos

aa LEFT LEFT que que se se

Curso 2006/07

con con la la diferencia diferencia de de muestran muestran son son las las de de

DSIC-UPV

22

11

Concatenaciones de Tablas Concatenación Concatenación Externa Externa (3): (3): EJERCICIO EJERCICIO 33: 33: Obtener Obtener nombre nombre de de todos todos los los equipos equipos indicando cuantos ciclistas tiene cada indicando cuantos ciclistas tiene cada uno: uno:

1ª 1ª Opción Opción (Incorrecta) (Incorrecta) El equipo PDM SELECT SELECT e.nomeq, e.nomeq, count(c.dorsal) count(c.dorsal) con 0 ciclistas FROM Equipo E, Ciclista no aparece FROM Equipo E, Ciclista CC WHERE E.nomeq=C.nomeq WHERE E.nomeq=C.nomeq GROUP GROUP BY BY e.nomeq; e.nomeq; 2ª 2ª Opción Opción (Correcta) (Correcta) SELECT SELECT e.nomeq, e.nomeq, count(c.dorsal) count(c.dorsal) FROM ( Equipo FROM ( Equipo EE LEFT LEFT JOIN JOIN Ciclista Ciclista CC )) on E.nomeq=C.nomeq on E.nomeq=C.nomeq GROUP GROUP BY BY e.nomeq; e.nomeq; Bases de Datos

Curso 2006/07

DSIC-UPV

23

Concatenaciones de Tablas Concatenación Concatenación Externa Externa (4): (4): EJERCICIO EJERCICIO 33: 33: Obtener Obtener nombre nombre de de todos todos los los equipos equipos indicando cuantos ciclistas tiene cada indicando cuantos ciclistas tiene cada uno: uno:

En En No No

versiones versiones existe existe el el

anteriores anteriores de de ORACLE ORACLE JOIN como tal. JOIN como tal.

SELECT SELECT e.nomeq, e.nomeq, count(c.dorsal) count(c.dorsal) Se pone (+) en FROM Equipo FROM Equipo E, E, Ciclista Ciclista CC la parte de la WHERE WHERE E.nomeq=C.nomeq(+) E.nomeq=C.nomeq(+) ICA de la tabla GROUP GROUP BY BY e.nomeq; e.nomeq; de la que no se quieren mantener las tuplas Bases de Datos

Curso 2006/07

DSIC-UPV

24

12

Concatenaciones de Tablas Concatenación Concatenación Unión: Unión: tabla1 tabla1 UNION UNION JOIN JOIN tabla2 tabla2 Crea Crea una una tabla tabla donde donde el el esquema esquema es es la la unión unión de de los los esquemas esquemas de de las las dos dos tablas, tablas, que que pueden pueden ser ser distintos. distintos. tuplas tuplas de de tabla1 tabla1 con con columnas de columnas de tabla2 tabla2 union union tuplas de tabla2 tuplas de tabla2 con con columnas columnas de de tabla1 tabla1 .. Bases de Datos

valores valores nulos nulos en en las las valores valores nulos nulos en en las las

Curso 2006/07

DSIC-UPV

25

Introducción de Información Instrucción Instrucción INSERT: INSERT: insert insert into into tabla tabla [(comalista_columna)] [(comalista_columna)] {default values {default values || values values (comalista_átomos) (comalista_átomos) || expresión_tabla} expresión_tabla} „ Si no se incluye la lista de columnas se deberán insertar filas

completas de tabla. „ Si se incluye la opción default values se insertará una única fila en la

tabla con los valores por defecto apropiados en cada columna (según la definición de tabla). „ En la opción values (comalista_átomos) los átomos vienen dados por expresiones escalares. „ En la opción expresión_tabla, se insertarán las filas resultantes de la ejecución de la expresión ( SELECT ). Bases de Datos

Curso 2006/07

DSIC-UPV

26

13

Introducción de Información Instrucción Instrucción INSERT: INSERT: Añadir Añadir un un ciclista ciclista de de dorsal dorsal 101, 101, nombre nombre ‘Joan ‘Joan Peris’, Peris’, edad 20 años y del equipo ‘Kelme’: edad 20 años y del equipo ‘Kelme’:

insert insert values values

Bases de Datos

into into Ciclista Ciclista (101, (101, ‘Joan ‘Joan Peris’, Peris’, 20, 20, ‘Kelme’); ‘Kelme’);

Curso 2006/07

DSIC-UPV

27

Modificación de Información Instrucción Instrucción UPDATE: UPDATE: update update tabla tabla set comalista_asignaciones set comalista_asignaciones [where [where expresión_condicional] expresión_condicional] „ En donde una asignación es de la forma:

columna = {default | null | expresión_escalar} „ Si se incluye la cláusula where sólo se aplicará a las filas

que hagan cierta la condición.

Bases de Datos

Curso 2006/07

DSIC-UPV

28

14

Modificación de Información Instrucción Instrucción UPDATE: UPDATE: Incrementar Incrementar un un 10% 10% la la pendiente pendiente del del puerto puerto ‘Aitana’ ‘Aitana’ al haberse cerrado la carretera que había al haberse cerrado la carretera que había en en buen buen estado estado yy ser ser necesario necesario subir subir por por otra otra peor: peor:

UPDATE UPDATE Puerto Puerto SET SET pendiente pendiente == pendiente pendiente ** 1.10 1.10 WHERE WHERE nompuerto nompuerto == ‘Aitana’ ‘Aitana’ ;;

Bases de Datos

Curso 2006/07

DSIC-UPV

29

Eliminación de Información Instrucción Instrucción DELETE: DELETE: delete delete from from tabla tabla [where [where expresión_condicional] expresión_condicional]

„ Si se incluye la cláusula where se eliminarán

aquéllas que hagan cierta la condición. Eliminar Eliminar la la información información del del ciclista ciclista ‘Miguel ‘Miguel Indurain’ ya que se ha jubilado: Indurain’ ya que se ha jubilado:

DELETE DELETE FROM FROM Ciclista Ciclista WHERE nombre WHERE nombre == ‘Miguel ‘Miguel Indurain’; Indurain’; Bases de Datos

Curso 2006/07

DSIC-UPV

30

15

Trabajo a realizar Consultas Generales (Ciclismo, Música y Biblioteca)

Bases de Datos

Curso 2006/07

DSIC-UPV

31

16