4.3.1. Cálculos Aritméticos¶

Los operadores + (suma), - (resta), * (multiplicación) y / (división), se pueden utilizar para hacer cálculos en las consultas. Cuando se utilizan como expresión en una consulta SELECT, no modifican los datos originales.

Ejemplo:

-- Consulta con 3 columnas
SELECT nombre, precio, precio*1.16 FROM ARTICULOS;


-- Ponemos un alias a la tercera columna.
-- Las comillas dobles en el alias hacen que se respeten mayúsculas y minúsculas,
-- de otro modo siempre aparece en mayúsculas
SELECT nombre, precio, precio*1.16 AS "Precio + IVA" FROM ARTICULOS;

La prioridad de esos operadores es: tienen más prioridad la multiplicación y división, después la suma y la resta. En caso de igualdad de prioridad, se realiza primero la operación que esté más a la izquierda. Como es lógico se puede evitar cumplir esa prioridad usando paréntesis; el interior de los paréntesis es lo que se ejecuta primero. Cuando una expresión aritmética se calcula sobre valores NULL, el resultado de la expresión es siempre NULL.

4.3.2. Concatenación¶

El operador || es el de la concatenación. Sirve para unir textos.

Ejemplo:

SELECT tipo, modelo, tipo || '-' || modelo "Clave Pieza" FROM PIEZAS;

El resultado de esa consulta tendría esta estructura:

4.3.3. Condiciones¶

Se puede realizar consultas que restrinjan los datos de salida de las tablas. Para ello se utiliza la cláusula WHERE. Esta cláusula permite colocar una condición que han de cumplir todos los registros que queremos que se muestren. Las filas que no la cumplan no aparecerán en la ejecución de la consulta.

Ejemplo:

-- Tipo y modelo de las piezas cuyo precio es mayor que 3
SELECT tipo, modelo
FROM PIEZAS
WHERE precio > 3;

4.3.3.1. Operadores de comparación¶

Los operadores de comparación que se pueden utilizar en la cláusula WHERE son:

Se pueden utilizar tanto para comparar números como para comparar textos y fechas. En el caso de los textos, las comparaciones se hacen en orden alfabético. Sólo que es un orden alfabético estricto. Es decir el orden de los caracteres en la tabla de códigos. Así la letra Ñ y las vocales acentuadas nunca quedan bien ordenadas ya que figuran con códigos más altos. Las mayúsculas figuran antes que las minúsculas (la letra “Z” es menor que la “a”).

4.3.3.2. Operadores lógicos¶

Son:

Ejemplos:

-- Personas entre 25 y 50 años
SELECT nombre, apellidos
FROM PERSONAS
WHERE edad >= 25 AND edad <= 50;

-- Personas de más de 60 y menos de 20
SELECT nombre, apellidos
FROM PERSONAS
WHERE edad > 60 OR edad < 20;

-- Selección de las piezas cuyo precio está entre 3 y 8
-- (ambos valores incluidos)
SELECT tipo, modelo, precio
FROM PIEZAS
WHERE precio BETWEEN 3 AND 8;

-- Selección de las piezas cuyo precio sea menor que 3 o mayor que 8
-- (los de precio 3 y precio 8 no estarán incluidos)
SELECT tipo, modelo, precio
FROM PIEZAS
WHERE precio NOT BETWEEN 3 AND 8;

-- Selección de las piezas cuyo precio sea igual a 3, 5 u 8
SELECT tipo, modelo, precio
FROM PIEZAS
WHERE precio IN ( 3,5,8 );

-- Selección de las piezas cuyo precio no sea igual a 3, 5 u 8
SELECT tipo, modelo, precio
FROM PIEZAS
WHERE precio NOT IN ( 3,5,8 );

4.3.3.5. LIKE¶

El operador LIKE se usa sobre todo con textos, permite obtener registros cuyo valor en un campo cumpla una condición textual. LIKE utiliza una cadena que puede contener estos símbolos:

