guia de sql

Introducción al Desarrollo de Aplicaciones ORACLE

__________________________________________________________________________________________

Conceptos Básicos de SQL/PLUS /1

II.- INTRODUCCION AL SQL*PLUS Pag. 1.- Comandos de SQL 5

2.- Funciones y Tipos de Datos 21

3.- Reportes en SQL*PLUS 28

4.- Manipulación de Datos 35

5.- Tablas y Vistas 39

6.- Indices y Generadores de Secuencias 47

7.- Consultas Avanzadas de ORACLE 50

8.- Optimización de Instrucciones de Manipulación

de Datos en SQL 53


__________________________________________________________________________________________


INTRODUCCIÓN AL SQL*PLUS

Para tener acceso a cualquier manejador de base de datos relacional, es necesario el uso de

SQL (Structured Query Language - Lenguaje de consultas estructuradas) como intermediario.

SQL es un lenguaje no procedimental, el cual se caracteriza, por no requerir (de parte del

desarrollador), información sobre la ubicación física de los archivos, es decir, indicarle la ruta de

acceso o PATH, ya que de esto se encargará el manejador de la base de datos, al momento de la

creación de las tablas. Otra de las características de un lenguaje no procedimental, como lo es

SQL, viene dada en poder accesar a múltiples registros a la vez, con el uso de una sola instrucción.

Por lo antes expuesto se puede inferir que al usuario de la base de datos, le es indiferente el

almacenamiento físico de los objetos con los que va a trabajar.

Para que los usuarios y programadores puedan tener acceso a SQL, ORACLE ofrece un

conjunto de herramientas, entre las cuales está SQL*Plus, el cual permite usar interactivamente los

comandos de SQL o los bloques de PL/SQL, con el objeto de escribir comandos en linea, o

producir reportes, que nos permitirán consultar la información almacenada en la base de datos, sin

tener la necesidad de construir sofisticados programas.


__________________________________________________________________________________________


CONCEPTOS BÁSICOS Tabla: Es la estructura básica de almacenamiento de datos, en un sistema manejador de base de datos relacional. Una tabla consiste de una o más unidades de información (filas), donde cada una de ellas, contiene un mismo tipo de valores (columnas). Columna: Es un campo que representa un tipo o atributo de datos en una tabla. Fila: Son las ocurrencias o registros de las tablas. Valor: Es el dato existente, entre el cruce de una columna y una fila Vista: Es un objeto de la base de datos que muestra una representación lógica de una o varias tablas. Esta se deriva de una tabla pero no tiene su propio almacenamiento. Su manejo es similar a una tabla. Sistema de manejo de base de datos relacional: Es un programa de computadora de propósito general, para el almacenamiento y recuperación de datos, donde los datos están organizados en tablas y pueden ser relacionados unos con otros. Administrador de base de datos: Es un usuario autorizado para otorgar y revocar el acceso de otros usuarios al sistema y cumple funciones de control y organización de las estructuras de los datos almacenados en la base de datos. CHAR: Son caracteres basados en letras mayúsculas y minúsculas, números, y signos especiales tales como "@", "#", "%", etc. El ancho máximo de este tipo, en caracteres, no debe ser mayor de 255. Ejemplo: CHAR(10). DATE: Puede almacenar valores de tipo hora y de tipo fecha; éstas últimas van, desde el 1ero de enero de 4.712 antes de Cristo, al 31 de diciembre de 4.712 después de Cristo. LONG: Similar a CHAR, pero puede almacenar 65.535 bytes de longitud. Restricciones: - No se puede usar más de una columna tipo long por tabla. - No puede usarse para hacer un JOIN de tablas con una columna de tipo LONG. - No se puede emplear una columna de tipo LONG, para realizar búsquedas. NUMBER: Es un valor numérico, el cual consiste de dígitos que van del cero (0) al nueve (9), con los signos opcionales positivo (+), negativo (-) y punto decimal (.). El máximo valor de NUMBER puede tener 38 dígitos.


__________________________________________________________________________________________


BUFFER SQL: Es el área donde SQL*PLUS almacena sus más recientes comandos de entrada.


__________________________________________________________________________________________


II.1.- COMANDOS DE SQL*PLUS ¿Como entrar en SQL*Plus? Para tener acceso a la base de datos, usando el lenguaje SQL, lo primero que debemos hacer, es activar a SQL*Plus. La forma de activarlo es la siguiente: - Escribir <sqlplus> y aceptar la ejecución pulsando la tecla <Enter> - Después, SQL*Plus le pedirá su nombre de usuario: username: NOMBRE DE USUARIO <Enter> - Inmediatamente después, SQL*Plus le pedirá su clave de acceso. Password: CLAVE DE ACCESO <Enter> Nota: SQL*Plus no dejará que su clave de acceso sea visualizada. Una vez conectado a SQL*Plus, aparecerá el prompt el cual indicará que SQL*Plus está activo: SQL > ¿Como salir de SQL*Plus? Para poder salir de SQL*Plus (en forma permanente), deberá escribir el comando EXIT o QUIT y aceptar la ejecución. ( Terminará cualquier transacción pendiente ) Ayuda en línea SQL*Plus ofrece la ayuda en linea, la cual podrá ser activada mediante el comando HELP. Nota: no siempre éste comando se encuentra disponible, ya que esto dependerá del criterio del Administrador de la base de datos. Ejemplos de HELP: Lista todos los comandos de SQL y SQL*Plus. SQL > HELP Visualiza la información sobre un comando en particular. SQL > HELP CREATE


__________________________________________________________________________________________


¿Como enviar un reporte a la impresora o almacenar su contenido a un archivo.? Esto se logra con el comando SPOOL. Sintaxis: SPOOL [nombre de archivo | OFF | OUT ] Cuando utilizamos un nombre de archivo, estamos dirigiendo el reporte hacia un archivo. Al utilizar OFF, desactivamos el comando. Al utilizar OUT, estamos dirigiendo el reporte hacia la impresora. Comandos de SQL*Plus que pueden editar el contenido de buffer de SQL. APPEND Uso: Adiciona texto en el buffer al final de la linea en curso. Forma abreviada: A Sintaxis: APPEND Texto CHANGE Uso: Realiza cambios en el texto de la linea en curso. Forma abreviada: C. Sintaxis: CHANGE separador TEXTO VIEJO [separador [TEXTO NUEVO [separador]]] Nota: como separador puede ser usado "/" o "!". CLEAR Uso: Limpia o borra el valor o selección en curso para una opción específica. Forma abreviada: CL. Sintaxis: CLEAR Opción Opción: BREAKS: Remueve las especificaciones realizadas con el comando BREAK. BUFFER: Remueve el texto del buffer. Tiene el mismo efecto que CLEAR SQL, a menos que se utilicen multiples buffers. COLUMNS: Remueve los valores impuestos por el comando COLUMN. COMPUTES: Remueve las especificaciones realizadas con el comando COMPUTE SCREEN: Borra la pantalla. SQL: Remueve el texto del buffer de SQL.


__________________________________________________________________________________________


DEL Uso: borra la línea en curso del buffer. Sintaxis: DEL INPUT Uso: adiciona en el buffer una o más lineas de texto inmediatamente después de la linea en curso. Forma abreviada: I. Sintaxis para adicionar una linea en el buffer: INPUT Texto (acceptar comando) Sintaxis para adicionar una o más lineas en el buffer: INPUT (acceptar comando) Texto_1 (acceptar comando) Texto_2 (acceptar comando) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Texto_n (acceptar comando) (acceptar nuevamente para concluir la adición de lineas al buffer) LIST Uso: muestra una o más lineas almacenadas en el buffer de SQL. Forma abreviada: L. Sintaxis: LIST [ n | n m | n * | n LAS | * | * n | * LAST | LAST ] Significado de los términos usados: n Lista la linea "n". n m Lista desde la linea "n" hasta la "m". n * Lista desde la linea "n" hasta la linea en curso. n LAST Lista desde la linea "n" hasta la ultima linea. * Lista la linea en curso. * n Lista desde la linea en curso hasta la linea "n". * LAST Lista desde la linea en curso hasta la ultima linea. LAST Lista la ultima linea.


__________________________________________________________________________________________


Diccionario de Datos (DD) El diccionario de datos es un conjunto de tablas y vistas que contienen información descriptiva, sobre tablas, privilegios de acceso a usuarios y estructuras de la base de datos. A continuación se muestra una lista de tablas frecuentemente usadas. ---------------------------------------------------------------------------------------- Nombre de la tabla Descripción ---------------------------------------------------------------------------------------- user_catalog Posee los nombres de las tablas, vistas, sinónimos, secuencias, de la cual es dueño un usuario. user_indexes Descripción de los índices pertenecientes a un usuario. user_sequences Descripción de las secuencias pertenecientes a un usuario. user_synonyms Descripción de los sinónimos pertenecientes a un usuario user_ts_quotas Muestra el tamaño máximo que pueden tener las tablas. user_users Muestra la información acerca del usuario en curso. user_views Lista las vistas pertenecientes a un usuario. obj Muestra todos los objetos del usuario activo. ---------------------------------------------------------------------------------------- Nota: Para ver otras tablas o vistas del diccionario de datos accesar las vistas: DICT, DICTIONARY, CAT , CATALOG Ejemplo de utilización: SQL > SELECT * 2 FROM user_catalog; Nota: Los detalles del comando SELECT se discutirán detalladamente en las próximas páginas.


__________________________________________________________________________________________


COMANDOS DE SQL Los comandos de SQL permiten crear, almacenar, cambiar, recuperar, y mantener la información almacenada en la base de datos de ORACLE. Podemos clasificar los comandos de SQL en tres grupos: * Comandos de manipulación de datos. * Comandos de definición de datos. * Comandos varios. Entre los comandos de manipulación de datos, se tiene: * Commit * Delete * Insert * Lock Table * Rollback * Savepoint * Select * Update Entre los comandos de definición de datos, se tiene: * Alter * Audit * Comment * Create * Drop * Grant * Noaudit * Rename * Revoke * Set Transaction * Validate Index Comandos varios: * Describe. * Host. * Connect


__________________________________________________________________________________________


COMANDO SELECT, PASO A PASO: SELECT: Esta sentencia muestra una o más filas, con sus respectivas columnas, las cuales pueden ser extraídas de una o más tablas, vistas o sinónimos. El número de columnas a visualizar puede ser parcial o total. Para poder utilizar esta sentencia, es necesario: - Existencia de la tabla, vista o sinónimo a visualizar. - Poseer el privilegio de acceso a la tabla, vista o sinónimo o los privilegios del administrador de la base de datos (DBA). Los parámetros mínimos requeridos para el uso de esta sentencia son: SELECT Columnas FROM Origen_datos Columnas: son los nombres de todas aquellas columnas que se desea visualizar, los cuales deberán de estar separados por una coma (,). Si se desean visualizar todas las columnas de la tabla, se podrá abreviar la operación, colocando en lugar de los nombre de las columnas, un asterisco (*); adicionalmente puede usarse como columnas de visualización las constantes, operaciones aritméticas, funciones, nidos de funciones y funciones de grupo. Origen_datos: son los nombres de todas aquellas tablas, vistas o sinónimos de los cuales se desea extraer la información, los cuales deberán estar separados por una coma (,). Selección de columnas Como se dijo anteriormente se pueden visualizar de forma total o parcial las columnas de una tabla. A continuación se muestra un ejemplo de cada caso: Caso 1: Visualización de todas las columnas de la tabla empleado, usando para ello, los nombres de cada una de las columnas. Nota: lo indicado en cursiva pertenece al prompt de SQL, por lo tanto no deberá transcribirse. SQL> SELECT NOMBRE, CARGO, SALARIO, NUMDEPT 2 FROM EMPLEADO;


__________________________________________________________________________________________


Visualización de la ejecución: NOMBRE CARGO SALARIO NUMDEPT ------------------------------ -------------------- ----------- ------------------ MARÍA PÉREZ SECRETARIA 20.000 02 LUIS CARRILLO CHOFER 10.000 01 FRANCISCO MONTERO GERENTE 35.000 01 LAURA MONTILLA GERENTE 38.000 01 CARLOS FERNANDEZ CHOFER 11.000 02 Caso 2: Visualización de todas las columnas de la tabla empleados, usando para ello, el asterisco (*). Nota: Suponer para este ejemplo que la tabla empleado, tiene las siguientes columnas: - NOMBRE - CARGO - SALARIO - NUMDEPT SQL> SELECT * 2 FROM EMPLEADO; Visualización de la ejecución: NOMBRE CARGO SALARIO NUMDEPT ----------------------------- -------------------- ----------- ------------------ MARÍA PÉREZ SECRETARIA 20.000 02 LUIS CARRILLO CHOFER 10.000 01 FRANCISCO MONTERO GERENTE 35.000 01 LAURA MONTILLA GERENTE 38.000 01 CARLOS FERNANDEZ CHOFER 11.000 02 Caso 3: Visualización de la columna NOMBRE y SALARIO, de la tabla empleados, calculando a su vez un bono del 10 %. SQL> SELECT NOMBRE, SALARIO, SALARIO*.10 BONO 2 FROM EMPLEADO;


__________________________________________________________________________________________


Visualización de la ejecución: NOMBRE SALARIO BONO ------------------------------ ----------------- ---------------- MARÍA PÉREZ 20.000 2.000 LUIS CARRILLO 10.000 1.000 FRANCISCO MONTERO 35.000 3.500 LAURA MONTILLA 38.000 1.800 CARLOS FERNANDEZ 11.000 1.100 Si al momento de manipular una tabla, no recuerda los nombres de las columnas o el tipo al cual pertenecen, se puede usar el comando DESCRIBE el cual facilitará dicha información. DESCRIBE Origen_datos Abreviación: DESC Origen_datos: es el nombre de la tabla, vista o sinónimo de la cual se desea conocer: el nombre de las columnas; con datos nulos; y tipo de las columnas (Number, Char, etc.). Ejemplo: SQL > DESCRIBE EMPLEADO; Visualización de la ejecución: Name NULL? Type -------------- -------------- ------------------------- NOMBRE CHAR(15) CARGO CHAR(15) SALARIO NUMBER(8,2) NUMDEPT CHAR(10) Suponga el caso donde se desea conocer, los nombre de todos los cargos de empleados, que existen en nuestra empresa. Para ello se asume que dicha información no se repetirá. Para lograr esto debe hacer uso del parámetro DISTINCT, perteneciente a la sentencia SELECT. La sintaxis es la siguiente: SELECT DISTINCT Columnas FROM Origen_datos A continuación se muestra un ejemplo para visualizar todos los cargos que posee nuestra empresa, sin repetir ningún cargo.


__________________________________________________________________________________________


SQL > SELECT DISTINCT CARGO 2 FROM EMPLEADO; Visualización de la ejecución: CARGO --------------------- SECRETARIA CHOFER GERENTE Operadores de selección Los operadores de selección combinan dos consultas o sub-consultas en un resultado. Operadores de selección: - UNION - INTERSECT - MINUS UNION: Busca filas comunes y no comunes en dos o más tablas.

Ejemplo:

Mostrar todos los empleados que ganen más de 20000. Esto se deberá de realizar para todos los departamentos.


__________________________________________________________________________________________


SQL > SELECT NOMBRE, SALARIO 2 FROM EMP_TALLER 3 WHERE SALARIO > 20000 4 UNION 5 SELECT NOMBRE, SALARIO 6 FROM EMP_ADMIN 7 WHERE SALARIO > 20000 8 UNION 9 SELECT NOMBRE, SALARIO 10 FROM EMP_COMPUT 11 WHERE SALARIO > 20000; INTERSECT: Busca las filas comunes en dos o más tablas.

Ejemplo: Mostrar todos los cargos que sean comunes, para todos los departamentos. SQL > SELECT CARGO 2 FROM EMP_TALLER 3 INTERSECT 4 SELECT CARGO 5 FROM EMP_ADMIN 6 INTERSECT 7 SELECT CARGO 8 FROM EMP_COMPUT;


__________________________________________________________________________________________


MINUS: Busca las filas que aparezcan en la primera consulta, pero que a su vez, no aparezcan en a segunda consulta.

Ejemplo: Mostrar todos los cargos que se encuentren en el departamento EMP_TALLER pero que a su vez, no se encuentren en el departamento EMP_ADMIN. SQL > SELECT CARGO 2 FROM EMP_TALLER 3 MINUS 4 SELECT CARGO 5 FROM EMP_ADMIN;


__________________________________________________________________________________________


Alias o nombre sustitutos Un alias es un nombre temporal que sólo tiene efecto durante la operación en curso, el cual sustituye a un nombre de tabla o de columna. Los alias son utilizados cuando: - Se necesita abreviar el nombre original. - Cuando se manejan dos o más tablas en una operación, y en estas existen columnas de igual nombre, se crea una ambigüedad, ya que el manejador de la base de datos, no sabrá a que tablas se está refiriendo. Cuando se pide visualizar, una columna en particular; por tal motivo, se debe usar un alias de tabla en una de las columnas. - Cuando queremos cambiar el nombre de la etiqueta que identifica la columna, al momento de visualizar la información de la base de datos. Ejemplo 1: SQL > SELECT NOMBRE "NOMBRE DEL TRABAJADOR" 2 FROM EMPLEADO; Ejemplo 2: SQL > SELECT NOMBRE NOM 2 FROM EMPLEADO; Ejemplo 3: SQL > SELECT NOMBRE, DEP.NUMDEPT 2 FROM EMPLEADO, DEPARTAMENTOS DEP; Ejemplo 4: SQL > SELECT NOMBRE, SALARIO * .10 BONO 2 FROM EMPLEADO; Selección de filas La instrucción SELECT, puede entregar múltiples registros o filas, de una o más tablas. Pero su verdadera utilidad radica en poder hacer una selección de los datos que cumplan con una determinada condición. Para tal fin, se hará uso del parámetro WHERE.


__________________________________________________________________________________________


