chuleta sql

Fundamentos de Bases de datos, 5ª Edición.

©Silberschatz, Korth y SudarshanConsulte www.db-book.com sobre condiciones de uso

Capítulo 3: SQLCapítulo 3: SQL

3.2 ©Silberschatz, Korth y SudarshanFundamentos de Bases de Datos – 5ª Edición, 2005

Capítulo 3: SQLCapítulo 3: SQL

Estructura básica de las consultas

Operaciones sobre conjuntos

Funciones de agregación

Valores nulos

Subconsultas anidadas

Consultas complejas

Vistas

Modificación de la base de datos

Reunión de relaciones

Definición de datos


Estructura básica de las consultasEstructura básica de las consultas

SQL está basado en operaciones relacionales y de conjunto con ciertas modificaciones y mejoras

Una consulta característica de SQL tiene la forma:select A1, A2, ..., An

from r1, r2, ..., rm

where P

Ai representan los atributos

ri representan las relaciones

P es un predicado Esta consulta es equivalente a la expresión del álgebra relacional.

A1, A2, ..., An(P (r1 x r2 x ... x rm))

El resultado de una consulta de SQL es una relación.


La cláusula selectLa cláusula select La cláusula select se utiliza para dar la relación de los atributos deseados

en el resultado de una consulta corresponde a la operación de proyección del álgebra

Ejemplo: obtener los nombres de todas las sucursales en la relación préstamo:

select nombre_sucursalfrom préstamo

En la sintaxis del álgebra relacional “puro”, la consulta debería ser:

nombre_sucursal (préstamo)

NOTA: los nombres de SQL son de tipo de letra insensitivo (lo que significa que se puede utilizar las mayúsculas o minúsculas.) Puede ser deseable utilizar las mayúsculas en los lugares en los que

utilizamos la fuente en negrita.


La cláusula select (Cont.)La cláusula select (Cont.)

SQL permite los duplicados en las relaciones además de en los resultados de la consulta.

Para forzar la eliminación de duplicados, insertar la clave distinct después de select.

Obtener los nombres de todas las sucursales en las relaciones préstamos, y anular los duplicados

select distinct nombre_sucursal from préstamo

La clave all especifica que los duplicados no se han anulado.

select all nombre_sucursalfrom préstamo


La cláusula select (Cont.)La cláusula select (Cont.)

Un asterisco (*) en la cláusula select indica “todos los atributos”

select *from préstamo

La cláusula select puede contener expresiones aritméticas que involucran la operación, +, –, * y /, y que funcionan en las constantes o en los atributos de las tuplas.

La consulta:

select número_préstamo, nombre_sucursal, importe * 100 from préstamo

volverá a una relación que es la misma que las relaciones préstamo, excepto que el atributo importe se multiplica por 100.


La cláusula whereLa cláusula where

La cláusula where especifica las condiciones que debe satisfacer el resultado Corresponde al predicado de la selección del álgebra relacional.

La búsqueda de todos los números de crédito de los préstamos ha dado como resultado la sucursal Navacerrada con las cantidades de préstamos mayores a $1200.

select número_préstamofrom préstamowhere nombre_sucursal = ‘ Navacerrada’ and importe >

1200

Los resultados de la comparación se pueden combinar utilizando las conectivas lógicas and, or y not.

Las comparaciones se pueden aplicar a los resultados de las expresiones aritméticas.


La cláusula where (Cont.)La cláusula where (Cont.)

SQL incluye un operador de comparación between

Ejemplo: Obtener el número de préstamo de aquellos con cantidades de crédito entre 90,000€ y 100,000€(es decir, 90,000€ y 100,000€)

select número_préstamofrom préstamowhere importe between 90000 and 100000


La cláusula fromLa cláusula from

La cláusula from hace una lista de las relaciones que se van a explorar en la evaluación de la expresión Corresponde a la operación del producto cartesiano del álgebra relacional.

Buscar el producto cartesiano prestatario X préstamo

select from prestatario, préstamo

