Bases de Datos 1

1. Bases de Datos 1 Teórico: Structured Query Language

2. Historia • Los orígenes del SQL están ligados a los orígenes de las bases de datos relacionales • Estandarizado por ANSI en 1986 (SQL-86) • Hubieron varias revisiones: • SQL-89 • SQL-92 • SQL-1999 • SQL-2003 • SQL-2006 • SQL-2008 • SQL-2011

3. Características • Opera sobre conjunto de tuplas • No elimina automáticamente tuplas repetidas • Lenguaje no procedural • Su poder de expresión incluye el álgebra relacional y lo extiende • Se distinguen dos sublenguajes: • DDL (Data Definition Language) • DML (Data Manipulation Language)

4. Data Definition Language (DDL) • Permite crear, modificar y eliminar objetos de la base de datos: • Tablas • Una tabla es un conjunto de valores organizados en filas y columnas. Es la representación de una relación aunque no son estrictamente equivalentes • Vistas • Una vista es una tabla virtual basada en el resultado de una consulta. Pueden usarse en consultas como si fueran tablas • Usuarios

5. Operaciones sobre tablas • CREATE TABLE • Crea una nueva tabla • Parámetros: • Nombre de la tabla • Nombre y tipo de dato de cada columna • Restricciones de clave primaria y clave foránea sobre otras tablas • ALTER TABLE • Modifica una tabla existente • DROP TABLE • Elimina una tabla existente y elimina los datos almacenados en ella

6. Ejemplo • Sistema de Información relativo a hospitales: HOSPITALES(codHosp, nomHosp, direccion) MEDICOS(ciMed, nomMed, especialidad) PACIENTES(ciPac, nomPac, fchNac, sexo)

7. Ejemplo Creo la tabla hospitales: CREATE TABLE hospitales( codHosp integer NOT NULL, nomHosp character varying(40), direccion character varying(20) ); ALTER TABLE hospitales add constraint hospitales_pkey PRIMARY KEY (codHosp);

8. Operaciones sobre vistas • CREATE VIEW • Crea una vista • Ejemplo: Creo una vista sobre la tabla hospitales en la que no aparezca la dirección: CREATE VIEWhospitalesSinDirAS (SELECT codHosp, nomHosp FROM hospitales ); • ALTER VIEW • Modifica una vista • DROP VIEW • Elimina una vista

9. Data Manipulation Language (DML) • Permite crear, modificar, eliminar y recuperar datos: • INSERT • Agrega tuplas a una tabla • Ejemplo: INSERT INTO hospitales VALUES (1, ‘Maciel’, ‘25 de Mayo 174’); • UPDATE • Actualiza tuplas de una tabla • Ejemplo: UPDATE hospitales SET direccion = ‘25 de Mayo 172’) WHEREcodHosp= 1;

10. DML • DELETE • Borra tuplas de una tabla • Ejemplo: DELETE hospitales WHERE nomHosp = ‘Maciel’; • SELECT • Recupera datos • Ejemplo: SELECT direccion FROM hospitales WHEREnomHosp = ‘Maciel’;

11. Recuperación de datos • Sintaxis: SELECTA1, …, An FROM R1, …, Rm WHEREC; donde: • A1, …, An son nombres de atributos. También se puede utilizar (*) • R1, …, Rm son nombres de tablas • C es una condición booleana

12. Cláusula ORDER BY • La cláusula ORDER BYpermite ordenar el resultado ascendentemente o descendentemente (ASC ó DESC) • Ejemplo: Devolver los códigos de los hospitales ordenados ascendentemente SELECTcodHosp FROMhospitales ORDER BY codHosp ASC;

13. Cláusula DISTINCT • La cláusula DISTINCTpermite filtrar tuplas repetidas • Ejemplo: Devolver las especialidades, sin repetir, de los médicos SELECTDISTINCT especialidad FROMmedicos;

14. Operador JOIN • JOIN es un operador que se utiliza para combinar datos de dos o más tablas basados en una relación entre determinadas columnas en estas tablas • En el JOIN sólo se incluyen en el resultado tuplas que coincidan en valor en los campos del JOIN

15. Ejemplo PERSONAS(ci, nombre, apellido, fchNac, lugarNac) TECNOLOGOS(ci, fchTitulo) • Obtener los nombres y apellidos de los Tecnólogos: SELECTnombre, apellido FROM(personas JOINtecnologos ON personas.ci = tecnologos.ci);

16. Otros tipos de JOIN • NATURAL JOIN: Elimina columnas con nombres repetidos • LEFT JOIN: Agrega para cada tupla de T1 que no satisface la condición de JOIN con ninguna de T2, una fila con nulos en las columnas de T2 • RIGHT JOIN: Análogo a LEFT JOIN pero se incluyen todos los de T2 • FULL JOIN: Equivalente a la unión de LEFT JOIN y RIGHT JOIN

17. Alias • Ejemplo: Devolver las parejas de cédulas de identidad de las personas que tienen igual nombre pero distinto apellido SELECTp1.ci, p2.ci FROMpersonas p1, personas p2 WHEREp1.nombre = p2.nombre and p1.apellido <> p2.apellido;

