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

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: selectbranch_name fromloan; • 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 selectdistinctbranch_name from loan; • La palabra reservada all especifica que los duplicados no sean removidos. selectallbranch_name fromloan;

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, amuont*100 • 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 selectloan_number fromloan wherebranch_name=‘Perryridge’ andamount>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

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. selectloan_number fromloan whereamountbetween 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 * fromborrower, loan • Encuentre el nombre, número de préstamos cantidad de todos los clientes que tengan préstamos en la sucursal de Perryridge selectcustomer_name, borrower.loan_number, amount fromborrower,loan whereborrower.loan_number=loan.loan_numberandbranch_name=‘Perryridge’;

15. Operación de renombrado • SQL permite el renombrado de relaciones y atributos mediante la cláusula as. • nombre_viejoasnombre_nuevo • Encuentre los nombre, números de préstamo y cantidades de todos los clientes; renombre la columna loan_number como loan_id selectcustomer_name,borrower.loan_numberasloan_id, amount fromborrower, loan whereborrower.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 selectcustomer_name,T.loan_number,S.amount fromborrowerasT, loanasS whereT.loan_numbre=S.loan_number; • Conjunto de nombres de sucursales cuyo capital es mayor que el capìtal de alguna sucursal de Brooklyn selectdistinctT.branch_name frombranchT, branchS whereT.assets > S.assetsand 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“ selectcustomer_name fromcustomer wherecustomer_streetlike '%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 selectdistinct customer_name fromborrower,loan whereborrower.loan_number=loan.loan_number and branch_name='Perryridge' orderbycustomer_name; • Podemos especificar desc para orden descendente o asc para ascendente, para cada atributo; el orden ascendente es por omisión. • orderbycustomer_namedesc;

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

20. Operaciones de conjunto • encontrar todos los clientes que tienen préstamo o cuenta o ambos (selectcustomer_namefromdepositor) union (selectcustomer_namefromborrower); • encontrar todos los clientes que tienen préstamo y cuenta (selectcustomer_namefromdepositor) intersect (selectcustomer_namefromborrower); • encontrar todos los clientes que tienen cuenta pero no préstamo (selectcustomer_namefromdepositor) except (selectcustomer_namefromborrower);

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 selectavg(balance) fromaccount wherebranch_name=‘Perryridge’; • Número de clientes selectcount(*) fromcustomer; • Número de depositantes en el banco selectcount(distinctcustomer_name) fromdepositor;

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 selectbranch_name, count(distinctcustomer_name) fromdepositor, account wheredepositor.account_number = account.account_number groupbybranch_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 selectbranch_name, avg( balance) fromaccount groupbybranch_name havingavg(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.

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. selectloan_number fromloan whereamountisnull; • 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. 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.

27. Null y agregados • Total de las cantidades de los préstamos selectsum(amount) fromloan; • 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 selectdistinctcustomer_name fromborrower wherecustomer_namein( selectcustomer_namefromdepositor); • nombre de los clientes que tienen préstamo pero no tiene una cuenta en el banco selectdistinctcustomer_name fromborrower wherecustomer_name not in( selectcustomer_namefromdepositor);

30. Consultas ejemplo • Encontrar todos los clientes que tienen ambas una cuenta y un préstamo en la sucursal Perryridge selectdistinctcustomer_name fromborrower, loan whereborrower.loan_number = loan.loan_numberand branch_name = 'Perryridge' and customer_namein (selectcustomer_name fromaccount, depositor whereaccount.account_number = depositor.account_numberand 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. selectdistinticTbranch_name frombranchasT, branchasS whereT.assets>S.assets s.branch_city=‘Brooklyn’ • Lo mismo usando la cláusula <some selectbranch_name frombranch whereassets> some (selectassets frombranch wherebranch_city=‘Brooklyn’);

32. Definición de la cláusula some • F <comp> some r t  r s.t.(F<comp>t) • Donde <comp> puede ser: <, , >, =.  (5< some )=true (se lee: 5<alguna tupla en la relación) (5< 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 <comp> all r t  r s.t.(F<comp>t) (5< all )=false (5< 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. selectbranch_name frombranch whereassets> all (selectassets frombranch wherebranch_city=‘Brooklyn’);

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  • notexists r  r =

36. Consulta ejemplo • Encuentre todos los clientes que tienen una cuenta e ntodas las sucursales localizadas en Brooklyn. selectdistinctS.customer_name fromdepositorasS wherenotexists ( (selectbranch_name frombranch wherebranch_city='Brooklyn') except (selectR.branch_name fromdepostorasT, accountasR whereT.account_number=R.account_numberand 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

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. selectT.customer_name fromdepositorasT whereunique ( (selectR.customer_name fromaccount, depositorasR whereT.customer_name=R.customer_nameand R.account_number=account.account_numberand 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. selectdistinctT.customer_name fromdepositorasT wherenotunique ( (selectR.customer_name fromaccount, depositorasR whereT.customer_name=R.customer_nameand R.account_number=account.account_numberand 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: • createview 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.

40. Consultas ejemplo • Una vista consistiendo de sucursales y sus clientes createviewall_customeras (selectbranch_name, customer_name fromdepositor, account wheredepositor.account_number = account.account_number) union (selectbranch_name, customer_name fromborrower, loan whereborrower.loan_number = loan.loan_number); • Encontrar todos los clientes de la sucursal Perryridge. selectcustomer_name fromall_customer Wherebranch_name=‘Perryridge’;

41. Relaciones derivadas • Encuentre el saldo promedio de las cuentas de aquellas sucursales donde el saldo promedio es mayor que $1,200. selectbranch_name, avg_balance from (selectbrach_name, avg( balance) fromaccount groupbybranch_name) asresult(branch_name,avg_balance) whereavg_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.

42. Modificación de la base de datos - borrado • Borrar todos los registros de cuentas en la sucursal Perryridge deletefromaccount wherebranch-name = ‘Perryridge’; • Borrar todas las cuentas en todas las sucursales localizadas en la ciudad de Needham. delete from accountwhere branch_name in (select branch_namefrom branchwhere branch_city = ‘Needham’)delete from depositorwhere account_number in (select account_numberfrom branch, accountwhere 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 accountwhere balance < (select avg (balance)from account);

44. Modificación de la base de datos – inserción • Agregar una tupla a account insert into accountvalues (‘A-9732’, ‘Perryridge’,1200); • O equivalente insertintoaccount (branch_name, balance, account_number)values (‘Perryridge’, 1200, ‘A-9732’) • Agregar nueva tupla a account con saldo nulo insert into accountvalues (‘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 accountselect loan_number, branch_name, 200from loanwhere branch_name = ‘Perryridge’;insert into depositorselect customer_name, loan_numberfrom loan, borrowerwhere 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 intotable1 select * fromtable1 • 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 =balance*1.06 where balance>10000; update account set balance =balance*1.05 where balance<=10000; • 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 balance<=10000 then balance*1.05 else balance*1.06 end;