Oracle básico (II): Creación y manejo de tablas

Con el artículo anterior iniciamos una entrega de Oracle Básico comenzando con el tema de creación y manejo de tablas. Ahora pasaremos a estudiar la consulta y selección de registros con el lenguaje estándar para bases de datos relacionales SQL (Structured Query Languague = Lenguaje de Consulta estructurado).

La ventaja principal del SQL, desde mi punto de vista, es su capacidad de combinar sencillez y facilidad con potencia y eficiencia, conteniendo un conjunto de herramientas que optimizan las consultas.

Vale la pena destacar que, aunque los conceptos a estudiar son específico de ORACLE, también son útiles para cualquier programador que esté trabajando con algún software que contenga SQL.

Sentencia SELECT

Como ya sabemos, la herramienta fundamental de SQL es la sentencia SELECT, que permite seleccionar registros desde las tablas de la Base de Datos, devolviendo aquellos que cumplan las condiciones establecidas y pudiendo presentar el resultado en el orden deseado.

Primeramente estudiaremos la forma básica de la sentencia SELECT, que esta formado por:

SELECT   Lista...  
FROM     Tabla, Tabla...  
WHERE    Condiciones...  
       ORDER BY Expresión,Expresión,... 
       ;      Fin de la sentencia.  

Donde:

La orden SELECT puede contener:

-      Columnas: nombre, telefono

-      Expresiones y funciones: SYSDATE-fecha,UPPER(direccion)

-      Pseudo-Columnas del Sistema: SYSDATE,USER.

-      Asterisco: Todas las columnas.

La orden FROM identifica la lista de tablas a consultar. Si alguna de las tablas a consultar no es propiedad del usuario, debe especificarse el nombre del propietario antes que el nombre de la tabla en la forma nombre_propietario.nombre_tabla.

La orden WHERE decide los registros a seleccionar según las condiciones establecidas, limitando el número de registros que se muestran.

La orden ORDER BY indica el orden en que aparece el resultado de la consulta.

Ilustremos lo explicado hasta el momento con el ejemplo del fuente 1, donde consultaremos las ventas realizadas en los últimos 10 días, mostrando el nombre del cliente, artículo vendido y su valor.

Fuente 1
SELECT nombre,articulo,valor  Lista nombre del cliente, nombre del artículo y el valor de la venta. 
    FROM                        clientes,ventas  
                                Tablas con la información de clientes y ventas.     WHERE clientes.codigo=ventas.codigo 
                                Establece la relación, según código de cliente,                                 entre las tablas clientes y ventas. 
           and sysdate-ventas.fecha>=10 
                                Consulta las ventas de los últimos 10 días.     ORDER BY nombre             Ordenar el listado por nombre del cliente. ;                               Fin de la sentencia.

El resultado de esta sentencia SELECT sería el de la tabla 1:

Tabla 1: Resultados de la sentencia SELECT del fuente 1

Obsérvese que las columnas que tienen el mismo nombre en ambas tablas se diferencian escribiendo el nombre de la tabla antes que el nombre de la columna, como en el caso de ventas.fecha, ventas.codigo y clientes.codigo.

Operadores lógicos

Para construir la condición de la consulta necesitamos conocer los operadores lógicos, por eso a continuación damos una lista de los operadores más usados, agrupados en cuatro grupos:

1.    Valor único:Comprueban un valor simple.

2.    Lista de valores:Comprueban más de un valor.

3.    Combinaciones lógicas:Combinan expresiones lógicas.

4.    Negación:Invierte el resultado de la expresión con operadores de valor único o de lista de valores.

Valor único

>  <   >=  <=  =

Operadores clásicos de comparación:

mayor, menor, mayor e igual, menor e igual, igual a.

!=  <> ^=

Operador "Distinto de" en sus tres formas.

IS NULL     

Comprueba la ausencia de datos (valor nulo).

No se puede usar la comparación = NULL.

LIKE

Selecciona registros según el reconocimiento de un patrón de consulta.