Ejemplos:

-- Selección el nombre de las personas que empiezan por A
SELECT nombre
FROM PERSONAS
WHERE nombre LIKE 'A%';

-- Selección el nombre y los apellidos de las personas
cuyo primer apellido sea Jiménez, Giménez, Ximénez
SELECT nombre, apellido1, apellido2
FROM PERSONAS
WHERE apellido1 LIKE '_iménez';

Si queremos que en la cadena de caracteres se busquen los caracteres “%” o “_” le anteponemos el símbolo escape:

Ejemplo:

-- Seleccionamos el tipo, el modelo y el precio de las piezas
-- cuyo porcentaje de descuento sea 3%
SELECT tipo, modelo, precio
FROM PIEZAS
WHERE descuento LIKE '3\%' ESCAPE '\';

-- Devuelve el nombre y los apellidos de las personas que NO tienen teléfono
SELECT nombre, apellido1, apellido2
FROM PERSONAS
WHERE telefono IS NULL;

-- Devuelve el nombre y los apellidos de las personas que SÍ tienen teléfono
SELECT nombre, apellido1, apellido2
FROM PERSONAS
WHERE telefono IS NOT NULL;

4.3.3.7. Precedencia de operadores¶

A veces las expresiones que se producen en los SELECT son muy extensas y es difícil saber que parte de la expresión se evalúa primero, por ello se indica la siguiente tabla de precedencia:

4.6.1. Funciones de caracteres¶

Para convertir el texto a mayúsculas o minúsculas:

En la siguiente tabla mostramos las llamadas funciones de transformación:

4.6.2. Funciones numéricas¶

Funciones para redondear el número de decimales o redondear a números enteros:

En el siguiente cuadro mostramos la sintaxis SQL de funciones matemáticas habituales:

4.6.3. Funciones de fecha¶

Las fechas se utilizan muchísimo en todas las bases de datos. Oracle proporciona dos tipos de datos para manejar fechas, los tipos DATE y TIMESTAMP. En el primer caso se almacena una fecha concreta (que incluso puede contener la hora), en el segundo caso se almacena un instante de tiempo más concreto que puede incluir incluso fracciones de segundo. Hay que tener en cuenta que a los valores de tipo fecha se les pueden sumar números y se entendería que esta suma es de días. Si tiene decimales entonces se suman días, horas, minutos y segundos. La diferencia entre dos fechas también obtiene un número de días.

Funciones para obtener la fecha y hora actual

Funciones para calcular fechas:

4.6.4. Funciones de conversión¶

Oracle es capaz de convertir datos automáticamente a fin de que la expresión final tenga sentido. En ese sentido son fáciles las conversiones de texto a número y viceversa.

Ejemplos:

-- El resultado es 8
SELECT 5+'3'
FROM DUAL;

-- El resultado es 53
SELECT 5||'3'
FROM DUAL;

Pero en determinadas ocasiones querremos realizar conversiones explícitas. Para hacerlo utilizaremos las funciones que se detallan a continuación.

4.6.4.1. Función de conversión TO_CHAR¶

Obtiene un texto a partir de un número o una fecha. En especial se utiliza con fechas (ya que de número a texto se suele utilizar de forma implícita). En el caso de las fechas se indica el formato de conversión, que es una cadena que puede incluir estos símbolos (en una cadena de texto):

Ejemplo:

-- Si esta consulta se ejecuta el 20 de Febrero de 2014 a las 14:15 horas,
-- devuelve:  20/FEBRERO/2014, JUEVES 14:15:03
SELECT TO_CHAR(SYSDATE, 'DD/MONTH/YYYY, DAY HH:MI:SS')
FROM DUAL;

Para convertir números a textos se usa esta función cuando se desean características especiales. En este caso en el formato se pueden utilizar estos símbolos:

4.6.4.2. Función de conversión TO_NUMBER¶

Convierte textos en números. Se indica el formato de la conversión.