18. Renombrar atributos • Ejemplo: PRODUCTOS(nroProd, nombre, peso) FABRICANTES( nroFab, nombre, departamento) VENTAS( nroFab, nroProd, precio) Dar los nombres de fabricantes y los nombres de los productos que venden SELECT fabricantes.nombre asnomFab, productos.nombre asnomProd FROM ventas, fabricantes, productos WHERE ventas.nroFab = fabricantes.nroFab and ventas.nroProd = productos.nroProd;

19. Unión (UNION) • Ejemplo: Devolver la cédula de identidad de las personas que nacieron en el año 2000 o que nacieron en Salto SELECT ci FROM personas WHERE personas.fchNac >= to_date('01/01/2000','dd/mm/yyyy') and personas.fchNac <= to_date('31/12/2000','dd/mm/yyyy') UNION SELECT ci FROM personas WHERE personas.lugarNac = ‘Salto’; • La UNION elimina tuplas repetidas

20. Diferencia (NOT IN) • Ejemplo: Devolver las cédulas de identidad de las personas que no se recibieron de Tecnólogo SELECT ci FROM personas WHERE ci NOT IN (SELECT ci FROM tecnologos);

21. Funciones y operadores aritméticos • En las cláusulas SELECT y WHERE se pueden aplicar funciones y operadores aritméticos sobre atributos: • Funciones: round (n), abs (n), etc • Operadores aritméticos: (+, *, /)

22. Funciones de agregación • Las funciones de agregación extienden el álgebra relacional • Se aplican sobre conjuntos de tuplas, no sobre tuplas individuales • Permiten sumar, obtener el máximo, contar tuplas, etc

23. Consultas anidadas • Las consultas anidadas son consultas dentro de la cláusula WHERE de otra consulta • Sintaxis: SELECTA1, …, An FROM R1, …, Rm WHERE Aj, …, Ak <op_comp> (SELECT B1, …, Bk FROM S1, …, Sm WHERE C); donde <op-comp> puede ser: IN = ANY > ANY = ALL > ALL

24. Función EXISTS • La función EXISTS sirve para chequear si el resultado de una consulta no es vacío • Ejemplo: Dar los nombres de los fabricantes que sólo venden el producto número 15. SELECT nombre FROM fabricantes f1, ventas v1 WHERE f1.nroFab = v1.nroFab andv1.nroProd = 15 AND NOT EXISTS (SELECT * FROM ventas v2 WHERE v2.nroFab = f1.nroFab and v2.nroProd <> 15);

25. Cláusula GROUP BY • La cláusula GROUP BY sirve para agrupar tuplas • El agrupamiento se realiza después de aplicar el WHERE, es decir, sobre las tuplas que cumplen la condición • En el SELECT sólo puede haber atributos presentes en la cláusula GROUP BY, funciones de agregación sobre atributos y/o expresiones aritméticas

26. Ejemplo • ACTIVIDADES(ci-est, cod-as, cod-car, tipo-act, fecha, aprobo, nota) • En esta relación: • Se guardan todas las actividades que realizan los estudiantes en la facultad • Estas actividades pueden ser de distintos tipos (examen (‘E’) o realización de curso (‘RC’)) • El atributo aprobo tiene valor ‘S’ o ‘N’. Si este atributo tiene valor ‘S’ asumimos que la asignatura esta aprobada totalmente, no importa el tipo de actividad que sea • El atributo fecha corresponde a la fecha en la que se realizó la actividad

27. Ejemplo • Dar una lista que contenga código de asignatura, código de carrera y cantidad de aprobados a partir del 01/12/2001 SELECTcod-as, cod-car, COUNT(*) FROMactividades WHERE fecha > to_date(‘01/12/2001’,’dd/mm/yyyy’) and aprobo= ‘S’ GROUP BYcod-as, cod-car;

28. Cláusula HAVING • La cláusula HAVING sirve para especificar condiciones sobre grupos • Ejemplo: Dar el número de fabricante y los promedios de precios a los cuales vendió, pero para los fabricantes con más de 3 ventas SELECT nroFab, avg(precio) FROM VENTAS GROUP BY nroFab HAVING count(*) > 3;

29. Sub-consultas en el FROM • Utilizar sub-consultas en el FROM es equivalente al uso de vistas • Ejemplo:Dar el máximo del promedio de ventas de cada fabricante SELECT MAX (promedio) FROM (SELECT AVG (precio) as promedio FROM ventas GROUP BY nroFab) as promedios

30. Cambios de formato • Por defecto existen funciones que permiten manipular los tipos de datos • Los manejadores también disponen de funciones específicas • Concatenación de string: Se utiliza el operador | | • Mayúsculas y minúsculas: upper(<atributo>), lower(<atributo>)

31. Material de consulta Tutorial de SQL • http://www.w3schools.com/sql/default.asp Tutorial interactivo de SQL • http://sqlzoo.net/ Documentación PostgreSQL • http://www.postgresql.org/docs/