SQL
Informática aplicada
Contenido
•
•
•
•
•
•
•
•
•
•
Definición de datos
Estructura básica de consultas
Operaciones con conjuntos
Funciones de agregación
Valores nulos
Subconsultas anidadas
Consultas complejas
Vistas
Modificación de la base de datos
Relaciones unidas
Historia
• Lenguaje de IBM Sequel desarrollado como parte del proyecto
sistema R en el laboratorio de investigación de IBM en San Jose
• Renombrado como Lenguaje estructurado de consultas (Structured
Query Language (SQL)
• Estándar ANSI y ISO de SQL:
–
–
–
–
–
SQL-86
SQL-89
SQL-92
SQL: 1999
SQL:2003
• Los sistemas comerciales ofrecen muchas, sino todas, las
facilidades de SQL-92, más variaciones de estándar más recientes.
– NO todo funcionara en el sistema que utilizamos.
Lenguaje de Definición de Datos
DDL
Permite la especificación de no solo conjuntos de
relaciones sino que información de cada relación,
incluyendo:
• El esquema para cada relación
• El dominio de valores asociado con cada atributo
• Restricciones de integridad
• El conjunto de índices a ser mantenido para cada
relación
• Información de seguridad y autorización para cada
relación
• La estructura de almacenaje físico para cada relación en
disco.
Tipos de dominios en SQL
• Char(n). Cadena de longitud fija, con especificación de longitud n
por el usuario
• Varchar(). Cadena de caracteres de longitud variable, con
especificación por el usuario de la longitud máxima n.
• Int. entero (un subconjunto de los enteros de la máquina)
• Smallint. Entero pequeño (un subconjunto dependiente de la
máquina de los enteros)
• Numeric(p,d). Números de punto fijo, precisión especificada por el
usuario de p dígitos con n dígitos a la derecha del punto decimal.
• Real, doble presicision, Punto flotante y punto flotante de doble
precisión, depende de la máquina.
• Float(n). Número de punto flotante, con precisión definida por el
usuario de n dígitos.
• Más en el cap. 4.
Construcción de creación de tablas
• Una relación SQL se define usando el comando create table.
create table r(A1, D1, A2, D2, …, An Dn,
(restricción de integridad1),
…
(restricción de integridadk)
);
– r es el nombre de la relación
– Cada Ai es un nombre de atributo en el esquema de la relación r.
– Di es un tipo de datos de valores en el dominio del atributo Ai.
• Ejemplo:
Create table branch{
(branch_name char(15) not null,
branch_city char(30),
assets integer);
Construcción de borrado y
alteración de tablas
• El comando drop table borra toda la información de la relación
borrada de la base de datos.
• El comando alter table es para agregar atributos a una relación
existente:
– alter table r add A D
• Donde a es el nombre del atributo a ser agregado a la relación r y D
es el dominio de A.
– A todas las tuplas de Ase les asigna el valor null para el nuevo atributo.
• El comando alter table puede ser usado para borara un atributo de
una relación:
– alter table r drop A
• Donde A es el nombre del atributo de la relación r.
– El borrado de atributos no es soportado por muchas bases de datos.
Estructura básica de consultas
• SQL está basado en operaciones de conjuntos y
relacionales con algunas modificaciones y
mejoras
• Una consulta típica de SQLP tiene la forma:
select A1, A2, …,An
from r1, r2, …, rn
where P
– Ai representa un atributo
– Ri representa una relación
– P es un predicado
• El resultado de una consulta SQL es una
relación
La cláusula select
• Le cláusula select lista los atributos deseados
en el resultado de la consulta
– Corresponde a la operación de proyección del
álgebra relacional
• Ejemplo:
select branch_name
from loan;
– Nota: Los nombres en SQL no distinguen entre
mayúsculas y minúsculas
• Estos es Branch_Name = BRANCH_NAME = branch_name
• Algunos usan mayúsculas
La cláusula select cont.
• SQL permite duplicar relaciones asi como en los
resultados de las consultas.
• Para forzar la eliminación de duplicados, inserte
la palabra distinct después de select
• Encontrar los nombres de todas las sucursales
de la relación loan, y remover duplicados
select distinct branch_name
from loan;
• La palabra reservada all especifica que los
duplicados no sean removidos.
select all branch_name
from loan;
La cláusula select cont.
• Un asterisco en la cláusula select denota “todos los
atributos”:
select *
from loan;
• La cláusula select puede contener expresiones
aritméticas involucrando +, -, * y /, y operando en
constantes o atributos de las tuplas.
• La consulta:
• select loan_number,branch_name, amuont*100
• frpm loan;
• Regresará una relación de la relación loan, excepto que
el valor del atributo amount está multiplicado por 100
La cláusula where
• La cláusula where especifica una condición que debe
ser satisfecha
– Corresponde a la selección del álgebra relacional
• Para encontrar todos los números de prestamos hechos
en la sucursal Perryridge con cantidades mayores a
1200
select loan_number
from loan
where branch_name=‘Perryridge’ and amount>1200;
• Los resultados de las comparacione3s pueden ser
usados con conectores lógicos and, or, y not.
• Las comparaciones pueden ser aplicadas a resultados
de expresiones aritméticas
La cláusula where cont.
• SQL incluye el operador de comparación
between (entre)
• Ejemplo: encuentre el número del
préstamo de aquellos préstamos con
cantidades prestadas entre $90000 y
$100000.
select loan_number
from loan
where amount between 90000 and 100000;
La cláusula from
• La cláusula from lista las relaciones involucradas en la
consulta
– Corresponde al producto cartesiano del álgebra relacional
• Encuentre elproducto cartesiano de borrower loan
select *
from borrower, loan
• Encuentre el nombre, número de préstamos cantidad de
todos los clientes que tengan préstamos en la sucursal
de Perryridge
select customer_name, borrower.loan_number, amount
from borrower,loan
where borrower.loan_number=loan.loan_number and
branch_name=‘Perryridge’;
Operación de renombrado
• SQL permite el renombrado de relaciones y
atributos mediante la cláusula as.
– nombre_viejo as nombre_nuevo
• Encuentre los nombre, números de préstamo y
cantidades de todos los clientes; renombre la
columna loan_number como loan_id
select customer_name,borrower.loan_number as
loan_id, amount
from borrower, loan
where borrower.loan_number = loan.loan_number;
Variables de tuplas
• Las variables de tuplas son definidas en la cláusula from vía el uso
de la cláusula as.
• Encontrar los nombres de clientes y sus números de cuenta para
todos los clientes que tengan un préstamo en la misma sucursal
select customer_name,T.loan_number,S.amount
from borrower as T, loan as S
where T.loan_numbre=S.loan_number;
• Conjunto de nombres de sucursales cuyo capital es mayor que el
capìtal de alguna sucursal de Brooklyn
select distinct T.branch_name
from branch T, branch S
where T.assets > S.assets and
S.branch_city = 'Brooklyn';
• La palabra as es opcional y puede ser omitida
Operaciones de cadena
• SQL incluye un operador de verificación de cadenas para
comparaciones en cadenas de caracteres. El operador “like” usa
patrones que son descritos usando dos caracteres especiales:
– Porciento(%). El carácter % concuerda con cualquier subcadena.
– Subraya(_). El carácter _ concuerda con cualquier carácter.
• nombre de clientes en calles con nombres terminados en "hill“
select customer_name
from customer
where customer_street like '%Hill';
• Para concordar con “Hill%”
– like ‘Hill\%’ escape ‘\’
• SQL soporta una variedad de operadores de cadena tales como:
– Concatenación (usando “||”)
– Conversión de mayúsculas a minúsculas y viceversa.
– Encontrar longitud de cadena, extraer subcadenas, etc.
Ordenado del despliegue de tuplas
• lista alfabéticamente los nombres de los clientes
que tengan un préstamo en la sucursal
Perryridge
select distinct customer_name
from borrower,loan
where borrower.loan_number=loan.loan_number and
branch_name='Perryridge'
order by customer_name;
• Podemos especificar desc para orden
descendente o asc para ascendente, para cada
atributo; el orden ascendente es por omisión.
– order by customer_name desc;
Operaciones de conjuntos
• Las operaciones de conjuntos union, intersect y
except operan en relaciones y corresponde a los
operadores del álgebra relacional ,,.
• Cada una de las operaciones anteriores elimina
automáticamente los duplicados; para mantener
los duplicados use la versión correspondiente
de multi conjunto union all, intersect all y except
all.
Suponga una tupla ocurriendo m veces en r y n veces
en s, entonces ocurre
• 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 de conjunto
• encontrar todos los clientes que tienen préstamo o cuenta o ambos
(select customer_name from depositor)
union
(select customer_name from borrower);
• encontrar todos los clientes que tienen préstamo y cuenta
(select customer_name from depositor)
intersect
(select customer_name from borrower);
• encontrar todos los clientes que tienen cuenta pero no préstamo
(select customer_name from depositor)
except
(select customer_name from borrower);
Funciones de agregación
• Estas funciones operan en valores de
multi conjunto de un columna de una
relación, y regresan un valor
•
•
•
•
•
Avg: valor promedio
Min: valor mínimo
Max: valor máximo
Sum: suma de valores
Count: número de valores
Funciones de agregación cont.
• Balance promedio de todas las cuentas de
Perryridge
select avg(balance)
from account
where branch_name=‘Perryridge’;
• Número de clientes
select count(*)
from customer;
• Número de depositantes en el banco
select count(distinct customer_name)
from depositor;
Funciones de agregación
agrupadas
• Nombres de sucursales que tengan al menos
una cuenta, con tamaño de conjunto de clientes
que tengan al menos una cuenta en esa
sucursal
select branch_name, count(distinct customer_name)
from depositor, account
where depositor.account_number =
account.account_number
group by branch_name;
• Nota: atributos en la cláusula select fuera de la
función de agregación deben aparecer en la
lista de group by.
Funciones de agregación –
cláusula having
• Nombres de sucursales que tengan donde el
promedio de saldo es mayor a $650
select branch_name, avg( balance)
from account
group by branch_name
having avg(balance)>650;
Nota: los predicados en la cláusula having son
aplicados después de la formación de los
grupos mientras los predicados de la cláusula
where son aplicados antes de la formación de
los grupos.
Valores nulos
• Es posible que una tupla tenga valores nulos, denotados por null,
para algunos de sus atributos.
• Null significa valor desconocido o que el valor no existe
• El predicado is null puede ser usado para verificar valores nulos.
– Ejemplo: encontrar todos los números de cuenta que aparecen en la
relación loan con valores nulos para amount.
select loan_number
from loan
where amount is null;
• El resultado de operaciones aritméticas involucrando null es null
– Ej. 5 + null es null
• Sin embargo, las funciones de agregación simplemente ignoran los
valores null
Valores nulos y lógica trivaluada
• Cualquier comparación con null regrese desconocido
– Ej. 5<num o nul<>nul o nul=nul
• Lógica de tres valores usando el valor desconocido:
– OR: (desconocido or true) = true
(desconocido or falso) = desconocido
(desconocido or desconocido ) = desconocido
– AND: (desconocido and true) = desconocido
(desconocido or falso) = false
(desconocido or desconocido ) = desconocido
– NOT: desconocido = desconocido
– “P es desconocido” se evalua como true si el predicado P se
evalua como desconocido.
• El resultado de la cláusula where es tratado como false
si se evalua a desconocido.
Null y agregados
• Total de las cantidades de los préstamos
select sum(amount)
from loan;
– La sentencia de arriba ignora los nulos
– El resultado en null si no hay valores no-nulos
para amount
• Todas las operaciones de agregación
excepto count(*) ignora las tupplas con
null en los atributos
Subconsultas anidadas
• SQL provee un mecanismo para anidar
consultas
• Una subconsulta es una expresión selectfrom-where que esta anidada en otra
• Un uso común de subconsultas es probar
pruebas de pertenencia a conjuntos,
comparación de conjuntos y poner
cardinalidad
Consultas ejemplo
• nombre de los clientes que tienen ambas una
cuenta y un préstamo en el banco
select distinct customer_name
from borrower
where customer_name in(
select customer_name from depositor);
• nombre de los clientes que tienen préstamo
pero no tiene una cuenta en el banco
select distinct customer_name
from borrower
where customer_name not in(
select customer_name from depositor);
Consultas ejemplo
• Encontrar todos los clientes que tienen ambas una
cuenta y un préstamo en la sucursal Perryridge
select distinct customer_name
from borrower, loan
where borrower.loan_number = loan.loan_number and
branch_name = 'Perryridge' and
customer_name in (select customer_name
from account, depositor
where account.account_number =
depositor.account_number and
branch_name = 'Perryridge');
• Nota: la consulta anterior se puede escribir de una
manera más sencilla. La formulación anterior es solo
para ilustrar.
Comparaciones de conjuntos
• Encontrar todas las sucursales que tienen el capital más
grande que alguna sucursal de Brooklyn.
select distintic Tbranch_name
from branch as T, branch as S
where T.assets>S.assets
s.branch_city=‘Brooklyn’
• Lo mismo usando la cláusula <some
select branch_name
from branch
where assets> some
(select assets
from branch
where branch_city=‘Brooklyn’);
Definición de la cláusula some
• F <comp> some r t  r s.t.(F<comp>t)
• Donde <comp> puede ser: <, , >, =. 
(5< some
0
5 )=true (se lee: 5<alguna tupla en la relación)
6
(5< some
0 )=false
5
(5= some
0 )= true
5
(5  some 0 )= true
5
(= some)  in
Si embargo, ( some)  not in
Definición de la cláusula all
• F <comp> all r t  r s.t.(F<comp>t)
0
5
6
(5< all
(5< all
6
10
(5= all
4
5
)=false
)= true
)= false
4
(5  all
6 )= true (ya que 5  4 y 5  6)
( all)  not in
Si embargo, ( all)  in
Consulta ejemplo
• Encuentre los nombres de todas las
sucursales que tiene un capital mayor que
todas las sucursales en brookyn.
select branch_name
from branch
where assets> all
(select assets
from branch
where branch_city=‘Brooklyn’);
Prueba de relaciones vacías
• La construcción exists regresa el valor
true si el argumento de la subconsulta
está no vacío.
• exists r  r  
• not exists r  r = 
Consulta ejemplo
• Encuentre todos los clientes que tienen una cuenta e ntodas las
sucursales localizadas en Brooklyn.
select distinct S.customer_name
from depositor as S
where not exists (
(select branch_name
from branch
where branch_city='Brooklyn')
except
(select R.branch_name
from depostor as T, account as R
where T.account_number=R.account_number and
S.customer_name=T.customer_name);
– (esquema usado en este ejemplo)
– Note que X – Y=   X  Y
– Nota: No puede escribir esta consulta usando = all y sus variantes
Prueba para ausencia de
duplicados
• La construcción unique prueba si en una consulta hay
cualquier tupla duplicada en el resultado.
• Encontrar todos los clientes que tienen a lo más una
cuenta en la sucursal Perryridge.
select T.customer_name
from depositor as T
where unique (
(select R.customer_name
from account, depositor as R
where T.customer_name=R.customer_name and
R.account_number=account.account_number and
account.branch_name='Perryridge');
• Esquema usado en este ejemplo
Consulta ejemplo
• Encontrar todos los clientes que tienen al menos
dos cuentas en la sucursal Perryridge.
select distinct T.customer_name
from depositor as T
where not unique (
(select R.customer_name
from account, depositor as R
where T.customer_name=R.customer_name and
R.account_number=account.account_number
and
account.branch_name='Perryridge');
Vistas
• Provee el mecanismo para ocultar ciertos
datos de la vista de ciertos usuarios. Para
crear una vista usamos el comando:
– create view v as <expresión de consulta>
– Donde:
• <expresión de consulta> es cualquier expresión
legal
• El nombre de la vista es representado por v.
Consultas ejemplo
• Una vista consistiendo de sucursales y sus clientes
create view all_customer as
(select branch_name, customer_name
from depositor, account
where depositor.account_number = account.account_number)
union
(select branch_name, customer_name
from borrower, loan
where borrower.loan_number = loan.loan_number);
• Encontrar todos los clientes de la sucursal Perryridge.
select customer_name
from all_customer
Where branch_name=‘Perryridge’;
Relaciones derivadas
• Encuentre el saldo promedio de las cuentas de aquellas
sucursales donde el saldo promedio es mayor que
$1,200.
select branch_name, avg_balance
from (select brach_name, avg( balance)
from account
group by branch_name)
as result(branch_name,avg_balance)
where avg_balance>1200;
Note que no hay necesidad de usar having, ya que
computamos la relación (vista) temporal result en la
cláusula from, y los atributos de result pueden ser
usados directamente en la cláusula where.
Modificación de la base de datos borrado
• Borrar todos los registros de cuentas en la sucursal
Perryridge
delete from account
where branch-name = ‘Perryridge’;
• Borrar todas las cuentas en todas las sucursales
localizadas en la ciudad de Needham.
delete from account
where branch_name in (select branch_name
from branch
where branch_city = ‘Needham’)
delete from depositor
where account_number in
(select account_number
from branch, account
where branch_city = ‘Needham’
and branch.branch_name = account.branch_name);
Consulta ejemplo
• Borra los registros de todas las cuentas con
saldos abajo del promedio en el banco
delete from account
where balance < (select avg (balance)
from account);
– Problema: conforme borramos tuplas de deposit, el
saldo promedio cambia
– Solución de SQL:
• Primero, calcular saldo promedio, y encontrar la tuplas a
borrar
• Después, borrar todas las tuplas de arriba (sin recalcular avg
o re-probar las tuplas)
Modificación de la base de datos –
inserción
• Agregar una tupla a account
insert into account
values (‘A-9732’, ‘Perryridge’,1200);
• O equivalente
insert into account (branch_name, balance,
account_number)
values (‘Perryridge’, 1200, ‘A-9732’)
• Agregar nueva tupla a account con saldo nulo
insert into account
values (‘A-777’,‘Perryridge’, null)
Modificación de la base de datos –
inserción
•
•
•
Dar un regalo a los prestatarios de la sucursal Perryridge, una
cuenta de ahorros de $200, utilizar el número de préstamo como
número de cuenta.
insert into account
select loan_number, branch_name, 200
from loan
where branch_name = ‘Perryridge’;
insert into depositor
select customer_name, loan_number
from loan, borrower
where branch_name = ‘Perryridge’
and loan.account_number = borrower.account_number;
El enunciado select-from-where es evaluado por completo antes de
que cualquier resultado sea insertado en la relación 8de otra forma
la consulta
insert into table1 select * from table1
Causaría problemas
Modificación de la base de datos –
update
• Incrementar todas las cuentas con saldos sobre
$10,000 un 6%, todas las otras recibirán el 5%.
– Escriba dos enunciados update:
update account
set balance =balance*1.06
where balance>10000;
update account
set balance =balance*1.05
where balance<=10000;
– El orden es importante
Enunciado case para
actualizaciones condicionales
• La misma consulta anterior: Incrementar
todas las cuentas con saldos sobre
$10,000 un 6%, todas las otras recibirán el
5%.
update account
set balance = case
when balance<=10000 then balance*1.05
else balance*1.06
end;
Descargar

SQL básico