4.6.5. Función DECODE¶

Se evalúa una expresión y se colocan a continuación pares valor,resultado de forma que si se la expresión equivale al valor, se obtiene el resultado indicado. Se puede indicar un último parámetro con el resultado a efectuar en caso de no encontrar ninguno de los valores indicados. Sintaxis:

DECODE (
   expresión, valor1, resultado1
           [, valor2, resultado2] ...
           [, valorPorDefecto]
);

Ejemplo:

SELECT
  DECODE (cotización, 1, salario*0.85,
                      2, salario*0.93,
                      3, salario*0.96,
                      salario)
FROM EMPLEADOS;

Este ejemplo es idéntico al mostrado con una expresión CASE.

4.6.6. Expresión CASE¶

Es una instrucción incorporada a la versión 9 de Oracle que permite establecer condiciones de salida (al estilo if-then-else de muchos lenguajes).

CASE expresión
  WHEN valor1 THEN resultado1
  [WHEN valor2 THEN resultado2] ...
  [ELSE resultado_por_defecto]
END;

El funcionamiento es el siguiente:

1. Se evalúa la expresión indicada.
2. Se comprueba si esa expresión es igual al valor del primer WHEN, de ser así se devuelve el primer resultado (cualquier valor excepto nulo).
3. Si la expresión no es igual al valor 1, entonces se comprueba si es igual al segundo. De ser así se escribe el resultado 2. De no ser así se continua con el siguiente WHEN.
4. El resultado indicado en la zona ELSE sólo se escribe si la expresión no vale ningún valor de los indicados.

Ejemplo:

SELECT
  CASE cotización
    WHEN 1 THEN salario*0.85
    WHEN 2 THEN salario*0.93
    WHEN 3 THEN salario*0.96
    ELSE salario
  END
FROM EMPLEADOS;

4.7.1. Funciones de cálculo con grupo (o funciones colectivas)¶

Lo interesante de la creación de grupos es la posibilidad de cálculo que ofrece. Para ello se utilizan las funciones que permiten trabajar con los registros de un grupo. Estas son:

Las funciones anteriores se aplicarán sobre todos los elementos del grupo. Así, por ejemplo, podemos calcular la suma de las cantidades para cada tipo y modelo de la tabla EXISTENCIAS. (Es como si lo hiciéramos manualmente con las antiguas fichas sobre papel: primero las separaríamos en grupos poniendo juntas las que tienen el mismo tipo y modelo y luego para cada grupo sumaríamos las cantidades). La sintaxis SQL de dicha consulta quedaría:

SELECT tipo, modelo, SUM(cantidad)
FROM EXISTENCIAS
GROUP BY tipo, modelo;

Y se obtiene el siguiente resultado, en el que se suman las cantidades para cada grupo.

4.7.2. Condiciones HAVING¶

A veces se desea restringir el resultado de una función agrupada y no aplicarla a todos los grupos. Por ejemplo, imaginemos que queremos realizar la consulta anterior, es decir queremos calcular la suma de las cantidades para cada tipo y modelo de la tabla EXISTENCIAS, pero queremos que se muestren solo los registros en los que la suma de las cantidades calculadas sean mayor que 500. si planteáramos la consulta del modo siguiente:

SELECT tipo, modelo, SUM(cantidad)
FROM EXISTENCIAS
WHERE SUM(cantidad) > 500
GROUP BY tipo, modelo;

Habríamos ejecutado una consulta ERRÓNEA.

ERROR  en línea 3:
ORA-00934: función de grupo no permitida aquí

La razón es que Oracle calcula primero el WHERE y luego los grupos; por lo que esa condición no la puede realizar al no estar establecidos los grupos. Es decir, no puede saber que grupos tienen una suma de cantidades mayor que 500 cuando todavía no ha aplicado los grupos. Por ello se utiliza la cláusula HAVING, cuya ejecución se efectúa una vez realizados los grupos. Se usaría de esta forma:

SELECT tipo, modelo, SUM(cantidad)
FROM EXISTENCIAS
GROUP BY tipo, modelo
HAVING SUM(cantidad) > 500;

Ahora bien, esto no implica que con la cláusula GROUP BY no podamos emplear un WHERE. Esta expresión puede usarse para imponer condiciones sobre las filas de la tabla antes de agrupar. Por ejemplo, la siguiente expresión es correcta:

SELECT tipo, modelo, SUM(cantidad)
FROM EXISTENCIAS
WHERE tipo='AR'
GROUP BY tipo, modelo
HAVING SUM(cantidad) > 500;

De la tabla EXISTENCIAS tomará solo aquellas filas cuyo tipo sea AR, luego agrupará según tipo y modelo y dejará sólo aquellos grupos en los que SUM(cantidad)>500 y por último mostrará tipo, modelo y la suma de las cantidades para aquellos grupos que cumplan dicha condición. En definitiva, el orden de ejecución de la consulta marca lo que se puede utilizar con WHERE y lo que se puede utilizar con HAVING.

Pasos en la ejecución de una consulta SELECT el gestor de bases de datos sigue el siguiente orden:

1. Se aplica la cláusula FROM, de manera que determina sobre que tablas se va a ejecutar la consulta.
2. Se seleccionan las filas deseadas utilizando WHERE. (Solo quedan las filas que cumplen las condiciones especificadas en el WHERE).
3. Se establecen los grupos indicados en la cláusula GROUP BY.
4. Se calculan los valores de las funciones de totales o colectivas que se especifiquen en el HAVING (COUNT, SUM, AVG,…)
5. Se filtran los registros que cumplen la cláusula HAVING
6. Se aplica la cláusula SELECT que indica las columnas que mostraremos en la consulta.
7. El resultado se ordena en base al apartado ORDER BY.

4.8.1. Producto cruzado o cartesiano de tablas¶

En el ejemplo anterior si quiere obtener una lista de los datos de las tareas y los empleados, se podría hacer de esta forma:

SELECT cod_tarea, descripción_tarea, dni_empleado, nombre_empleado
FROM TAREAS, EMPLEADOS;

Aunque la sintaxis es correcta ya que, efectivamente, en el apartado FROM se pueden indicar varias tareas separadas por comas, al ejecutarla produce un producto cruzado de las tablas. Es decir, aparecerán todos los registros de las tareas relacionados con todos los registros de empleados (y no para cada empleado sus tareas específicas). El producto cartesiano pocas veces es útil para realizar consultas. Nosotros necesitamos discriminar ese producto para que sólo aparezcan los registros de las tareas relacionadas con sus empleados correspondientes. A eso se le llama asociar tablas (join) y se ve en el siguiente apartado.

4.8.2. Asociando tablas¶

La forma de realizar correctamente la consulta anterior (asociado las tareas con los empleados que la realizaron) sería:

SELECT cod_tarea, descripción_tarea, dni_empleado, nombre_empleado
FROM TAREAS, EMPLEADOS
WHERE TAREAS.dni_empleado = EMPLEADOS.dni_empleado;

Nótese que se utiliza la notación tabla.columna para evitar la ambigüedad, ya que el mismo nombre de campo se puede repetir en ambas tablas. Para evitar repetir continuamente el nombre de la tabla, se puede utilizar un alias de tabla:

SELECT T.cod_tarea, T.descripción_tarea, E.dni_empleado, E.nombre_empleado
FROM TAREAS T, EMPLEADOS E
WHERE T.dni_empleado = E.dni_empleado;

A la sintaxis WHERE se le pueden añadir condiciones sin más que encadenarlas con el operador AND.

Ejemplo:

SELECT T.cod_tarea, T.descripción_tarea
FROM TAREAS T, EMPLEADOS E
WHERE T.dni_empleado = E.dni_empleado AND E.nombre_empleado = 'Javier';