Lista de valores

BETWEEN valor AND valor  

Comprueba que el valor se encuentre en el rango de valores.

IN (valor,...,valor)        

Verifica si el valor pertenece a la lista de valores.

Combinaciones lógicas.

AND  

Retorna Verdadero si todas las condiciones son verdaderas.

OR

Retorna Verdadero si alguna de las condiciones es verdadera.

Negación

NOT

Invierte el resultado de una expresión lógica, por ejemplo.

IS NOT NULL  
NOT BETWEEN valor AND valor  
NOT IN  (valor,...,valor)  
NOT LIKE

A continuación mostramos algunas consultas con el uso de diferentes operadores lógicos:

-      Clientes a los que no se les ha registrado su dirección.

SELECT nombre,telefono  
FROM    clientes  
WHERE  direccion IS NULL;  

-      Clientes dados de alta en los últimos 10 días.

SELECT nombre,direccion,telefono  
FROM   clientes  
WHERE  fecha BETWEEN sysdate-10  
       AND sysdate;  

-      Datos de los Clientes que pertenecen a una lista de clientes.

SELECT nombre,direccion,telefono  
FROM   clientes  
WHERE  nombre  
       IN ('PINTURAS','CASA DE DEPORTES') ;

-      Clientes dados de altas en lo que va del mes y cuyo nombre comience con la letra P u otra letra mayor o su teléfono contenga el código (0567). Ver fuente 2

Fuente 2 
SELECT nombre,direccion,telefono,fecha  
FROM   clientes  
WHERE   
fecha BETWEEN  
       to_date('01/'||to_char(sysdate,'MM/YY'),'DD/MM/YY') 
       AND  
             sysdate 
       AND (nombre  >= 'P' OR  telefono LIKE '%(0567)%');  

El manejo de fecha es una de las capacidades de mayor variedad e interés en ORACLE por las posibilidades que presenta en el almacenamiento, cálculo y presentación de fechas. Por eso, en el último ejemplo damos un vistazo a algunas funciones útiles en el uso de fechas como son:

to_char(sysdate,'MM/YY')  

Devuelve una cadena de caracteres de la forma mes/año de la fecha actual.

'01/'||to_char(sysdate,'MM/YY')  

Forma la cadena de caracteres 01/mes/año que representa el primer día del mes. El operador || se usa para unir o concatenar cadenas de caracteres.

 . . .

to_date('01/'||to_char(sysdate, 
                       'MM/YY' 
                      ), 
        'DD/MM/YY') . . .  

Convierte la cadena de caracteres 01/mes/año al tipo fecha.

Patrón de consulta

Una de las herramientas lógicas más poderosas de SQL es el reconocimiento de un patrón de consulta, instrumento éste que permite la búsqueda por nombre, dirección u otro dato parcialmente recordado. Los patrones de consulta juegan un papel importante en el momento de realizar consultas, ya que es común que necesitemos encontrar un texto y no recordemos exactamente cómo fue ingresado. Con el uso del operador LIKE podemos comparar patrones y ubicar un texto, independientemente de la posición en que se encuentre.

Para la definición del patrón de consulta existen dos tipos de caracteres especiales:

% (signo de porcentaje)      llamado comodín, representa cualquier cantidad de espacios o caracteres en esa posición. Significa que se admite cualquier cosa en su lugar: un caracter, cien caracteres o ningún caracter.

_ (signo de subrayado)       llamado marcador de posición, representa exactamente una posición e indica que puede existir cualquier caracter en esa posición.

En los fuentes 3, 4 y 5 detallamos tres ejemplos de consulta con el Operador LIKE:

Listar los clientes cuya dirección contengan la palabra URUGUAY independientemente de su ubicación:

Fuente 3  

SELECT nombre,direccion,telefono  
FROM   clientes  
WHERE  direccion LIKE '%URUGUAY%';  
Listar los clientes  cuyos teléfonos tienen comienzan con el código de área el 0722  