Buscar el nombre, el número de préstamo y la cantidad del préstamo de todos los clientes que tengan un crédito en la sucursal Navacerrada.

select nombre_cliente, prestatario.número_préstamo, importe from prestatario, préstamo where prestatario.número_préstamo = préstamo.número_prestamo

and nombre_sucursal = ‘Navacerrada’


La operación de renombramientoLa operación de renombramiento

SQL permite las relaciones y atributos de renombramiento utilizando la cláusula as:

nombre_antiguo as nombre_nuevo

Obtener el nombre, el número de préstamo y la cantidad del préstamo de todos los clientes; renombrar el nombre de la columna número_préstamo como identificador_préstamo.

select nombre_cliente, prestatario.número_préstamo as identificador_préstamo, importe from prestatario, préstamo where prestatario.número_préstamo = préstamo.número_préstamo


Variables tuplaVariables tupla

Las variables tupla se definen en la cláusula from mediante el uso de la cláusula as.

Obtener los nombres y números de préstamo de todos los clientes que tengan un préstamo en alguna sucursal.

select distinct T.nombre_sucursal from sucursal as T, sucursal as S where T.activos> S.activos and S.ciudad_sucural = ‘ Barcelona’

Obtener los nombres de todas las sucursales que tengan activos mayores que las sucursales situadas en Barcelona.

select nombre_cliente, T.número_préstamo, S.importe from prestatario as T, préstamo as S where T.número_préstamo = S.número_préstamo


Operaciones con cadenasOperaciones con cadenas

SQL incluye un operador de coincidencia de cadenas para comparaciones de cadenas de caracteres. *The operator “like” uses patterns that are described using two special characters: tanto por ciento(%). El carácter % encaja con cualquier subcadena. guión bajo (_). El carácter _ encaja con cualquier carácter.

Obtener los nombres de todos los clientes cuyas calles incluyan la subcadena “Mayor”.

select nombre_clientefrom clientewhere calle_cliente like ‘%Mayor%’

Coincide el nombre “Mayor%”like ‘Mayor\%’ escape ‘\’

SQL soporta una variable de operaciones con cadenas como concatenación (que utiliza “||”) conversión de mayúscula a minúsculas (y viceversa) Búsqueda de la longitud de la cadena, extracción de subcadena, etc.


Valores nulosValores nulos

Es posible que las tuplas tengan un valor nulo, indicado por medio de nullnull, en alguno de sus atributos

null significa un valor desconocido o un valor que no existe.

El predicado is null se puede utilizar para comprobar los valores nulos.

Ejemplo: obtener todos los números de préstamos que aparecen en la relación préstamo con valores nulos para importe

select número_préstamofrom préstamowhere importe is null

El resultado de la expresión aritmética que involucra a null es nulo

Ejemplo: 5 + null devuelve nulo

Sin embargo, las funciones de agregación simplemente ignoran los valores nulos

Se ofrecerá más información más adelante


Valores nulos y lógica de tres valoresValores nulos y lógica de tres valores

Cualquier comparación con null se convierte en desconocido

Ejemplo: 5 < null o null <> null o null = null

Lógica de tres valores que utiliza el valor real desconocido:

OR: (desconocido or cierto) = true, (desconocido or falso) = desconocido, (desconocido or unknown) = unknown

AND: (cierto and desconocido) = desconocido, (falso and desconocido) = falso, (desconocido and desconocido) = desconocido

NOT: (not desconocido) = desconocido

“P is unknown” se evalua a cierto si el predicado P se evalua a desconocido

El resultado del predicado de la cláusula where se toma como falso si se evalúa en desconocido


Valores nulos y agregadosValores nulos y agregados

El total de todas las cantidades de préstamos

select sum (importe)from préstamo

La instrucción anterior ignora las cantidades nulas

El resultado es null si no hay cantidad no nula

Todas las operaciones agregadas excepto count(*) ignoran las tuplas con valores nulos de los atributos agregados.


Orden en la presentación de las tuplasOrden en la presentación de las tuplas

