Fundamentos de Bases de datos, 5 Edicin.

Silberschatz, Korth y Sudarshan

Consulte www.db-book.comsobre condiciones de uso
Captulo 3: SQL Captulo 3: SQL
3.2 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Captulo 3: SQL Captulo 3: SQL
Estructura bsica de las consultas
Operaciones sobre conjuntos
Funciones de agregacin
Valores nulos
Subconsultas anidadas
Consultas complejas
Vistas
Modificacin de la base de datos
Reunin de relaciones
Definicin de datos
3.3 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Estructura bsica de las consultas Estructura bsica de las consultas
SQL est basado en operaciones relacionales y de conjunto con ciertas
modificaciones y mejoras
Una consulta caracterstica de SQL tiene la forma:
select A
1
, A
2
, ..., A
n
fromr
1
, r
2
, ..., r
m
where P
A
i
representan los atributos
r
i
representan las relaciones
P es un predicado
Esta consulta es equivalente a la expresin del lgebra relacional.

A1, A2, ..., An

(W
P
(r
1
x r
2
x ... x r
m
))
El resultado de una consulta de SQL es una relacin.
3.4 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
La clusula select La clusula select
La clusula select se utiliza para dar la relacin de los atributos deseados en el
resultado de una consulta
corresponde a la operacin de proyeccin del lgebra
Ejemplo: obtener los nombres de todas las sucursales en la relacin prstamo:
select nombre_sucursal
from prstamo
En la sintaxis del lgebra relacional puro, la consulta debera ser:

