sql - bases de datos 2 - tecnologo de informatica · 2016. 4. 20. · structured query language...

SQLBases de Datos 2 - Tecnologo de Informatica

BD2 - SQL

Outline

1 Structured Query Language (SQL)Introducción y características

2 SQL como Data Definition LanguageConceptos básicos, Tabla baseTabla virtual, Vista MaterializadaTipos de datosRestricciones, Acciones por violación, Valores pordefecto

3 SQL como Data Manipulation LanguageTablas concatenadas (INNER/OUTER JOIN)Valores NULLConsultas anidadas y comparación deconjuntos/multiconjuntosFunciones de agregación y agrupamiento de tuplasConstructor CASE y consultas recursivas

BD2 - SQL

Structured Query Language (SQL)Historia

SQL significa Lenguaje de consulta estructurado(Sructured Query Language). Originalmente llamadoSEQUEL (Sructured English QUEry Language).En 1986 el Instituto nacional americano de normalización(ANSI, American National Standards Institute) y laOrganización internacional para la normalización (ISO,International Standards Organization) estandarizaron unaprimer versión denominada SQL-86 (o SQL1).Luego le siguieron las versiones SQL-92 (o SQL2), el másampliamente conocido SQL-99 (o SQL3), lasactualizaciones SQL:2003 y SQL:2006 que incorporaronfuncionalidades de XML, SQL:2008 y la última estableSQL:2011.Comenzando con SQL-99, el standard se divide en laespecificación de un nucleo y extensiones.

BD2 - SQL

Structured Query Language (SQL)Características de SQL Standard

Opera sobre multiconjuntos de tuplas:No elimina automáticamente tuplas repetidas.El mismo lenguaje puede ser usado:

En forma interactiva (Ej.: consola)Embebido en un lenguaje de programación

ISO/IEC 9075-2:2011

BD2 - SQL

Structured Query Language (SQL)Características de SQL Standard

Su poder de expresión incluye el álgebra relacional y laextiende.Se identifican dos sublenguajes:

DDL (Data Definition Language):Permite crear, modificar y eliminar objetos de la base.EJ.: CREATE, ALTER, DROP

DML (Data Manipulation Language):Permite crear, modificar, eliminar y recuperar datos.EJ.: INSERT, UPDATE, DELETE, SELECT

BD2 - SQL

Outline




BD2 - SQL

DDLData Definition Language

DDL permite especificar información acerca de relaciones,incluyendo:

El esquema de cada relación.El dominio de valores asociado a cada atributo.Restricciones de integridad.Más adelante veremos que también otra información comoser:

El conjunto de índices a mantener para cada relación.Información sobre seguridad y autorización para cadarelación.Estructura del almacenamiento físico en disco de cadarelación.

BD2 - SQL

DDL - Tabla¿Qué es una tabla?

SQL utiliza los términos tabla (table), fila (row) y columna(column) para los términos relación, tupla y atributo delmodelo relacional formal, respectivamente.Una tabla es conjunto de valores organizados encolumnas y filas.Es la representación de una relación pero no sonestrictamente equivalentes.

BD2 - SQL

DDL - TablaOperaciones sobre tablas

CREATE TABLECrea una nueva tabla de la base.Parámetros:

Nombre de la tablaNombre de cada columnaTipo de datos de cada columnaRestricciones de clave primaria y clave foránea sobre otrastablas

Los atributos se considerarán ordenados en la secuenciaen que se especifiquen.

ALTER TABLEModifica una tabla existente

DROP TABLEElimina la definición de la tabla (y los datos almacenadosen ella si existen)

BD2 - SQL

DDL - TablaEjemplo

Figura : Esq. base de datos relacional Empresa

BD2 - SQL

DDL - TablaEjemplo

BD2 - SQL

DDL - TablaEjemplo