Lista en orden alfabético los nombres de todos los clientes que tengan un crédito en la sucursal Navacerrada

select distinct nombre_clientefrom prestatario, prestamowhere prestatario.número_préstamo =préstamo.número_prestamo

and sucural_nombre = ‘Navacerrada’order by nombre_cliente

Se puede especificar la cláusula desc para orden descendente o asc para orden ascendente, de cada atributo; el orden ascendente es el orden por defecto.

Ejemplo: order by nombre_cliente desc


DuplicadosDuplicados

En las relaciones con duplicados, SQL definir cuantas copias de las tuplas aparecen en el resultado.

Las versiones multiconjunto de algunos de los operadores del álgebra relacional – dadas las relaciones multiconjunto r1 y r2:

1. (r1): Si hay c1 copias de la tupla t1 en r1, y t1 satisface las

selecciones ,, entonces hay c1 copias de t1 en (r1).

2. A (r ): Para cada copia tupla t1 en r1, hay una copiade la tupla

A (t1) en A (r1) donde A (t1) denota la proyección de la tupla

singular t1.

3. r1 x r2 : Si hay c1 copias de la tupla t1 en r1 y c2 copias de la tupla t2 en r2, hay c1 x c2 copias de la tupla t1. t2 en r1 x r2


Duplicados (Cont.)Duplicados (Cont.)

Ejemplo: Supóngase que las relaciones multiconjunto r1 (A, B) y r2 (C) son las siguientes:

r1 = {(1, a) (2,a)} r2 = {(2), (3), (3)}

Entonces B(r1) debería ser{(a), (a)}, mientras B(r1) x r2

debería ser

{(a,2), (a,2), (a,3), (a,3), (a,3), (a,3)}

SQL duplica la semántica:

select A1,, A2, ..., An

from r1, r2, ..., rm

where P