La condición de búsqueda se puede formar bajo la siguiente regla: Nombre de columna Expresión o operador de comparación o Expresión Nombre de columna Operadores de comparación (en orden de precedencia). ------------------------------------------------------------------------------------------------------ Símbolo Significado ------------------------------------------------------------------------------------------------------ = Igual a. != < > ^= Diferente de. > Mayor que. >= Mayor o igual que. < Menor que. <= Menor o igual que. IN (lista) Igual a uno de los elementos de la lista de valores. LIKE Busca todo lo que cumpla con el patrón de búsqueda. IS NULL Es un valor nulo. BETWEEN ... AND ... Entre dos valores. ------------------------------------------------------------------------------------------------------ Operadores lógicos (en orden de precedencia). ------------------------------------------------------------------------------------------------------ Símbolo Significado -------------------------------------------------------------------------------------- NOT No. (Niega una condición) AND Y. OR O. -------------------------------------------------------------------------------------- Nota: para cambiar el orden de precedencia de alguno de los operadores antes mencionados se deberá hacer uso de los paréntesis "(", ")". Ejemplo 1: Mostrar todos los empleados, que pertenezcan al departamento 01 y que devenguen un salario mayor de 10.000 y el cargo sea GERENTE SQL> SELECT * 2 FROM EMPLEADO 3 WHERE NUMDEPT = 01 4 AND SALARIO > 20.000 5 AND CARGO = 'GERENTE';


__________________________________________________________________________________________


Ejemplo 2: Mostrar los salarios de todos los empleados que estén entre 10.000 y 20.000. SQL> SELECT * 2 FROM EMPLEADO 3 WHERE SALARIO BETWEEN 10000 AND 20000; Ejemplo 3: Mostrar todos los empleados que devenguen salarios de 10.000, 11.000 y 20.000. SQL> SELECT * 2 FROM EMPLEADO 3 WHERE SALARIO IN (10000, 11000, 20000); Ejemplo 4: Mostrar todos los empleados que tengan en la segunda posición de su nombre la letra "A". SQL> SELECT * 2 FROM EMPLEADO 3 WHERE NOMBRE LIKE '_A%'; En ocasiones, al ejecutar una sentencia SELECT, observamos la existencia de varias filas con el mismo número de departamento, pero en forma dispersa. Cuando esto ocurre, tal vez sea un poco difícil, poder apreciar en conjunto, el nombre de los empleados existentes por cada departamento. Esta dificultad desaparecería, si toda ésta información apareciese agrupada por departamento. Para solucionar este problema, se hace uso de la cláusula GROUP BY. Para usar la cláusula GROUP BY es necesario, que el o los nombres de las columnas que aparecen en la sentencia SELECT, hallan sido especificadas en la sentencia GROUP BY, o en su defecto poseer una de las funciones de agrupamiento (explicadas en la sección Tipos de datos y funciones). Ejemplo: Mostrar una lista de todos los cargos que existen por departamento. En dicha lista se mostrará: número de departamento, cargo y cantidad de cargos similares por departamento. SQL> SELECT NUMDEPT, CARGO, COUNT(CARGO) CANTIDAD 2 FROM EMPLEADO 3 GROUP BY NUMDEPT, CARGO;


__________________________________________________________________________________________


GROUP BY puede ser acompañado por la cláusula HAVING, siendo su uso no obligatorio, pero cuando se usa, se debe acompañar de la cláusula GROUP BY. Su función, es la de crear una condición de agrupamiento; su forma de trabajo es similar a la cláusula WHERE. Ejemplo: Realizar el ejemplo anterior, teniendo en cuenta, que esta vez, se realizará la agrupación y mostrará todos aquellos departamentos cuya cantidad por cargo sea menor de 5. SQL> SELECT NUMDEPT, CARGO, COUNT(*) CANTIDAD 2 FROM EMPLEADO 3 GROUP BY NUMDEPT, CARGO 4 HAVING COUNT(*) < 5; En ocasiones es necesario mostrar la información de la base de datos, ordenada por una o más columnas, ya sea ascendente o descendente. Para este fin, haremos uso de la cláusula ORDER BY. Nota: el uso de la cláusula ORDER BY, sólo tendrá efecto sobre la visualización de la información de la base de datos, y no, sobre la base de datos. Ejemplo: Mostrar todos los empleados del departamento 01, ordenados de la siguiente manera: - Nombre, cargo y departamento ordenado ascendentemente. - Salario ordenado descendentemente. La prioridad de ordenación es la siguiente: - Nombre. - Cargo. - Salario. - Departamento. SQL> SELECT * 2 FROM EMPLEADO 3 WHERE NUMDEPT = 01 4 ORDER BY NOMBRE, CARGO, SALARIO DESC, NUMDEPT; Hasta este momento, hemos ordenado la visualización de una tabla, por medio de los nombre de las columnas. También podemos realizar esta operación colocando en vez del nombre de la columna, la posición de la misma. Cuando se indican los nombres de las columnas a visualizar, la posición de esta, será la misma posición que hemos colocado en la sentencia; pero cuando se coloca el asterisco (*), para indicar que se visualicen todas las columnas, la posición de estas será, la misma que elegimos al momento de crear la tabla.


__________________________________________________________________________________________


Ejemplo 1: Mostrar la tabla empleados ordenada por la segunda columna, teniendo en cuenta, que la información deberá aparecer de la siguiente manera: NOMBRE, SALARIO, CARGO SQL> SELECT NOMBRE, SALARIO, CARGO 2 FROM EMPLEADO 3 ORDER BY 2; Ejemplo 2: Mostrar la tabla empleados ordenada por la segunda columna, teniendo en cuenta, que la posición de dicha columna, deberá ser la misma que se uso, al momento de crear la tabla. SQL> SELECT * 2 FROM EMPLEADO 3 ORDER BY 2;


__________________________________________________________________________________________


II.2.- FUNCIONES Y TIPOS DE DATOS Las sentencias de SQL, permiten hacer diferentes cálculos aritméticos, como lo son los siguientes (en orden de precedencia): * MULTIPLICACIÓN. / DIVISIÓN. + SUMA. - RESTA. Nota: a los anteriores se le puede cambiar el orden de precedencia con el uso de paréntesis "(", ")". Ejemplo: SQL> SELECT NOMBRE, SALARIO*12 "SALARIO ANUAL", SALARIO/30 2 FROM EMPLEADO; Como se pudo apreciar, el poder de las sentencias de SQL, se ve incrementado, al tener la posibilidad de realizar operaciones aritméticas; pero esto no es todo, ya que existe una gran variedad de funciones matemáticas, que hacen más poderoso a SQL. A continuación una breve reseña: --------------------------------------------------------------------------------------------------- Funciones numéricas Utilidad --------------------------------------------------------------------------------------------------- NVL(n, m) Convierte el valor "n", a un valor "m", cuando "n" es nulo. MOD(n, m) Devuelve el residuo de la división de "n" entre "m". SQRT(n) Devuelve la raíz cuadrada de "n". ROUND(n, m) "n" es redondeado a la cantidad de "m" dígitos decimales si "m" es positivo y unidades si "m" es negativo. TRUNC(n, m) "n" es truncado a la cantidad de "m" dígitos decimales si "m" es positivo y unidades si "m" es negativo. SIGN(n) Si n es menor que cero, la función retorna -1. Si n es igual que cero, la función retorna 0. Si n es mayor que cero, la función retorna 1. POWER(n, m) Obtiene el resultado de "n" elevado a la "m". ABS(n) Retorna el valor absoluto de un valor "n".


__________________________________________________________________________________________


--------------------------------------------------------------------------------------------------- Funciones numéricas Utilidad <cont> --------------------------------------------------------------------------------------------------- Ceil(n) Retorna el menor entero, mayor o igual que "n". Floor(n) Retorna el mayor entero, menor o igual que "n". --------------------------------------------------------------------------------------------------- Nota: "n", "m" son valores de tipo numérico entero, los cuales pueden ser constantes o nombres de columnas. "f" son formatos numéricos --------------------------------------------------------------------------------------------------- Funciones de fecha y hora Utilidad --------------------------------------------------------------------------------------------------- ADD_MONTHS(n, m) Adiciona a "n" la cantidad de meses que indique "m", "m" puede ser positivo o negativo. LAST_DAY(n) Retorna el último día del mes "n". NEXT_DAY(n, c) Retorna una fecha superior a "n" cuyo día de la semana, sea igual a "c". MONTHS_BETWEEN(n, m) Retorna la cantidad de meses entre las fechas "n" y "m". Si "n" es mayor que "m" el resultado será positivo, caso contrario, será negativo. SYSDATE Retorna la fecha y hora en curso del sistema. --------------------------------------------------------------------------------------------------- Nota: "n", "m" son valores de tipo fecha y hora (según el caso). "c" es un valor de tipo caracter. --------------------------------------------------------------------------------------------------- Funciones de caracteres Utilidad --------------------------------------------------------------------------------------------------- INITCAP(c1) Retorna la primera letra del contenido de "c1", en mayúsculas, y todas las demás letras en minúsculas. UPPER(c1) Retorna el contenido de "c1" en mayúsculas. LOWER(c1) Retorna el contenido de "c1" en minúsculas.


__________________________________________________________________________________________


--------------------------------------------------------------------------------------------------- Funciones de caracteres Utilidad <cont> --------------------------------------------------------------------------------------------------- SUBSTR(c1, n1, n2) Extrae del contenido de "c1", "n2" caracteres, partiendo desde la posición "n1". LENGTH(c1) Retorna el número de dígitos del contenido de "c1" LPAD(c1, n1,[c2]) Muestra el contenido de c1, colocando por el lado izquierdo los caracteres "c2". El espacio (en caracteres) para la visualización de "c2" será igual a "n1" menos la cantidad de caracteres de "c1". Si "c2" no existe, se tomará por defecto espacio en blanco. RPAD(c1, n1,[c2]) Muestra el contenido de c1, colocando por el lado derecho los caracteres "c2". El espacio (en caracteres) para la visualización de "c2" será igual a "n1" menos la cantidad de caracteres de "c1". Si "c2" no existe, se tomará por defecto espacio en blanco. REPLACE(c1, c2, [c3]) Se busca "c2" en el contenido de "c1", si es encontrada, se sustituye por "c3", de lo contrario la función devolverá NULL. Si no se especifica "c3", y "c2" es encontrada en "c1", por defecto se borrará. TRANSLATE(c1, c2, c3) Se busca "c2", caracter a caracter en el contenido de "c1", si es encontrado, se sustituye por su caracter correspondiente almacenado en "c3", de lo contrario no tendrá ningún efecto sobre "c1". Ejemplo: TRANSLATE('HOLA', 'AHOL', 'OCHA') Resultado de la ejecución: CHAO. --------------------------------------------------------------------------------------------------- Nota: "c1", "c2", "c3", es un valor de tipo caracter, el cual puede ser un nombre entre comillas, o un nombre de columna. "n1", "n2" son valores numéricos enteros. --------------------------------------------------------------------------------------------------- Funciones de conversión Utilidad --------------------------------------------------------------------------------------------------- TO_CHAR(d, [f]) Convierte un valor de tipo fecha u hora "d", a un valor de (Conversión tipo char, pudiendo especificar opcionalmente un formato de tipo fecha y hora) "f". Si no se especifica el formato "f", por defecto se tomará "DD-MON-YY". La función de TO_CHAR, es cambiar la forma de visualización de la fecha u hora.


__________________________________________________________________________________________


--------------------------------------------------------------------------------------------------- Funciones de conversión Utilidad <cont> --------------------------------------------------------------------------------------------------- TO_CHAR(n, [f]) Convierte un valor de tipo numérico "n", a un valor de tipo (Conversión numérica) char, pudiendo especificar opcionalmente un formato "f". La función de TO_CHAR, es cambiar la forma de visualización de un número. TO_DATE(c, [f]) Convierte el contenido de "c", a un valor de tipo fecha u hora, pudiendo especificar opcionalmente un formato "f" Si no se especifica el formato "f", por defecto se tomará "DD-MON-YY". TO_NUMBER(c) Convierte el contenido de "c", a un valor de tipo numérico. Nota: se asume que "c" contienen caracteres numéricos. CONVERT(c, set1, set2) Conversión entre dos diferentes implementaciones de una misma selección de caracteres, ejemplo: de caracteres multilenguaje DEC8 bit a caracteres multilenguaje HP 8 bit. "c" es el caracter a convertir. "set1", es la selección de caracteres destino. "set2" es la selección de caracteres fuente. --------------------------------------------------------------------------------------------------- Nota: "d" es un valor de tipo fecha u hora. "n" es un valor de tipo numérico. "c" es un valor de tipo caracter. "f" es un modelo de formato. JUEGO DE CARACTERES DE MULTILENGUAJE MAS COMUNES: --------------------------------------------------------------------------------------------------- Nombre Significado --------------------------------------------------------------------------------------------------- US7ASCII US 7-bit ASCII (usado por defecto) WE8DEC DEC's Western European 8 bit ASCII. WE8HP HP's Western European 8 bit ASCII F7DEC DEC's French 7-bit ASCII. WEIBMPC Western European 8-bit ASCII (usado por las IBM PC). --------------------------------------------------------------------------------------------------- MODELOS DE FORMATO: Formatos numericos más utilizados: --------------------------------------------------------------------------------------------------- Elemento Ejemplo Descripción --------------------------------------------------------------------------------------------------- 9 9999 La cantidad de nueves (9) determina el ancho del numero a mostrar. 0 0999 Muestra ceros a la izquierda de un número. $ $9999 Prefija a un valor con el simbolo de moneda. B B9999 Muestra espacios en blanco (B) a la izquierda de un número. Coma 9,999 Muestra una coma en esta posición. Punto 99.99 Muestra un punto decimal en esta posición. ---------------------------------------------------------------------------------------------------


__________________________________________________________________________________________


Formatos de tipo fecha más utilizados: --------------------------------------------------------------------------------------------------- Elemento Significado --------------------------------------------------------------------------------------------------- YYYY Los cuatro dígitos del año. YYY Los últimos tres dígitos del año. YY Los últimos dos dígitos del año. Y El último digito del año. MM Número del mes (del 1 al 12). MON Nombre del mes (abreviado a 3 letras) MONTH Nombre del mes, acompañado de un máximo de 9 caracteres en blanco. D Días de la semana (del 1 al 7). DD Días del mes (del 1 al 31). DDD Días del año (del 1 al 366). MI Minutos. SS Segundos. --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- Funciones de agrupamiento Utilidad --------------------------------------------------------------------------------------------------- AVG(n) Retorna el average de la columna "n", ignorando valores nulos. COUNT(n) Retorna el número de filas que tiene la columna "n", ignorando valores nulos. MAX(n) Retorna el máximo valor de la columna "n". MIN(n) Retorna el mínimo valor de la columna "n". SUM(n) Retorna la sumatoria de los valores de la columna "n". --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- Funciones adicionales Utilidad --------------------------------------------------------------------------------------------------- GREATEST(n, m) Retorna el mayor entre "n" y "m". LEAST(n, m) Retorna el menor entre "n" y "m".


__________________________________________________________________________________________


DECODE(c, n1, r1, n2, r2) d Retorna el valor "r1", si "c" es igual a "n1", Retorna el valor "r2", si "c" es igual a "n2", En caso de no coincidir con ningún valor de la lista retorna "d". El valor "d" puede omitirse. Nota: pueden haber tantos n y r como se deseen. USER Retorna el nombre del usuario que se encuentra conectado actualmente a la base de datos. USERENV({'ENTRYID' | Retorna la información sobre el usuario y/o sección que 'SESSIONID' | está siendo utilizada por una aplicación, funcionando para 'TERMINAL' | una tabla de auditoria. Si la opción es ENTRYID, la función 'LANGUAGE' | retorna un identificador de entrada disponible para }) auditoria; si la opción es SESSIONID retorna un identificador de la sección de auditoria del usuario; si la opción es TERMINAL, retorna el identificador del sistema operativo del terminal del usuario; si la opción es LANGUAGE retorna el lenguaje en uso (como el ingles), determinado por los parámetros LANGUAGE en el INIT.ORA. VSIZE(n) Retorna el número de bytes usados para almacenar una expresión "n". --------------------------------------------------------------------------------------------------- Nota: Las funciones anteriores sirven para todo tipo de datos. Ejemplo: Mostrar el número de departamento, el salario más alto, el salario promedio y el total de salarios por departamento y la información deberá aparecer de la siguiente manera: Departamento Máximo Salario Salario Promedio Total Salarios SQL> SELECT NUMDEPT Departamento, 2 MAX(SALARIO) "Máximo Salario", 3 AVG(SALARIO) "Salario Promedio", 4 SUM(NVL(SALARIO, 0)) "Total Salario" 5 FROM EMPLEADO 6 WHERE NUMDEPT IS NOT NULL 7 GROUP BY NUMDEPT;


__________________________________________________________________________________________


Mostrar la cantidad de empleados, que devengen salarios: - Menores o iguales a 15.000; - Entre 15.001 y 20.000; - Superiores o iguales a 20.000. Esto se realizará para todos aquellos departamentos que tengán más de 5 empleados. A continuación un ejemplo del formato de aparición. Departamento Del # AL 15000 HAY x# Del 15001 AL 20000 HAY x# Más de 20001 HAY x# SQL> SELECT 'Departamento ' || RPAD(NUMDEPT,66,' ') || 2 'Del ' || 3 TO_CHAR(NVL(MIN(DECODE(SIGN(SALARIO-15000),-1, SALARIO)),0)) || 4 ' AL 15000 HAY ' || 5 TO_CHAR(COUNT(DECODE(SIGN(SALARIO-15000),-1,1,0,1))) || 6 ' Del 15001 AL 20000 HAY ' || 7 TO_CHAR(COUNT(DECODE(LEAST(GREATEST(SALARIO,15001),20000), 8 SALARIO, SALARIO))) || 9 ' Mas de 20001 HAY ' || TO_CHAR(COUNT( 10 DECODE(GREATEST(SALARIO, 20001),SALARIO, SALARIO))) " " 11 FROM EMPLEADO02 12 WHERE NUMDEPT IS NOT NULL 13 GROUP BY RPAD(NUMDEPT,66,' ') 14 HAVING COUNT(*) >= 5


