sql

REPASO SQL, DDL Y DML

OBJETIVOS

• Repasar las sentencias que forman el lenguaje de definición de datos (DDL Data Definition Language) de SQL, es decir, el subconjunto de órdenes que nos permitirán crear o editar tablas de la base de datos.

• Repasar el lenguaje de consultas para el acceso a las tablas de la BD. MATERIAL: TECNOLOGIA ORACLE TEMAS

• Definición de Datos • Creación de Tablas • Restricciones de Columnas • Restricciones de Tablas • Renombrar Tablas • Eliminar Tablas • Modificar Tablas • Crear y Borrar Sinónimos • Crear Dominios • Consultas I • Select • From • Where • Diccionario de Datos

SQL

En realidad SQL no es un lenguaje en si, como podría ser un lenguaje de programación de 3ª generación (C, Pascal…), sino que en un sublenguaje orientado a acceso y manipulación de base de datos relacionales. Con SQL como única herramienta sólo podemos acceder a las bases de datos, pero no tenemos las estructuras típicas de un lenguaje de programación. Una buena analogía podría ser un sistema operativo. El interfaz de comandos de un SO nos da todo lo que necesitamos para acceder al sistema de ficheros, pero sólo podemos hacer eso, acceder a ficheros. SQL actúa de la misma manera, nos da todo lo que necesitamos para acceder a bases de datos, pero no podemos hacer más. Se dice que SQL es estructurado porque trabaja con conjuntos de resultados (result set) abstractos como unidades completas, Un conjunto de resultados es el esquema básico de una tabla: N filas x N columnas; este esquema se trata como un todo y es la idea principal de SQL, a la hora de recuperar un conjunto de resultados, éste se trata de la misma forma tenga el número de filas que tenga (0-N) y tenga el número de columnas que tenga (1-N). Además SQL es consistente, esto significa que los "estilos" de las distintas sentencias son uniformes, por lo que el aprendizaje es rápido.

El lenguaje de definición de datos (DDL)

El DDL (Data Definition Language) es el conjunto de sentencias que está orientadas a la creación, modificación y configuración de objetos en base de datos. Entre sus principales funciones se tienen:

• Definir y crear una nueva tabla. • Suprimir una tabla que ya no se necesita. • Cambiar la definición de una tabla existente. • Definir una tabla virtual (o vista) de datos. • Construir un índice para hacer más rápido el acceso a una tabla. • Controlar el almacenamiento físico de los datos por parte del SGBD.

Tipos de datos:

Oracle maneja tipos de datos para manejar desde números hasta objetos como fotos, archivos, cada dato se debe especificar de una manera especial:

− Los valores alfanuméricos van encerrados entre comilla simple: 'Alfanumérico' − Los valores numéricos son número simples: 123 − Las fechas van encerradas entre comillas simples: '1/12/2000' − Los valores binarios no pueden ser representados (son fotos, videos…)

Los diferentes tipos de datos que maneja Oracle son los siguientes:

Tipo de dato CHAR(b) Almacena cadenas de caracteres de longitud fija, desde 1 a 2.000 bytes de ocupación. El número de caracteres que se pueden almacenar se rige según la siguiente fórmula.

nº caracteres = bytes / character set Para ASCII, el conjunto de caracteres ocupa un byte, por lo que coincide el número de caracteres máximos con la ocupación del tipo de dato. Si se introduce un valor de 10 caracteres en un campo de CHAR(100), se rellenará con espacios las 90 posiciones restantes. Así la siguiente expresión es cierta:

'Hola pepe' = 'Hola pepe '

Si se intenta introducir un valor demasiado grande para el campo, se intentará eliminar los espacios finales, y si cabe sin espacios, se introduce. Si aún así no cabe, se retorna un error.

Tipo de dato VARCHAR2(b) Almacena cadenas de caracteres de longitud variable. Si se define una columna de longitud 100 bytes, y se introduce en ella un valor de 10 bytes, la columna ocupará 10 y no 100 como hacía con el tipo de dato CHAR.

Tipo de dato VARCHAR(b) En Oracle10g es equivalente a VARCHAR2

Tipo de dato NCHAR(b) Almacena un valor alfanumérico de longitud fija con posibilidad de cambio de juego de caracteres. Puede almacenar tanto caracteres ASCII, EBCDIC, UNICODE…

Tipo de dato NVARCHAR2(b) Almacena un valor alfanumérico de longitud variable con posibilidad de cambio de juego de caracteres. Puede almacenar tanto caracteres ASCII, EBCDIC, UNICODE…

