SQL

1
SQL

• Informática aplicada

2
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

3
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
• SQL2003
• 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.

4
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.

5
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.

6
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)

7
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.

8
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

9
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

10
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

11
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, amuont100
• frpm loan
• Regresará una relación de la relación loan,
  excepto que el valor del atributo amount está
  multiplicado por 100

12
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_namePerryridge and amountgt1200
• 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

13
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

14
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_numberloan.loan_number and
  branch_namePerryridge

15
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

16
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_numbreS.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 gt S.assets and
• S.branch_city 'Brooklyn'
• La palabra as es opcional y puede ser omitida

17
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.

18
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_numberloan.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

19
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
• mn 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

20
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)

21
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

22
Funciones de agregación cont.

• Balance promedio de todas las cuentas de
  Perryridge
• select avg(balance)
• from account
• where branch_namePerryridge
• Número de clientes
• select count()
• from customer
• Número de depositantes en el banco
• select count(distinct customer_name)
• from depositor

23
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.

24
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)gt650
• 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.

25
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

26
Valores nulos y lógica trivaluada

• Cualquier comparación con null regrese
  desconocido
• Ej. 5ltnum o nulltgtnul o nulnul
• 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.

27
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

28
Subconsultas anidadas

• SQL provee un mecanismo para anidar consultas
• Una subconsulta es una expresión
  select-from-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

29
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)

30
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.

31
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.assetsgtS.assets
• s.branch_cityBrooklyn
• Lo mismo usando la cláusula ltsome
• select branch_name
• from branch
• where assetsgt some
• (select assets
• from branch
• where branch_cityBrooklyn)

32
Definición de la cláusula some

• F ltcompgt some r?? t ? r s.t.(Fltcompgtt)
• Donde ltcompgt puede ser lt, ?, gt, . ?
• (5lt some )true (se lee 5ltalguna tupla
  en la relación)
• (5lt some )false
• (5 some ) true
• (5 ? some ) true
• ( some) ? in
• Si embargo, (? some) ? not in

0
5
6
0
5
0
5
0
5
33
Definición de la cláusula all

• F ltcompgt all r?? t ? r s.t.(Fltcompgtt)
• (5lt all )false
• (5lt all ) true
• (5 all ) false
• (5 ? all ) true (ya que 5 ? 4 y 5 ? 6)
• (? all) ? not in
• Si embargo, (? all) ? in

0
5
6
6
10
4
5
4
6
34
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 assetsgt all
• (select assets
• from branch
• where branch_cityBrooklyn)

35
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 ?

36
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_numberR.account_numb
  er and
• S.customer_nameT.customer_name
  )
• (esquema usado en este ejemplo)
• Note que X Y ? ? X ? Y
• Nota No puede escribir esta consulta usando
  all y sus variantes

37
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_nameR.customer_name
  and
• R.account_numberaccount.account_nu
  mber and
• account.branch_name'Perryridge')
• Esquema usado en este ejemplo

38
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_nameR.customer_name
  and
• R.account_numberaccount.account_nu
  mber and
• account.branch_name'Perryridge')

39
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 ltexpresión de consultagt
• Donde
• ltexpresión de consultagt es cualquier expresión
  legal
• El nombre de la vista es representado por v.

40
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_namePerryridge

41
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_balancegt1200
• 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.

42
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 accountwhere branch_name in (select
  branch_name from branch where
  branch_city Needham)delete from
  depositorwhere account_number in
  (select account_number from branch,
  account where branch_city Needham and
  branch.branch_name account.branch_name)

43
Consulta ejemplo

• Borra los registros de todas las cuentas con
  saldos abajo del promedio en el banco
• 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)

delete from account where
balance lt (select avg (balance) from
account)
44
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)

45
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_num
  ber
• 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

46
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 balance1.06
• where balancegt10000
• update account
• set balance balance1.05
• where balancelt10000
• El orden es importante

47
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 balancelt10000 then balance1.05
• else balance1.06
• end