__________________________________________________________________________________________


II.3.- REPORTES EN SQL*PLUS

Anteriormente se han elaborado reportes simples, los cuales carecen de ciertos detalles,

como títulos, formato, totalización, etc. Para lograr esto se hará uso de los comandos de

SQL*Plus, lo cual permitirá, crear reportes de mejor calidad, los cuales se podrán visualizar por

pantalla, por impresora, o simplemente almacenar su salida en un archivo.

Comandos de formato de SQL*Plus TTITLE Uso: Muestra un título en el tope cada página del reporte. Forma abreviada: TTI. Sintaxis: TTITLE [ep [texto | variable ] ] | [OFF | ON ]. ep: ver: especificaciones de impresión. OFF: desactiva la opción. ON: activa la opción. BTITLE Uso: Muestra un título en el pie de página del reporte. Forma abreviada: BTI. Sintaxis: BTITLE [ep [texto | variable ] ] | [OFF | ON ]. ep: ver: especificaciones de impresión. OFF: desactiva la opción. ON: activa la opción. COLUMN Uso: Sirve para especificar los atributos con los cuales van a ser mostrados una columna, tal como encabezados de las columnas, alineación, formato de los datos y el manejo de los datos. Forma abreviada: COL Sintaxis: COLUMN [{Columna | expresión} [opciones] ]. Opciones: ALIAS JUSTIFY [ LEFT | CENTER | CENTRE | RIGHT ] OLD_VALUE CLEAR LIKE PATTERN COLOR LINEAPP [ LINE | MARK | BOTH] WRAPPED FOLD_AFTER NEWLINE FOLD_BEFORE NEW_VALUE FORMAT NOPRINT HEADING NULL


__________________________________________________________________________________________


BREAK Uso: Indica cuando y como cambia el formato de impresión en el reporte, tal como, suprimiendo los valores duplicados para la columna especificada, saltando lineas, al cambiar el valor de la columna, mostrando cálculos especificados a través de un COMPUTE. Forma abreviada: BRE Sintaxis: BREAK [ ON columnas del Select [ acción [ acción]]] ... COMPUTE Uso: Permite realizar cálculos e imprimir sumatorias, usando varios cálculos estándares, sobre subgrupos, de filas seleccionadas. Forma abreviada: COMP Sintaxis: COMPUTE [función ... OFF {expresiones | columna | alias}... ON {expresiones | columna | alias | REPORT | ROW}] Funciones permitidas en el comando COMPUTE: AVG Obtiene el promedio de valores no nulos. COUNT Obtiene la cantidad de los valores no nulos. MAXIMUM Obtiene el máximo valor. MINIMUM Obtiene el mínimo valor. NUMBER Obtiene el número de filas. STD Obtiene la desviación estándar de valores no nulos. SUM Obtiene la sumatoria de valores no nulos. VARIANCE Obtiene la varianza de valores no nulos. SET Uso: Establece los aspectos de ambiente de SQL*Plus, para la sección actual de trabajo, tales como: - Fija los atributos del display para los datos numéricos LONG, CHAR y DATE; definición de tamaño de página PAGESIZE, linea LINESIZE, arreglos de memoria ARRAYSIZE, espaciado entre columnas SPACE, etc. - Fija los atributos para los parámetros de ambiente tales como impresión de encabezados, cubierta, salidas a pantallas, pie de página, mensajes, etc. Sintaxis: SET variable del sistema.


__________________________________________________________________________________________


VARIABLES DEL SISTEMA MAS USADAS ECHO Uso: Cuando está activo, muestra los comandos que están siendo ejecutados. Caso contrario cuando esta desactivado. Sintaxis: ECHO {OFF | ON} FEEDBACK Uso: Muestra el número de registros retornados por una consulta, cuando la misma selecciona un mínimo de "n" registros. Cuando se coloca ON, "n" será igual a uno. Cuando se coloca OFF, "n" será igual a cero; por defecto "n", vale 6. Forma abreviada: FEED. Sintaxis: FEEDBACK {6 | n | OFF | ON} n: es el mínimo número de registros que debe procesar la instrucción para ser mostrado el FEEDBACK NULL Uso: Selecciona un texto que representará a los valores nulos. Por defecto este será espacio. Sintaxis: NULL texto PAGESIZE Uso: Selecciona el número de lineas por páginas Forma abreviada: PAGES Sintaxis: PAGESIZE {14 | n} SPACE Uso: Selecciona el número de espacios entre columnas. Forma abreviada: SPA. Sintaxis: SPACE {1 | n} ESPECIFICACIONES DE IMPRESIÓN COL Uso: Hace que el próximo valor de tipo CHAR, aparezca en la columna "n" de una linea. Sintaxis: COL n SKIP Uso: Salta hacia una nueva linea "n" veces. Si n es mayor que 1, n-1 lineas en blanco aparecerán después del próximo valor de tipo carácter. Sintaxis: SKIP n TAB Uso: Salta "n" columnas hacia adelante o hacia atrás dependiendo si el valor de "n" es positivo o negativo. Columnas se refiere a posición de impresión. Sintaxis: TAB n


__________________________________________________________________________________________


LEFT Uso: Alinea un valor de tipo char a la izquierda. Sintaxis: LEFT CENTER Uso: Alinea un valor de tipo char al centro. Sintaxis: CENTER RIGHT Uso: Alinea un valor de tipo char a la derecha Sintaxis: RIGHT BOLD Uso: Imprime los datos en negrita Sintaxis: BOLD FORMAT Uso: Especifica el modelo de formato o máscara en el cual se mostrarán los datos de la columna especificada. Sintaxis: FORMAT máscara o formato. A continuación se mostrará la codificación para la generación de un reporte, el cual reúne una buena parte, de lo expuesto anteriormente. Para ello se necesitará hacer uso de un editor, o procesador de palabras (que grabe en formato ASCII); una vez realizado esto, guárdelo con el nombre de su preferencia, pero con extensión SQL. Una vez realizado el paso anterior, se puede ejecutar de dos formas: - START nombre.sql o - @nombre.sql


__________________________________________________________________________________________


Ejemplo: SET PAUSE ON SET PAGESIZE 24 SET ECHO OFF SET FEEDBACK OFF TTITLE RIGHT 'PAGINA' SQL.PNO SKIP 2 - CENTER 'REPORTE DE PERSONAL' SKIP 2 - CENTER 'Producido por el departamento de personal' SKIP 2 BTITLE CENTER 'Información confidencial' COLUMN DEPTNO HEADING 'DEPARTAMENTO' FORMAT 99 COLUMN NOMBRE FORMAT A10 COLUMN CARGO FORMAT A10 COLUMN SALARIO HEADING 'SALARIO|MENSUAL' FORMAT $99,990.00 COLUMN ANUAL HEADING 'SALARIO|ANUAL' FORMAT 999,990.00 SET SPACE 6 BREAK ON NUMDEPT SKIP 2 ON REPORT COMPUTE SUM AVG OF SALARIO ON NUMDEPT REPORT COMPUTE STD OF ANUAL ON NUMDEPT REPORT SET NULL 'DESCONOCIDO' SELECT NUMDEPT, NOMBRE, CARGO, SALARIO, SALARIO*12 ANUAL FROM EMPLEADO ORDER BY NUMDEPT Como salida se visualizará PAGINA 1 REPORTE DE PERSONAL Producido por el departamento de personal SALARIO SALARIO DEPARTAMENTO NOMBRE CARGO MENSUAL ANUAL ----------------------- ---------------- ------------------ ---------------------- --------------------------- 10 PEDRO OBRERO $2,450.00 29,400.00 MANUEL PRESIDENTE $5,000.00 60,000.00 EMILIO GERENTE $1,500.00 18,000.00 TERESA LIMPIEZA $1,300.00 15,600.00 JOSÉ SUB-GERENTE $1,500.00 18,000.00 LUIS PASANTE DESCONOCIDO DESCONOCIDO CHANG PASANTE DESCONOCIDO DESCONOCIDO INGRID SECRETARIA $2,000.00 24,000.00 CARMEN SECRETARIA $2,550.00 30,600.00 **************** ---------------------- ----------------------------- avg $2,328.57 sum $16,300.00 std 34,824.70 Información confidencial PAGINA 2


__________________________________________________________________________________________


REPORTE DE PERSONAL Producido por el departamento de personal SALARIO SALARIO DEPARTAMENTO NOMBRE CARGO MENSUAL ANUAL ----------------------- -------------- ------------------ ---------------------- ---------------------- 20 RODRIGO LIMPIEZA $800.00 9,600.00 ISABEL LIMPIEZA $1,100.00 3,200.00 GERARDO ANALISTA $3,000.00 36,000.00 DILIA ANALISTA $3,000.00 36,000.00 LUIS SECRETARIO $2,000.00 24,000.00 PETRONILO GERENTE $2,975.00 35,700.00 **************** ---------------------- ---------------------- avg $2,145.83 sum $12,875.00 std 71,068.05 Información confidencial PAGINA 3 REPORTE DE PERSONAL Producido por el departamento de personal SALARIO SALARIO DEPARTAMENTO NOMBRE CARGO MENSUAL ANUAL ----------------------- -------------- --------------------- ---------------------- ---------------------- 30 TOMAS VENDEDOR $1,600.00 19,200.00 RAQUEL LIMPIEZA $2,850.00 34,200.00 ORLANDO VENDEDOR DESCONOCIDO DESCONOCIDO GLORIA VENDEDOR $1,250.00 15,000.00 JUDITH VENDEDOR $950.00 1,400.00 MARÍA VENDEDOR $1,500.00 8,000.00 JOSEFINA VENDEDOR $1,250.00 15,000.00 **************** ---------------------- ---------------------- avg $1,566.67 sum $9,400.00 std 55,051.68 Información confidencial


__________________________________________________________________________________________


PAGINA 4 REPORTE DE PERSONAL Producido por el departamento de personal SALARIO SALARIO DEPARTAMENTO NOMBRE CARGO MENSUAL ANUAL -------------------------- -------------- ------------------ ---------------------- ---------------------- ------------------------ ---------------------- avg $2,030.26 sum $38,575.00 std 689,055.92 Información confidencial


__________________________________________________________________________________________


II.4.- MANIPULACIÓN DE DATOS En ocasiones se hace necesario, insertar información en alguna tablas, de la base de datos. Para este fin se tiene el comando INSERT. INSERT INTO nombre_de_tabla [(columna1, columna2...)] VALUES (valor1, valor2...). INSERT permite adicionar una nueva fila, a la tabla especificada; esto se puede hacer con todas las columnas (colocando los valores de todas las columnas en el mismo orden de la tabla) o con algunas columnas (colocando después del nombre de la tabla, el nombre de las columnas en las cuales se desea insertar la información y después de VALUES, los valores que tendrán estas). Ejemplo 1: Insertar una nueva fila, (con todas sus columnas), a la tabla empleado. SQL > INSERT INTO EMPLEADO 2 VALUES ('INGRID', 'SECRETARIA', 18000, 01); Ejemplo 2: Insertar una nueva fila, (con las columnas nombre y salario), a la tabla empleado. SQL > INSERT INTO EMPLEADO (NOMBRE, SALARIO) 2 VALUES ('INGRID', 18000); Ejemplo 3: Insertar una nueva fila, (con las columnas nombre y fecha), a la tabla cumpleaños. Nota: cumpleaños será reemplazado por cumpleanos, para evitar problemas con SQLPLUS. SQL > INSERT INTO CUMPLEANOS (NOMBRE, FECHA) 2 VALUES ('INGRID', TO_DATE('931802', 'YYDDMM')); Ejemplo 4: Insertar una o más fila, a la tabla empleado usando las columnas nombre y salario, donde el sistema, sea el que pida los valores de cada columna, gracias al uso de & para crear variables. SQL > INSERT INTO EMPLEADO (NOMBRE, SUELDO) 2 VALUES (&NOMBRE, &SUELDO);


__________________________________________________________________________________________


Ejemplo 5: Insertar una o más fila, a la tabla empleado usando las columnas nombre y salario tomando datos de otra tabla. SQL > INSERT INTO EMPLEADO (NOMBRE, SUELDO) 2 SELECT NOMBRE, SUELDO * 1.25 FROM EMPLEADO_VIEJO; Imaginese el caso, que una vez impreso un reporte, hemos detectado un error en los datos del mismo y por lo tanto deseamos corregirlo, es aquí donde entra en juego el comando UPDATE. UPDATE nombre_de_la_tabla SET columna1 = valor, columna2 = valor... [WHERE condición] Ejemplo 1: Aumentar el salario un 20% a todos los empleados del departamento 01. SQL > UPDATE EMPLEADO 2 SET SALARIO = SALARIO+SALARIO*0.2 3 WHERE NUMDEPT = 01; Ejemplo 2: Aumentar el salario un porcentaje a todos los empleados, usando un porcentaje por departamento. SQL > UPDATE EMPLEADO 2 SET SALARIO = SALARIO + SALARIO * DECODE(NUMDEPT,01,0.2,02,0.3,.015) Ejemplo 3: Aumentar el salario con el 10 % del promedio de salarios de su departamento. SQL > UPDATE EMPLEADO A SET SALARIO = 2 ( SELECT SALARIO+ AVG(SALARIO)*.10 FROM EMPLEADO B 3 WHERE B.NUMDEPT = A.NUMDEPT);


__________________________________________________________________________________________


Para poder borrar una fila o un conjunto de filas se hará uso del comando DELETE DELETE FROM nombre_de_la_tabla [WHERE condición] Ejemplo 1: Eliminar los empleados del departamento 02 SQL > DELETE FROM EMPLEADO 2 WHERE NUMDEPT = 02; Ejemplo 2: Eliminar los empleados que tengan un salario mayor del promedio de salarios de su departamento. SQL > DELETE EMPLEADO A WHERE SALARIO > 2 ( SELECT AVG(B.SALARIO) FROM EMPLEADO B 3 WHERE B.NUMDEPT = A.NUMDEPT); Transacción Se considera como una transacción, todos aquellos cambios realizados a la base de datos, una vez ejecutado el comando COMMIT. COMMIT guarda en forma "permanente", todos los cambios realizados a la base de datos. ------------------ COMMIT | | INSERT... | | DELETE... TRANSACCIÓN | | UPDATE... | | INSERT... | ------------------ COMMIT La contra parte de COMMIT, es ROLLBACK. Esta instrucción ignora todos los cambios realizados a la base de datos, excepto si previamente se ha realizado un COMMIT.


__________________________________________________________________________________________


------------------ COMMIT | | INSERT... TRANSACCION | | DELETE... | | ---------------- ROLLBACK | TRANSACCION | UPDATE... | | INSERT... | ------------------ COMMIT Existe también una forma de ignorar, parcialmente los cambios realizados a la base de datos. Esto se logra introduciendo marcas, que tienen como finalidad, informar al comando ROLLBACK, hasta donde deben ser ignorados, los cambios efectuados a la base de datos. La forma de realizar estas marcas, es por medio del comando SAVEPOINT. ------------------ COMMIT | | INSERT... | | SAVEPOINT A | | DELETE... TRANSACCION | | UPDATE... | | ROLLBACK TO SAVEPOINT A | | INSERT... | ------------------ COMMIT


__________________________________________________________________________________________


II.5.- TABLAS Y VISTAS ¿Como crear una tabla? El nombre de una tabla debe de cumplir con las siguientes reglas. - Debe ser de 1, a máximo 30 caracteres y el primero debe ser alfabético. - Debe contener los caracteres: A - Z, a - z, 0 - 9, subrayado (_) $ y # (son símbolos legales pero pueden causar problemas). - No deben ser palabras reservadas de SQL. - No pueden tener el mismo nombre de los objetos usados por ORACLE. Sintaxis: CREATE TABLE [usuario.]nombre_de_la_tabla ({nombre_de_columna tipo | tabla_de_integridad} [, {{nombre_de_columna tipo | tabla_de_integridad}]...) STORAGE (Initial nK Next nK Pctincrease n Maxextents n Minextents n); El "tipo", puede ser de tipo CHAR, DATE, LONG, NUMBER. Reglas de integridad PRIMARY KEY Es la columna o conjunto de columnas, que permite la identificación única, de cada fila de la tabla; llamadas claves primarias. CHECK Es la definición de la regla del negocio, las cuales involucran columnas contra valores constantes. FOREIGN KEY Es la columna o conjunto de ellas, que establecen una relación con una clave primaria de otra tabla. Esto indica, que para el conjunto de columnas, de la fila de la tabla "A", debe existir, el mismo conjunto de columnas y valores en la tabla "B".


__________________________________________________________________________________________


[NOT] NULL Permite validar que la columna pueda tener o no, valores nulos, en otras palabras, si la columna es mandatoria. UNIQUE Es la columna o conjunto de ellas, que permiten una identificación única alterna, de cada fila de la tabla, llamadas claves secundarias o alternas. REFERENCES Es una clave foránea de una columna simple. Reglas de STORAGE Indica el almacenamiento con que trabajará la tabla que vaya a ser creada, basado en el tamaño de la extenciones o anexos de espacio (extents). Initial Indica el espacio inicial de creación de la tabla (Primer Extents) Next Segundo extents a ser asignado a la tabla. Pctincrease Porcentaje de incremento para entre los extents requeridos por la tabla a partir del tercero. Maxextents Es la cantidad máxima de extents que puede crecer una tabla. Minextents Es la cantidad mínima necesaria de extents para poder crear la tabla. Ejemplo 1: Crear una tabla llamada EMPLEADO2, con las columnas: - CODIGO - NOMBRE - CARGO - CODJEFE - SALARIO - NUMDEPT Caso Clave Primaria Simple, Clave foranea Simple SQL > CREATE TABLE EMPLEADO2 2 (CODIGO NUMBER(4) PRIMARY KEY, 3 NOMBRE CHAR(10) CHECK(NOMBRE = UPPER(NOMBRE)), 4 CARGO CHAR(10), 5 CODJEFE NUMBER(4) REFERENCES EMPLEADO2 (CODIGO), 6 SALARIO NUMBER(7,2) CHECK(SALARIO <= 80000), 7 NUMDEPT NUMBER(2) NOT NULL REFERENCES DEPT (NUMDEPT));