PRODS(#p, Nombre, Peso, Desc)Contiene todos los productos.

FABS(#f, Nombre, Departamento)Contiene los fabricantes de productos.

VENTAS(#f, #p, Precio)Indica qué fabricante vende qué producto y a que precio.

BD2 - SQL

DDL - TablaEjemplo

Creo la tabla PRODS

CREATE TABLE PRODS(#p integer NOT NULL,Nombre character varying(50),CONSTRAINT prods_pk PRIMARY KEY (#p));

Agrego la columna “Peso” a la tabla PRODS.

ALTER TABLE PRODSADD Peso double precision;

Modifico la especificación de la columna “Peso”.

ALTER TABLE PRODS ALTER COLUMN PesoSET DEFAULT ’0’;

BD2 - SQL

Outline




BD2 - SQL

DDL - Vista¿Qué es una vista?

Una vista es una tabla virtual que se basa en el resultadode una consulta.Sus atributos son atributos de tablas o de otras vistas.Pueden usarse en consultas como si fueran tablas.La vista se supone que está siempre actualizada.

Es responsabilidad del DBMS y no del usuario asegurarsede mantener actualizada la vista.

BD2 - SQL

DDL - VistaOperaciones sobre vistas

CREATE VIEWCrea una vista.

ALTER VIEWModifica una vista.

DROP VIEWElimina la vista.

BD2 - SQL

DDL - VistaEjemplo

Quiero ofrecer una vista sobre la tabla PRODS en la queno aparezca el peso.

CREATE VIEW PRODS2 AS (SELECT #p, NombreFROM PRODS )

. . . devuelve sólo el código y nombre de cada producto.Quiero una vista más amigable que la relaciónTRABAJA_EN.

CREATE VIEW TRABAJA_EN1 AS (SELECT Nombre, Apellido1, NombreProyecto, HorasFROM EMPLEADO, PROYECTO, TRABAJA_ENWHERE DniEmpleado = Dni AND NumProy =

NumProyecto)

BD2 - SQL

DDL - Vista MaterializadaImplementación de vistas

Materializar una vista implica la creación física de unatabla de vista temporal cuando la vista es consultada porprimera vez1y se mantiene en la suposición de que seharán otras consultas en la vista.Para actualizar éstas vistas se aplican estrategias deactualización incremental.

Immediate update. Actualiza la vista tan pronto las tablasbase cambian.Lazy update. Actualiza la vista cuando la consulta de lavista lo requiere.Periodic update. Actualiza la vista periodicamente

1El comportamiento por defecto para la materialización de vistas puedevariar según el DBMS

BD2 - SQL

DDL - Vista Materializada

CREATE MATERIALIZED VIEW nombre AS . . .

Si bien la sintaxis básica se respeta, puede variar la formade especificar opciones según el DBMS.Distintos DBMS implementan y ofrecen distintasestrategias de actualzación automáticas.Generalmente, una actualización (mediante sentenciaUPDATE) de vista es factible cuando sólo unaactualización posible en las relaciones base puede lograrel efecto de actualización deseado en la vista.

BD2 - SQL

Outline




BD2 - SQL

DDL - Tipos de datos

Los tipos de datos básicos disponibles para atributos son:Numérico: INTEGER, SMALLINT2, FLOAT(n), REAL1,DOUBLE PRECISION1, DECIMAL, NUMERIC(p,d).Cadenas de caracteres (character-string): CHAR(n),VARCHAR(n), CLOB.Cadenas de bit (bit-string): BIT(n), BIT VARYING(n), BLOBBoolean, puede tomar valores TRUE, FALSE, NULL(UNKNOW)Fecha y hora: TIME, DATE, TIMESTAMP.

Se pueden crear tipos y dominios: TYPE, DOMAIN.

2la presicón dependende de la maquinaBD2 - SQL

DDL - Tipos de datosUser-defined TYPEs

CREATE TYPE se utiliza para crear tipos definidos por elusuario.

CREATE TYPE TDni AS NUMERIC(7,0)

CREATE TABLEEMPLEADO (Dni TDni,SuperDni TDni,...)

BD2 - SQL

DDL - Tipos de datosDOMAIN

CREATE DOMAIN, incluido en SQL-92, permite creardominios definidos por el usuario.

CREATE DOMAIN Nombre CHAR(20) NOT NULL

TYPEs y DOMAINs son similares. DOMAIN puede tenerespecificadas restricciones.

CREATE DOMAIN color VARCHAR(8) CONSTRAINTcolores_permitidos CHECK (VALUE IN(’rojo’, ’verde’,’azul’));

BD2 - SQL

DDL - Tipos de datosTipos Large-Object

Objetos grandes (fotos, videos, pdf, etc.) se almacenancomo Large OBject :blob binary large object: El objeto es una colección de datos

binarios cuya interpretación la realiza una aplicaciónexterna al sistema de base de datos.

clob character large object: El objeto es una gran colección decaracteres.

Cuando una el resultado de una consulta es un LOB, seretorna un puntero en lugar del objeto en si.

BD2 - SQL

Outline




BD2 - SQL

DDL - RestriccionesPrimary Key, Foreign Key, Check

PRIMARY KEYEspecifica uno o más atributos que constituyen la claveprincipal de una relación.

FOREIGN KEYEspecifica una restricción de integridad referencial.Acciones por violación: rechazo, SET NULL, CASCADE ySET DEFAULT (deben aplicarse con ON DELETE u ONUPDATE).

CONSTRAINTNombrar restricciones.

CHECKRestricciones que se aplican individualmente a cada tupla.

UNIQUEEspecifica calves (secundarias) alternativas.

BD2 - SQL

DDL - Acciones por violación / Valor por defectoEjemplo Elmasri ed7 fig. 6.2

CREATE TABLE EMPLEADO( ... ,Dno INT NOT NULL DEFAULT 1,CONSTRAINT EMPPK PRIMARY KEY (Dni),CONSTRAINT EMPSUPERFK FOREIGN KEY (SuperDni)

REFERENCES EMPLEADO(Dni) ON DELETE SET NULL ONUPDATE CASCADE,

CONSTRAINT EMPDEPTFK FOREIGN KEY(Dno) REFERENCESDEPARTAMENTO(NumeroDpto) ON DELETE SET DEFAULTON UPDATE CASCADE)

La restricción EMPSUPERFK actualiza SuperDni con valorNULL si se borra la túpla de su supervisor.Si en una sentencia INSERT no se indica el atributo Dno,se carga con valor 1.

BD2 - SQL

DDL - Acciones por violación / Valor por defectoEjemplo Elmasri ed7 fig. 6.2

CREATE TABLE LOCALIZACIONES_DPTO( ... ,PRIMARY KEY (NumeroDpto, UbicacionDpto),FOREIGN KEY (NumeroDpto) REFERENCES

DEPARTAMENTO(NumeroDpto) ON DELETE CASCADE ONUPDATE CASCADE)

Se borrará la túpla si en la tabla DEPARTAMENTO seborra la túpla del departamento NumeroDpto.Si en una túpla de la tabla DEPARTAMENTO se actualizael valor de NumeroDpto, también se actualizará en todatúpla de LOCALIZACIONES_DPTO que haga referencia adicha túpla (mismo valor de NumeroDpto).

BD2 - SQL

Outline




BD2 - SQL

DMLData Manipulation Language

Permite crear, modificar, eliminar y recuperar datos:INSERT

Agrega tuplas a una tabla.UPDATE

Cambia tuplas de una tabla.DELETE

Borra tuplas de una tabla.SELECT

Recupera datos.

BD2 - SQL

DML - INSERTEjemplo

Insertar una nueva venta del producto 530 a precio 15000,para el fabricante 25.

INSERT INTO VENTASVALUES (25, 530, 15000);

¿Por qué funciona?Otra forma es especificar explicitamente los nombres delos atributos que corresponda con cada valor de lasentencia.

INSERT INTO VENTAS(#p, #f, Precio)VALUES (530, 25, 15000);

Atributos no especificados tomaran su valor por defecto(DEFAULT) o NULL.

BD2 - SQL

DML - INSERTEjemplo

Tabla que contiene nombre, apellido y salario de losempleados que trabajan en el departamento nro 5.

CREATE DEP5_EMPS (Nombre char(50),Apellido char(50) ,Salario float);

¿Cómo podría realizar la carga inicial de esta tabla?

INSERT INTO DEP5_EMPS( Nombre, Apellido, Salario )SELECT Nombre, Apellido1, SueldoFROM EMPLEADOWHERE Dno=5;

Asumo mismo tipo/dominio de atributos.

BD2 - SQL

DML - DELETEBorrar tuplas

La sentencia DELETE remueve tuplas de una relación.Puede incluir la cláusula WHERE que se utiliza paraseleccionar las tuplas a borrar.Sólo permite borrar tuplas de una relacón a la vez.Dependiendo de la cláusula WHERE, puede borrarse cero,una o más túplas con una misma sentencia DELETE.La ausencia de la cláusula WHERE indica que todas lastuplas de la relación serán borradas.

BD2 - SQL

DML - DELETEEjemplo

Borrar los empleados con Dni 321 (Dni es clave, entoncesse borrará una tupla o ninguna).

DELETE FROM EMPLEADO WHERE Dni=321

Borrar los empleados con apellido Ochoa.

DELETE FROM EMPLEADOWHERE Apellido1=’Ochoa’ OR Apellido2=’Ochoa’

Borrar los empleados del departamento 5.

DELETE FROM EMPLEADO WHERE Dno=5

Borrar todos los empleados (ausencia del WHERE).

DELETE FROM EMPLEADO

BD2 - SQL

DML - UPDATEActualizar tuplas

La sentencia UPDATE se utiliza para modificar valores deatributos de una o más tuplas.Puede incluir la cláusula WHERE para seleccionar lastuplas a actualizar.Sólo permite actualizar tuplas de una relacón pero. . .

Si se actualiza el valor de una clave primaria podríapropagarse a los valores foreign keys de tuplas en otrasrelaciones (ver previo ejemplo con ON UPDATECASCADE).

Dependiendo de la cláusula WHERE, puede actualizarseel/los atributo/os de cero, una o más túplas con una mismasentencia UPDATE.La ausencia de la cláusula WHERE indica que todas lastuplas de la relación serán actualizadas.

BD2 - SQL

DML - UPDATE, DELETEEjemplo

Incrementar el precio del producto número 530 en un10 %.

UPDATE VENTASSET Precio = Precio ∗ 1.1WHERE #p = 530;

Incrementar el precio de todo producto en un 2 %.

UPDATE VENTASSET Precio = Precio ∗ 1.02;

Borrar toda la información de ventas con precio mayor a$50000.

DELETE FROM VENTASWHERE Precio = 50000;

BD2 - SQL

DML - Recuperación de datos

Sentencia SQL que nos permite recuperar información dela base de datos.

SELECT <lista de atributos>FROM <lista de tablas>WHERE <condicion>GROUP BY <expresion>HAVING <expresion>ORDER BY <expresion>;

donde:<lista de atributos> lista de los atributos cuyos valores seránrecuperados por la consulta.<lista de tablas> es una lista de las relaciones necesariaspara procesar la consulta.<condicion> es una expresión condicional (booleana) queidentifica las tuplas que la consulta recuperará (es unperdicado).<expresion> más adelante veremos la expresión de cadacláusula.

BD2 - SQL

DML - Recuperación de datosSELECT

La cláusula SELECT lista los atributos que queremos queaparezcan en el resultado de la consulta.

¿Encuentra una equivalencia en Álgebra Relacional de lacláusula SELECT?Corresponde a la operación de proyección del álgebrarelacional.

Ejemplo:SELECT Nombre, Apellido1, Apellido2 FROM EMPLEADO

Nota: nombres SQL son case insensitive, ej.:Apellido1 ≡ APELLIDO1 ≡ apellido1.

BD2 - SQL

DML - Recuperación de datosFiltrado de repetidos

SQL permíte que una tabla (relación) tenga dos o mástuplas idénticas en todos sus valores de atributo.Por tanto, en general, una tabla SQL no es un conjunto detuplas. Es un multiconjunto (o bolsa).Algunas relaciones SQL están restringidas a ser conjuntosporque se ha declarado una restricción de clave o porquese ha utilizado la keyword DISTINCT en la cláusulaSELECT.

BD2 - SQL

DML - Filtrado de repetidosRazones para no eliminar automáticamente tuplas repetidas

La eliminación de duplicados es una operación muycostosa.El usuario puede querer ver las tuplas duplicadas en elresultado de una consulta.Al aplicar una función de agregación a las tuplas, en lamayoría de los casos no queremos eliminar los duplicados(veremos más adelante).

BD2 - SQL

DML - Filtrado de repetidosEjemplo

Dar los #p de los productos vendidos, filtrando losrepetidos.

SELECT DISTINCT #pFROM VENTAS

Dar los precios a los que se vende cada producto.

SELECT DISTINCT #p, PrecioFROM VENTAS

La cláusula DISTINCT filtra tuplas repetidas.

BD2 - SQL

DML - SELECT* y expresiones aritméticas

Un asterisco en SELECT denota “todos los atributos” delesquema resultado.

SELECT ∗ FROM VENTAS;

. . . es equivalente a:

SELECT #f, #p, Precio FROM VENTAS;

La cláusula también puede contener expresionesaritméticas que involucren los operadores +, -, * y /, yoperen sobre constantes o atributos de túplas.La siguiente consulta retorna el nombre del Empleado y eltotal de salarios que percibe en el año.

SELECT Nombre, Salario ∗ 12 FROM EMPLEADO;

BD2 - SQL

DML - WHERE

La cláusula where especifica condiciones que el resultadodebe cumplir.

Corresponde al predicado de la operación de selección delálgebra relacional.

Las comparaciones sobre el resultado pueden sercombinadas utilizando conectores lógicos: AND, OR yNOT.Las comparaciones también pueden aplicarse sobreresultados de expresiones aritméticas.

BD2 - SQL

DML - WHEREOperadores de comparación

Operadores de comparación aplicables en las condiciones dela cláusula WHERE:

=, >, >=, <, <=, <>BETWEEN m AND nEntre m y n (inclusive)IN(lista)Se encuentra en la listaLIKESe ajusta a un patrón.IS [NOT] NULLEs/No es valor nulo (veremos ejemplos más adelante)

BD2 - SQL

DML - Recuperación de datosEjemplo

Dar el precio al que vende el producto 7 el fabricante 2.

SELECT PrecioFROM VENTASWHERE #f = 2 and #p = 7;

Dar el número de los fabricantes que vendieron elproducto 4 a menos de $100.

SELECT #fFROM VENTASWHERE Precio < 100 and #p = 4;

¿Qué expresión utilizaría en álgebra relacional?

BD2 - SQL

DML - Recuperación de datosFROM

La cláusula FROM lista las relaciones involucradas en laconsulta.

Corresponde con la operación producto cartesiano delálgebra relacional.

Ejemplo: Obtener el producto cartesiano de las ventas, losfabricantes y los productos.

SELECT ∗FROM VENTAS, PRODS

genera todo posible par venta-producto, con todos losatributos de ambas relaciones.

BD2 - SQL

DML - Tablas como conjuntosUNION, EXCEPT, INTERSECT

SQL incorpora las operaciones del álgebra relacional.Únion de conjuntos: UNIONDiferencia de conjuntos: EXCEPT (MINUS)Intersección de conjuntos: INTERSECT

Las relaciones resultantes de estas operaciones sonconjuntos de tuplas.Precondición: se aplican a las relaciones compatibles conla unión.

Mismos atributosy aparecen en el mismo orden en ambas relaciones.

BD2 - SQL

DML - UNIONEjemplos

Dar los #f que son de Montevideo o que venden algúnproducto a precio mayor que $500.

SELECT #f FROM FABSWHERE departamento=’Montevideo’UNIONSELECT #f FROM VENTASWHERE Precio > 500;

La UNION elimina tuplas repetidas.

BD2 - SQL

DML - Diferencia (EXCEPT, MINUS)Ejemplos

Dar los #f que no venden ningún producto.

SELECT #f FROM FABSEXCEPTSELECT #f FROM VENTAS;

Hay otras alternativas.

SELECT #f FROM FABSWHERE #f NOT IN ( SELECT #f FROM VENTAS );

BD2 - SQL

DML - Tablas como multiconjuntoUNION ALL, EXCEPT ALL, INTERSECT ALL

SQL también dispone de las operaciones multiconjuntocorrespondientes, que van seguidas por la palabra claveALL

UNION ALL, EXCEPT ALL, INTERSECT ALL.

Sus resultados son multiconjuntos. Es decir, losduplicados no se eliminan.

BD2 - SQL

DML - UNION ALL, EXCEPT ALL, INTERSECT ALLEjemplos

R

Aa1a2a2a3

S

Aa1a2a4a5

R UNION ALL S

Aa1a1a2a2a2a3a4a5

R EXCEPT ALL S

Aa2a3

R INTERSECT ALL S

Aa1a2

BD2 - SQL

DML - Orden de tuplasORDER BY

Dar los #p de los productos fabricados, pero ordenados enforma ascendente.

SELECT #pFROM VENTASORDER BY #p ASC;

La cláusula ORDER BY permite indicar el campo por elcual se ordena el resultado.Se indica el orden ascendente o descendente (ASC oDESC)

BD2 - SQL

DML - Resolver ambiguedades de nombreRenombre de atributos, Alias, Referencia explícita

Dar los nombres de fabricantes y los nombres de losproductos que venden.

SELECT Nombre, NombreFROM VENTAS, FABS, PRODSWHERE VENTAS.#f=FABS.#f AND VENTAS.#p=PRODS.#p

Una forma de resolver la ambiguedad es indicar la tablaexplícitamente.

SELECT FABS.Nombre, PRODS.NombreFROM VENTAS, FABS, PRODSWHERE VENTAS.#f=FABS.#f AND

VENTAS.#p=PRODS.#p

BD2 - SQL

DML - Alias y Renombre de atributosEjemplo

También se puede asignar un alias a las tablas.En este ejemplo se asignan diferentes alias a la “misma”tabla.

SELECT E.Apellido1, S.Apellido1FROM EMPLEADO AS E, EMPLEADO AS SWHERE E.SuperDni=S.Dni;

La sentencias AS permite renombrar los atributos.

SELECT E.Ap1, S.Apellido1FROM EMPLEADO AS E(Nombre, Ap1, Ap2, ..., SsDni,

Dno), EMPLEADO AS SWHERE E.SsDni=S.Dni;

BD2 - SQL

Outline




BD2 - SQL

DML - INNER JOINTablas concatenadas y concatenación interna

El concepto de tabla concatenada (o relaciónconcatenada) se incorporó a SQL para poder especificaruna tabla como resultado de una operación deconcatenación en la cláusula from de una consulta.Esta estructura es más fácil que mezclar todas lascondiciones de selección y concatenación en la cláusulaWHERE.El tipo predeterminado de concatenación en una tablaconcatenada es una concatenación interna

La tupla se incluye en el resultado si en la otra relaciónexiste una tupla coincidente.

BD2 - SQL

DML - INNER JOINEjemplo - Imponiendo la igualdad

Dar los nombres de los fabricantes y los productos quevenden.

SELECT Nombre, #pFROM VENTAS, FABSWHERE VENTAS.#f = FABS.#f;

Dar los nombres de fabricantes y los productos quevenden, tales que el precio es $100.

SELECT Nombre, #pFROM VENTAS, FABSWHERE VENTAS.#f = FABS.#f AND Precio = 100;

BD2 - SQL

DML - INNER JOINEjemplo - Utilizando operador JOIN

SELECT Nombre, #pFROM (VENTAS JOIN FABS ON VENTAS.#f=FABS.#f)

SELECT Nombre, #pFROM (VENTAS JOIN FABS ON VENTAS.#f=FABS.#f)WHERE Precio = 100;

En lugar de imponer la igualdad utilizo el operador JOINpara resolver las consultas previas.

BD2 - SQL

DML - NATURAL JOINEjemplo

SELECT Nombre, #pFROM (VENTAS NATURAL JOIN FABS)

SELECT Nombre, #pFROM (VENTAS NATURAL JOIN FABS)WHERE Precio = 100;

No se especifica condición de concatenación alguna.Se crea una condición EQUIJOIN implícita para cada parde atributos con el mismo nombre (ejemplo el atributo #f).

BD2 - SQL

DML - NATURAL JOIN

Apareceran en el resultad las túplas con el mismo valorpara todos los atributos comunes, y se conservará solouna copia para cada columna en común.Hay que tener cuidado: atributos con el mismo nombre yque no tienen relación pueden ser comparadosincorrectamente.Ejemplo: ¿qué recupera esta consulta?

SELECT ∗FROM FABS NATURAL JOIN PRODS

BD2 - SQL

DML - OUTER JOINConcatenación externa, LEFT, RIGHT y FULL

Es una extensión de la operación join que evita perdida deinformación.Agrega al resultado del join las túplas de una relación queno coincide con la otra ralación.Usa valores NULL para rellenar.

BD2 - SQL


Obtener para cada empleado su apellido y el de susupervisor.

SELECT E.Apellido1, S.Apellido1FROM (EMPLEADO AS E JOIN EMPLEADO AS S ON

E.SuperDni=S.Dni);

Ahora quiero que se incluyan todos los empleados. Estoes, tengan o no supervisor.

SELECT E.Apellido1, S.Apellido1FROM (EMPLEADO AS E LEFT OUTER JOIN

EMPLEADO AS S ON E.SuperDni=S.Dni);

BD2 - SQL


Sea la concatenación entre las tablas T1 y T2,“...T1 <operador_concatenacion> T2...”

Si <operador_concatenacion>:LEFT JOIN se agrega, para cada tupla de T1 que nosatisface la condición de JOIN con NINGUNA de T2, unafila con NULOS (NULLs) en las columnas de T2.RIGHT JOIN análogo al LEFT pero se incluyen todos losde T2FULL JOIN equivale a la unión del LEFT y RIGHT.

BD2 - SQL

DML - CROSS JOINProducto cartesiano

La keyword CROSS JOIN es utilizada para especificar laoperación Producto Cartesiano.Como vimos en álgebra relacional, este genera todas lasposibles combinaciones de tuplas.Se debe utilizar con cuidado.

BD2 - SQL

Outline




BD2 - SQL

DML - Valores NULLComparaciones con valores NULL y Lógica de tres valores

El valor NULL tiene una de tres interpretaciones:Valor desconocido (Unknown). Existe, pero no se conoce.Valor no disponible. Existe, pero no se especifíca apropósito.Atributo no aplicable. No definido para esta tupla.

El resultado de cualquier operación aritmética queinvolucre un valor NULL es NULL.Cuando se compara un atributo con valor NULL, elresultado se considera desconocido (podría ser TRUE opodría ser FALSE).SQL usa una “Lógica de tres valores” cuyos valores sonTRUE, FALSE y UNKNOWN.

BD2 - SQL

DML - Valores NULLLógica de tres valores

Figura : Logica de tres valores

BD2 - SQL

DML - Valores NULLEs o no es

SQL permite chequear en una consulta si un valor es nulo.Predicados: IS NULL, IS NOT NULLEjemplo:

Devolver los nombres de los fabricantes de los que no seconoce el departamento donde trabajan.

SELECT NombreFROM FABSWHERE departamento IS NULL;

Nombre y apellidos de los empleados que no tienen unsupervisor.

SELECT Nombre, Apellido1, Apellido2FROM EMPLEADOWHERE SuperDni IS NULL;

BD2 - SQL

Outline




BD2 - SQL

DML - Consultas anidadasConsulta anidada y consulta externa

Permiten obtener valores existentes en la base de datospara usarlos después en una comparación.Son bloques competos select-from-where que puedenaparecer dentro de la cláusula WHERE o SELECT u otras.Se le llama consulta externa (outer query) a la consultadonde se anidan éstas consultas.

BD2 - SQL

DML - Consultas anidadasEjemplos

Dar los nombres de los fabricantes que venden productosque también son vendidos por el fabricante número 1.

SELECT f.NombreFROM FABS f, VENTAS vWHERE f.#f = v.#f AND f.#f <> 1 AND v.#p IN ( SELECT

#p FROM VENTAS WHERE VENTAS.#f=1 )

BD2 - SQL

DML - Comparación de conjuntos/multiconjuntosIN, ANY, ALL

Los operadores/keywords IN, ANY, ALL permiten comprarcon conjuntos o multiconjuntos.En el ejemplo anterior se introduce el operador decomparación IN que compara un valor con un conjunto (omulticonjunto) de valores V y se evalúa TRUE si v es unode los elementos de V.El keyword ANY (o SOME) se puede combinar conoperadores: =, >, >=, <, <= y <>.

Ej. = ANY se comporta igual que el operador IN.El keyword ALL también puede combinarse con dichosoperadores.

Ej. > ALL retornará TRUE si el valor v es mayor que todoslos valores del conjunto o multiconjunto.

BD2 - SQL

DML - EXISTS y UNIQUE

EXISTS y UNIQUE son funciones booleanas que retornanTRUE o FALSE.EXISTS

Se utiliza para chequear si el resultado de una consultaanidada es vacio o no.Retorna TRUE si dicho resultado contiene al menos unatupla.

UNIQUE( Q )Retorna TRUE si no hay tuplas duplicadas en el resultadode la consulta Q.Puede ser utilizada para probar si el resultado de unaconsulta anidada es un conjunto (no contiene duplicados) oes un multiconjunto (existen duplicados).

BD2 - SQL

Outline




BD2 - SQL

DML - Funciones de agregaciónCOUNT, SUM, MIN, MAX, AVG, RANK

Nos permiten resumir información de varias tuplas en unsimple tupla.Por tanto, aplican sobre conjutos de tuplas, no sobretuplas individuales.Ej.: Obtener la suma de sueldos de todos los empleados,el sueldo más bajo, el más alto y calcular el sueldopromedio.

SELECT SUM (Sueldo), MAX (Sueldo), MIN (Sueldo),AVG (Sueldo)

FROM EMPLEADO;

Ej.: Total de fabricantes.

SELECT COUNT(∗) FROM FABS;

BD2 - SQL

DML - Funciones de agregaciónCOUNT

Número de empleados (cuenta el número de túplas).

SELECT COUNT(∗) FROM EMPLEADO;

Número de empleados que tienen un supervisor (ignoraNULLs).

SELECT COUNT(SuperDni) FROM EMPLEADO;

Número de empleados que son supervisores.

SELECT COUNT( DISTINCT SuperDni ) FROMEMPLEADO;

BD2 - SQL

DML - Funciones de agregaciónRANK

Ranking se realiza en combinación con un especificaciónde orden.

SELECT Dni, RANK() OVER (ORDER BY horas desc)AS dedicacion

FROM TRABAJA_EN;

Una cláusula ORDER BY extra se necesita para ordenar elresultado en función del ranking.

SELECT Dni, NumProy, RANK() OVER (ORDER BYhoras desc) AS Dedicacion

FROM TRABAJA_ENORDER BY Dedicacion;

¿De qué otra forma se puede implementar?

BD2 - SQL

DML - Funciones de agregaciónRANK

Se pueden especificar varias cláusulas rank en una mismacláusula SELECT.El ranking se realiza lueg de aplicar la cláusula GROUPBY.SQL:1999 permite que el usuario especifique si rankearlos NULLs al final (last) o primeros (first).

SELECT Dni, RANK() OVER (ORDER BY horas descNULLS LAST) AS dedicacion

FROM TRABAJA_EN;

Puede utilizarse para encontrar los top-n.Es más general que la cláusula LIMIT que soporta muchasbases de datos.

BD2 - SQL

DML - Agrupamiento de tuplasGROUP BY

En ciertas situaciones se quiere aplicar funciones deagregación a subgrupos de tuplas en una relación, dondelos subgrupos se confeccionan en base a algunos valoresde atributos.Ejemplo:

Dar, para cada fabricante, la cantidad de productos quevende.

Esto implica:Tomar cada grupo de tuplas en VENTAS correspondiente aun fabricante.Contar la cantidad de tuplas en el grupo.

Para realizar esta operación es necesaria la cláusulaGROUP BY.

BD2 - SQL


Es una cláusula más que se agrega al bloqueselect-from-where.

SELECT #f, COUNT(∗)FROM VENTASGROUP BY #f;

¿Cómo funciona?Particiona las ventas en subconjuntos o grupos de tuplasdonde cada grupo corresponde a un fabricante distinto.De cada grupo devuelve el #f del fabricante y la cantidadde tuplas de dicho grupo.

BD2 - SQL


En una sentencia SQL que tiene cláusula GROUP BY, lasexpresiones en el SELECT pueden ser sólo:

Atributos presentes en la cláusula GROUP BY.Funciones de agregación sobre atributos.Expresiones aritméticas que utilicen los anteriores.

El agrupamiento se realiza después de aplicar el WHERE.O sea, sobre las tuplas que cumplen la condición.Si existen NULL en el atributo de agrupamiento, se crea ungrupo separado para todas las tuplas con valor NULL endicho atributo.Otro ejemplo:

Empleados agrupados por su fecha de nacimiento.

SELECT FechaNac, COUNT(∗)FROM EMPLEADOGROUP BY FechaNac;

BD2 - SQL

DML - Agrupamiento de tuplasHAVING

A veces interesa recuperar información de grupos quecumplen cierta condición.Con la cláusula HAVING se pueden especificarcondiciones sobre los grupos.La condición trabaja sobre información resumida delgrupo.Ejemplo:

Dar los nombres de fabricantes que hicieron más de 5ventas sobre productos con #p mayor que 2, junto con eltotal de precio vendido.

SELECT #f, Nombre, SUM(Precio)FROM FABS, VENTASWHERE FABS.#f = VENTAS.#f AND #p > 2GROUP BY #f, NombreHAVING count(∗) > 5;

BD2 - SQL

Outline




BD2 - SQL

DML - Constructor CASE

El constructor CASE suele utilizarse cuando un valorpuede ser diferente según ciertas condiciones.Puede ser utilizado ya sea al recuperar, insertar oactualizar tuplas.Ejemplo:

Aumentamos el sueldo de los empleados según eldepartamento donde trabajan.

UPDATE EMPLEADOSET Sueldo =CASEWHEN Dno=5 THEN Sueldo+2000WHEN Dno=4 THEN Sueldo+1500WHEN Dno=1 THEN Sueldo+3000ELSE Sueldo+500;

BD2 - SQL

DML - Consultas recursivasWITH [RECURSIVE]

Las consultas WITH, también llamadas Common TableExpressions (CTEs), a grandes rasgos permiten dividirconsultas complicadas en consultas más simples y fácilesde interpretar.Estructura:

WITH RECURSIVE <cte_nombre> (columna, ...) AS (<termino_no_recursivo>UNION ALL<termino_recursivo>)SELECT ... FROM <cte_nombre>;

BD2 - SQL

DML - Consultas recursivasEjemplo

Un ejemplo de relación recursiva entre tuplas del mismotipo es la relación entre un empleado y un supervisor.En álgebra relacional vimos que no podiamos recuperartodos los supervisados directa e indirectamente porOchoa.

BD2 - SQL

DML - Consultas recursivasEjemplo - cláusula WITH

WITH RECURSIVE EMP_SUP( EmpDni, SupDni ) AS (SELECT Dni, SuperDniFROM EMPLEADOWHERE SuperDni IN ( SELECT Dni FROM EMPLEADO

WHERE Nombre=’Eduardo’ AND Apellido1=’Ochoa’ )UNIONSELECT E.Dni, E.SuperDniFROM EMPLEADO AS E, EMP_SUP AS SWHERE S.EmpDni = E.SuperDni)SELECT ∗ FROM EMP_SUP;

BD2 - SQL

DML - Consultas recursivasWITH [RECURSIVE]

Sin recursión, un programa no-recursivo y no-iterativo solopuede realizar un número fijo de JOINs de la relaciónEMPLEADO consigo misma.Esto solo nos podría dar un número fijo de niveles desupervisores.Dada una consulta no-recursiva, podemos construir unabase de datos con más niveles de supervisión en loscuales la consulta no va a funcionar.Alternativamente: tendríamos que escribir unprocedimiento para iterar tantas veces como seanecesario.

BD2 - SQL

DML - Consultas recursivasEjemplo - cláusula CONNECT BY de Oracle

SELECT Dni, SuperDniFROM EMPLEADOCONNECT BY PRIOR Dni = SuperDniSTART WITH SuperDni IN ( SELECT Dni FROM

EMPLEADO WHERE Nombre=’Eduardo’ ANDApellido1=’Ochoa’ )

BD2 - SQL

sql - bases de datos 2 - tecnologo de informatica · 2016. 4. 20. · structured query language...

Documents