Tipo de dato NUMBER(p,s) Almacena valores numéricos en punto flotante que pueden ir desde 1.0 x 10-130 hasta 9.9…(38 nueves)… 9 x 10125. El almacenamiento interno de los valores numéricos en notación científica:

Mantisa x 10exponente

La mantisa (parte de una representación en punto flotante que contiene los dígitos significativos del número a representar) puede contener cualquier número, entero o decimal, positivo o negativo. El exponente podrá contener cualquier número entero, positivo o negativo. El parámetro “p” indica la precisión (número de dígitos contando los decimales) que contendrá el número como máximo. Oracle garantiza los datos con precisiones de 1 a 38. El parámetro “s” indica la escala, esto es, el máximo de dígitos decimales. Hay que tener en cuenta que una columna definida NUMBER(10,5), podrá contener como máximo cualquier número siempre y cuando el número de dígitos enteros más el número de dígitos decimales no supere 10 (y no 15). La escala puede ir de -84 a 127. Para definir número enteros, se puede omitir el parámetro s o bien poner un 0 en su lugar. Se puede especificar una escala negativa, esto lo que hace es redondear el número indicado a las posiciones indicadas en la escala. Por ejemplo un número definido como NUMBER(5,-2), redondeará siempre a centenas. Así si intentamos introducir el valor 1355, en realidad se almacenará 1400.

Tipo de dato FLOAT(b) Almacena un número en punto decimal sin restricción de dígitos decimales. El parámetro b indica la precisión binaria máxima que puede moverse en el rango 1 a 126. Si se emite el defecto será 126. Una columna FLOAT(126) es equivalente a una columna NUMBER(38), aunque la diferencia está en que la columna NUMBER no podrá contener decimales y la columna FLOAT si y en con cualquier escala.

Tipo de dato DATE Almacena un valor de fecha y hora.

Para un tipo de dato DATE, Oracle almacena internamente los siguiente datos: ƒ Siglo ƒ Año ƒ Mes ƒ Día ƒ Hora ƒ Minuto ƒ Segundo

El formato por defecto de las fechas es: 'DD-MON-YYYY'

Esto es: Dos dígitos para el día Las tres primeras siglas del año (depende del idioma instalado). Cuatro dígitos para el año.

Por ejemplo: '1-JAN-2001' ó '2-DEC-1943'

Este formato puede ser alterado en cualquier momento.

Internamente un fecha se almacena como el número de días desde cierto punto de inicio (por ejemplo el año 0). Esto permite que las fechas puedan ser tratadas en operaciones aritméticas normales:

'1-JAN-2001' + 10 = '11-JAN-2001' '1-JAN-2000' - 1 = '31-DEC-1999' '10-MAY-2000' - '1-MAY-2000' = 9

Tipos de datos binarios Permiten almacenar información en formato "crudo", valores binarios tal y como se almacenan en el disco duro o como residen en memoria. Estas columnas se pueden utilizar tanto para almacenar grandes cantidades de datos (hasta 4Gb.), como para almacenar directamente cualquier tipo de

fichero (ejecutables, sonidos, videos, fotos, documentos Word, librerías…) o para transportar datos de una base de datos a otra, ya que el formato binario es el único formato común entre cualquier sistema informático.

Tipo de dato LONG Almacena caracteres de longitud variable hasta 2 Gb. Este tipo de dato se soporta para compatibilidad con versiones anteriores. En Oracle8 y siguientes versiones se deben usar los tipos de datos CLOB y NLOB para almacenar grandes cantidades de datos alfanuméricos.

Tipo de dato ROWID Representa una dirección de la base de datos, ocupada por una única fila. El ROWID de una fila es un identificador único para una fila dentro de una base de datos. No hay dos filas con el mismo ROWID. Este tipo de dato sirve para guardar punteros a filas concretas. El ROWID se compone de:

-Número de datafile donde se almacena la fila (se pueden ver en DBA_DATA_FILES) -Dirección del bloque donde está la fila -Posición dentro del bloque

Siempre que queramos obtener una fila de la firma más rápida posible, debemos hacerlo a través de su ROWID. Un uso típico suele ser obtener un listado de ROWIDs con un SELECT, y después acceder a cada una de las filas directamente con la condición del ROWID.