__________________________________________________________________________________________


Caso Clave Primaria Compuesta, Clave foranea Compuesta SQL > CREATE TABLE EMPLEADO2 2 (CODIGO NUMBER(4) NOT NULL, 3 NOMBRE CHAR(10) CHECK(NOMBRE = UPPER(NOMBRE)), 4 CARGO CHAR(10), 5 CODJEFE NUMBER(4) , 6 SALARIO NUMBER(7,2) CHECK(SALARIO <= 80000), 7 NUMDEPT NUMBER(2) NOT NULL REFERENCES DEPT (NUMDEPT), 8 PRIMARY KEY(CODIGO,NUMDEPT), 9 FOREIGN KEY(CODJEFE,NUMDEPT) REFERENCES EMPLEADO2); Ejemplo 2: Crear una tabla llamada EMPLEADO3, basada en EMPLEADO2. Los nombres de las columnas serán: NOMBRE, SALARIO SQL > CREATE TABLE EMPLEADO3 2 AS SELECT NOMBRE, SALARIO 3 FROM EMPLEADO2 4 WHERE NUMDEPT = 01; ¿Como hacer modificaciones en una tabla? La sentencia ALTER TABLE, permite alterar las siguiente definiciones de una tabla: - Adicionar columnas o integridad. - Modificar las definiciones de una columna (CHAR, NUMBER, NOT NULL, etc.). - Borrar definiciones de integridad. Sintaxis para adicionar: ALTER TABLE nombre_de_la_tabla ADD ({nombre_columna tipo | tabla integridad} [, {nombre_columna tipo | tabla integridad}]...) Ejemplo: SQL > ALTER TABLE DEPARTAMENTO 2 ADD (CODJEFE NUMBER(4) 3 REFERENCES EMPLEADO(CÓDIGO) CONSTRAINT EMP_FK); Sintaxis para modificar:


__________________________________________________________________________________________


ALTER TABLE nombre_de_la_tabla MODIFY({nombre_columna definición_de_columna} [, {nombre_columna definición_de_columna}...) Ejemplo: SQL > ALTER TABLE EMPLEADO 2 MODIFY NOMBRE CHAR(20); Sintaxis para borrar ALTER TABLE nombre_de_la_tabla DROP CONSTRAINT (nombre_de_integridad) Ejemplo: SQL > ALTER TABLE DEPARTAMENTO 2 DROP CONSTRAINT EMP_FK; ¿Como renombrar una tabla? Usamos la sentencia RENAME Sintaxis: RENAME tabla_vieja TO tabla_nueva ¿Como borrar una columna de una tabla? En el caso de querer remover una columna de una tabla, debemos hacer uso de nuestra imaginación y conocimientos antes adquiridos. Ejemplo: Suponga que la tabla EMPLEADO2, posee las siguientes columnas: - NOMBRE - CARGO - SALARIO - NUMDEPT Se pide eliminar la columna NUMDEPT Se crea una nueva tabla a partir de la tabla EMPLEADO2 SQL > CREATE TABLE TEMPORAL (NOMBRE, CARGO, SALARIO) 2 AS SELECT NOMBRE, CARGO, SALARIO


__________________________________________________________________________________________


3 FROM EMPLEADO2; Se borra la tabla EMPLEADO2 SQL > DROP TABLE EMPLEADO2; Se renombra la tabla TEMPORAL por EMPLEADO2 SQL > RENAME TEMPORAL 2 TO EMPLEADO2; ¿Como crear una vista? La forma de crear una vista es la misma que se utiliza para crear una tabla, pero utilizando una instrucción SELECT accesando columnas de una o varias tablas. Sintaxis: CREATE VIEW nombre de la vista AS SELECT clausula Select; Ejemplo; Crear una visión de los datos de la table empleado para el departamento 10 SQL > CREATE VIEW VISTA_EMP 2 AS SELECT NOMBRE, CARGO, SALARIO 3 FROM EMPLEADO 4 WHERE NUMDEPT = 10; Ventajas de las vistas - Seguridad (Permite dar acceso a partes de una tabla a otros usuario). - Conveniencia (puede acortar la codificación en un programa, ya que evita la realización de instrucciones SELECT que se repiten). - Perspectivas (Se puede visualizar pedazos de varias tablas). select numero from factura_cliente order by numero desc


__________________________________________________________________________________________


¿Cómo visualizar una vista? La forma de visualizar una vista es la misma que se utiliza para visualizar una tabla. (Se utilizan los mismos parámetros de la sentencia SELECT). SQL > SELECT * 2 FROM VISTA_EMP; Actualización de una tabla a través de una vista SQL > UPDATE VISTA_EMP 2 SET SALARIO = 20000 3 WHERE SALARIO = 15000; Como se pudo observar en el ejemplo anterior, un usuario puede cambiar el contenido de una tabla, a través de una vista. En ocasiones es necesario evitar, que se modifiquen o inserten filas, que luego no podrán ser visualizadas por medio de la sentencia SELECT. Con este objetivo se utilizará la cláusula WITH CHECK OPTION. Ejemplo: SQL > CREATE VIEW VISTA_EMP2 2 AS SELECT NOMBRE, CARGO 3 FROM EMPLEADO 4 WHERE NUMDEPT = 01 5 WITH CHECK OPTION; De esta manera cuando intentemos realizar una actualización no permitida tendremos lo siguiente: SQL > UPDATE VISTA_EMP2 2 SET NUMDEPT = 05 3 WHERE SALARIO > 30000; ORA-014022: view WITH CHECK OPTION where-clause violation. (Mensaje arrojado). Este error se debe, a que la vista creada, sólo permite visualizar a los empleados del departamento 01. Si este cambio se hubiese efectuado, no se podría observar los cambios realizados en la tabla, dando a pensar, que se eliminó la información, cosa que sería falso. Sólo se pueden modificar valores sobre aquellas vistas que sean sobre una sola tabla y que no involucren funciones de grupo, columnas artificiales 0u operadores. Accesos a tablas y vistas


__________________________________________________________________________________________


El DBA y los dueños de tablas son los únicos que pueden otorgar privilegios a otros usuarios para que puedan visualizar, modificar o borrar, un objeto de la base de datos (tablas, vistas, etc.). Estos privilegios se otorgan o quitan por medio del comando GRANT. Sintaxis: GRANT {privilegio_del_objeto [, privilegio_del_objeto] ... } | ALL [ PRIVILEGES]} ON [usuario.]objeto TO {usuario | PUBLIC } [, usuario]... [WITH GRANT OPTION] Los privilegios de los objetos son los siguientes: - Para las tablas: ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT o UPDATE. - Para las vistas: DELETE, INSERT, SELECT o UPDATE. - Para las secuencias: ALTER o SELECT. ALL PRIVILEGES: significa que el objeto tendrá todos los privilegios. ON: especifica el nombre de una tabla, vista o sinónimo, al cual se le van a otorgar los privilegios. TO: usuario o usuarios a los cuales se les están dando los privilegios. PUBLIC: son todos los usuarios que se encuentran en la base de datos y los que en un futuro se presenten. WITH GRANT OPTION: permite a un usuario, otorgar privilegios a otros usuarios, sobre los objetos a los cuales tiene acceso. Ejemplo 1: Otorgar todos los privilegios de la tabla empleado, a todos los usuarios de la base de datos. SQL > GRANT ALL 2 ON EMPLEADO 3 TO PUBLIC; Ejemplo 2: Otorgar el privilegio SELECT al usuario system, permitiéndole a este, otorgar dicho privilegio a otros usuarios. SQL > GRANT SELECT 2 ON EMPLEADO 3 TO SYSTEM 4 WITH GRANT OPTION;


__________________________________________________________________________________________


Sinónimos: Los sinónimos son nombres por medio de los cuales, se puede hacer referencia a una tabla o vista. Estos se utilizan por razones de seguridad y conveniencia, teniendo las siguientes ventajas: - Referenciar una tabla o vista, sin tener que especificar el dueño de la misma. - Referenciar una tabla o vista, sin tener que especificar la base de datos en donde la tabla o la vista existe. - Proveer otro nombre para la tabla. Sintaxis para la creación de un sinónimo: CREATE [PUBLIC] SYNONYM [usuario.]sinónimo FOR [usuario.]tabla_o_vista [@enlace_con_la_base_de_datos] PUBLIC: indica que el sinónimo, podrá ser utilizado por todos los usuarios. Por defecto sólo podrá ser utilizado por el usuario que creó el sinónimo. SYNONYM: nombre del sinónimo. ENLACE_CON_LA_BASE_DE_DATOS: Cadena de caracteres que se utiliza para enlazar con una base de datos remota. Ejemplo: SQL > CREATE SYNONYM TRABAJADOR 2 FOR PABLO.EMPLEADO; SQL > SELECT * 2 FROM TRABAJADOR;


__________________________________________________________________________________________


II.6.- INDICES Y GENERADORES DE SECUENCIAS El acceso a los datos contenidos en las tablas, puede ser de dos formas: - Búsqueda secuencial por toda la tabla. - Búsqueda selectiva, a través de un índice. Los índices son estructuras opcionales, los cuales están asociados a tablas, con la finalidad de aumentar la velocidad de las consultas y evitar la duplicidad de filas. Cada tabla puede tener un número ilimitado de índices. La regla para escribir el nombre de un índice, es similar al de una tabla, pero es recomendable colocar como prefijo, la letra "I" a todo nombre de índice. ¿Cuando crear un índice? Si Ud. anticipadamente sabe que se va a recuperar un poco menos del 15%, de las filas contenidas en una gran tabla, cuyas columnas van a ser frecuentemente referenciadas por una cláusula WHERE, entonces Ud. deberá de crear un índice. Características de las columnas a indexar. - Las columnas deben ser frecuentemente referenciadas por la cláusula WHERE. - Todos sus valores deben ser únicos (el mejor caso). - Ancho rango de valores. - Muchos valores nulos o relativamente pocos valores. Sintaxis: CREATE [UNIQUE] INDEX índice ON tabla (columna [ASC | DESC] [, columna [ASC | DESC]] ...)}... STORAGE (.... ... ... ); UNIQUE: indica que las columnas involucradas, deben contener valores únicos. índice: nombre del índice a crear tabla: nombre de una tabla existente, a partir de la cual se creará un índice. ASC / DESC: crea los índices en forma ascendente o descendente. columna: nombre de la columna por la cual se va a ordenar. Nota: el número máximo de columnas concatenadas, que se puede tener en un índice simple, es de 16.


__________________________________________________________________________________________


Ejemplo 1: SQL > CREATE INDEX I_EMPLEADO 2 ON EMPLEADO(NOMBRE); Ejemplo 2: SQL > CREATE UNIQUE INDEX I_EMPLEADO 2 ON EMPLEADO(NOMBRE); ¿Cómo remover un índice de la base de datos? Sintaxis: DROP INDEX nombre_del_indice Ejemplo: SQL > DROP INDEX I_EMPLEADO; Validación de índices: Los problemas de hardware, pueden en ocasiones corromper los índices de una tabla. Para verificar que los índices no estén corrompidos se hace uso de VALIDATE INDEX. Sintaxis: VALIDATE INDEX nombre_del_indice Ejemplo: SQL > VALIDATE INDEX I_EMPLEADO; Index validated. (Mensaje arrogado). Secuencias Las secuencias son estructuras opcionales, su finalidad es generar números enteros únicos, los cuales pueden ser utilizados por múltiples usuarios. Una utilidad que se le puede dar a una secuencia, es la de generar automáticamente los números de una clave primaria. La regla para escribir el nombre de una secuencia, es similar al de una tabla, pero es recomendable colocar como prefijo, la letra "S" a todo nombre de secuencia. Sintaxis: CREATE SEQUENCE secuencia [INCREMENT BY {1|n}] [START WITH n] [ {MAXVALUE n | NOMAXVALUE}] [ {MINVALUE n | NOMINVALUE}]...


__________________________________________________________________________________________


secuencia: nombre de la secuencia. INCREMENT BY: determina el intervalo de una secuencia numérica. START WITH: es el número con el cual se comienza la secuencia. Por defecto cuando es ascendente será igual a MINVALUE y cuando es descendente será igual a MAXVALUE. MAXVALUE / NOMAXVALUE: mínimo valor de la secuencia a generar. Por defecto es uno. MINVALUE / NOMINVALUE: máximo valor de la secuencia a generar. Por defecto es 10e27 - 1. Ejemplo: SQL > CREATE SEQUENCE S_DEPARTAMENTOS START WITH 200; SQL > INSERT INTO DEPARTAMENTOS(NUMDEPT, DEPARTAMENTO) 2 VALUES (S_DEPARTAMENTOS.NEXTVAL, 'INFORMÁTICA'); NEXTVAL: es un nombre de seudo-columna, el cual es usado para generar un número de secuencias especificas. CURVAL: es un nombre de seudo-columna, el cual es usado para obtener el valor de la secuencia en curso. ¿Cómo borrar una secuencia? Sintaxis: DROP SEQUENCE nombre_de_secuencia Ejemplo: SQL > DROP SEQUENCE S_DEPARTAMENTOS;


__________________________________________________________________________________________


II.7.- CONSULTAS AVANZADAS DE ORACLE JOIN: es la forma del comando SELECT para combinar filas de dos o más tablas. Tipos de JOIN - Simple - Equijoin (usando una condición de igualdad). - non-equijoin (usando varias condición de igualdad ú otras). - Externo - A si mismo. Join simple Retorna filas de dos o más tablas combinadas, a través de una condición de igualdad. Sintaxis: SELECT [tabla.]columna, [tabla.]columna... FROM tabla1, tabla2... WHERE [tabla1.]columna = [tabla2.]columna Ejemplo (caso equijoin): SQL > SELECT NOMBRE, SALARIO, EMPLEADO.NUMDEPT 2 FROM EMPLEADO, DEPARTAMENTO 3 WHERE EMPLEADO.NUMDEPT = DEPARTAMENTO.NUMDEPT; Ejemplo (caso non-equijoin): SQL > SELECT NOMBRE, SALARIO, EMPLEADO.NUMDEPT 2 FROM EMPLEADO, DEPARTAMENTO 3 WHERE EMPLEADO.NUMDEPT = DEPARTAMENTO.NUMDEPT 4 AND SALARIO BETWEEN 10000 AND 20000; Join externo Retorna el contenido de un join simple, más, el contenido de las filas de una tabla que no hallan podido ser combinado en la otra tabla. Sintaxis: SELECT [tabla.]columna, [tabla.]columna... FROM tabla1, tabla2... WHERE [tabla1.]columna CONDICION [tabla2.]columna(+)


__________________________________________________________________________________________


o puede ser SELECT [tabla.]columna, [tabla.]columna... FROM tabla1, tabla2... WHERE [tabla1.]columna(+) CONDICION [tabla2.]columna Ejemplo: Mostrar un join de las tablas EMPLEADO y DEPARTAMENTOS. El campo de combinación es NUMDEPT. Si existe alguna fila del campo departamento que no haga una combinación con la tabla EMPLEADO, deberá ser mostrada. SQL > SELECT NOMBRE, SALARIO, DEPARTAMENTOS.NUMDEPT 2 FROM EMPLEADO, DEPARTAMENTOS 3 WHERE EMPLEADO.NUMDEPT(+) = DEPARTAMENTOS.DEPTNO; Join a si mismo Realiza una combinación en una misma tabla. Ejemplo: Obtener una lista de empleados, en la cual se especifique el nombre de su jefe inmediato. SQL > SELECT TRABAJADOR.NOMBRE, JEFE.NOMBRE 2 FROM EMPLEADO2 TRABAJADOR, EMPLEADO2 JEFE 3 WHERE TRABAJADOR.CODIGO = JEFE.CODJEFE; Sub-query (sub-consulta). Las sub-consultas seleccionan filas de una tabla con una condición que depende de valores de otra tabla. Las sub-consultas deben aparecer en la cláusula WHERE de una de las siguientes sentencias: - SELECT - INSERT - UPDATE - DELETE


__________________________________________________________________________________________


Sintaxis: SELECT ... FROM ... WHERE ... (SELECT ... FROM ... WHERE ...); Sub-consultas de filas simples Las sub-consultas de filas simples, retornarán un valor para completar la condición de la cláusula WHERE. Ejemplo: SQL > SELECT * 2 FROM DEPARTAMENTO 3 WHERE NUMDEPT = (SELECT MAX(NUMDEPT) 4 FROM EMPLEADO 5 WHERE CARGO = 'SECRETARIA'); Sub-consultas de filas múltiples Las sub-consultas de filas múltiples, retornan más de un valor para completar la condición de la cláusula WHERE. Ejemplo: SQL > SELECT NOMBRE, CARGO 2 FROM EMPLEADO 3 WHERE NUMDEPT IN (SELECT NUMDEPT 4 FROM EMPLEADO 5 GROUP BY NUMDEPT HAVING COUNT(*) > 2 ) 6 AND SALARIO > (SELECT AVG(SALARIO) 7 FROM EMPLEADO);


__________________________________________________________________________________________