es equivalente a la versión multiconjunto del la expresión:

))(( 21,,, 21 mPAAA rrrn


Operaciones con conjuntosOperaciones con conjuntos

Las operaciones de conjuntos union, intersect, y except operan sobre relaciones y corresponden a las operaciones de álgebra relacional

Cada una de las operaciones antes citadas elimina duplicados automáticamente; para retener todos los duplicados se utilizan las versiones de multiconjunto correspondientes union all, intersect all y except all.

Supóngase que una tupla se produce m veces en r y n veces en s, entonces, se produce:

m + n veces en r union all s

min(m,n) veces en r intersect all s

max(0, m – n) veces en r except all s


Operaciones con conjuntosOperaciones con conjuntos

Obtener todos los clientes que tengan un préstamo, una cuenta o ambos:

(select nombre_cliente from impositor)except(select nombre_cliente from prestatario)

(select nombre_cliente from impositor)intersect(select nombre_cliente from prestatario)

Obtener todos los clientes que tengan una cuenta pero no un préstamo.

(select nombre_cliente from impositor)union(select nombre_cliente from prestatario)

Obtener todos los clientes que tengan un préstamo y una cuenta.


Funciones de agregaciónFunciones de agregación

Estas funciones operan en el multiconjunto de valores de una columna de una relación, y devuelven un valor

avg: valor mediomin: valor mínimomax: valor máximosum: suma de valorescount: número de valores


Funciones de agregación (cont.)Funciones de agregación (cont.)

Obtener el saldo medio de las cuentas de la sucursal Navacerrada.

Obtener el número de impositores en el banco

Obtener el número de tuplas de la relación cliente

select avg (saldo)from cuentawhere nombre_sucursal = ‘Navacerrada’

select count (*)from cliente

select count (distinct nombre_clientes)from impositor


Funciones de agregación – Group ByFunciones de agregación – Group By

Obtener el número de impositores de cada sucursal.

Nota: Los atributos de la cláusula select fuera de las funciones de agregación deben aparecer en la lista group by

select nombre_sucursal, count (distinct nombre_cliente) from impositor, cuenta where impositor.número_cuenta = cuenta.número_cuenta group by número_sucursal


Funciones de agregación – Funciones de agregación – Cláusula HavingCláusula Having

Obtener los nombres de todas las sucursales en las que el saldo medio de las cuentas es mayor de $1.200.

Nota: los predicados de la cláusula having se aplican después de la formación de grupos mientras que los permitidos en la

cláusula where se aplican antes de la formación de grupos

select nombre_sucursal, avg (saldo) from cuenta group by nombre_sucursal having avg (saldo) > 1200


Subconsultas anidadasSubconsultas anidadas

SQL proporciona un mecanismo para las subconsultas anidadas.

Una subconsulta es una expresión select-from-where que se anida dentro de otra consulta.

Un uso común de subconsultas es llevar a cabo comprobaciones sobre pertenencia a conjuntos, comparación de conjuntos y cardinalidad de conjuntos.


Ejemplo de consultaEjemplo de consulta

Obtener todos los clientes que tengan una cuenta y un préstamo en el banco.

Obtener todos los clientes que tengan un préstamo en el banco pero que no tengan una cuenta en dicho banco

select distinct nombre_clientefrom prestatariowhere nombre_cliente not in (select nombre_cliente

from impositor)

select distinct nombre_clientefrom prestatariowhere nombre_cliente in (select nombre_cliente

from impositor )


Ejemplo de consultaEjemplo de consulta

Obtener todos los clientes que tengan tanto una cuenta como un préstamo en la sucursal Navacerrada

Note: Se puede escribir la consulta anterior de forma mucho más simple. La formulación anterior es simplemente para ilustrar las

características de SQL

select distinct nombre_clientefrom prestatario, préstamowhere prestatario.número_préstamo = préstamo.número_préstamo and

nombre_sucursal = ‘Navacerrada’ and (nombre_sucursal, nombre_cliente) in

(select nombre_sucursal, nombre_cliente from impositor, cuenta where impositor.número_cuenta =

cuenta.número_cuenta )


Comparación de conjuntosComparación de conjuntos

Obtener los nombres de todas las sucursales que tengan activos mayores que al menos una sucursal situada en Barcelona.

La misma consulta utilizando la clausula > some

select nombre_sucursalfrom sucursalwhere activo > some (select activo from sucursal

where ciudad_sucursal = ‘Barcelona’)

select distinct T.nombre_sucursalfrom sucursal as T, sucursal as Swhere T.activo > S.activo and S.ciudad_sucursal = ‘ Barcelona ’


Definición de la clausula SomeDefinición de la clausula Some

F <comp> some r t r tal que (F <comp> t )donde <comp> puede ser:

056

(5 < some ) = verdadero

05

0

) = falso

5

05(5 some ) = verdadero (y que 0 5)

(leer: 5 < alguna tupla de la relación)

(5 < some

) = verdadero(5 = some

(= some) inSin embargo, ( some) not in


Consulta ejemploConsulta ejemplo

Obtener los nombres de todas las sucursales que tienen activos mayores que todas las sucursales situadas en Barcelona.

select nombre_sucusalfrom sucursalwhere activo > all

(select activofrom sucursalwhere branch_city = ‘Brooklyn’)


Definición de la cláusula allDefinición de la cláusula all

F <comp> all r t r (F <comp> t)

056

(5 < all ) = falso

610

4

) = verdadero

5

46(5 all ) = verdadero (ya que 5 4 y 5 6)

(5 < all

) = falso(5 = all

( all) not inSin embargo, (= all) in


Comprobación de relaciones vacíasComprobación de relaciones vacías

El construcción exists devuelve el valor true si la subconsulta argumento no es vacía.

exists r r Ø

not exists r r = Ø



Obtener todos los clientes que tengan una cuenta en todas las sucursales situadas en Barcelona.

select distinct S.nombre_clientefrom impositor as Swhere not exists (

(select nombre_sucursalfrom sucursalwhere ciudad_sucursal = ‘Barcelona’)

except(select R.nombre_sucursalfrom impositor as T, cuenta as Rwhere T.número_cuenta = R.número_cuenta and

S.nombre_cliente = T.nombre_cliente)

Tenga en cuenta que X – Y = Ø X Y

Nota: No se puede escribir una consulta utilizando = all y sus variantes


Comprobación de ausencia de tuplas Comprobación de ausencia de tuplas duplicadasduplicadas

La construcción unique comprueba si una subconsulta tiene alguna tupla duplicada en sus resultados.

Obtener todos los clientes que sólo tengan una cuenta en la sucursal Navacerrada.

select T.nombre_cliente from impositor as T where unique (

select R.nombre_cliente from cuenta, impositor as R where T.nombre_cliente = R.nombre_cliente and

R.número_cuente = cuenta.número_cuenta and cuenta.nombre_sucursal = ‘ Navacerrada’ )



Obtener todos los clientes que tengan al menos dos cuentas en la sucursal Navacerrada.

select distinct T.nombre_clientefrom impositor as Twhere not unique ( select R.nombre_cliente from cuenta, impositor as R where T.nombre_cliente = R.nombre_cliente and

R.número_cliente = cuenta.número_cuenta and cuenta.nombre_sucursal = ‘Navacerrada’)


Relaciones derivadasRelaciones derivadas

SQL permite utilizar expresiones de subconsulta en la cláusula from

Obtener el saldo promedio de las cuentas en las que dicho saldo sea mayor de 1200€.

select nombre_sucursal, saldo_mediofrom (select nombre_sucursal, avg (saldo)

from cuenta group by nombre_sucursal)

as media_sucursal(nombre_sucursal, saldo_medio)where saldo_medio > 1200

Téngase en cuenta que no es necesario utilizar la cláusula having, puesto que se calcula la relación temporal (vista) resultado en la cláusula from, y los atributos de media_sucursal se pueden utilizar directamente en la cláusula where.


La cláusula withLa cláusula with

La cláusula with proporciona una forma de definir una vista temporal cuya definición sólo se puede utilizar en la consulta en la que está la cláusula with

Obtener todas las cuentas con el saldo máximo

with saldo_maximo (valor) as select max (saldo) from cuenta select number_cuenta from cuenta, saldo_máximo where cuenta.saldo = saldo_máximo.valor


Consulta compleja que utiliza la cláusula withConsulta compleja que utiliza la cláusula with

Obtener todas las sucursales donde el depósito total de las cuentas es mayor que la media del total de depósitos de cuentas en todas las sucursales

with total_sucursales(nombre-sucursal, valor) as select nombre_sucursal, sum (saldo) from cuenta group by nombre_sucursal with media_total_sucursales (valor) as select avg (valor) from total_sucursales select nombre_sucursal from total_sucursales, media_total_sucursales where total_sucursales.valor >= media_total_sucursales.valor


VistasVistas

En algunos casos, no es deseable para todos los usuarios ver el modelo lógico completo (es decir, todas las relaciones actuales almacenadas en la base de datos).

Considere una persona que necesita conocer un número de préstamo de un cliente pero no tiene necesidad de conocer el importe del préstamo. Esta persona debería ver una relación descrita en SQL como

(select nombre_cliente, número_préstamo from prestatario, préstamo where prestatario.número_préstamo = préstamo.número_préstamo)

Una vista proporciona un mecanismo para ocultar ciertos datos de la vista de ciertos usuarios.

Cualquier relación que no es del modelo conceptual pero se hace visible para el usuario como una “relación virtual” se denomina una view.


Definición de vistaDefinición de vista

Una vista se define utilizando la instrucción create view que tiene la forma

create view v as <expresión de consulta>

donde <expresión de consulta> es cualquier expresión de consulta legal de SQL. El nombre de la vista se representa por v.

Una vez definida la vista, su nombre puede utilizarse para referirse a la relación virtual que la vista genera.

La definición de vista no es lo mismo que la creación de una nueva relación mediante la evaluación de la expresión de consulta.

Una definición de vista permite el ahorro de una expresión para ser sustituida por consultas que utilizan esa vista.


Consultas de ejemploConsultas de ejemplo

Una vista de las sucursales y sus clientes.

Averiguar todos los clientes de la sucursal de Navacerrada

create view todos_los_clientes as (select nombre_sucursal, nombre_cliente from impositor, cuenta where impositor.número_cuenta = cuenta. número_cuenta ) union (select nombre_sucursal, nombre_cliente from prestatario, préstamo where prestatario.número_cuenta = préstamo.número_cuenta )

select nombre_clientefrom todos_los_clienteswhere nombre_sucursal = ‘Navacerrada’


Vistas definidas en función de otrasVistas definidas en función de otras

Una vista puede utilizarse en la expresión que define a otra vista

Se dice que una relación de vistas v1 depende directamente de una relación de vistas v2, si v2 se utiliza en la expresión que define a v1

Se dice que una relación de vistas v1 depende de la relación de vistas v2 tanto si v1 depende directamente de v2 como si hay un camino de dependencias de v1 a v2

Se dice que una relación de vistas es recursiva si depende de sí misma


Expansión de vistasExpansión de vistas

Una forma de definir el significado de las vistas definidas en términos de otras vistas.

Sea la vista v1 definida por una expresión e1 que puede contener a su vez usos de relaciones de vistas.

La expansion de vistas de una expresión repite la siguiente etapa de sustitución:

repeatAveriguar todas las relaciones de vistas vi en e1

Sustituir la relación de vistas vi por la expresión que define vi until no queden más relaciones de vistas en e1

Mientras las definiciones de vistas no sean recursivas, este bucle concluirá


Modificación de la base de datos– BorradoModificación de la base de datos– Borrado

Borrar todos los registros de cuentas de la sucursal Navacerrada

delete from cuentawhere nombre_sucursal = ‘Navacerrada’

Borrar todas las cuentas de cada sucursal situada en la ciudad de Navacerrada.

delete from cuentawhere nombre_sucursal in (select nombre_sucursal

from sucursal where ciudad_sucursal =

‘Navacerrada’)



Borrar el registro de todas las cuentas con saldos inferiores a la media del banco.

delete from cuenta where saldo < (select avg (saldo)

from cuenta )

Problema: al borrar tuplas, el saldo medio cambia

Solución utilizada en SQL:

1. Primero, calcular el saldo medio avg (saldo) de todas las tuplas que se van a borrar

2. Después, borrar todas las tuplas encontradas antes (sin recalcular avg (saldo) o recomprobando las tuplas)


Modificación de la base de datos– InserciónModificación de la base de datos– Inserción

Añadir una nueva tupla a cuenta

insert into cuentavalues (‘A-9732’, ‘Navacerrada’,1200)

o equivalente

insert into cuenta (nombre_sucursal, saldo, número_cuenta)values (‘Navacerrada’, 1200, ‘A-9732’)

Añadir una nueva tupla a la cuenta con saldo a nulo

insert into cuentavalues (‘A-777’,‘Navacerrada’, null)


Modificación de la base de datos– InserciónModificación de la base de datos– Inserción

Se proporciona como regalo a todos los clientes que tengan un préstamo en la sucursal Navacerrada, una cuenta de ahorro de 200€. Hacer que el número de préstamo sirva como número de cuenta de la nueva cuenta de ahorro

insert into cuentaselect número_préstamo, nombre_sucursal, 200from préstamowhere nombre_sucursal = ‘Navacerrada’

insert into impositorselect nombre_cliente, número_préstamofrom préstamo, prestatariowhere nombre_sucursal = ‘Navacerrada’ and préstamo.número_cuenta= prestatario.número_cuenta

La sentencia select from where se evalúa completamente antes de que ninguno de sus resultados se inserte en la relación (de otra forma las consultas como

insert into tabla1 select * from tabla1generarían problemas)


Modificación de la base de datos– ActualizacionesModificación de la base de datos– Actualizaciones

Aumentar todas las cuentas con saldos por encima de 10.000€ con el 6%, todas las demás cuentas reciben un 5%.

Escribir dos instrucciones update:

update cuentaset saldo = saldo 1,06where saldo > 10000

update cuentaset saldo = saldo 1,05where saldo 10000

El orden es importante

Se puede hacer utilizando la instrucción case (siguiente transparencia)


Instrucción case para actualizaciones Instrucción case para actualizaciones condicionalescondicionales

Misma consulta que la anterior: Aumentar todas las cuentas con saldos por encima de 10.000€ con el 6%, todas las otras cuentas reciben el 5%.

update cuenta set saldo = case when saldo <= 10000 then saldo *1,05 else saldo * 1,06 end


Actualización de una vistaActualización de una vista

Crear una vista de todos los datos de préstamos en la relación préstamo, ocultando el atributo importe

create view sucursal_préstamo asselect número_préstamo, nombre_sucursal, from préstamo

Añadir una tupla nueva a sucursal_préstamo

insert into sucursal_préstamo values (‘Navacerrada’, ‘P-37’)

Esta inserción se debe representar mediante la inserción de la tupla

(‘P-37’, ‘Navacerrada’, null)

dentro de la relación préstamo


Updates Through Views (Cont.)Updates Through Views (Cont.)

Algunas actualizaciones de vistas son difíciles o imposibles de traducir en relaciones de la base de datos

create view v asselect nombre_sucursal from cuenta

insert into v values (‘P-99’, ‘ Navacerrada’, ‘23’)

Otras no se pueden traducir de forma única

insert into todos_los_clientes values (‘Navacerrada’, ‘Juan’)

¡Hay que elegir préstamo o cuenta y crear un nuevo número de préstamo/cuenta!

La mayor parte de las implementaciones de SQL permiten actualizar sólo vistas simples (sin agregados) definidas sobre una sola relación.


Reunión de relaciones**Reunión de relaciones**

Las operaciones de reunión toman dos relaciones y las devuelven como resultado otra relación.

Estas operaciones adicionales se utilizan generalmente como expresiones de subconsulta de la cláusula from

Condición de reunión – define qué tuplas de las dos relaciones coinciden, y qué atributos están presentes en el resultado de la reunión.

Tipo de reunión – define cómo se tratan las tuplas de cada relación que no coincide con ninguna tupla de la otra relación (basada en la condición de reunión).

Tipos de reunión

inner joinleft outer joinright outer joinfull outer join

Condiciones de reunión

naturalon <predicado>using (A1, A2, ..., An)


Reunión de relaciones – Conjuntos de Reunión de relaciones – Conjuntos de datos para ejemplosdatos para ejemplos

Relación préstamo

Relación prestatario

Nota: no se tiene la información del prestatario para P-260 ni la información de préstamo para P-155

importe

3000

4000

1700

nombre-sucursal

Centro

Moralzarzal

Navacerrada

número-préstamo

P-170

P-230

P-260

nombre-cliente número-préstamo

Santos

Gómez

López

P-170

P-230

P-155


Reunión de relaciones – EjemplosReunión de relaciones – Ejemplos

préstamo inner join prestatario onpréstamo.número_préstamo = prestatario.número_préstamo

préstamo left inner join prestatario onpréstamo.número_préstamo = prestatario.número_préstamo

nombre-sucursal importe

Centro

Moralzarzal

3000

4000


Santos

Gómez

P-170

P-230

número-préstamo

P-170

P-230


Centro

Moralzarzal

Navacerrada

3000

4000

1700


Santos

Gómez

null

P-170

P-230

null

número-préstamo

P-170

P-230

P-260



préstamo natural inner join prestatario

préstamo natural right outer join prestatario

branch-name amount

Downtown

Redwood

3000

4000

loan-number

L-170

L-230


Centro

Moralzarzal

3000

4000

nombre-cliente

Santos

Gómez

número-préstamo

P-170

P-230


Centro

Moralzarzal

null

3000

4000

null

nombre-cliente

Santos

Gómez

López

número-préstamo

P-170

P-230

P-155



préstamo full outer join prestatario using (número_préstamo)

Obtener todos los clientes que tengan una cuenta o un préstamo (pero no ambos) en el banco.

select nombre_clientefrom (impositor natural full outer join prestatario)where número_cuenta is null or número_préstamo is null


Centro

Moralzarzal

Navacerrada

null

3000

4000

1700

null

nombre-cliente

Santos

Gómez

null

López

número-préstamo

P-170

P-230

P-260

P-155


Lenguaje de definición de datosLenguaje de definición de datos

El esquema para cada relación El dominio de los valores asociados de cada atributo Las restricciones de integridad El conjunto de índices para mantener con cada relación La información de seguridad y autorización para cada

relación La estructura del almacenamiento físico en disco para cada

relación

Permite la especificación del conjunto de relaciones y de ola información sobre cada relación incluyendo:


Tipos de Dominio en SQLTipos de Dominio en SQL

char(n). Cadena de caracteres de longitud fija con longitud n especificada por el usuario.

varchar(n). Cadena de caracteres de longitud variable con longitud máxima n especificada por el usuario.

int. Entero (un subconjunto finito de los enteros que es dependiente de la máquina).

smallint. Entero pequeño (un subconjunto dependiente de la máquina del dominio del tipo de los enteros).

numeric(p,d). Número real, con precisión especificada por el usuario de p dígitos, con n dígitos a la derecha del punto decimal.

real, double precision. Número real, y de doble precisión, con precisión dependiente de la máquina.

float(n). Número real, con precisión especificada por el usuario de al menos n dígitos.

…


Constructor Create TableConstructor Create Table

Una relación SQL es definida utilizando el comando create table:

create table r (A1 D1, A2 D2, ..., An Dn,(integrity-constraint1),...,(integrity-constraintk))

r es el nombre de la relación

cada Ai es un nombre de atributo del esquema de la relación r

Di es el tipo de datos de los valores en el dominio del atributo Ai

Ejemplo:

create table branch(branch_name char(15) not null,branch_city char(30),assets integer)


Restricciones de integridad en Create TableRestricciones de integridad en Create Table

not null

primary key (A1, ..., An )

Ejemplo: Declara branch_name como la clave primaria de branch y asegurate que los valores de assets no son negativos.

create table branch (branch_name char(15), branch_city char(30), assets integer, primary key (branch_name))

La declaración de primary key en un atributo automáticamente asegura not null en SQL-92, y necesita ser explícitamente establecida en SQL-89


Borrar y modificar tablasBorrar y modificar tablas

El comando drop table borra toda la información acerca de la relación borrada de la base de datos.

El comando alter table se utiliza para añadir atributos a una relación existente:

alter table r add A D

donde A es el nombre del atributo a ser añadido a la relación r y D es el dominio de A.

En todas las tuplas de la relación es asignado como null el valor del nuevo atributo.

El comando alter table también puede ser utilizado para borrar atributos de la relación:

alter table r drop A

donde A es el nombre de una atributo de la relación r

El borrado de atributos no está soportado en muchas bases de datos.

Fundamentos de Bases de datos, 5ª Edición.

©Silberschatz, Korth y SudarshanConsulte www.db-book.com sobre condiciones de uso

Fin del capítulo 3Fin del capítulo 3


Figura 3.1: Esquema de la base de datosFigura 3.1: Esquema de la base de datos

sucursal (nombre_sucursal, ciudad_sucursal, activos)

cliente (nombre_cliente, calle_cliente, ciudad_cliente)

préstamo (número_préstamo, nombre_sucursal, importe)

prestatario (nombre_cliente, número_préstamo)

cuenta (número_cuenta, nombre_sucursal, saldo)

impositor (nombre_cliente, número_cuenta)


Figure 3.3: Tuples inserted into Figure 3.3: Tuples inserted into loan loan and and borrowerborrower


Figura 3.4:Figura 3.4:Relaciones Relaciones préstamo préstamo y prestatarioy prestatario

chuleta sql

Documents