Sentencias sobre Tablas Create Table Crea una tabla en la base de datos, sintaxis General de la sentencia CREATE TABLE: CREATE TABLE nombre_tabla( COLUMNA TIPO [NOT NULL], COLUMNA TIPO [NOT NULL], . . . {CONSTRAINT nombre_clave_primaria PRIMARY KEY (columnas_clave)} {CONSTRAINT nombre_clave_foránea FOREIGN KEY(columnas_clave) REFERENCES tabla_detalle( columnas_clave ) {ON DELETE CASCADE} } ) Un ejemplo de la utilización de esta sentencia es:

Figura 1 Tabla Factura

La creación de la tabla FACTURA, definida en la figura 1, sería la siguiente: CREATE TABLE FACTURA( REFERENCIA VARCHAR2(10) NOT NULL, DESCRIPCION VARCHAR2(50), C_PAIS NUMBER(3), C_CLIENTE NUMBER(5), IMPORTE NUMBER(12), CONSTRAINT PK_FACTURA PRIMARY KEY( REFERENCIA ) CONSTRAINT FK_CLIENTE(C_PAIS,C_CLIENTE) REFERENCES CLIENTE(C_PAIS, C_CLIENTE) ON DELETE CASCADE); Restricciones de columnas: NOT NULL: La columna no permitirá valores nulos. CONSTRAINT: Permite asociar un nombre a una restricción. DEFAULT valor: La columna tendrá un valor por defecto. El SBGD utiliza este valor cuando no se especifica un valor para dicha columna. PRIMARY KEY: Permite indicar que esta columna es la clave primaria. UNIQUE: Obliga a que los valores de una columna tomen valores únicos (no puede haber dos filas con igual valor). Se implementa creando un índice para dicha(s) columna(s). CHECK (condición): Permite indicar una condición que debe de cumplir esa columna. Restricciones de tablas: PRIMARY KEY (columna1, columna2...): Permite indicar las columnas que forman la clave primaria. FOREIGN KEY (columna1, columna2....) REFERENCES NombreTabla: Indica las columnas que son clave ajena referenciando a una clave candidata de otra tabla. UNIQUE (columna1, columna2...): El valor combinado de una o varias columnas es único.

CHECK (condición): Permite indicar una condición que deben cumplir las filas de la tabla. Puede afectar a varias columnas. EJEMPLOS areas(codigo, nombre, departamento) (código es la clave primaria) departamentos(codigo_dpto, nombre) (código_dpto es la clave primaria) La tabla areas tiene una clave ajena areas.departamento - departamentos CREATE TABLE areas ( codigo char(3) not null, nombre char(55) not null, departamento char(3) not null, Primary key(codigo), Foreign key(departamento) REFERENCES departamentos ON DELETE SET NULL); ON DELETE Set Null Significa que si se borra algún departamento de la tabla departamentos el campo departamento de las filas de la tabla areas que le reverenciaban se pone como Null. ON UPDATE CASCADE Significa que si se modifica el código_dpto de una fila de la tabla departamento, también se modificara en las filas de la tabla áreas que le referencian. CREATE TABLE departamentos ( codigo_dpto char(3) not null, nombre char(40) not null, Primary key(codigo_dpto) ); Renombrar una tabla: RENAME TABLE <nombre tabla existente> TO <nuevo nombre tabla> Eliminar una tabla de la base de datos: DROP TABLE <nombre tabla> [CASCADE, RESTRICT] Ejemplos: – DROP TABLE DEPARTAMENTOS CASCADE (La tabla se borra, así como las posibles restricciones relativas a esta tabla) – DROP TABLE DEPARTAMENTOS RESTRICT (La tabla se borra sólo si no se hace referencia a ella en ninguna restricción, p.e. en la definición de claves ajenas) Ver la descripción de una tabla: Para ver un resumen de la definición de una tabla se utiliza el siguiente comando: DESCRIBE nombre_tabla;