II.8.- Optimización de Instrucciones de manipulación de datos en SQL Para escribir instrucciones optimas, se debe tener conocimiento del funcionamiento del optimizador de SQL. El optimizador realiza un plan de ejecución, el cual considera que será eficiente basado en factores tales como: a.- Reglas de Construcción de la Instrucción b.- Utilización de Indices En la Versión 6 del Manejador de Base de Datos no se consideran los siguientes factores: - Cantidad de filas existentes en las tablas - Distribución física de los datos en la tabla - Distribución de los valores de las claves en los índices Reglas de construcción: - El orden de las tablas en la cláusula FROM Influye para la evaluación de la tabla base a usarse al construir JOINS. Se recomienda que de izquierda a derecha se coloquen las tablas de mayor volumen de datos accesados a la de menor volumen. Ejemplo: EMPLEADOS tiene 5000 filas, DEPARTAMENTO tiene 20 filas, FAMILIARES tiene 20000 filas FROM FAMILIARES, EMPLEADOS, DEPARTAMENTO Por cada registro de Departamento va a ir a Empleados, luego a familiares La Evaluación que realiza el manejador es de abajo hacia arriba y de derecha a izquierda, lo optimo es ir de menos filas enlazando búsquedas que retornen menos filas. .- Columnas mencionadas en la cláusula WHERE Reducen la cantidad de filas retornadas por instrucción del total de filas de la tabla, permitiendo ademas el uso de índices.


__________________________________________________________________________________________


Ejemplo: WHERE nombre = 'MIGUEL' and Salario > 10000 and Codigo_departamento = 10 Buscara todos los departamentos 10 cuyo salario sea mayor de 10000 y el nombre sea MIGUEL. .- Columnas mencionadas seleccionadas en la cláusula SELECT Reducen la cantidad de valores por fila retornados de la tabla, si las columnas mencionadas existen en los índices, la instrucción no necesitara ir a los datos. El uso del (*) como indicador de selección de todas las columnas es menos eficiente que indicar los nombres de las columnas y el orden en que fueron creadas. Ejemplo: SELECT numero_empleado, codigo_departamento más eficiente que SELECT nombre, numero_empleado, codigo_departamento, salario, apellido más eficiente que SELECT * .- Nombres de Tablas y Columnas Para obtener mejores tiempos de interpretación y validación sintáctica (PARSE), es preferible usar nombres cortos; esto no quiere decir nombre codificados, al contrario, indica usar abreviaciones o conjunción con palabras. Ejemplo: SELECT numero_del_empleado, codigo_del_departamento, primer_nombre, primer_apellido FROM empleados_compania Menos eficiente que SELECT nro_emp, cod_depto, nombre, apellido FROM empleados


__________________________________________________________________________________________


No quiere decir que SELECT nro_e, c_dep, nom, ape FROM C00101_emp - Uso de alias y preposiciones Al construir JOINS con muchas tablas y nombres de columnas o tablas iguales es necesario y recomendable el uso de alias o preposiciones. Esta regla permite utilizar menos caracteres en el texto de la instrucción, lo que involucra menos tiempo de interpretación (PARSE). Ejemplo: SELECT a.codigo_departamento, b.nombre_departamento, a.nombre empleado FROM empleados a, departamentos b WHERE a.codigo_departamento = b.codigo_departamento Más eficiente que SELECT empleados.codigo_departamento, departamentos.nombre_departamento, empleados.nombre empleado FROM empleados a, departamentos b WHERE empleados.codigo_departamento = departamentos.codigo_departamento Más eficiente que SELECT a.codigo_departamento, b.nombre_departamento, c.nombre jefe, a.nombre empleado FROM empleados a, empleados c, departamentos b WHERE a.nro_jefe = c.nro_empleado AND a.codigo_departamento = b.codigo_departamento .- El uso de paréntesis para clarificar los predicados Al construir columnas con funciones agrupadas o realizar muchas evaluaciones WHERE que involucren muchos operandos, los paréntesis facilitan al optimizador la interpretación de una instrucción. Ejemplo: WHERE numero_empleado = 10 or numero_empleado = 20 and codigo_departamento = 10 or salario > 10000 and codigo_departamento = 20 Seria mejor si WHERE numero_empleado = 10 or (numero_empleado = 20 and codigo_departamento = 10) or (salario > 10000 and codigo_departamento = 20)


__________________________________________________________________________________________


Construcción de las tablas e Indices .- Orden de las Columnas al construir las tablas Las columnas con mayor acceso deben ser las primeras, tales como Claves Primarias, Claves Alternas, Claves Foráneas; si existe alguna columna que acepte valores nulos debe ir de última. Las columnas que se modifican mucho deben tener precedencia a las de valores nulos. La Columna con formato LONG debe ser la última. Ejemplo: CREATE TABLE emp (nro_emp number(4) not null PRIMARY KEY, cod_depto number(2) not null REFERENCE depto, nombre char(30) not null, apellido char(30) not null, salario number(12,2) not null, direccion char(90) not null, nro_jefe number(4) REFERENCE emp, comision number(12,2), bonificacion number(12,2), fotografia long raw); .- Orden de las Columnas al construir índices Se construirán índices principalmente a la clave primaria y clave alterna para conservar la unicidad. Adicionalmente a las claves foráneas para mejorar los JOINS y aquellas columnas con mucho acceso a través de cláusulas WHERE. Al construir Indices compuestos se debe tomar en cuenta lo siguiente: - La prioridad de las columnas debe venir dado de las columnas con mayor cantidad de valores reales a la de menor. - Las columnas de mayor acceso deben preceder a las de menor acceso - Columnas con Valores Numéricos preceden a la de caracteres y estas a su vez a las de fechas. - Debe tomarse en cuenta el orden de las columnas en la tabla.


__________________________________________________________________________________________


Ejemplo: CREATE UNIQUE INDEX i_emp1 ON emp (nro_emp); CREATE INDEX i_emp2 ON emp (nombre, apellido); CREATE INDEX i_emp3 ON emp (cod_deptno, salario) CREATE INDEX i_emp4 ON emp (salario, comision) Más óptimos que CREATE INDEX i_emp1 ON emp (nro_emp); CREATE INDEX i_emp2 ON emp (apellido, nombre); CREATE INDEX i_emp3 ON emp (salario, cod_deptno) CREATE INDEX i_emp4 ON emp (comision, salario) .- Parámetros de Almacenamiento La forma en que se almacenaran los datos influirá en el rendimiento de las instrucciones, por lo tanto hay que prevenir y planificar un buen almacenamiento. Lo principal es conocer la cantidad de filas que va almacenar una tabla y en base a esto la cantidad de bytes a reservar para los datos, evitando crecimiento descontrolados, fraccionados y dispersos, tratando de mantener los datos en espacios continuos y con privilegios de acceso rápido (Discos más rápidos). Estimar crecimientos y si es posible reservar el espacio con anterioridad. Determinar los grados de volatibilidad y actualización de los datos, para así poder sacar mejor provecho de los bloques de datos. (PCTUSED y PCTFREE). Utilización de Indices El uso de índices es ineficiente, al punto de no crearlos para los siguientes casos - Tablas con menos de 5 bloques de datos - Columnas cuyo porcentaje de valores iguales sea mayor al 20% del total de filas de la tabla. - Columnas cuyo porcentaje de valores nulos sea muy alto - Columnas cuyos valores sean transformados durante el acceso de la instrucción. La columna tiene formato fecha, pero al realizar el acceso se transformo a caracter.


__________________________________________________________________________________________


Ejemplo: WHERE to_char(fecha_ingreso, 'MM') = '01' WHERE nro_emp + 100 = 101 WHERE nombre || apellido like 'GERARDO%ARAQUE%' Ineficiente al lado de WHERE fecha_ingreso between to_date('01', 'MM') and last_day(to_date('01','MM')) WHERE nro_emp = 101 -100 WHERE nombre like 'GERARDO%ARAQUE%' or apellido like 'GERARDO%ARAQUE%' or (nombre like 'GERARDO%' and apellido like '%ARAQUE%') - Columnas con muchas actualizaciones de los valores Las Actualizaciones serán lentas si existe un índice, pero si el volumen de acceso sobre este índice es superior y es bien aprovechado se deben pesar, que es más importante actualizaciones rápidas o lecturas rápidas. Ejemplo: Una empresa de servicios de personal, ubica grupos de trabajo en alrededor de 1000 empresas y tiene 10000 empleados. UPDATE emp set empresa_sirve = 10 where grupo_trabajo = 'O'; a la semana UPDATE emp set empresa_sirve = 41 where grupo_trabajo = 'O'; Comparado con accesos continuos SELECT nombre FROM emp WHERE empresa_sirce = 41; SELECT nombre FROM emp WHERE empresa_sirce = 11; - Que índices serán usados cuando existen varios índices Los índices serán usados según su especificación en la cláusula WHERE y al peso que tenga la evaluación que se este realizando.


__________________________________________________________________________________________


PESOS DE LAS EVALUACIONES Rango Evaluación de Acceso 1.- Acceso por ROWID 2.- Uso completo de una Indice único Compuesta mediante una igualdad a una expresión. 3.- Uso de una Indice único simple mediante una igualdad una expresión. 4.- Uso completo de una Clave de Enlace de un CLUSTER mediante una igualdad con otra Clave de Enlace de otra tabla del mismo CLUSTER. 5.- Uso completo de una Clave de Enlace de un CLUSTER mediante una igualdad una expresión. 6.- Uso completo de una índice no único compuesta mediante una igualdad a una expresión. 7.- Uso completo de una índice no único mediante una igualdad a una expresión 8.- Un índice compuesto usado mediante rango evaluativos de búsqueda limitada de las primeras columnas o la primer columna igualdad a una expresión y el resto o un índice simple usando (like 'caracteres%' , between ). 9.- Un índice compuesto usado mediante rango evaluativos de búsqueda ilimitada de las primeras columnas usando ( >= , > , < , <= , like '%caracteres%') Si existen durante la misma evaluación índices con el mismo rango y son índices simples, serán usados todos los del menor rango. .- Uso de índices en JOIN Para el uso de un índice en un JOIN el optimizador va a considerar el predicado de enlace de las tablas, evaluando de cada lado del join cuales índices tienen menor rango, si de ambos lados es el mismo rango tendrá precedencia la posición de la tabla en la cláusula FROM. Hay que tener en cuenta en el predicado de join solo se usaran índices de un lado del JOIN. Fuera del Predicado de JOIN los índices serán evaluados para la tabla que no haya usado indices. Lo principal en un JOIN de muchas tablas es determinar cual debe ser la ruta de acceso a los datos. Ejemplo: La tabla emp tiene tres índices único i_emp1 (nro_emp) i_emp2 (cod_depto) i_emp3(nombre, apellido) La tabla depto tiene un índice único i_dep1 (cod_depto) La tabla familiares tiene dos índices único i_fam1 (nro_emp, cedula) i_fam2 (cedula)

APUNTES DE BASES DE DATOS LMD (Lenguaje de Manipulación de Datos) SELECT SELECT [DISTINCT] <lista_columnas> | *

FROM <lista_tablas> [WHERE <predicado>]

EJ: Visualizar todos los vuelos que tengan como origen o destino Cáceres. SELECT *

FROM VUELOS WHERE ORIGEN='CACERES' OR DESTINO='CACERES'

EJ: Visualizar todos los vuelos que tengan como origen Madrid o Londres y como destino Londres o Madrid. SELECT *

FROM VUELOS WHERE (ORIGEN='MADRID'

AND DESTINO='LONDRES') OR (ORIGEN='LONDRES'

AND DESTINO='MADRID') Claúsula IN Expresa la pertenencia del valor de una columna a un determinado conjunto de valores. EJ: Seleccionar aquellos vuelos que tengan como origen Madrid, Barcelona o Sevilla. SELECT *

FROM VUELOS WHERE ORIGEN IN ('MADRID','BARCELONA','SEVILLA')

ó también SELECT *

FROM VUELOS WHERE ORIGEN='MADRID' OR ORIGEN='BARCELONA' OR ORIGEN='SEVILLA'

EJ: Visualizar todos los vuelos existentes excepto aquellos que llegan a Londres o a Copenhague. SELECT *

FROM VUELOS WHERE DESTINO NOT IN ('LONDRES','COPENHAGUE')

Claúsula BETWEEN Sirve para establecer o expresar un rango de valores. Obedece a la siguiente sintaxis: <nombre_columna> BETWEEN valor1 AND valor2 El rango será [valor1, valor2], extremos incluidos. EJ: Recuperar todos los vuelos que salgan entre las 6 y las 12 de la mañana. SELECT *

FROM VUELOS WHERE HORA_SALIDA BETWEEN '06.00.00'

AND '12.00.00'


FROM VUELOS WHERE HORA_SALIDA >= '06.00.00' AND HORA_SALIDA <= '12.00.00'

EJ: En la columna NUM_VUELO representaré los vuelos con 6 caracteres. Los dos primeros caracteres indicarán la compañía a la que pertenece cada vuelo (IB®Iberia, BA®British Airways), los cuatro caracteres siguientes corresponderán al número de vuelo. Bajo estas condiciones recupérense todos los vuelos que no pertenecen a IBERIA. SELECT *

FROM VUELOS WHERE NUM_VUELO NOT BETWEEN 'IB0000'

AND 'IB9999' Claúsula LIKE Sirve para especificar, con la ayuda de metasímbolos, cadenas de caracteres que comparten ciertos caracteres en común. Los metasímbolos que serán utilizados son: % Equivale a una cadena de caracteres de longitud comprendida entre 0 y n.

'AB%' AB, ABCDE, AB 497 _ Equivale a un único carácter

'A_B' A B, A4B, AJB EJ: Recuperar todos los vuelos pertenecientes a la compañía IBERIA. SELECT *

FROM VUELOS WHERE NUM_VUELOS LIKE 'IB%'


FROM VUELOS WHERE NUM_VUELOS LIKE 'IB_ _ _ _'

Expresiones aritméticas +, -, *, / Pueden ser utilizadas tanto después de SELECT como después de WHERE. En el primer caso trabajarían sobre columnas y en el segundo sobre filas. EJ: Visualizar la longitud y la envergadura de todos los aviones, expresando las magnitudes en pies (en la base de datos está almacenado en metros, para pasar 1 metro a pies se ha de multiplicar por 3.28), y la velocidad de crucero en mph(está en Km/h, habrá que dividir por 1.6). SELECT LONGITUD*3.28, ENVERGADURA*3.28, VELO_CRUC/1.6

FROM AVIONES

┌──┐ ┌──┐ ┌──┐ Etiquetas ® └──┘ └──┘ └──┘

-------- -------- ------- -------- -------- -------

-------- ------- ------ En DB/2 de IBM las etiquetas toman los nombres de las columnas (col1, col2, col3) En SQL-SERVER las etiquetas quedarían así (LONGITUD*3.28, ENVERGADURA*3.28, VELO_CRUC/1.6) EJ: Relación entre la longitud y la envergadura de todos los aviones.

SELECT LONGITUD/ENVERGADURA

FROM AVIONES EJ: Seleccionar aquellos aviones cuya longitud supere a su envergadura en más de un 10%. SELECT *

FROM AVIONES WHERE LONGITUD > ENVERGADURA*1.10

Funciones de columna Son funciones que operan con todas las filas que cumplen la condición expuesta en la claúsula WHERE. Su resultado es un único valor. Sintaxis: 1º) <f_columna> ([DISTINCT] <nombre_columna>) 2º) <f_columna> (<expresión>), donde <expresión> es una expresión aritmética en la cual debe participar, al menos, una columna. 3º) COUNT(*) Funciones <f_columna>: MIN: Calcula el valor mínimo de una columna. MAX: Calcula el valor máximo de una columna. AVG: Calcula la media aritmética de una columna. SUM: Calcula la suma de todos los campos de una columna. COUNT: Cuenta el nº de filas de una columna.

A

B

3

5

2

8

3

7

4

3

COUNT(A)=COUNT(B) COUNT(A)=4, COUNT(B)=4 El COUNT de dos columnas de una misma tabla es igual. COUNT(*) sirve para obtener el nº de filas. EJ: Seleccionar los valores mínimo y máximo de la columna que almacena las velocidades de crucero. SELECT MIN(VELO_CRUC), MAX(VELO_CRUC)

FROM AVIONES EJ: Averiguar a que hora parte el primer vuelo hacia Madrid. SELECT MIN (HORA_SALIDA)

FROM VUELOS WHERE DESTINO='MADRID'

Regla que cumplen las funciones de columna La función de columna sólo podrá especificarse detrás de la particula SELECT o en la claúsula HAVING, pero nunca dentro de la claúsula WHERE. EJ: Se desea saber cuál es el vuelo que tiene la mínima hora de salida. SELECT *

FROM VUELOS WHERE HORA_SALIDA=(SELECT MIN(HORA_SALIDA) FROM VUELOS)

Claúsula GROUP BY-HAVING Sirve para dividir una tabla en grupos de filas que comparten características comunes. La sintaxis es: SELECT <lista_columnas>, <funciones_de_columna>

FROM <lista_tablas> [WHERE <predicado>] [GROUP BY <lista_columnas>] [HAVING <predicado>]

EJ: Efectúese una SELECT que visualice el mínimo valor de hora de salida para cada uno de los diferentes destinos. SELECT DISTINCT DESTINO

FROM VUELOS SELECT MIN(HORA_SALIDA)

FROM VUELOS WHERE DESTINO LIKE '%'

A continuación se muestra un ejemplo de lo que no se debe hacer: SELECT MIN(HORA_SALIDA)

FROM VUELOS WHERE DESTINO IN (SELECT DISTINCT DESTINO

FROM VUELOS) Sentencia GROUP BY: SELECT DESTINO, MIN(HORA_SALIDA)

FROM VUELOS GROUP BY DESTINO