SELECT nombre,direccion,telefono  
FROM   clientes  
WHERE  telefono LIKE '(0722)%';

Listar los clientes cuyo nombre terminan con la palabra LIBRO:

Fuente 4  

SELECT nombre,direccion,telefono  
FROM   clientes  
WHERE  nombre LIKE '%LIBRO';

Listar los clientes que tengan la palabra LIBRO a partir de la 5ª posición en el nombre.

Fuente 5  

SELECT nombre,direccion,telefono  
FROM   clientes  
WHERE  nombre LIKE '____LIBRO%';

Agrupamiento de datos

SQL proporciona una forma eficiente para manejar la información con el agrupamiento de datos a través de la formación de grupos y las funciones correspondientes, dando la posibilidad de procesar no solo registros individuales como hemos hecho hasta ahora. También podemos agrupar registros por un criterio determinado, como por ejemplo, agrupar por clientes las ventas realizadas.

Cada grupo tendrá como resultado de la consulta una fila resumen que contiene la información del grupo.

Para la formación de grupos adicionamos, a la forma básica de la sentencia SELECT vista anteriormente, la orden GROUP BY ubicada antes de ORDER BY, como se muestra a continuación:

SELECT     Lista...  
FROM       Tabla, Tabla...  
WHERE      Condiciones  
GROUP BY   Expresión, Expresión,... 
           ORDER BY Expresión, Expresión,...  
;          Terminador  

Las funciones para el procesamiento de grupos son:

COUNT(columna)      Cantidad de registros en que la columna tiene valores no nulos.

COUNT(*)                 Cantidad de registros que hay en la tabla, incluyendo los valores nulos.

MIN(columna)           Valor mínimo del grupo.

MAX(columna)          Valor máximo del grupo.

SUM(columna)         Suma los valores del grupo.

AVG(columna)          Calcula valor medio del grupo, sin considerar los valores nulos.

La lista de columnas a mostrar en la consulta puede contener las funciones de grupo, así como la columna o expresión usada para formar los grupos en la orden GROUP BY. En una misma consulta no se pueden mezclar funciones de grupo con columnas o funciones que trabajan con registros individuales.

Las ventas por cliente es un buen ejemplo para mostrar el uso de los grupos. En el siguiente caso se hace un resumen de ventas por cliente, con la cantidad de ventas, valor mínimo, medio y máximo, así como la suma total de ventas. La formación del grupo será por el nombre del cliente y la columna a cuantificar para cada grupo será el valor de las ventas.

SELECT nombre "CLIENTE", 
     COUNT(valor) "VENTAS", 
     MIN(valor) "MINIMA", 
     AVG(valor) "MEDIA", 
     MAX(valor) "MAXIMA", 
     SUM(VALOR) "TOTAL" 
   FROM clientes,ventas 
   WHERE clientes.codigo=ventas.codigo 
   GROUP BY nombre ;  

Tabla 2: Estadística de la base de datos

Como se puede observar en la tabla 2, a cada columna se le asignó un título de cabecera, que se escribe entre comillas dobles a la derecha de la columna, con el objetivo de mejorar la presentación de la consulta.

El orden en las consultas por grupos, cuando no esta presente la orden ORDER BY, está dado por la columna que forma los grupos. Si deseamos cambiar ese orden, como es el caso de ordenar por el valor total de ventas, se debe adicionar al final la orden ORDER BY SUM(VALOR).

En el ejemplo del fuente 6 se forman grupos por artículo para obtener la cantidad de ventas, valor mínimo, medio y máximo, así como la suma total de ventas para cada artículo, ordenado de mayor a menor por la venta total.

Fuente 6  

SELECT articulo, COUNT(valor) "VENTAS",MIN(valor) "MINIMA", 
    AVG(valor) "MEDIA",MAX(valor) "MAXIMA",SUM(VALOR) "TOTAL" 
    FROM ventas 
    GROUP BY articulo 
    ORDER BY