Finalmente indicar que se pueden enlazar más de dos tablas a través de sus claves principales y foráneas. Por cada relación necesaria entre tablas, aparecerá una condición (igualando la clave principal y la foránea correspondiente) en el WHERE.

Ejemplo:

SELECT T.cod_tarea, T.descripción_tarea, E.nombre_empleado, U.nombre_utensilio
FROM TAREAS T, EMPLEADOS E, UTENSILIOS U
WHERE T.dni_empleado = E.dni_empleado AND T.cod_tarea = U.cod_tarea;

4.8.3. Relaciones sin igualdad¶

A las relaciones descritas anteriormente se las llama relaciones en igualdad (equijoins), ya que las tablas se relacionan a través de campos que contienen valores iguales en dos tablas. A veces esto no ocurre, en las tablas:

En el ejemplo anterior podríamos averiguar la categoría a la que pertenece cada empleado, pero estas tablas poseen una relación que ya no es de igualdad.

La forma sería:

SELECT E.empleado, E.sueldo, C.categoria
FROM EMPLEADOS E, CATEGORÍAS C
WHERE E.sueldo BETWEEN C.sueldo_mínimo AND C.sueldo_máximo;

4.8.4. Combinación de tablas (JOIN)¶

Existe otra forma más moderna e intuitiva de trabajar con varias tablas. Para ello se utiliza la clausula JOIN. Supongamos que tenemos una base de datos de una entidad bancaria. Disponemos de una tabla con sus empleados y otra tabla con sus sucursales. En una sucursal trabajan varios empleados. Los empleados viven en una localidad y trabajan en una sucursal situada en la misma localidad o en otra localidad. El esquema E-R es el siguiente:

Los datos de las tablas son:

Se observa que Ana vive en Almería y trabaja en la sucursal 0001 situada en Almería. Bernardo vive en Granada pero trabaja en la sucursal 0001 de Almería. Carlos es un empleado del que no disponemos el dato acerca de la sucursal en la que trabaja. David es un empleado que vive en Jerez de la Frontera y trabaja en la sucursal 0003 en Cádiz. Existe otra sucursal 0002 en Granada donde no aparece registrado ningún empleado.

Existen diversas formas de combinar (JOIN) las tablas según la información que deseemos obtener. Los tipos de JOIN se clasifican en:

• INNER JOIN ( o simplemente JOIN): Combinación interna.
  • JOIN
  • SELF JOIN
  • NATURAL JOIN
• OUTER JOIN: Combinación externa.
  • LEFT OUTER JOIN (o simplemente LEFT JOIN)
  • RIGHT OUTER JOIN (o simplemente RIGHT JOIN)
  • FULL OUTER JOIN (o simplemente FULL JOIN)
• CROSS JOIN: Combinación cruzada.

Pasamos a continuación a explicar cada uno de ellos.

SELECT TABLA1.columna1, TABLA1.columna2, ...
       TABLA2.columna1, TABLA2.columna2, ...
FROM TABLA1 JOIN TABLA2
ON TABLA1.columnaX = TABLA2.columnaY;

SELECT E.*, S.LOCALIDAD
FROM EMPLEADOS E JOIN SUCURSALES S
ON E.COD_SUCURSAL = S.COD_SUCURSAL;

SELECT ALIAS1.columna1, ALIAS1.columna2, ..., ALIAS2.columna1, ...
FROM TABLA ALIAS1 JOIN TABLA ALIAS2
ON ALIAS1.columnaX = ALIAS2.columnaY;

SELECT E1.NOMBRE, E2.NOMBRE, E1.LOCALIDAD
FROM EMPLEADOS E1 JOIN EMPLEADOS E2 ON E1.LOCALIDAD = E2.LOCALIDAD;

SELECT TABLA1.columna1, TABLA1.columna2, ...
       TABLA2.columna1, TABLA2.columna2, ...
FROM TABLA1 NATURAL JOIN TABLA2;