Tabla VUELOS ® Tabla auxiliar ® Tabla x 'MADRID' WHERE GROUP BY DESTINO Tabla y 'BARCELONA' Tabla z 'SEVILLA' <f_columna> GROUP BY crea una serie de subtablas compuestas por filas con el mismo valor para la columna de agrupamiento (en este ejemplo la columna DESTINO). Se aplicarán a continuación funciones de columna sobre cada subtabla de forma independiente. MADRID, x BARCELONA, y SEVILLA, z No se puede poner en GROUP BY un campo que no se haya incluido en la sentencia SELECT. EJ: Obtener el origen del vuelo para cada uno de los vuelos que tienen la mínima hora de salida para cada uno de los destinos. EJ: Obtener el número de vuelos que existen para cada uno de los orígenes. SELECT ORIGEN, COUNT(*)

FROM VUELOS GROUP BY ORIGEN

Claúsula HAVING Permite elegir aquellos grupos que se quieren visualizar. EJ: Visualizar los grupos que tienen para cada uno de los orígenes la mínima hora de salida siendo anterior a las 12 horas. SELECT ORIGEN, MIN(HORA_SALIDA)

FROM VUELOS GROUP BY ORIGEN HAVING MIN(HORA_SALIDA) < '12.00'

HAVING no interferirá en la agrupación por filas de GROUP BY. EJ: Se desea seleccionar la hora de salida más temprana para cada origen y destino. SELECT ORIGEN, DESTINO, MIN(HORA_SALIDA)

FROM VUELOS GROUP BY ORIGEN, DESTINO

EJ: Visualizar los orígenes que tengan más de dos vuelos. SELECT ORIGEN

FROM VUELOS GROUP BY ORIGEN HAVING COUNT(*) > 2

EJ: Visualizar los vuelos de IBERIA que tengan más de 150 plazas libres. SELECT NUM_VUELO, SUM(PLAZAS_LIBRES)

FROM RESERVAS GROUP BY NUM_VUELO HAVING NUM_VUELO LIKE 'IB%' AND SUM(PLAZAS_LIBRES)>150

ó también SELECT NUM_VUELO, SUM(PLAZAS_LIBRES)

FROM RESREVAS WHERE NUM_VUELO LIKE 'IB%' GROUP BY NUM_VUELO HAVING NUM_VUELO 'IB%' AND SUM(PLAZAS_LIBRES)>150

TRATAMIENTO DE NULOS

Operaciones aritméticas Cualquier operación aritmética sobre un campo nulo nos devolverá como resultado un valor nulo. Tomemos como ejemplo la siguiente tabla: NULOS

COL_A

COL_B

15

10

35

35

140

NULL

NULL

100

NULL

NULL

7

110

33

60

NULL

NULL

NULL

NULL

SELECT COL_A+COL_B

FROM NULOS COL_A+COL_B 25 70 NULL NULL NULL 117 93 NULL NULL

Funciones de columna Ignoran los campos NULL, exceptuando la función COUNT. SELECT AVG(COL_A) SELECT SUM(COL_A)/COUNT(*) AVG(COL_A)=46 SUM(COL_A)/COUNT(*)=25.5

Comparaciones Dos valores nulos no son iguales ni son distintos, sino indeterminados. SELECT *

FROM NULOS WHERE COL_A=COL_B

COL_A COL_B 35 35 SELECT *

FROM NULOS WHERE COL_A<>COL_B

COL_A COL_B 15 10 140 NULL NULL 100 7 110 33 60 SELECT *

FROM NULOS WHERE COL_A IS NULL

Esta orden visualiza todas las filas en las que el campo perteneciente a la columna COL_A es nulo.

Ordenación Dependiendo del sistema gestor en uso los valores nulos serán los de mayor o los de menor peso. DB/2 de IBM: NULL ® Mayor peso. en ordenación ascendente serán los últimos. SELECT COL_A

FROM NULOS ORDER BY COL_A

COL_A 7 15 33 35 140 NULL NULL NULL NULL SQL-SERVER: NULL ® Menor peso. En ordenación ascendente serán los primeros. SELECT COL_A

FROM NULOS ORDER BY COL_A

COL_A NULL NULL

NULL NULL 7 15 33 33 140

DISTINCT No elimina los valores nulos repetidos. SELECT DISTINCT COL_A

FROM NULOS COL_A 15 35 140 NULL NULL 7 33 NULL NULL

Indices únicos Sobre una columna de índice único sólo está permitida la existencia de un valor nulo. CREATE UNIQUE INDEX IXNULOS

ON NULOS (COL_A)

Devolvería un error, ya que existe más de un campo con NULL. Para este caso los nulos se interpretan como valores iguales.

GROUP BY Todos los nulos quedarán agrupados en el mismo grupo. SELECT COL_A, COUNT(*)

FROM NULOS GROUP BY COL_A

COL_A COUNT(*) 15 1 35 1 140 1 NULL 4 7 1 33 1 Todos los valores NULL se agrupan y COUNT devuelve el número de filas que tenían NULL en COL_A.

SUBSELECT Responde a la siguiente sintaxis: SELECT <lista_columnas>

FROM <lista_tablas> WHERE <nombre_columna> <CONCATENADOR> (SELECT <nombre_columna>

FROM <lista_tablas> WHERE <Predicado>)

<CONCATENADOR> Puede ser un operador de comparación o la claúsula IN Operadores de comparación: >,<,>=,<=,=,<> Restricciones: ha de exigirse que el resultado de la Subselect sea un único valor al usar como concatenador un operador de comparación. Si usamos IN puede devolver más de un valor. Cada Select se ejecuta una única vez, desde la más interna, hasta la más externa. EJ: Se desea recuperar las plazas libres que hay en cada vuelo MADRID-LONDRES del día 20/02/92. {Las plazas libres es un campo de la tabla de reservas. En la tabla de vuelos tenemos el origen y el destino de cada vuelo.} SELECT *

FROM RESERVAS WHERE FECHA_SALIDA='20.02.1992' AND NUM_VUELO IN(SELECT NUM_VUELO

FROM VUELOS WHERE ORIGEN='MADRID' AND DESTINO='LONDRES')

ANY, ALL Se usan para poder utilizar operadores de comparación con subselects que nos devuelvan más de un valor único como resultado. SELECT <lista_columna>

FROM <lista_tablas> WHERE <nombre_columna> <CONCATENADOR> {ANY/ALL} (<Subselect>)

Una expresión ANY es cierta si lo es para algún valor de los que devuelve la Subselect. Una expresión ALL es cierta si lo es para todos los valores que devuelve la Subselect. 3>ANY(2,5,7) ® Cierto 3=ANY(2,5,7) ® Falso 3>ALL(2,5,7) ® Falso 3<ALL(9,10,11) ® Cierto EJ: Se quiere recuperar los aviones cuya longitud sea mayor que la envergadura de todos ellos. SELECT *

FROM AVIONES WHERE LONGITUD > ALL (SELECT ENVERGADURA

FROM AVIONES) ó también SELECT *

FROM AVIONES WHERE LONGITUD > (SELECT MAX(ENVERGADURA)

FROM AVIONES)

=ANY e IN tienen la misma función. 3=ANY(2,3,5) y 3 IN (2,3,5) devuelven ambos Cierto. Subselects correlacionadas Son un tipo especial de subselect. La sintaxis es similar: SELECT <lista_columnas>

FROM <nombre_tabla_externa> WHERE <nombre_columna> <CONCATENADOR> (SELECT <nombre_columna>

FROM <nombre_tabla_interna> WHERE <Predicado>)

En <Predicado> habrá una sentencia del tipo <nombre_columna_tabla_interna> <operador> <nombre_columna_tabla_externa> Las formas de ejecutar una subselect ordinaria y una correlacionadas son diferentes. Las subselects correlacionadas obedecen al siguiente algoritmo: ALGORITMO Subselect_Correlacionada 1 Seleccionar fila de tabla externa 2 Ejecutar SELECT interno 3 Evaluar la condición del WHERE externo - Cierto: la fila seleccionada en 1 será una fila de salida 4 Si existe alguna fila más en la tabla externa ir al paso 1 EJ: Se desea recuperar las reservas cuyo número de plazas libres sea mayor que la media para ese mismo vuelo. SELECT *

FROM RESERVAS, A WHERE PLAZAS_LIBRES > (SELECT AVG(PLAZAS_LIBRES)

FROM RESERVAS WHERE NUM_VUELO=A.NUM_VUELO)

RESERVAS NUM_VUELO FECHA_SALIDA PLAZAS_LIBRES IB740 20.02.92 5 IB740 25.02.92 15 IB740 03.03.92 10 AVG(PLAZAS_LIBRES)=10 Alias Es un sobrenombre que se le da a una tabla y que debe ser único para toda la consulta. Se escribe dejando un blanco detrás del nombre de la tabla a la cual se va a calificar. EJ: Se quiere recuperar los aviones que tienen menos de 1 hora y cuarto de recorrido como término medio. VUELOS NUM_VUELO ORIGEN DESTINO DISTANCIA B747 MADRID LONDRES 10000 B747 MADRID PARIS 4000

AVIONES NUM_VUELO VELO_CRUC ............ ............ v=e/t, t>e/v, 1.25>e/v, v*1.25 > AVG(DISTANCIA)

SELECT AVIONES SELECT VUELOS SELECT *

FROM AVIONES WHERE 1.25*VELO_CRUC > (SELECT AVG(DISTANCIA)

FROM VUELOS WHERE NUM_VUELO=AVIONES.NUM_VUELO)

EXISTS-NOT EXISTS Se define para comprobar la existencia o ausencia del valor devuelto por una Subselect. Una expresión con EXIST devuelve Cierto si la Subselect nos devuelve al menos un valor. WHERE EXISTS (<Subselect>) ¬ Cierto EJ: Seleccionar toda la información de vuelos para aquellos que tengan origen Madrid y en los que queden plazas libres. SELECT *

FROM VUELOS WHERE ORIGEN='MADRID' AND EXISTS (SELECT *

FROM RESERVAS WHERE PLAZAS_LIBRES > 0 AND NUM_VUELO=VUELOS.NUM_VUELO)

EJ: Obtener los tipos de avión y capacidades para aquellos en los que queden menos de 30 plazas libres (JOIN). ORDER BY Se define para ordenar la salida de una consulta por los campos que se especifiquen a continuación. Sintaxis:

SELECT FROM WHERE GROUP BY HAVING ORDER BY

ORDER BY <especificación de columna><orden>{,<especificación de columna><orden>} <especificación de columna>=<nombre de columna>|<posición de columna> <orden>=ASC|DESC Ej: Obtener el número de plazas libres que quedan para cada vuelo y ordenar el resultado de más a menos plazas libres. Para igual número de plazas ordénese por número de vuelo. SELECT NUM_VUELO, SUM(PLAZAS-LIBRES)

FROM RESERVAS GROUP BY NUM_VUELO ORDER BY 2 DESC, NUM_VUELO

UNION-UNION ALL Se define para recuperar, usando una única consulta, información que se obtiene a partir de más d una consulta. Sintaxis:

<SELECT> UNION [ALL] <SELECT>

{UNION[ALL] <SELECT>}

Características: Cada SELECT devuelve un conjunto de filas. La unión será la tabla resultado. Condiciones de cada estructura SELECT: - Todas deben ser iguales o compatibles una a una. Esto supone que por cada columna tengamos un único tipo de dato. - Pueden ser completas (WHERE, GROUP BY, ...), exceptuando la claúsula ORDER BY, que se ubicará al final de la última SELECT. UNION sin ALL proporciona un resultado sin filas duplicadas. Ej: Sacar una lista de todas aquellas ciudades para las que haya vuelo, ordenadas alfabéticamente. SELECT ORIGEN

FROM VUELOS UNION

SELECT DESTINO FROM VUELOS ORDER BY 1

Catálogo del sistema o diccionario de datos: Es el alma de un sistema gestor. Se define como un conjunto de tablas que forman una base de datos, y son definidas y mantenidas automáticamente por el sistema gestor. Sirven para almacenar información sobre los objetos definidos por los usuarios. SELECT *

FROM VUELOS 1- El sistema busca en el catálogo si existe la tabla VUELOS. 2- Verifica si el usuario tiene acceso a esa información. 3- Se pregunta cuáles y cuántas columnas tiene la tabla VUELOS. DB2 IBM: SYSTABLES: una fila por cada tabla definida en la instalación. SYSCOLUMNS: una fila por cada columna definida. SYSINDEXES: una fila por cada índice definido. SYSVIEW: una fila por cada vista. SYSTABAUTH: una fila por cada autorización definida. Todas las tablas son directamente consultadas por usuarios autorizados. ADM (Administrador): es la persona que concede autorizaciones a los usuarios. Un usuario autorizado puede efectuar operaciones del tipo:

SELECT *

FROM SYSTABLES WHERE NAME='RESERVAS'

NAME DBNAME CARD RESERVAS AEROPUERTO .......... DBNAME: Nombre de la BdD a la que pertenece la tabla. CARD: Nº de filas de la tabla. OWNER: Usuario creador de la tabla. SELECT *

FROM SYSCOLUMNS WHERE DBNAME='RESERVAS'

Nos da la información sobre todas las columnas que pertenecen a la tabla reservas. NAME TBNAME COL_NO COL_TYPE LENGTH NULLS NUM_VUELO RESERVAS 1 CHAR 6 N FECHA_SALIDA RESERVAS 2 DATE 8 N TBNAME: Nombre de la tabla. COL_NO: Posición de la columna en la tabla. COL_TYPE: Tipo de dato LENGTH: Longitud del dato de la columna. NULLS: Indica si se permite valor nulo. Ej: Obténgase la última hora de salida para cada destino de los vuelos realizados por aviones capaces de almacenar más combustible que un tercio de la media que pueden almacenar los demás aviones. SELECT DESTINO, MAX(HORA_SALIDA)