Modificar una tabla ALTER TABLE: La sentencia Alter Table se utiliza para cambiar una tabla existente, dentro de la tabla podemos Add (añadir) o Drop (borrar) columnas y restricciones (PRIMARY KEY, FORING KEY, UNIQUE, CHECK CONSTRAINT) o modificar (modify) campos. Su sintaxis es: ALTER TABLE <nombre table> { ADD <nombre columna nueva> <tipo de dato> [NOT NULL] MODIFY <nombre columna> [DEFAULT valor | DROP DEFAUL| tipo de dato] DROP <nombre columna> [CASCADE | RESTRICT] ADD [PRIMARY KEY (nombre columna) | FOREIGN KEY (nombre columna) REFERENCES nombre_tabla | UNIQUE (nombre columna) | CHECK (condición) DROP CONSTRAINT nombre-restricción [CASCADE| RESTRICT] EJEMPLOS Agregar a la tabla areas el campo Responsable de tipo char(30) alter table areas ADD responsable char(30) not null; Modificar el campo nombre de la tabla departamentos a char(50) alter table departamentos MODIFY nombre char(50);

EJERCICIOS (DEFINICIÓN DE DATOS)

Utilizando oracle 10g crear las tablas correspondientes al siguiente esquema de base de dato relacional relativo a la gestión de los préstamos de una biblioteca: Libro (LI_id, autor, titulo, editor, clase, precio) Usuario (carnet, nombre, direccion) Clase (CL_id, tiempo_de_prestamo) Prestamo (numero, carnet, fecha_inicio, fecha_fin) Con las siguientes claves ajenas: Libro.clase → Clase Prestamo.signatura → Libro Prestamo.carnet → Usuario La información de la tablas referidas debe manejarse en cascada para la actualización y en setnull para la eliminación. 1. Cree las tablas del esquema anterior, ubicalas en un tablespace previamente definido por

usted, tenga en cuenta que puede crear más de un tablespace si quiere, en todo caso justifique el por qué de la creación y la ubicación de las tablas.

2. Agregar a la tabla Usuario el campo Fecha_Ingreso (que sea obligatorio).

3. Agregar a la tabla Libro el campo Prestado (que sea obligatorio), y asignarle por defecto el valor 1.

4. Agregar al campo clase la condición de que debe ser un entero entre 0 y 5. 5. Imagínese que el tablespace principal se quedo sin espacio, que haría? 6. Si usted estuviera administrando un sistema de información, y sin importar que operación se

realice, siempre sale un error, que haría, que revisaría?, muestre las instrucciones que utilizaría.

7. Usted como DBA de la empresa desea realizar un mantenimiento a las tablas de la base de datos, lo que necesita que ningún usuario pueda acceder a ella, realice las operaciones SQL necesarias para garantizar esta restricción.

LENGUAJE DE MANIPILACION DE DATOS DML El DDL (Data Definition Language) es el conjunto de sentencias que está orientadas a la creación, modificación y configuración de objetos en base de datos. El DDL es el subconjunto más extenso dentro de SQL así que sólo vamos a hacer una referencia rápida a algunas sentencias.

Ventas Referencia Codigo_Cliente Codigo_Ciudad Total 1 1 12 50000,12 2 2 11 34343,00 3 1 10 23444,12 4 3 11 2372382,2 5 4 13 3232323,01 6 2 12 234000,00

Figura 2 Tabla Ejemplo de ventas

Instrucción INSERT La sentencia INSERT nos permite introducir nuevas filas en una tabla de base de datos. La sintaxis básica es: INSERT INTO tabla[( campos )] VALUES( lista de valores ); Los nombres de los campos son opcionales y si no se ponen se supondrá que se agregarán valores a todos los campos de la tabla en su orden original,si se ponen, se podrán indicar cualquier número de columnas, en cualquier orden. La lista de valores es el registro que se insertará en la tabla. Los tipos de datos deben coincidir con los campos indicados o con la definición de la tabla si omitimos el nombre de los atributos. Las columnas que no se incluyan se inicializarán con NULL, (si no se ha definido valor en el DEFAULT). Ejemplo: INSERT INTO FACTURA VALUES('A111', 'Factura nueva', 1, 5, 50000); Existe otra sintaxis para insertar el resultado de una consulta: INSERT INTO tabla{( campos )} SELECT . . . Este tipo de INSERT permite introducir un gran número de registros en una sola sentencia, al igual que con el INSERT normal, los tipos de datos del SELECT deben coincidir con los de los campos indicados en la sentencia. Ejemplo: INSERT INTO FACTURA(C_PAIS, C_CLIENTE) (SELECT C_PAIS, C_CLIENTE FROM CLIENTE);

Instrucción DELETE La sentencia DELETE nos permite eliminar nuevas filas en una tabla de base de datos conforme a una condición, es equivalente al SELECT, pero en vez de mostrar las filas que cumplan la condición, las elimina. Su sintaxis es: DELETE {FROM} tabla {WHERE condición}; Si se omite la cláusula WHERE se borrarán todas las filas de la tabla, las condiciones pueden ser las mismas que las aplicadas en una sentencia SELECT. Instrucción SELECT La sentencia SELECT es la encargada de la recuperación (selección) de datos, con cualquier tipo de condición, agrupación u ordenación. Una sentencia SELECT retorna un único conjunto de resultados, por lo que podrá ser aplicada en cualquier lugar donde se espere un conjunto de resultados. La sintaxis básica es: SELECT [campos, operación, función] FROM [tablas|consulta] WHERE condición GROUP BY columnas de agrupación HAVING condición agrupada ORDER BY columnas de ordenación; Todas las cláusulas son opcionales excepto SELECT y FROM. Clausulas en el Select: Estas se escriben después del Select y antes del from SELECT [ALL | DISTINCT | UNIQUE ] <lista-de selecciones> <lista-de selecciones>: Lista de nombre de columnas o expresiones que se desean mostrar, deben estar separadas por comas. Ejemplo: Select codigo_cliente from ventas;

Codigo_Cliente 1 2 1 3 4 2

*: indica que muestre todos los campos de las tablas o resultados del from, este no puede ir acompañado de ningún nombre de campo. Ejemplo: Select * from ventas;


DISTINCT: Elimina las filas duplicadas en el resultado de la consulta. Ejemplo: SELECT DISTINC codigo_cliente FROM ventas;

Codigo_Cliente 1 2 3 4

UNIQUE: es igual que DISTINCT. ALL: Muestra todos los resultados generados por la consulta, este es el valor por defecto de un select no es necesario escribirlo. NOTAS: - Las columnas ambiguas se preceden del nombre de la tabla: <tabla>.<columna> - * refiere a todas las columnas de todas las tablas. Cláusula WHERE. Sirve para indicar la condición que deben cumplir las filas resultantes. WHERE <condición> Una condición está formada por una o varias expresiones condicionales conectadas por los operadores lógicos AND, OR y NOT. Una expresión condicional tiene una de las formas indicadas en la figura 3. Ejemplo: SELECT codigo_cliente FROM ventas WHERE total > 40000 and total < 300000

Codigo_Cliente 1 2

Figura 3. Operadores lógicos

CLAUSULA ORDER BY: Esta clausula se utiliza para ordenar el resultado de una consulta según la o las columnas seleccionadas, esta clausula se escribe al final de la consulta. Sintaxis: … ORDER BY CAMPO1 [DESC|ASC][,CAMPON [DESC|ASC]]. (valor por defecto es ASC) Si se desea ordenar más de un campo, estos se separan por coma, cuando esto ocurre el resultado de la consulta ordena teniendo en cuenta el primer campo indicado, luego sin desorganizar este campo, ordena el siguiente, y así hasta llegar al ultimo atributo. Ejemplo: Mostrar los datos de las ventas, ordenando por cliente de forma descendente y por el total de la factura en forma ascendente. SELECT * FROM ventas ORDER BY codigo_cliente DESC, total ASC;


En este ejemplo se observa que primero se ordena de forma descendente el codigo_cliente, y después se ordena el total de forma descendente, sin desordenar el codigo_cliente. CLAUSULA GROUP BY: Una consulta con GROUP BY se utiliza para considerar los registros cuyos ciertos campos tienen el mismo valor, y procesarlos de la misma manera, para contarlos, sumarlos, hacer la media…, esto quiere que los valores iguales se agrupan en uno solo, por ejemplo para calcular el total comprado por cada cliente, se deben tomar los diferentes valores del campo codigo_cliente y mostrar un valor. Las funciones más comunes que se pueden utilizar son: Max(campo): calcula el máximo valor almacenado en el campo indicado, con respecto al grupo descrito. Min(campo): calcula el mínimo valor almacenado en el campo indicado, con respecto al grupo descrito. Sum(campo): Suma los valores almacenados en el campo indicado, que pertenezcan al grupo especificado. Avg(campo): calcula el valor medio o promedio de los valores almacenados en el campo indicado, que pertenezcan al grupo especificado. la función que se desee calcular se debe escribir en el SELECT junto con los campos que conformarán el grupo (de necesitarse), en la clausula GROUP BY que va después del FROM O DEL WHERE (de haber condición) se especifica el o los atributos que generan el grupo. Sintaxis: SELECT [campos|funcion] FROM tabla [WHERE condicion]

GROUP BY campos_grupo; Si en el SELECT solo se especifica la función no se escribe el GROUP BY. Ejemplos:

• calcular el total comprado por cada cliente. (el grupo lo forma el codigo_cliente) SELECT codigo_cliente, sum(total) FROM ventas GROUP BY codigo_cliente;

Codigo_Cliente Sum(total) 1 73500,24 2 268000,00 3 23750000,2 4 3200323,01

• Calcular el total vendido (la función es el único campo a mostrar, no hay grupos)

SELECT sum(total) FROM ventas;

Sum(total) 73500,24 268000,00 23750000,2 3200323,01 27291823,45

CLAUSULA HAVING: No se pueden usar funciones de agrupación en la cláusula WHERE de un SELECT. O sea, no se puede usar el WHERE para, de forma selectiva eliminar datos que no interesan del resultado de una consulta agrupada. Sintaxis: SELECT [campos|funcion] FROM tabla [WHERE condicion] GROUP BY campos_grupo HAVING function condición valor; Ejemplo: Mostrar el total comprado por cada cliente, pero que superen un 1000000 de pesos. (el grupo lo forma el codigo_cliente) SELECT codigo_cliente, sum(total) FROM ventas GROUP BY codigo_cliente;

Codigo_Cliente Sum(total) 3 23750000,2 4 3200323,01

CONSULTAS CON COMBINACION ENTRE TABLAS (JOIN) Todas las explicaciones que están a continuación utilizan las siguientes dos tablas para ilustrar el efecto de diferentes clases de uniones JOIN. Tabla Empleado Apellido IDDepartamento Rafferty 31 31 Jordán 33 Steinberg 33 Róbinson 34 Smith 34 Gaspar 36 Tabla Departamento NombreDepartamento IDDepartamento Ventas 31 Ingeniería 33 Producción 34 Marketing 35 La tabla Empleado contiene a los empleados con el número del departamento al que pertenecen; mientras que la tabla Departamento, contiene el nombre de los departamentos de la empresa, se puede notar que existe un empleado que tiene asignado un número de departamento que no se encuentra en la tabla Departamento (Gaspar), igualmente, en la tabla Departamento existe un departamento al cual no pertenece empleado alguno (Marketing). Esto servirá para presentar algunos ejemplos más adelante. Combinación interna (INNER JOIN o JOIN) Con esta operación es calculado el producto cruzado de todos los registros; así cada registro en la tabla A es combinado con cada registro de la tabla B; pero sólo permanecen aquellos registros en la tabla combinada que satisfacen las condiciones que se especifiquen. Este es el tipo de JOIN más utilizado por lo que es considerado el tipo de combinación predeterminado. SQL especifica dos formas diferentes para expresar estas combinaciones. La primera, conocida como explícita usa la palabra JOIN, mientras que la segunda es implícita y usa ',' para separar las tablas a combinar en la sentencia FROM de la declaración SELECT. Entonces siempre se genera el producto cruzado del cual se seleccionan las combinaciones que cumplan lo que indica la sentencia WHERE. Es necesario tener especial cuidado cuando se combinan columnas con valores nulos NULL ya que el valor nulo no se combina con otro valor o con otro nulo, excepto cuando se le agregan predicados tales como IS NULL o IS NOT NULL.

Como ejemplo, la siguiente consulta toma todos los registros de la tabla Empleado y encuentra todas las combinaciones en la tabla Departamento. La sentencia JOIN compara los valores en la columna IDDepartamento en ambas tablas. Cuando no existe esta correspondencia entre algunas combinaciones, éstas no se muestran; es decir que si el número de departamento de un empleado no coincide con los números de departamento de la tabla Departamento, no se mostrará el empleado con su respectivo departamento en la tabla resultante. Las dos consultas siguientes son similares, y se realizan de manera explicita (A) e implícita (B). A. Ejemplo de la sentencia INNER JOIN explícita: SELECT * FROM empleado INNER JOIN departamento ON empleado.IDdepartamento = departamento.IDdepartamento B. Ejemplo de la sentencia JOIN implícita: SELECT * FROM empleado, departamento WHERE empleado.IDdepartamento = departamento.IDDepartamento Resultado Empleado.Apellido Empleado.IDdepartamento departamento.NombreDepartamento departamento.IDDepartamento

Smith 34 Producción 34 Jordán 33 Ingeniería 33 Róbinson 34 Producción 34 Steinberg 33 Ingeniería 33 Rafferty 31 Ventas 31 El empleado Gaspar y el departamento de Marketing no son presentados en los resultados ya que ninguno de éstos tiene registros correspondientes en la otra tabla. No existe un departamento con número 36 ni existe un empleado con número de identificación 35. A la combinación que utiliza comparaciones dentro del predicado JOIN se le llama theta-join. C. Ejemplo de combinación tipo theta: SELECT * FROM empleado INNER JOIN departamento ON empleado.IDDepartamento < departamento.IDDepartamento Natural (Natural join): Es una especialización de la combinación de equivalencia, anteriormente mencionada. En este caso se comparan todas las columnas que tengan el mismo nombre en ambas tablas. La tabla resultante contiene sólo una columna por cada par de columnas con el mismo nombre.

D. Ejemplo de combinación natural: SELECT * FROM empleado NATURAL JOIN departamento El resultado es un poco diferente al de el ejemplo D, ya que esta vez la columna IDDepartamento se muestra sola una vez en la tabla resultante. Empleado.Apellido Empleado.IDdepartamento departamento.NombreDepartamento Smith 34 Producción Jordán 33 Ingeniería Róbinson 34 Producción Steinberg 33 Ingeniería Rafferty 31 Ventas El uso de esta la sentencia NATURAL puede producir resultados ambiguos y generar problemas si la base de datos cambia, porque al añadir, quitar, o renombrar las columnas, puede perder el sentido la sentencia; por esta razón es preferible expresar el predicado usando las otras expresiones nombradas anteriormente (ejemplos A y B). Cruzada (Cross join): Presenta el producto cartesiano de todos los registros de las dos tablas, el código SQL para realizar este producto cartesiano enuncia las tablas que serán combinadas, pero no incluye algún predicado que filtre el resultado. E. Ejemplo de combinación cruzada explícita: SELECT * FROM empleado CROSS JOIN departamento F. Ejemplo de combinación cruzada implícita: res SELECT * FROM empleado, departamento; Empleado.Apellido Empleado.IDdepartamento departamento.NombreDepartamento departamento.IDDepartamento

Rafferty 31 Ventas 31 Jordán 33 Ventas 31 Steinberg 33 Ventas 31 Smith 34 Ventas 31 Róbinson 34 Ventas 31 Gaspar 36 Ventas 31 Rafferty 31 Ingeniería 33 Jordán 33 Ingeniería 33

Steinberg 33 Ingeniería 33 Smith 34 Ingeniería 33 Róbinson 34 Ingeniería 33 Gaspar 36 Ingeniería 33 Rafferty 31 Producción 34 Jordán 33 Producción 34 Steinberg 33 Producción 34 Smith 34 Producción 34 Róbinson 34 Producción 34 Gaspar 36 Producción 34 Rafferty 31 Marketing 35 Jordán 33 Marketing 35 Steinberg 33 Marketing 35 Smith 34 Marketing 35 Róbinson 34 Marketing 35 Gaspar 36 Marketing 35 Esta clase de combinaciones son usadas pocas veces, generalmente se les agregan condiciones de filtrado con la sentencia WHERE para hallar resultados específicos. Combinación externa (OUTER JOIN): Mediante esta operación no se requiere que cada registro en las tablas a tratar tenga un registro equivalente en la otra tabla. El registro es mantenido en la tabla combinada así no existe otro registro que le corresponda. Este tipo de operación se subdivide dependiendo de la tabla a la cual se le admitirán los registros que no tienen correspondencia, ya sean de tabla izquierda, de tabla derecha, o combinación completa. De tabla izquierda (LEFT OUTER JOIN o LEFT JOIN): El resultado de esta operación siempre contiene todos los registros de la tabla de la izquierda (la primera tabla que se menciona en la consulta), aun cuando no exista un registro correspondiente en la tabla de la derecha, para uno de la izquierda. La sentencia LEFT OUTER JOIN retorna la pareja de todos los valores de la tabla izquierda con los valores de la tabla de la derecha correspondientes, o retorna un valor nulo NULL en caso de no correspondencia. A diferencia del resultado presentado en los ejemplos A y B (de combinación interna) donde no se mostraba el empleado cuyo departamento no existía; en el siguiente ejemplo se presentarán los empleados con su respectivo departamento, e inclusive se presentará el empleado, cuyo departamento no existe. G. Ejemplo de tabla izquierda para la combinación externa: SELECT distinct * FROM empleado LEFT OUTER JOIN departamento ON empleado.IDDepartamento = departamento.IDDepartamento;

Empleado.Apellido Empleado.IDdepartamento departamento.NombreDepartamento departamento.IDDepartamento

Jordán 33 Ingeniería 33 Rafferty 31 Ventas 31 Róbinson 34 Producción 34 Smith 34 Producción 34 Gaspar 36 NULL NULL Steinberg 33 Ingeniería 33 de tabla derecha (RIGHT OUTER JOIN o RIGHT JOIN): Esta operación inversa a la anterior; el resultado de esta operación siempre contiene todos los registros de la tabla de la derecha (la segunda tabla que se menciona en la consulta), aun cuando no exista un registro correspondiente en la tabla de la izquierda, para uno de la derecha. La sentencia RIGHT OUTER JOIN retorna la pareja de todos los valores de la tabla derecha con los valores de la tabla de la izquierda correspondientes, o retorna un valor nulo NULL en caso de no correspondencia. H. Ejemplo de tabla derecha para la combinación externa: SELECT * FROM empleado RIGHT OUTER JOIN departamento ON empleado.IDDepartamento = departamento.IDDepartamento Empleado.Apellido Empleado.IDdepartamento departamento.NombreDepartamento departamento.IDDepartamento

Smith 34 Producción 34 Jordán 33 Ingeniería 33 Róbinson 34 Producción 34 Steinberg 33 Ingeniería 33 Rafferty 31 Ventas 31 NULL NULL Marketing 35 En este caso el área de Marketing fue presentada en los resultados, aunque aún no hay empleados registrados en dicha área. combinación completa (FULL OUTER JOIN): Esta operación presenta los resultados de tabla izquierda y tabla derecha aunque no tengan correspondencia en la otra tabla. La tabla combinada contendrá, entonces, todos los registros de ambas tablas y presentará valores nulos NULLs para registros sin pareja. I. Ejemplo de combinación externa completa: SELECT * FROM empleado FULL OUTER JOIN departamento ON empleado.IDDepartamento= departamento.IDDepartamento

Empleado.Apellido Empleado.IDdepartamento departamento.NombreDepartamento departamento.IDDepartamento

Smith 34 Producción 34 Jordán 33 Ingeniería 33 Róbinson 34 Producción 34 Gaspar 36 NULL NULL Steinberg 33 Ingeniería 33 Rafferty 31 Ventas 31 NULL NULL Marketing 35 Como se puede notar, en este caso se encuentra el empleado Gaspar con valor nulo en su área correspondiente, y se muestra además el departamento de Marketing con valor nulo en los empleados de esa área. Algunos sistemas de bases de datos no soportan esta funcionalidad, pero esta puede ser emulada a través de las combinaciones de tabla izquierda, tabla derecha y de la setencia de union union. J. El mismo ejemplo puede expresarse así: SELECT * FROM empleado LEFT JOIN departamento ON empleado.IDDepartamento = departamento.IDDepartamento UNION SELECT * FROM empleado RIGHT JOIN departamento ON empleado.IDDepartamento = departamento.IDDepartamento WHERE empleado.IDDepartamento IS NULL ALGUNAS FUNCIONES QUE EXISTEN EN SQL Entre las funciones que manejan cadenas de caracteres se encuentran las siguientes:

cad1||cad2: Concatena las cadenas de caracteres. Los nombres de columnas son cadenas validas.

LENGTH (cadena): Encuentra la longitud de la cadena.

SUBSTR (cad,posic_inicial [,long]): Encuentra la subcadena de "long" caracteres, empezando en "posic_inicial".

UPPER (cadena): Cambia los caracteres minúsculas por mayúsculas.

LOWER (cadena): Cambia los caracteres mayúsculas por minúsculas.

TO_NUMBER (cadena): Convierte los datos carácter (compuestos por números) en valore numéricos.

TO_CHAR (cadena): Convierte un campo numérico en tipo carácter.

LPAD (cadena,long_n [,carac]): Rellena la izquierda de la cadena con el "carac" especificado (por defecto blancos), hasta que la longitud de la cadena nueva sea "long_n".

RPAD (cad,long_n [,caract]): Rellena la derecha de la cadena con el "carac" especificado (por defecto blancos), hasta que la longitud de la cadena nueva sea "long_n".

NVL (cad1,cad2): Si cad1 es nulo retorna cad2. En otro caso retorna cad1.

DECODE (cadena,caso1,rcad1, .,defecto): La salida es el resultado "rcad1" donde la cadena iguale la ocurrencia de caso1 y asi sucesivamente. El último argumento es el valor por defecto.

INITCAP(cadena): Vuelve mayúscula la letra inicial de la cadena.

LTRIM(cadena,grupo): Va removiendo de izquierda a derecha los caracteres que se encuentran en el grupo.

RTRIM(cadena,grupo): Va removiendo de derecha a izquierda los caracteres que se encuentran en el grupo.

TO_CHAR (fecha,formato): Convierte una fecha a cadena de caracteres para poder imprimirse.

TO_DATE (fecha,formato): Convierte cadena de caracteres a fechas.

sql

Documents