nombre_sucursal
(prstamo)
NOTA: los nombres de SQL son de tipo de letra insensitivo (lo que significa que se
puede utilizar las maysculas o minsculas.)
Puede ser deseable utilizar las maysculas en los lugares en los que utilizamos
la fuente en negrita.
3.5 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
La clusula select (Cont.) La clusula select (Cont.)
SQL permite los duplicados en las relaciones adems de en los resultados de la
consulta.
Para forzar la eliminacin de duplicados, insertar la clave distinct despus de
select.
Obtener los nombres de todas las sucursales en las relaciones prstamos, y
anular los duplicados
select distinct nombre_sucursal
from prstamo
La clave all especifica que los duplicados no se han anulado.
select all nombre_sucursal
from prstamo
3.6 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
La clusula select (Cont.) La clusula select (Cont.)
Un asterisco (*) en la clusula select indica todos los atributos
select *
from prstamo
La clusula select puede contener expresiones aritmticas que involucran la
operacin, +, , * y /, y que funcionan en las constantes o en los atributos de las
tuplas.
La consulta:
select nmero_prstamo, nombre_sucursal, importe * 100
from prstamo
volver a una relacin que es la misma que las relaciones prstamo, excepto que
el atributo importe se multiplica por 100.
3.7 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
La clusula where La clusula where
La clusula where especifica las condiciones que debe satisfacer el resultado
Corresponde al predicado de la seleccin del lgebra relacional.
La bsqueda de todos los nmeros de crdito de los prstamos ha dado como
resultado la sucursal Navacerrada con las cantidades de prstamos mayores a
$1200.
select nmero_prstamo
from prstamo
where nombre_sucursal = Navacerrada and importe > 1200
Los resultados de la comparacin se pueden combinar utilizando las conectivas
lgicas and, or y not.
Las comparaciones se pueden aplicar a los resultados de las expresiones
aritmticas.
3.8 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
La clusula where (Cont.) La clusula where (Cont.)
SQL incluye un operador de comparacin between
Ejemplo: Obtener el nmero de prstamo de aquellos con cantidades de crdito
entre 90,000 y 100,000(es decir, u 90,000 y e 100,000)
select nmero_prstamo
from prstamo
where importe between 90000 and 100000
3.9 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
La clusula from La clusula from
La clusula fromhace una lista de las relaciones que se van a explorar en la evaluacin de
la expresin
Corresponde a la operacin del producto cartesiano del lgebra relacional.
Buscar el producto cartesiano prestatario X prstamo
select -
from prestatario, prstamo
Buscar el nombre, el nmero de prstamo y la cantidad del prstamo de todos los
clientes que tengan un crdito en la sucursal Navacerrada.
select nombre_cliente, prestatario.nmero_prstamo, importe
from prestatario, prstamo
where prestatario.nmero_prstamo = prstamo.nmero_prestamo
and
nombre_sucursal = Navacerrada
3.10 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
La operacin de renombramiento La operacin de renombramiento
SQL permite las relaciones y atributos de renombramiento utilizando la clusula as:
nombre_antiguo as nombre_nuevo
Obtener el nombre, el nmero de prstamo y la cantidad del prstamo de todos los
clientes; renombrar el nombre de la columna nmero_prstamo como
identificador_prstamo.
select nombre_cliente, prestatario.nmero_prstamo as identificador_prstamo,
importe
from prestatario, prstamo
where prestatario.nmero_prstamo = prstamo.nmero_prstamo
3.11 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Variables tupla Variables tupla
Las variables tupla se definen en la clusula frommediante el uso de la
clusula as.
Obtener los nombres y nmeros de prstamo de todos los clientes que tengan
un prstamo 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.nmero_prstamo, S.importe
from prestatario as T, prstamo as S
where T.nmero_prstamo = S.nmero_prstamo
3.12 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Operaciones con cadenas Operaciones 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 carcter % encaja con cualquier subcadena.
guin bajo (_). El carcter _ encaja con cualquier carcter.
Obtener los nombres de todos los clientes cuyas calles incluyan la subcadena
Mayor.
select nombre_cliente
from cliente
where calle_cliente like %Mayor%
Coincide el nombre Mayor%
like Mayor\% escape \
SQL soporta una variable de operaciones con cadenas como
concatenacin (que utiliza ||)
conversin de mayscula a minsculas (y viceversa)
Bsqueda de la longitud de la cadena, extraccin de subcadena, etc.
3.13 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Valores nulos Valores nulos
Es posible que las tuplas tengan un valor nulo, indicado por medio de null null, 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 nmeros de prstamos que aparecen en la
relacin prstamo con valores nulos para importe
select nmero_prstamo
fromprstamo
where importe is null
El resultado de la expresin aritmtica que involucra a null es nulo
Ejemplo: 5 + null devuelve nulo
Sin embargo, las funciones de agregacin simplemente ignoran los valores
nulos
Se ofrecer ms informacin ms adelante
3.14 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Valores nulos y lgica de tres valores Valores nulos y lgica de tres valores
Cualquier comparacin con null se convierte en desconocido
Ejemplo: 5 < null o null <> null o null = null
Lgica 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 clusula where se toma como falso si se
evala en desconocido
3.15 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Valores nulos y agregados Valores nulos y agregados
El total de todas las cantidades de prstamos
select sum (importe)
fromprstamo
La instruccin 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.
3.16 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Orden en la presentacin de las tuplas Orden en la presentacin de las tuplas
Lista en orden alfabtico los nombres de todos los clientes que tengan un crdito en la
sucursal Navacerrada
select distinct nombre_cliente
from prestatario, prestamo
where prestatario.nmero_prstamo =prstamo.nmero_prestamo
and sucural_nombre = Navacerrada
order by nombre_cliente
Se puede especificar la clusula 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
3.17 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Duplicados Duplicados
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 r
1
y r
2
:
1. W
U
(r
1
): Si hay c
1
copias de la tupla t
1
en r
1
, y t
1
satisface las selecciones
W
U
,
, entonces hay c
1
copias de t
1
en W
U
(r
1
).
2. 4
A
(r ): Para cada copia tupla t
1
en r
1
, hay una copiade la tupla 4
A
(t
1
) en
4
A
(r
1
) donde 4
A
(t
1
) denota la proyeccin de la tupla singular t
1
.
3. r
1
x r
2
: Si hay c
1
copias de la tupla t
1
en r
1
y c
2
copias de la tupla t
2
en r
2
,
hay c
1
x c
2
copias de la tupla t
1
. t
2
en r
1
x r
2
3.18 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Duplicados (Cont.) Duplicados (Cont.)
Ejemplo: Supngase que las relaciones multiconjunto r
1
(A, B) y r
2
(C)
son las siguientes:
r
1
= {(1, a) (2,a)} r
2
= {(2), (3), (3)}
Entonces 4
B
(r
1
) debera ser{(a), (a)}, mientras 4
B
(r
1
) x r
2
debera ser
{(a,2), (a,2), (a,3), (a,3), (a,3), (a,3)}
SQL duplica la semntica:
select A
1
,
,
A
2
, ..., A
n
from r
1
, r
2
, ..., r
m
where P
es equivalente a la versin multiconjunto del la expresin:
)) ( (
2 1 , , ,
2 1
m P A A A
r r r
n
- - - -
-
W
3.19 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Operaciones con conjuntos Operaciones 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
automticamente; para retener todos los duplicados se utilizan las versiones
de multiconjunto correspondientes union all, intersect all y except all.
Supngase 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
3.20 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Operaciones con conjuntos Operaciones con conjuntos
Obtener todos los clientes que tengan un prstamo, una cuenta o ambos:
(select nombre_cliente from impositor)
except
(select nombre_cliente fromprestatario)
(select nombre_cliente from impositor)
intersect
(select nombre_cliente fromprestatario)
Obtener todos los clientes que tengan una cuenta pero no un prstamo.
(select nombre_cliente from impositor)
union
(select nombre_cliente fromprestatario)
Obtener todos los clientes que tengan un prstamo y una cuenta.
3.21 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Funciones de agregacin Funciones de agregacin
Estas funciones operan en el multiconjunto de valores de una columna
de una relacin, y devuelven un valor
avg: valor medio
min: valor mnimo
max: valor mximo
sum: suma de valores
count: nmero de valores
3.22 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Funciones de agregacin (cont.) Funciones de agregacin (cont.)
Obtener el saldo medio de las cuentas de la sucursal Navacerrada.
Obtener el nmero de impositores en el banco
Obtener el nmero de tuplas de la relacin cliente
select avg (saldo)
fromcuenta
where nombre_sucursal = Navacerrada
select count (*)
from cliente
select count (distinct nombre_clientes)
from impositor
3.23 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Funciones de agregacin Funciones de agregacin Group By Group By
Obtener el nmero de impositores de cada sucursal.
Nota: Los atributos de la clusula select fuera de las funciones de agregacin deben
aparecer en la lista group by
select nombre_sucursal, count (distinct nombre_cliente)
from impositor, cuenta
where impositor.nmero_cuenta = cuenta.nmero_cuenta
group by nmero_sucursal
3.24 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Funciones de agregacin Funciones de agregacin
Clusula Having Clusula 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 clusula having se aplican despus de
la formacin de grupos mientras que los permitidos en la
clusula where se aplican antes de la formacin de grupos
select nombre_sucursal, avg (saldo)
fromcuenta
group by nombre_sucursal
having avg (saldo) > 1200
3.25 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Subconsultas anidadas Subconsultas anidadas
SQL proporciona un mecanismo para las subconsultas anidadas.
Una subconsulta es una expresin select-from-where que se anida dentro de otra
consulta.
Un uso comn de subconsultas es llevar a cabo comprobaciones sobre
pertenencia a conjuntos, comparacin de conjuntos y cardinalidad de conjuntos.
3.26 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Ejemplo de consulta Ejemplo de consulta
Obtener todos los clientes que tengan una cuenta y un prstamo en el banco.
Obtener todos los clientes que tengan un prstamo en el banco pero que
no tengan una cuenta en dicho banco
select distinct nombre_cliente
from prestatario
where nombre_cliente not in (select nombre_cliente
from impositor)
select distinct nombre_cliente
from prestatario
where nombre_cliente in (select nombre_cliente
fromimpositor )
3.27 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Ejemplo de consulta Ejemplo de consulta
Obtener todos los clientes que tengan tanto una cuenta como un prstamo en
la sucursal Navacerrada
Note: Se puede escribir la consulta anterior de forma mucho ms simple. La
formulacin anterior es simplemente para ilustrar las caractersticas de SQL
select distinct nombre_cliente
from prestatario, prstamo
where prestatario.nmero_prstamo = prstamo.nmero_prstamo and
nombre_sucursal = Navacerrada and
(nombre_sucursal, nombre_cliente) in
(select nombre_sucursal, nombre_cliente
from impositor, cuenta
where impositor.nmero_cuenta =
cuenta.nmero_cuenta )
3.28 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Comparacin de conjuntos Comparacin 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_sucursal
from sucursal
where activo > some
(select activo
from sucursal
where ciudad_sucursal = Barcelona)
select distinct T.nombre_sucursal
fromsucursal as T, sucursal as S
where T.activo > S.activo and
S.ciudad_sucursal = Barcelona
3.29 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Definicin de la clausula Some Definicin de la clausula Some
F <comp> some r n t r tal que (F <comp> t )
donde <comp> puede ser: , e, >, =, =
0
5
6
(5 < some ) = verdadero
0
5
0
) = falso
5
0
5 (5 = some ) = verdadero (y que 0 = 5)
(leer: 5 < alguna tupla de la relacin)
(5 < some
) = verdadero (5 = some
(= some) | in
Sin embargo, (= some) | not in
3.30 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Consulta ejemplo Consulta ejemplo
Obtener los nombres de todas las sucursales que tienen activos mayores que
todas las sucursales situadas en Barcelona.
select nombre_sucusal
from sucursal
where activo > all
(select activo
fromsucursal
where branch_city = Brooklyn)
3.31 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Definicin de la clusula all Definicin de la clusula all
F <comp> all r V t r (F <comp> t)
0
5
6
(5 < all ) = falso
6
10
4
) = verdadero
5
4
6 (5 = all
) = verdadero (ya que 5 = 4 y 5 = 6)
(5 < all
) = falso (5 = all
(= all) | not in
Sin embargo, (= all) | in
3.32 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Comprobacin de relaciones vacas Comprobacin de relaciones vacas
El construccin exists devuelve el valor true si la subconsulta argumento no es
vaca.
exists r r =
not exists r r =
3.33 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Consulta ejemplo Consulta ejemplo
Obtener todos los clientes que tengan una cuenta en todas las sucursales
situadas en Barcelona.
select distinct S.nombre_cliente
from impositor as S
where not exists (
(select nombre_sucursal
from sucursal
where ciudad_sucursal = Barcelona)
except
(select R.nombre_sucursal
fromimpositor as T, cuenta as R
where T.nmero_cuenta = R.nmero_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
3.34 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Comprobacin de ausencia de tuplas duplicadas Comprobacin de ausencia de tuplas duplicadas
La construccin unique comprueba si una subconsulta tiene alguna tupla
duplicada en sus resultados.
Obtener todos los clientes que slo tengan una cuenta en la sucursal
Navacerrada.
select T.nombre_cliente
from impositor as T
where unique (
select R.nombre_cliente
fromcuenta, impositor as R
where T.nombre_cliente = R.nombre_cliente and
R.nmero_cuente = cuenta.nmero_cuenta and
cuenta.nombre_sucursal = Navacerrada )
3.35 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Consulta ejemplo Consulta ejemplo
Obtener todos los clientes que tengan al menos dos cuentas en la sucursal
Navacerrada.
select distinct T.nombre_cliente
from impositor as T
where not unique (
select R.nombre_cliente
from cuenta, impositor as R
where T.nombre_cliente = R.nombre_cliente and
R.nmero_cliente = cuenta.nmero_cuenta and
cuenta.nombre_sucursal = Navacerrada)
3.36 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Relaciones derivadas Relaciones derivadas
SQL permite utilizar expresiones de subconsulta en la clusula from
Obtener el saldo promedio de las cuentas en las que dicho saldo sea mayor de
1200.
select nombre_sucursal, saldo_medio
from (select nombre_sucursal, avg (saldo)
fromcuenta
group by nombre_sucursal)
as media_sucursal(nombre_sucursal, saldo_medio)
where saldo_medio > 1200
Tngase en cuenta que no es necesario utilizar la clusula having, puesto que se
calcula la relacin temporal (vista) resultado en la clusula from, y los atributos
de media_sucursal se pueden utilizar directamente en la clusula where.
3.37 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
La clusula with La clusula with
La clusula with proporciona una forma de definir una vista temporal cuya
definicin slo se puede utilizar en la consulta en la que est la clusula with
Obtener todas las cuentas con el saldo mximo
with saldo_maximo (valor) as
select max (saldo)
from cuenta
select number_cuenta
from cuenta, saldo_mximo
where cuenta.saldo = saldo_mximo.valor
3.38 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Consulta compleja que utiliza la clusula with Consulta compleja que utiliza la clusula with
Obtener todas las sucursales donde el depsito total de las cuentas es mayor
que la media del total de depsitos de cuentas en todas las sucursales
with total_sucursales(nombre-sucursal, valor) as
select nombre_sucursal, sum (saldo)
fromcuenta
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
3.39 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Vistas Vistas
En algunos casos, no es deseable para todos los usuarios ver el modelo lgico
completo (es decir, todas las relaciones actuales almacenadas en la base de
datos).
Considere una persona que necesita conocer un nmero de prstamo de un
cliente pero no tiene necesidad de conocer el importe del prstamo. Esta persona
debera ver una relacin descrita en SQL como
(select nombre_cliente, nmero_prstamo
from prestatario, prstamo
where prestatario.nmero_prstamo = prstamo.nmero_prstamo)
Una vista proporciona un mecanismo para ocultar ciertos datos de la vista de
ciertos usuarios.
Cualquier relacin que no es del modelo conceptual pero se hace visible para el
usuario como una relacin virtual se denomina una view.
3.40 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Definicin de vista Definicin de vista
Una vista se define utilizando la instruccin create view que tiene la forma
create view v as <expresin de consulta>
donde <expresin de consulta> es cualquier expresin 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
relacin virtual que la vista genera.
La definicin de vista no es lo mismo que la creacin de una nueva relacin
mediante la evaluacin de la expresin de consulta.
Una definicin de vista permite el ahorro de una expresin para ser
sustituida por consultas que utilizan esa vista.
3.41 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Consultas de ejemplo Consultas 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.nmero_cuenta =
cuenta. nmero_cuenta )
union
(select nombre_sucursal, nombre_cliente
from prestatario, prstamo
where prestatario.nmero_cuenta = prstamo.nmero_cuenta )
select nombre_cliente
from todos_los_clientes
where nombre_sucursal = Navacerrada
3.42 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Vistas definidas en funcin de otras Vistas definidas en funcin de otras
Una vista puede utilizarse en la expresin que define a otra vista
Se dice que una relacin de vistas v
1
depende directamente de una relacin de
vistas v
2,
si v
2
se utiliza en la expresin que define a v
1
Se dice que una relacin de vistas v
1
depende de la relacin de vistas v
2
tanto
si v
1
depende directamente de v
2
como si hay un camino de dependencias de
v
1
a v
2
Se dice que una relacin de vistas es recursiva si depende de s misma
3.43 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Expansin de vistas Expansin de vistas
Una forma de definir el significado de las vistas definidas en trminos de otras
vistas.
Sea la vista v
1
definida por una expresin e
1
que puede contener a su vez usos
de relaciones de vistas.
La expansion de vistas de una expresin repite la siguiente etapa de
sustitucin:
repeat
Averiguar todas las relaciones de vistas v
i
en e
1
Sustituir la relacin de vistas v
i
por la expresin que define v
i
until no queden ms relaciones de vistas en e
1
Mientras las definiciones de vistas no sean recursivas, este bucle concluir
3.44 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Modificacin de la base de datos Modificacin de la base de datos Borrado Borrado
Borrar todos los registros de cuentas de la sucursal Navacerrada
delete from cuenta
where nombre_sucursal = Navacerrada
Borrar todas las cuentas de cada sucursal situada en la ciudad de Navacerrada.
delete from cuenta
where nombre_sucursal in (select nombre_sucursal
from sucursal
where ciudad_sucursal = Navacerrada)
3.45 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Consulta ejemplo Consulta ejemplo
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
Solucin utilizada en SQL:
1. Primero, calcular el saldo medio avg (saldo) de todas las tuplas que se van a
borrar
2. Despus, borrar todas las tuplas encontradas antes (sin recalcular avg
(saldo) o recomprobando las tuplas)
3.46 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Modificacin de la base de datos Modificacin de la base de datos Insercin Insercin
Aadir una nueva tupla a cuenta
insert into cuenta
values (A-9732, Navacerrada,1200)
o equivalente
insert into cuenta (nombre_sucursal, saldo, nmero_cuenta)
values (Navacerrada, 1200, A-9732)
Aadir una nueva tupla a la cuenta con saldo a nulo
insert into cuenta
values (A-777,Navacerrada, null)
3.47 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Modificacin de la base de datos Modificacin de la base de datos Insercin Insercin
Se proporciona como regalo a todos los clientes que tengan un prstamo en la
sucursal Navacerrada, una cuenta de ahorro de 200. Hacer que el nmero de
prstamo sirva como nmero de cuenta de la nueva cuenta de ahorro
insert into cuenta
select nmero_prstamo, nombre_sucursal, 200
from prstamo
where nombre_sucursal = Navacerrada
insert into impositor
select nombre_cliente, nmero_prstamo
from prstamo, prestatario
where nombre_sucursal = Navacerrada
and prstamo.nmero_cuenta= prestatario.nmero_cuenta
La sentencia select from where se evala completamente antes de que ninguno de
sus resultados se inserte en la relacin (de otra forma las consultas como
insert into tabla1 select * from tabla1
generaran problemas)
3.48 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Modificacin de la base de datos Modificacin de la base de datos Actualizaciones Actualizaciones
Aumentar todas las cuentas con saldos por encima de 10.000 con el 6%, todas
las dems cuentas reciben un 5%.
Escribir dos instrucciones update:
update cuenta
set saldo = saldo - 1,06
where saldo > 10000
update cuenta
set saldo = saldo - 1,05
where saldo e 10000
El orden es importante
Se puede hacer utilizando la instruccin case (siguiente transparencia)
3.49 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Instruccin case para actualizaciones condicionales Instruccin case para actualizaciones condicionales
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
3.50 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Actualizacin de una vista Actualizacin de una vista
Crear una vista de todos los datos de prstamos en la relacin prstamo,
ocultando el atributo importe
create view sucursal_prstamo as
select nmero_prstamo, nombre_sucursal,
from prstamo
Aadir una tupla nueva a sucursal_prstamo
insert into sucursal_prstamo
values (Navacerrada, P-37)
Esta insercin se debe representar mediante la insercin de la tupla
(P-37, Navacerrada, null)
dentro de la relacin prstamo
3.51 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Updates Through Views (Cont.) Updates Through Views (Cont.)
Algunas actualizaciones de vistas son difciles o imposibles de traducir en
relaciones de la base de datos
create view v as
select 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 prstamo o cuenta y crear un nuevo nmero de
prstamo/cuenta!
La mayor parte de las implementaciones de SQL permiten actualizar slo
vistas simples (sin agregados) definidas sobre una sola relacin.
3.52 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Reunin de relaciones** Reunin de relaciones**
Las operaciones de reunin toman dos relaciones y las devuelven como
resultado otra relacin.
Estas operaciones adicionales se utilizan generalmente como expresiones de
subconsulta de la clusula from
Condicin de reunin define qu tuplas de las dos relaciones coinciden, y
qu atributos estn presentes en el resultado de la reunin.
Tipo de reunin define cmo se tratan las tuplas de cada relacin que no
coincide con ninguna tupla de la otra relacin (basada en la condicin de
reunin).
Tipos de reunin
inner join
left outer join
right outer join
full outer join
Condiciones de reunin
natural
on <predicado>
using (A
1
, A
2
, ..., A
n
)
3.53 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Reunin de relaciones Reunin de relaciones Conjuntos de datos para Conjuntos de datos para
ejemplos ejemplos
Relacin prstamo
Relacin prestatario
Nota: no se tiene la informacin del prestatario para P-260 ni la
informacin de prstamo para P-155
importe
3000
4000
1700
nombre-sucursal
Centro
Moralzarzal
Navacerrada
nmero-prstamo
P-170
P-230
P-260
nombre-cliente nmero-prstamo
Santos
Gmez
Lpez
P-170
P-230
P-155
3.54 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Reunin de relaciones Reunin de relaciones Ejemplos Ejemplos
prstamo inner join prestatario on
prstamo.nmero_prstamo = prestatario.nmero_prstamo
prstamo left inner join prestatario on
prstamo.nmero_prstamo = prestatario.nmero_prstamo
nombre-sucursal importe
Centro
Moralzarzal
3000
4000
nombre-cliente nmero-prstamo
Santos
Gmez
P-170
P-230
nmero-prstamo
P-170
P-230
nombre-sucursal importe
Centro
Moralzarzal
Navacerrada
3000
4000
1700
nombre-cliente nmero-prstamo
Santos
Gmez
null
P-170
P-230
null
nmero-prstamo
P-170
P-230
P-260
3.55 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Reunin de relaciones Reunin de relaciones Ejemplos Ejemplos
prstamo natural inner join prestatario
prstamo natural right outer join prestatario
branch-name amount
Downtown
Redwood
3000
4000
loan-number
L-170
L-230
nombre-sucursal importe
Centro
Moralzarzal
3000
4000
nombre-cliente
Santos
Gmez
nmero-prstamo
P-170
P-230
nombre-sucursal importe
Centro
Moralzarzal
null
3000
4000
null
nombre-cliente
Santos
Gmez
Lpez
nmero-prstamo
P-170
P-230
P-155
3.56 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Reunin de relaciones Reunin de relaciones Ejemplos Ejemplos
prstamo full outer join prestatario using (nmero_prstamo)
Obtener todos los clientes que tengan una cuenta o un prstamo (pero no
ambos) en el banco.
select nombre_cliente
from (impositor natural full outer join prestatario)
where nmero_cuenta is null or nmero_prstamo is null
nombre-sucursal importe
Centro
Moralzarzal
Navacerrada
null
3000
4000
1700
null
nombre-cliente
Santos
Gmez
null
Lpez
nmero-prstamo
P-170
P-230
P-260
P-155
3.57 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Lenguaje de definicin de datos Lenguaje de definicin de datos
El esquema para cada relacin
El dominio de los valores asociados de cada atributo
Las restricciones de integridad
El conjunto de ndices para mantener con cada relacin
La informacin de seguridad y autorizacin para cada relacin
La estructura del almacenamiento fsico en disco para cada relacin
Permite la especificacin del conjunto de relaciones y de ola
informacin sobre cada relacin incluyendo:
3.58 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Tipos de Dominio en SQL Tipos 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 mxima n
especificada por el usuario.
int. Entero (un subconjunto finito de los enteros que es dependiente de la mquina).
smallint. Entero pequeo (un subconjunto dependiente de la mquina del dominio
del tipo de los enteros).
numeric(p,d). Nmero real, con precisin especificada por el usuario de p dgitos,
con n dgitos a la derecha del punto decimal.
real, double precision. Nmero real, y de doble precisin, con precisin dependiente
de la mquina.
float(n). Nmero real, con precisin especificada por el usuario de al menos n
dgitos.

3.59 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Constructor Create Table Constructor Create Table
Una relacin SQL es definida utilizando el comando create table:
create table r (A
1
D
1
, A
2
D
2
, ..., A
n
D
n
,
(integrity-constraint
1
),
...,
(integrity-constraint
k
))
r es el nombre de la relacin
cada A
i
es un nombre de atributo del esquema de la relacin r
D
i
es el tipo de datos de los valores en el dominio del atributo A
i
Ejemplo:
create table branch
(branch_name char(15) not null,
branch_citychar(30),
assets integer)
3.60 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Restricciones de integridad en Create Table Restricciones de integridad en Create Table
not null
primary key (A
1
, ..., A
n
)
Ejemplo: Declara branch_name como la clave primaria de branch y
asegurate que los valores de assets no son negativos.
create table branch
(branch_namechar(15),
branch_city char(30),
assets integer,
primary key (branch_name))
La declaracin de primary key en un atributo automticamente asegura
not null en SQL-92, y necesita ser explcitamente establecida en SQL-
89
3.61 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Borrar y modificar tablas Borrar y modificar tablas
El comando drop table borra toda la informacin acerca de la relacin
borrada de la base de datos.
El comando alter table se utiliza para aadir atributos a una relacin
existente:
alter table r add A D
donde A es el nombre del atributo a ser aadido a la relacin r y D es el
dominio de A.
En todas las tuplas de la relacin es asignado como null el valor del
nuevo atributo.
El comando alter table tambin puede ser utilizado para borrar atributos
de la relacin:
alter table r drop A
donde A es el nombre de una atributo de la relacin r
El borrado de atributos no est soportado en muchas bases de datos.
Fundamentos de Bases de datos, 5 Edicin.
Silberschatz, Korth y Sudarshan
Consulte www.db-book.comsobre condiciones de uso
Fin del captulo 3 Fin del captulo 3
3.63 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Figura 3.1: Esquema de la base de datos Figura 3.1: Esquema de la base de datos
sucursal (nombre_sucursal, ciudad_sucursal, activos)
cliente (nombre_cliente, calle_cliente, ciudad_cliente)
prstamo (nmero_prstamo, nombre_sucursal, importe)
prestatario (nombre_cliente, nmero_prstamo)
cuenta (nmero_cuenta, nombre_sucursal, saldo)
impositor (nombre_cliente, nmero_cuenta)
3.64 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Figure 3.3: Tuples inserted into Figure 3.3: Tuples inserted into loan loan and and
borrower borrower
3.65 Silberschatz, Korth y Sudarshan Fundamentos de Bases de Datos 5 Edicin, 2005
Figura 3.4: Figura 3.4:
Relaciones Relaciones prstamo prstamo y prestatario y prestatario