FROM VUELOS WHERE TIPO_AVION IN (SELECT TIPO

FROM A WHERE COMBUSTIBLE>1/3*(SELECT AVG(COMBUSTIBLE)

FROM AVIONES WHERE TIPO<>A.TIPO)

Ej: Crear una vista sobre la tabla vuelos con las columnas ORIGEN y DESTINO para aquellos vuelos que no sean de IBERIA. Visualizar el contenido de la lista para los vuelos que no partan de Madrid. Borrar la vista. CREATE VIEW V_VUELOS (V_ORIGEN, V_DESTINO) AS SELECT ORIGEN, DESTINO

FROM VUELOS WHERE NUM_VUELO NOT LIKE 'IB%'

SELECT *

FROM V_VUELOS WHERE V_ORIGEN<>'MADRID'

DROP VIEW V_VUELOS Ej: Visualice los tipos de avión , el doble de su longitud y la mitad de su envergadura, para aquellos aviones con envergadura mayor que la media y que realizan vuelos desde o hacia Barcelona, ordenándolos de mayor a menor longitud. SELECT TIPO_AVION, 2*LONGITUD, .5*ENVERGADURA

FROM AVIONES, VUELOS WHERE ENVERGADURA>(SELECT AVG(ENVERGADURA)

FROM AVIONES) AND

AVIONES.TIPO_AVION=VUELOS.TIPO_AVION AND

(ORIGEN='BARCELONA' OR DESTINO='BARCELONA) ORDER BY 2 DESC

Ej: Visualice las tres primeras letras de los orígenes y destinos de los vuelos realizados por aviones con longitud mayor con longitud mayor que la media y envergadura menor que 2/3 de la máxima envergadura, ordenados alfabéticamente por destino. SUBSTRING (SQL) (SUBSTRNG), (DB2) SUBSTRING (string, posición, nºcaracteres)

¯ nom_col / cadena con comillas (")

SELECT SUBSTRING (ORIGEN, 1, 3), SUBSTRING (DESTINO, 1, 3)

FROM VUELOS WHERE TIPO_AVION IN (SELECT TIPO

FROM AVIONES WHERE LONGITUD > (SELECT AVG(LONGITUD)

FROM AVIONES) AND ENVERGADURA*3/2 < (SELECT MAX(ENVERGADURA)

FROM AVIONES) ORDER BY 2

Ej: Visualice el total de plazas libres por número de vuelo para aquellos realizados desde Madrid a Barcelona o Sevilla y que recorran una distancia mayor que la media de todos los vuelos que salen de Madrid, ordenándolos de menor a mayor. SELECT SUM(PLAZAS_LIBRES), NUM_VUELO

FROM RESERVAS, VUELOS WHERE RESERVAS, NUM_VUELO=VUELOS.NUM_VUELO AND ORIGEN='MADRID' AND DESTINO IN ('BARCELONA', 'SEVILLA') AND DISTANCIA > (SELECT AVG(DISTANCIA)

FROM VUELOS WHERE ORIGEN='MADRID')

ORDER BY 1 Ej: Obtener para cada número de vuelo el total de plazas libres de los vuelos que recorran distancias menores que 2/3 de la media de las distancias recorridas por vuelos de otras compañías. SELECT NUM_VUELOS, SUM(PLAZAS_LIBRES)

FROM RESERVAS, VUELOS ¬ V WHERE RESERVAS.NUM_VUELO=VUELOS.NUM_VUELOS AND DISTANCIA*3/2 < (SELECT AVG(DISTANCIA)

FROM VUELOS WHERE SUBSTRING (NUM_VUELO, 1, 2) <>

SUBSTRING (VUELOS.NUM_VUELO, 1, 2)

TEORIA DE LA NORMALIZACION Introducción: Nos basaremos en la siguiente tabla:

AUTORES-LIBROS

NOMBRE

NACION

COD_LIB

TITULO

EDITOR

Date

USA

999

IBD

AW

Ad.Mig.

ESP

888

CyD

RM

Ma.Piat.

ITA

888

CyD

RM

Date

USA

777

BdD

AW

Bibliografía: DIseño y Gestión de Bases de Datos. Angle Lucas. Se plantean una serie de problemas: Redundancia: cuando un autor tiene varios libros, se repite la nacionalidad. Anomalías de modificación: Si Ad.Mig. y Ma.Piat. desean cambiar de editor, se modifica en los 2 lugares. A priori no podemos saber cuántos autores tiene un libro. Los errores son frecuentes al olvidar la modificación de un autor. Se pretende modificar en un sólo sitio. Anomalías de inserción: Se desea dar de alta un autor sin libros, en un principio. NOMBRE y COD_LIB son campos clave, una clave no puede tomar valores nulos. Teoría de la normalización: La teoría de la normalización ofrece una serie de reglas para efectuar una modelización óptima. La tabla anterior debería dividirse en 3 tablas: AUTORES (NOMBRE, NACION) LIBROS (COD_LIB, TITULO, EDITOR) ESCRIBE (NOMBRE, COD_LIB) En los años 70 Codd creó las bases de la teoría de la normalización. A cada regla de la teoría la denominó forma normal. Codd creó las formas normales 1ª, 2ª y 3ª. La 3ª forma normal originó problemas. Boyce ayudo a solventarlos con la f.n. de Boyce-Codd (FNBC). A finales de los 70 Fagin creó las formas normales 4ª y 5ª. Las formas normales se basan en el concepto de dependencia, que comprende las restricciones definidas sobre los atributos de una relación. Existen diferentes tipos de dependencia: - Dependencias funcionales (Formas normales 1ª, 2ª y 3ª y FNBC) - Dependencias multivaluadas (4ª forma normal) - Dependencia de JOIN (5ª forma normal) Formas normales 1ª forma normal: es una restricción inherente del modelo relacional. Se decie que una tabla está en 1ª forma normal si no existen en ella grupos repetitivos. Una tabla no puede tener en un campo más de un valor. TITULO AUTOR CyD Ad.Mig.

Ma.Piat.

Hay un grupo repetitivo. De este modo la tabla no es plana y no está en 1ª forma normal. Para convertirla a 1ª forma normal: TITULO AUTOR CyD Ad.Mig. CyD Ma.Piat. 2ª forma normal: partimos de la idea de dependencia funcional: Un atributo o conjunto de atributos B depende funcionalmente de A sí y sólo si a cada valor de A le corresponde un único valor de B: A ® B <=> a cada valor de A le corresponde un único valor de B A B x1 y1 x2 y2 x3 y3 Ej: DNI depende funcionalmente de NOMBRE y NOMBRE de DNI DNI ® NOMBRE NOMBRE ® DNI NOMBRE ¬® DNI Ej: DIRECCION depende funcionalmente de DNI, pero DNI no depende funcionalmente de DIRECCION DNI ® DIRECCION DIRECCION ─/® DNI Ej: TITULO, LIBRO no dependen funcionalmente de DNI, AUTOR, porque un autor puede escribir varios libros DNI, AUTOR ─/® TITULO, LIBRO Ej: Se tiene una base de datos de pluriempleados: Atributos: DNI, EMPRESA, SUELDO DNI ─/® EMPRESA DNI ─/® SUELDO Se puede concatenar atributos, obteniendo: DNI, EMPRESA ® SUELDO Sueldo es el atributo implicado que depende de DNI y EMPRESA juntos, que son atributos implicantes. También: DNI ® NOMBRE, DIRECCION Las 3 primeras formas normales más la forma normal de Boyce-Codd se basan en dependencias funcionales obedenciendo al siguiente teorema: Dada una relación R con un conjunto de atributos A que cumple R(A), x®y, es posible una descomposición en dos tablas de la siguiente manera: R(A), x®y R(x,y) es una relación compuesta por los atributos que forman la dependencia funcional R(A-y) es una relación compuesta por los atributos de R excluyendo el atributo implicado R(A)=R(x, y) R(A-y) el JOIN de ambas forma la relación original.

Las relaciones a partir de ahora se definirán como un conjunto de atributos con dependencias funcionales R(A, DF). Para normalizar la tabla habrá que conocer todas las dependencias funcionales, pero en la relación que nos den sólo tendremos algunas, a partir de las cuales podremos hallar el resto. Aplicaremos las propiedades de las dependencias funcionales para obtener todo el conjunto de posibles dependencias funcionales que puedan existir en la relación. Al conjunto inicial de depndencias funcionales lo llamaremos F, conjunto a partir del cual obtendremos el resto de depndencias funcionales. A cada nueva dependencia funcional obtenida a partir de F la llamaré f. Al nuevo conjunto que contenga todas las dependencias funcionales que obtenga el llamaré F+. Una vez hallado F+ podré aplicar las formas normales de la teoría de la normalización. Dependencia funcional derivada Dado un conjunto F de dependencias funcionales se dice que f deriva de F (F├ f) si f se obtiene a partir de F. Cierre de un conjunto de dependencias funcionales Se define Cierre (F+) como el conjunto de todas las dependencias funcionales implicadas por F o halladas a partir de F.

Propiedades de las dependencias funcionales a) Axiomas b) Propiedades propiamente dichas a) Axiomas

a.1) Axioma reflexivo

Si Y está incluido en X entonces X ® Y (Si Y c X => X ® Y)

Ej: CODPROV c CODPOSTAL CODPOSTAL ® CODPROV A un código postal le corresponde un único código de provincia.

a.2) Aumentatividad

Si X ® Y y Z c W => XW ® YZ

Se demuestra del siguiente modo: Z c W equivale a W ® Z. Si tenemos X ® Y y W ® Z

podemos afirmar que XW ® YZ a.3) Transitividad

Si X ® Y y Y ® Z => X ® Z

b) Propiedades propiamente dichas

b.1) Unión

X ® Y y X ® Z => X ® YZ

Demostración:

Si X ® Y (aumentatividad con X) => X ® XY Si X ® Z (aumentatividad con Y) => XY ® XZ Si X ® XY y XY ® YZ (transitividad) => X ® YZ

b.2) Pseudotransitividad

X ® Y y WY ® Z => WX ® Z

Demostración:

Si X ® Y (aumentatividad con W) => WX ® WY Si WX ® WY y WY ® Z (transitividad) => WX ® Z

b.3) Descomposición X ® Y y Z c Y => X ® Z

Demostración:

Si Z c Y (axioma reflexivo) => Y ® Z Si X ® Y y Y ® Z (transitividad) => X ® Z

Dependencia funcional total El conjunto de atributos Y tiene dependencia funcional total con X si Y tiene dependencia funcional con X (X ® Y) y además no existe ningún subconjunto Z de X (Z c X) con el cual Y tenga dependencia funcional (Z ─/® Y).

Diagramas de dependencias funcionales Son una herramienta que sirve para tener una visión general de los datos y de las dependencias funcionales entre ellos. S e representa en forma de grafo con los implicantes de las dependencias funcionales en un rectángulo, de los que salen flechas hacia los implicados. Ej: Dado: ABC ® MNS

M ® N BC ® OPR O ® P C ® Q , obtener el diagrama de dependencias funcionales.

A C AB ® C B B ® C es una dependencia funcional total. Ej: Hallar si las siguientes dependencias funcionales son totales: a) DNI, EMPRESA ® SUELDO b) DNI, EMPRESA ® NOMBRE

SUPERCLAVE Y CLAVE Superclave (SK): es el atributo o conjunto de atributos tales que en una relación R(A, DF) se cumple que SK ® A. SK es el implicante capaz de implicar a la tabla completa. Ej: En una tabla compuesta por X, Y, Z y W, si W es superclave se cumple que:

W ® X W ® Y W ® Z

Clave (K): es el atributo o conjunto de atributos tales que en una relación R(A, DF) es superclave y además no existe ningún subconjunto K' c K tal que K' ® A K, R(A, DF), K ═ SK /\ no existe K' c K / K' ® A Debe tener una dependencia funcional total con los atributos de la tabla. Si tenemos: WZ ® A WZ ® B WZ ® C pero Z ─/® A Z ─/® B Z ─/® C la clave es la mínima superclave, no descomponible en claves menores. 2ª forma normal: se dice que una relación está en 2ª forma normal si cumple las siguientes condiciones: 1) Está en 1ª forma normal 2) Cada atributo no principal o secundario (no forma parte de la clave), tiene una dependencia funcional total con la clave. Ej: AB ® C B ® D A C B D D depende funcionalmente de B sólo y debería depender de AB para estar en 2ª forma normal. Para convertirlo en 2ª forma normal se descompone en 2 tablas: Tabla 1: clave con dependencias totales Tabla 2: parte de la clave implicante con dependencias parciales A

C B D B Esto cumple el teorema 1º enunciado en el capítulo (Descomposición por JOIN) Ej: Pasar a 2ª forma normal la siguiente tabla: DNI NOMBRE EMPLEADO SUELDO DNI

SUELDO DNI NOMBRE EMPLEADO Dependencia funcional transitiva: se cumple si: A ® B B ─/® A => A ® C B ® C Gráficamente:

3ª forma normal: se dice que una tabla está en 3ª forma normal si está en 2ª forma normal y además cumple que ningún atributo no principal depende transitivamente de la clave. Ej: Pasar a 3ª forma normal: A ® B B ® A C ® D A ® C B ® C C ® E A ® D B ® D A ® E B ® E A y B son claves candidatas a principales. Elegimos una de las 2, por ejemplo A.

B A C

D E

B

A D C

E no está en 3ª forma normal porque existen atributos no principales que dependen transitivamente de la clave de la relación. Para pasarlo a 3ª forma normal lo descompongo en 2 tablas: 1ª tabla: clave con dependencias no transitivas.

B A

C 2ª tabla: clave con dependencias transitivas.

D C

E Ej: Tenemos las siguientes dependencias de la tabla ALUMNOS: NºMATRICULA ® AULA, GRUPO GRUPO ® AULA Pasarlo a 3ª forma normal. Descomponemos en 2 tablas: 1ª tabla: clave con dependencias no transitivas.

B A

C 2ª tabla: dependencias transitivas.

D C

E

Ej: Pasar a 3ª forma normal las siguientes dependencias de la tabla alumnos: NºMATRICULA ® AULA, GRUPO GRUPO ® AULA Forma normal de Boyce-Codd: Trata de resolver los problemas que origina la 3ª forma normal. Se dice que una relación R está en FNBC sí y sólo si todo determinante o todo implicante (conjunto de atributos a la izquierda de la relación) es clave. Ej:

A C

B Está en 3ª forma normal, pero no en FNBC. AB ® C C ® B AB ® B Para pasar a FNBC una relación R en la cual existe una dependencia del tipo X ® Y siendo X un atributo no principal y siendo Y un atributo principal, descomponemos R en 2 proyecciones: R1 formada por los atributos X e Y ® R1=(X, Y) R2 formada por todos los atributos de R exceptuando Y ® R2=(A - Y) Obtenemos:

C B

A

C Ej: Tenemos una tabla de un callejero: CALLEJERO (DIRECCION, CIUDAD, C_POSTAL) C_POSTAL ® CIUDAD DIRECCION, CIUDAD ® C_POSTAL

C_POSTAL CIUDAD DIRECCION

C_POSTAL CIUDAD DIRECCION

C_POSTAL

Dependencia multivaluada: Sean A y B dos subconjuntos distintos de atributos de una tabla T se dice que A tiene una dependencia multivaluada con B ó que A multidetermina a B ó que B depende multivaluadamente de A (A ®® B) ai para cada valor de A tenemos un conjunto, bien sea de valores de B que son independientes de los demás atributos, o la relación.

1. A <= B 2. Independientemente del resto de atributos de A Ej:

Los profesores de una facultad imparten varias asignaturas y una asignatura es impartida por varios profesores.

Una asignatura tiene varios textos y un texto puede utilizarse en varias asignaturas, independientemente del profesor que las imparte.

PROFESOR

ASIGNATURA

TEXTO

ANA

DIGITALES

T1

ANA

DIGITALES

T2

LUIS

DIGITALES

T1

LUIS

DIGITALES

T2

LUIS

COMUNICACIONES

T2

LUIS

COMUNICACIONES

T3

Asignatura ®® Profesor a) Cada asignatura tiene definidos varios profesores b) Se cumple Asignatura ®® Texto a) Cada asignatura tiene asignado más de un texto b) Se cumple Siempre que se dé una dependencia X ®® Y tiene que darse una dependencia X ®® A - (X U Y). Para que se dé debe tener más de 2 atributos. Profesor ®/® Texto a) Para cada profesor hay definidos más de un texto b) Depende de Asignatura Profesor ®® Asignatura a) Un profesor tiene asignadas varias asignaturas b) No se puede dar por una serie de teoremas matemáticos 4ª Forma Normal: Una tabla está en 4ªFN si está en 3ªFN y se cumple que las únicas dependencias multivaluadas existentes son las existentes con los atributos secundarios. Cuando no existen dependencias multivaluadas y la tabla está en 3ªFN para pasar a 4ªFN tendremos en cuenta el teorema de FAGIN: "Una tabla T con los atributos A, B, C se puede descomponer sin pérdida de información en 2 proyecciones: T1 con los atributos A y B y T2 con los atributos A y C, sólo si A multidetermina a B y C (A ®® B / C). Para pasar a 4ªFN si existe una dependencia multivaluada X ®® Y la dividimos en 2 tablas. 1. R1 (x, y) 2. R2 (A - y)

R1 (x, y) R: x ®® y

R2 (A - y) Ej: Asignatura ®® Texto

Asignatura ®® Profesor (asignatura, texto) (asignatura, profesor) R (Asignatura, Texto, Profesor) 1. Asignatura ®® Texto, x 2. Asignatura ®® Profesor, y R1 (Asignatura, Texto, x) R2 (Asignatura, Profesor, y)

Dependencia de JOIN Se dice que una relación T formada por los atributos A1, A2, ..., An tiene una dependencia con sus proyecciones T1, T2, ..., Tn si T=T1 T2 T3 ... Tn

T

A

B

C

T1

A

B

T2

B

C

a1

b1

c1

a1

b1

b1

c1

a2

b1

c1

a2

b1

b2

c1

a1

b2

c1

a1

b2

b1

c2

a1

b1

c2

T<> T1 T2

T1 T2

A

B

C

a1

b1

c1

a1

b1

c2

a2

b1

c1

a2

b1

c2

a1

b2

c1

La 4ª fila (a2, b1, c2) es una tupla intrusa o espúrea. Hay otra proyección que hace que se cumpla:

T3

A

C

a1

c1

a2

c1

a1

c2

T=T1 T2 T3

5ª Forma Normal: Se dice que una tabla está en 5ªFN si está en 4ªFN y además toda dependencia de JOIN está implicada por las claves de la tabla. Las columnas de enlace deben ser los atributos que componen la clave. En la siguiente tabla no existen dependencias funcionales. Bebida ─/® Camarero.

T

Clientes

Bebidas

Camarero

López

Cerveza

Juan

López

Fanta

Luis

Garcia

Fanta

Juan

Pérez

Cerveza

Juan

Dependencias de JOIN: T=T1 T2 T3 Columna enlace: T1 T2 = Cliente Los tres atributos forman la clave. T no está en 5ª forma normal. Si las columnas de enlace son las columnas de la clave entonces está en 5ª forma normal. Para pasarlo a 5ª forma normal habrá que descomponer T en sus proyecciones. EMPLEADOS (DNI, NOMBRE, DIRECCION, NSS, FISS, CATEGORIA) T1 (DNI, NOMBRE, DIRECCION) T2 (DNI, NSS, FISS) T3 (DNI, CATEGORIA) EMPLEADO = T1 t2 t3 T está en 5ª forma normal, aún así es factible descomponerla en sus proyecciones. Ej: Competiciones En una prueba hay varios árbitros. Un árbitro puede arbitrar en diferentes pruebas. Un atleta puede competir en diferentes pruebas. En un único país no pueden existir nº de pasaportes iguales, pero sí si son de países diferentes. Pueden existir distintos atletas con el mismo nombre. Atributos: NPa: Nº de pasaporte del atleta Na: Nombre del atleta da: Dirección del atleta dpa: Dirección postal del atleta ca: Ciudad del atleta Pra: Provincia del atleta Pa: País del atleta cpa: Código de país del atleta Fn: Fecha de nacimiento del atleta Sa: Sexo del atleta cp: Código de la prueba np: Nombre de la prueba ma: Marca del atleta NPar: Nº de pasaporte del árbitro Nar: Nombre del árbitro Ej: Barco de pasajeros

Un barco pertenece a un propietario. Se construye en un sólo astillero. El código es único dentro del país. Pueden existir barcos diferentes con el mismo código si son de distintos países. Un barco es de un sólo tipo. Los marineros del barco sólo tienen una dirección y un código dentro del barco. Un marinero puede tener varios teléfonos. No existen ciudades repetidas. Dos pasajeros pueden tener el mismo camarote en un barco. Un pasajero puede embarcar en varios barcos en distintas fechas. Dos barcos pueden haber tenido a un mismo marinero, pero nunca al mismo tiempo. Ej: Coleccionistas de sellos El código de sello particular es único para la colección del propietario. El código internacional es único para cada tipo de sello emitido, del cual pueden existir varios ejemplares. Dos coleccionistas pueden tener ejemplares de un mismo tipo de sello, pero cada uno tendrá un código particular del sello que podría ser el mismo. No existen 2 DNIs, ciudades, provincias o países iguales. Pueden existir nombres iguales. Un propietario pude tener varios teléfonos. Atributos: CS: Código particular del sello para el propietario actual CI: Código internacional del sello PS: País del sello CP: Código del país VE: Valor de emisión del sello AE: Año de emisión del sello EP: Estado político del país en el año de emisión del sello CE: Código del estado político CLS: Clase de sello CCL: Código de la clase de sello EC: Estado de conservación del sello CC: Código del estado de conservación del sello DNIP: DNI del propietario del sello NP: Nombre del propietario del sello DP: Dirección del propietario del sello CiP: Ciudad del propietario del sello CPP: Código postal del propietario del sello PP: Provincia del propietario del sello PaP: País del propietario del sello TP: Teléfono del propietario del sello DNIA: DNI del antiguo propietario del sello