SELECT *
FROM EMPLEADOS E NATURAL JOIN SUCURSALES S;

SELECT TABLA1.columna1, TABLA1.columna2, ...
       TABLA2.columna1, TABLA2.columna2, ...
FROM TABLA1 LEFT JOIN TABLA2
ON TABLA1.columnaX = TABLA2.columnaY;

SELECT E.*, S.LOCALIDAD
FROM EMPLEADOS E LEFT JOIN SUCURSALES S
ON E.COD_SUCURSAL = S.COD_SUCURSAL;

SELECT TABLA1.columna1, TABLA1.columna2, ...
       TABLA2.columna1, TABLA2.columna2, ...
FROM TABLA1 RIGHT JOIN TABLA2
ON TABLA1.columnaX = TABLA2.columnaY;

SELECT E.DNI, E.NOMBRE, S.*
FROM EMPLEADOS E RIGHT JOIN SUCURSALES S
ON E.COD_SUCURSAL = S.COD_SUCURSAL;

SELECT TABLA1.columna1, TABLA1.columna2, ...
       TABLA2.columna1, TABLA2.columna2, ...
FROM TABLA1 FULL JOIN TABLA2
ON TABLA1.columnaX = TABLA2.columnaY;

SELECT E.DNI, E.NOMBRE, S.COD_SUCURSAL, S.LOCALIDAD
FROM EMPLEADOS E FULL JOIN SUCURSALES S
ON E.COD_SUCURSAL = S.COD_SUCURSAL;

SELECT TABLA1.columna1, TABLA1.columna2, ...
       TABLA2.columna1, TABLA2.columna2, ...
FROM TABLA1 CROSS JOIN TABLA2;

SELECT E.DNI, E.NOMBRE, E.LOCALIDAD, S.COD_SUCURSAL, S.LOCALIDAD
FROM EMPLEADOS E CROSS JOIN SUCURSALES S;

4.9.1. Uniones¶

La palabra UNION permite añadir el resultado de un SELECT a otro SELECT. Para ello ambas instrucciones tienen que utilizar el mismo número y tipo de columnas.

Ejemplo:

--  Tipos y modelos de piezas que se encuentren el almacén 1, en el 2 o en ambos.

SELECT tipo,modelo FROM existencias
WHERE n_almacen = 1

UNION

SELECT tipo,modelo FROM existencias
WHERE n_almacen = 2;

Es decir, UNION crea una sola tabla con registros que estén presentes en cualquiera de las consultas. Si están repetidas sólo aparecen una vez, para mostrar los duplicados se utiliza UNION ALL en lugar de la palabra UNION.

4.9.2. Intersecciones¶

De la misma forma, la palabra INTERSECT permite unir dos consultas SELECT de modo que el resultado serán las filas que estén presentes en ambas consultas. Ejemplo: tipos y modelos de piezas que se encuentren en los almacenes 1 y 2 (en ambos).

-- Tipos y modelos de piezas que se encuentren en los almacenes 1 y 2 (en ambos).

SELECT tipo,modelo FROM existencias
WHERE n_almacen = 1

INTERSECT

SELECT tipo,modelo FROM existencias
WHERE n_almacen = 2;

4.9.3. Diferencia¶

Con MINUS también se combinan dos consultas SELECT de forma que aparecerán los registros del primer SELECT que no estén presentes en el segundo. Ejemplo:

-- Tipos y modelos de piezas que se encuentren el almacén 1 y no en el 2.

SELECT tipo,modelo FROM existencias
WHERE n_almacen = 1

MINUS

SELECT tipo,modelo FROM existencias
WHERE n_almacen = 2;

Se podrían hacer varias combinaciones anidadas (una unión a cuyo resultado se restará de otro SELECT por ejemplo), en ese caso es conveniente utilizar paréntesis para indicar qué combinación se hace primero:

(
SELECT ...

UNION

SELECT ...
)

MINUS

SELECT ... ;

-- Primero se hace la unión y luego la diferencia