SQL Embedido 1. SQL autocontenido 2. SQL embedido SQL embedido: Sentencia de SQL que se utiliza dentro de un programa llamado anfitrión, escrito en cualquier lenguaje. Tendremos tablas con datos de entrada y de salida. Las sentencias de SQL serán sentencias embedidas en el programa anfitrión. Características: * Todas las sentencias SQL van a estar enmarcadas por:

EXEC SQL <sentencias>

END-EXEC * Antes de utilizar un compilador para manejar SQL embedido es necesario pasar el programa fuente por un precompilador: PROG precompilador PROG FUENTE compilador PROG LINKER FUENTE MODIFICADO OBJETO

│ ├® sintaxis sentencias SQL ├® comentar sentencias SQL (para que las ignore el compilador) └® sustituir sentencias SQL por llamadas a rutinas de librerías

* Manejo de variables de programa dentro de sentencias SQL: Se pone dos puntos (:) delante del nombre de la variable. Ej: Para un vuelo, visualizar plazas libres y nº de vuelo (datos de salida), sabiendo el origen, destino, hora de salida y fecha de salida (datos de entrada). INICIO

escribir 'Introducir ORIGEN, DESTINO, FECHA, HORA' leer ORIGEN, DESTINO, FECHA, HORA EXEC SQL

SELECT RESERVAS.NUM_VUELO, PLAZAS_LIBRES INTO :VUELO, :PLAZAS FROM RESERVAS, VUELOS WHERE RESERVAS.NUM_VUELO=VUELOS.NUM_VUELO AND ORIGEN=:ORIGEN AND DESTINO=:DESTINO AND FECHA=:FECHA AND HORA=:HORA

END-EXEC escribir VUELO, PLAZAS

FIN INTO sirve para determinar las variables de salida. Devuelve en la salida 1 sóla fila. En caso de que la salida devuelva más de una fila tendremos los cursores. Cursor: Es una estructura de datos tabular (en forma de tabla) que sirve para almacenar un número indeterminado de filas. Sólo permite manejar una única fila a la vez, mediante un puntero.

Para manejar cursores en SQL embedido hay 4 etapas: 1) Definición del cursor

Se define junto a la declaración de variables del programa anfitrión:

EXEC SQL DECLARE

<nom_cursor> CURSOR FOR

<sent_select> END-EXEC

2) Abrir cursor

Rellena de filas la estructura del cursor:

EXEC SQL OPEN <nom_cursor>

END-EXEC 3) Recuperar filas (1)

EXEC SQL FETCH <nom_cursor> {avanza el puntero una posición} INTO <lista_variables> {una sóla variable por columna}

END-EXEC Cuando se hace un OPEN se rellena la estructura y el puntero se posiciona anterior a la primera fila. El primer FETCH posiciona el cursor en la primera fila. 4) Cerrar cursor

EXEC SQL CLOSE <nom_cursor> {liberando memoria}

END-EXEC Ej: Realizar un programa que presente todos aquellos vuelos existentes entre un origen y un destino determinados. Pasos: 1. Declaración de variables 2. Petición de información de origen y destino 3. Abrir cursor 4. Recuperar primera fila del cursor 5. Comprobar si existe alguna fila. Si no existe ir a 9 6. Escribir fila en pantalla 7. Recuperar siguiente fila 8. Ir a 5 9. Fin VARIABLES

ORIGEN, DESTINO, HORA, VUELO: STRING EXEC SQL

DECLARE lista_vuelos CURSOR FOR SELECT NUM_VUELO, HORA_SALIDA

FROM VUELOS WHERE ORIGEN=:ORIGEN

AND DESTINO=:DESTINO

END-EXEC INICIO

ESCRIBIR 'INTRODUZCA ORIGEN Y DESTINO' LEER ORIGEN, DESTINO EXEC SQL

OPEN lista_vuelos END-EXEC EXEC SQL

FETCH lista_vuelos INTO :VUELO, :HORA

END-EXEC {Variable predefinida SQL-CODE: nos da información sobre la ejecución de cada sentencia en SQL:

SQL-CODE < 0 ® Error SQL-CODE = 100 ® CURSOR vacío SQL-CODE > 0 ® Warning} MIENTRAS SQL-CODE <> 100

ESCRIBIR VUELO, HORA EXEC SQL

FETCH lista_vuelos INTO :VUELO, :HORA

END-EXEC FIN_MIENTRAS EXEC SQL

CLOSE lista_vuelos END-EXEC

FIN INTEGRIDAD E INTEGRIDAD REFERENCIAL 1. Introducción 2. Integridad referencial

2.1 DDL 2.2 DML

3. Disparadores 4. Reglas semánticas 1. INTRODUCCION Integridad: característica que nos permite tener coherencia y veracidad en la información. Existen ciertas operaciones de SQL que pueden hacer peligrar la integridad de la operación: - Inserción - Borrado - Modificación T.PADRE EMPLEADO DNI Clave Principal

depende cod_fam T.HIJA FAMILIARES DNI_EMP Clave Ajena Puede que borremos un empleado y olvidemos eliminar los familiares. Tipos de integridad: Integridad de dominio: restringimos los valores que puede tomar un atributo respecto a su dominio, por ejemplo

EDAD ® 18 - 65. Integridad de entidad: la clave primaria de una entidad no puede tener valores nulos y siempre deberá ser única, por ejemplo DNI. Integridad referencial: las claves ajenas de una tabla hija se tienen que corresponder con la clave primaria de la tabla padre con la que se relaciona. Por ejemplo, en familiares necesitaremos el DNI de empleado, que es la clave ajena de la tabla. 2. INTEGRIDAD REFERENCIAL Clave principal: conjunto de atributos capaz de diferenciar cada tupla de la tabla. Clave ajena: Clave en la tabla padre. 2.1 DDL Se basa en la definición de la clave principal y de la clave ajena de la tabla. CREATE TABLE EMPLEADO

(PRIMARY KEY DNI, DNI CHAR(8) NOT NULL, NOMBRE VARCHAR(30) NOT NULL, {columnas de la tabla, clave incluida} . . .

) CREATE TABLE FAMILIARES

(PRIMARY KEY COD_FAMILIAR, COD_FAMILIAR CHAR(4) NOT NULL, NOM_FAMILIAR CHAR(30) NOT NULL, DNI_EMPL CHAR(8) NOT NULL, . . .

) FOREIGN KEY REL_EMPL_FAM

DNI_EMPL REFERENCE EMPLEADO RESTRICT

ON DELETE CASCADE SET NULL

2.2 DML Tendremos una serie de reglas para: 2.2.1 Inserción 2.2.2 Actualización Implícitas o determinadas por el sistema. 2.2.3 Borrado Explícita o definida por el usuario de tres que proporciona el sistema. 2.2.1 Regla de inserción Se ejecuta sobre la tabla hija. Sólo se podrá insertar una fila en la tabla hija si el valor de clave ajena de esa fila es un valor nulo o un valor de los existentes en la clave primaria de la tabla padre. 2.2.2 Regla de actualización Se ejecuta sobre las tablas padre o hija. Tabla padre: no se puede modificar el valor de clave primaria de la tabla padre si existe alguna fila en la tabla hija que lo referencie en la clave ajena.

nommbre, cantidad SELECT v.vennom, vn.cantidad as total FROM vendedor v, ventas vn, producto p where p.procod=vn.procod AND vn.vencod=v.vencod nombre vendedor, nombre producto, cantidad SELECT v.vennom, p.pronom, vn.cantidad FROM vendedor v, ventas vn, producto p where p.procod=vn.procod AND vn.vencod=v.vencod nº de ventas del producto cod=10 SELECT count(ventas.cantidad) WHERE ventas.procod = "10" FROM ventas GROUP BY ventas.procod nº de ventas realizadas por vendedor SELECT ventas.vencod,count(ventas.cantidad) FROM ventas GROUP BY ventas.vencod nº pero con el nombre SELECT vendedor.vennom, count(ventas.cantidad) FROM ventas GROUP BY ventas.vencod Valor de las ventas realizadas por vendedor SELECT vendedor.vennom, (ventas.cantidad*producto.propvp) WHERE vendedor.vencod = ventas.vencod FROM ventas, vendedor Total de ventas SELECT vendedor.vennom, sum(ventas.cantidad*producto.propvp) WHERE vendedor.vencod = ventas.vencod FROM ventas, vendedor GROUP BY vendedor.vennom Crear vista del anterior consulta CREATE SQL VIEW total as SELECT vendedor.vennom, sum(ventas.cantidad*producto.propvp) as total WHERE vendedor.vencod = ventas.vencod FROM ventas, vendedor GROUP BY vendedor.vennom Total general de las ventas SELECT SUM(total.sum_exp_2) as total_general FROM total

Ver productos(nombre,antidad) entre 2 y 5 SELECT p.pronom, vn.cantidad FROM ventas vn, producto p WHERE vn.cantidad between 2 AND 5 AND p.procod=vn.procod Lista de productos vendidos por cada vendedor SELECT v.vennom as vendedor, p.pronom FROM ventas vn, producto p, vendedor v WHERE v.vencod=vn.vencod AND p.procod = vn.procod ORDER BY vendedor distinct Producto vendió mayor cantidad SELECT v.procod,p.pronom,v.cantidad FROM ventas v, producto p WHERE v.cantidad=(Select MAX(v.cantidad) FROM ventas v) AND v.procod = p.procod Productos con cantidades mayores al promedio SELECT v.procod,p.pronom,v.cantidad FROM ventas v, producto p WHERE v.cantidad>(Select AVG(v.cantidad) FROM ventas v) AND v.procod = p.procod Maxima cantidad vendida por productos con cantidades > AVG SELECT p.procod, MAX(v.cantidad) FROM producto p, ventas v GROUP BY p.procod WHERE v.cantidad > (SELECT AVG(v.cantidad) FROM ventas v) AND p.procod = v.procod

Tabla hija: el valor de clave ajena de la tabla hija sólo se puede modificar si el nuevo valor que va a adoptar es un valor nulo o es igual a un valor de clave primaria de la tabla padre. 2.2.3 Regla de borrado Esta regla se especifica al crear la tabla padre y se activará cuando se intente eliminar una fila de la tabla. CREATE TABLE . . . FOREIGN KEY . . . RESTRICT ON DELETE CASCADE SET NULL Es una regla explícita, ya que el usuario puede elegir la regla de borrado que desee, a partir de 3 reglas que nos va a proporcionar el sistema. a) RESTRICT

No se puede borrar una fila de la tabla padre si existen filas en la tabla hija cuyos valores de clave ajena sean iguales a valores de clave primaria de la fila que queremos borrar. b) CASCADE

Cada vez que se borre una fila de la tabla padre se eliminarán aquellas filas de la tabla hija que tengan como valor de clave ajena el mismo valor que el de la clave primaria de la fila que se quiere borrar. c) SET NULL

Cada vez que se elimine una fila de la tabla padre se actualizarán los campos de la clave ajena de la tabla hija a valores nulos para aquellas filas que tengan en el campo de la clave ajena el mismo valor que la clave primaria de la fila de la tabla padre que se quiere borrar. Casos específicos de integridad: Integridad auto-referencial Una tabla es al mismo tiempo padre e hija de si misma. Ej: Dada la tabla T_EMP con las siguientes columnas:

COD_EMP NOMBRE COD_EMP_JEFE Todos los valores que aparezcan en la última columna existirán también en la primera, por tanto COD_EMP_JEFE se convierte en clave ajena de la tabla. Ciclos de integridad referencial Conjunto de tablas que funcionará como tablas padres e hijas unas de otras, formando un camino cerrado. Ej: Tenemos 3 tablas: COCHES (MATRICULA, MARCA, COD_DIRECTOR) DIRECTORES (COD_DIRECTOR, NOMBRE_DIR, COD_SECRETARIA)

SECRETARIAS(COD_SECRETARIA, NOMBRE_SEC, MATRICULA) COCHES es tabla hija de DIRECTORES DIRECTORES es tabla hija de SECRETARIAS SECRETARIAS es tabla hija de COCHES

COCHES

DIRECTORES SECRETARIAS

Forma un ciclo de integridad referencial.

Por haber ciclos debemos incorporar restricciones de borrado, para un correcto funcionamiento de la integridad referencial. Restricciones de borrado a) Integridad auto-referencial

Siempre que tengamos una tabla con integridad auto-referencial debemos definir un borrado en cascada. Tabla EMPLEADO

COD_EMP

COD_EMP_JEFE

A00

-

B01

A00

C02

B01

D03

C02

DELETE FROM EMPLEADO

WHERE COD_EMP_JEFE >= 'B01' Con RESTRICT no se puede borrar la 3ª fila, porque C02, clave primaria, está siendo referenciada en la fila 4ª como clave ajena. Salta a la 4 fila y la borra. DELETE FROM EMPLEADO

WHERE COD_EMP_JEFE IS NULL Con SET NULL borraría la primera fila, porque su clave ajena es NULL, pasaría a la 2ª y como su clave ajena es igual a la clave primaria de la fila borrada pondría la clave ajena a NULL. Como le hemos dicho que borre aquellas filas que tengan por clave ajena un valor nulo, ahora borraría esta fila y así sucesivamente hasta eliminar la tabla completa. b) Ciclos de integridad referencial DELETE FROM T3

WHERE FKT2 IS NULL Restricciones de borrado en ciclos: CICLO DE 2 TABLAS: ninguna de ellas podrá tener definida la opción de borrado en cascada. CICLO DE MAS DE 2 TABLAS: Sólo una de las tablas podrá tener la opción de borrado en cascada.

El esquema anterior no cumple las restricciones expuestas. c) Tablas conectadas por múltiples caminos (con más de una tabla padre) DELETE FROM T2

WHERE PKT2 = 'T2A' Restricciones de borrado en tablas conectadas por múltiples caminos: Los dos caminos que llegan a una tablas deben tener definida siempre la misma regla de borrado, que debe ser RESTRICT o CASCADE. En el ejemplo funcionaría bien por el primer camino, pero por el segundo hay restrinción. Ej. propuesto: Pruébese que ocurriría si entre T3 y T1 hay SET NULL y entre T2 y T1 hay SET NULL. Ventajas del uso de las reglas de integridad referencial proporcionadas por el sistema frente a la implementación de estas por parte del usuario. 1. Al usar las proporcionadas por el sistema los analistas tienen menor responsabilidad. 2. Habrá un incremento en la productividad y un decremento en los costes de la aplicación, porque el número de pruebas a realizar será menor si la integridad referencial es implementada por el propio sistema, el cual nos garantizará el correcto funcionamiento de las reglas de integridad referencial. El tiempo de desarrollo de la aplicación será menor. 3. La información será más coherente, ya que está garantizado que todas las filas de las tablas cumplen las normas de integridad referencial. 3. DISPARADORES O 'TRIGGERS'

Son un conjunto de sentencias que el sistema ejecuta a la hora de efectuar una inserción, actualización o borrado en una tabla. Para definir un TRIGGER necesitamos:

- Nombre de la tabla sobre la que actuará el trigger - Sentencia que activará el trigger (inserción, actualización o borrado) - Acciones que realizará el trigger

DB2 no incorpora la posibilidad de definir triggers, pero SQL sí. Las reglas de integridad referencial se

emulan en SQL mediante la implementación de triggers. Emulación de inserción en tabla hija mediante el uso de trigger en SQL: Según la regla de integridad referencial, no se puede efectuar una inserción en una tabla hija a menos que exista un valor de clave primaria igual al de clave ajena de la fila que se desea insertar. Definición del trigger: DEFINE TRIGGER insercion

ON INSERT OF HIJA El trigger comparará el valor de clave ajena de la fila a insertar con el valor de clave primaria de la tabla hija cada vez que se intente insertar una fila. Implementación del trigger: (if (SELECT COUNT (#)

FROM PADRE, INSERTED WHERE PADRE.CL_PRIMARIA = INSERTED.CL_AJENA) = 0) Condición que activa el trigger

begin PRINT 'ERROR' ROLLBACK TRANSACTION Acciones que ejecuta el trigger si se activa

end

SELECT COUNT devuelve el número de filas de la tabla padre con un valor de clave primaria igual a la clave ajena de la fila a insertar. El sistema proporciona una tabla de almacenamiento temporal (INSERTED) donde se guardarán las filas a insertar. Si CL_AJENA = CL_PRIMARIA, permite la inserción, en caso contrario deshace la operación con ROLLBACK TRANSACTION. El trigger correspondiente a la operación de actualización sería similar.


__________________________________________________________________________________________


FROM familiares a, emp b, depto c WHERE a.nro_emp = b.nro_emp AND b.cod_deptno = c.cod_deptno AND a.cedula between 10000000 and 12000000; La ruta de acceso del optimizador será: depto(i_dep1) --> emp(i_emp1) --> familiares(i_fam2)L

Uso del plan EXPLAIN PLAN. El explain plan, es un utilitario del manejador, el permite ver cual es el camino de búsqueda de la información , que utiliza una instrucción SQL. A su vez nos indica si esta usando los índices. Forma de uso: - Crear las tablas de Explain Plan en la cuenta de usuario ORACLE a la cual se accesan las tablas. @?/rdbms/admin/xplainpl - Una vez creadas las tablas, se pueden correr los query sql o selección de datos, de la siguiente forma en SQL*Plus: Explain plan set statement_id = 'identificación' for 'el query SQL o instrucciones; - Una vez corrido el query, el no arroja filas; indica que la operación termino. A continuación se desea ver el camino o ruta recorrido por la instrucción, para lo cual se corre el siguiente Script SQL. Select lpad(' ',3*Level) || operation || options || ' ' || object_name query_plan from plan_table where statement_id = 'identificación' connect by prior id = parent_id and statement_id = 'identificación' start with id=1 El resultado, mostrara la ruta que siguen el manejador ORACLE para extraer los datos. Su interpretación y mayor información referirse al Manual Oracle Rdbms Performance Tuning Guide. NOTA: Luego de cada corrida del Explain Plan, el inserta información en la tabla de plan_table, por lo que se recomienda eliminar los registros creados.

guia de sql

Documents