CAPÍTULO 4. Misceláneo de Tópicos

 
Índices 

 
Los índices permiten a DBMS acceder a los datos más rápidamente (esto no ocurre en todos los sistemas). El sistema crea esta estructura de datos interna (el índice) con la cual se puede seleccionar filas (cuando la selección se basa en columnas indexadas, esto se hace más rápidamente). Este índice le dice a la DBMS donde esta cierta fila dando el valor de una columna indexada, como un libro, cuyo índice te dice en que páginas aparece una cierta palabra. Vamos a crear un índice por el ID_Propietario en la tabla Propietarios_Antigüedades:

CREATE INDEX OID_IDX ON PROPIETARIOS_ANTIGÜEDADES (ID_PROPIETARIO);

Ahora en los nombres:

CREATE INDEX NAME_IDX ON PROPIETARIOS_ANTIGÜEDADES (APELLIDOPROPIETARIO, NOMBREPROPIETARIO);

Para borrar un índice, utiliza la sentencia DROP:

DROP INDEX OID_IDX;

Así mismo, también puedes "borrar" una tabla (DROP TABLE nombretabla). En el segundo ejemplo, el índice se mantine en las dos columnas, agregado junto.

Algunos DBMS no fuerzan la existencia de claves primarias; en otras palabra, la unicidad de una columna no es forzada automáticamente. Lo que significa que, por ejemplo, si intento insertar otra fila dentro de la tabla Propietarios_Antigüedades con el ID_Propietario de 02, algunos sistemas lo permitirán hacer, incluso, si esta columna es la única de la tabla (cada fila se supone que es diferente). Una forma de evitar esto es crear un único índice en la columna que queramos que sea la clave primaria para forzar al sistema a prohibir los duplicados.

CREATE UNIQUE INDEX OID_IDX ON PROPIETARIOS_ANTIGÜEDADES (ID_PROPIETARIO);

GROUP BY & HAVING

 
Un uso especial de GROUP BY es asociar una función agregada (especialmente COUNT) con grupos de filas. Primero, imagina que la tabla Antigüedades tiene la columna Precio, y que cada fila tiene un valor para esta columna. Queremos ver el precio del producto más caro comprado por cada comprador. Tenemos que decirle a SQL que agrupe cada tipo de compra, y nos diga la compra que tenga el máximo precio:

SELECT ID_COMPRADOR, MAX(PRECIO)
FROM ANTIGÜEDADES
GROUP BY ID_COMPRADOR;

Ahora, queremos decir que sólo queremos ver la precio máximo de la compra si éste es sobre $1000, así que usamos la cláusula HAVING:

SELECT ID_COMPRADOR, MAX(PRECIO)
FROM ANTIGÜEDADES
GROUP BY ID_COMPRADOR
HAVING PRECIO > 1000;

Más subconsultas

 
Otro uso común de las subconsultas involucra el uso de operadores para permitir a una condición WHERE incluir la salida de un Select de una subconsulta. Primero, lista los compradores que compraron un producto caro (el precio del producto es $100 mayor que la media de precio de todos los productos):

SELECT ID_COMPRADOR
FROM ANTIGÜEDADES
WHERE PRECIO
    (SELECT AVG(PRECIO) + 100
     FROM ANTIGÜEDADES);

La subconsulta calcula la media del Precio más $100, y usando esta figura, los ID_Propietario son impresos por cada producto que cuesta más. Se puede usar DISTINCT ID_PROPIETARIO, para eliminar duplicados.

Lista los apellidos de aquellos de la tabla Propietarios_Antigüedades SÓLO si han comprado un producto:

SELECT APELLIDOPROPIETARIO
FROM PROPIETARIOS_ANTIGÜEDADES
WHERE ID_PROPIETARIO IN
    (SELECT DISTINCT ID_COMPRADOR
     FROM ANTIGÜEDADES);

La subconsulta devuelve la lista de compradores, y el apellido es impreso para un antigüo propietario si y sólo si el ID_Propietario aparece en la lista de la subconsulta (también llamada lista de candidatos). Nota: en algunas DBMS, el igual puede ser usado de la misma forma que IN, aunque, por previsión, IN es una mejor elección.

Para un ejemplo de actualización, nosotros sabemos que el hombre que compró la librería tiene el Nombre equivocado en la base de datos, éste debería ser John:

UPDATE PROPIETARIOS_ANTIGÜEDADES
SET NOMBREPROPIETARIO = 'John'
WHERE ID_PROPIETARIO =
    (SELECT ID_COMPRADOR
     FROM ANTIGÜEDADES
     WHERE PRODUCTO = 'Librería');

Primero, la subconsulta encuentra el ID_comprador de la persona(s) que compró la librería, después la consulta de salida actualiza el apellido.

Recuerda esta regla sobre las subconsultas: cuando tienes una subconsulta como parte de una condición WHERE, la cláusula Selec en la subconsulta tiene que tener columnas que concuerden en número y tipo con aquellas que formen parte de la condición WHERE de la subconsulta. En otras palabras, si tienes "WHERE ColumnName = (SELECT...);", Select debe de tener sólo una columna en ella, para coincidir con la salida en la cláusula Where, y estas deberán de coincidir en tipo

EXISTS & ALL

 
EXISTS usa una subconsulta como condición, donde la condición es verdadera si la subconsulta devuelve alguna fila, y falsa si la subconsulta no devuelve ninguna fila; esta es una característica no intuitiva con sólo algunos usos. Sin embargo, si un empleado quiere ver la lista de Propietarios sólo si hay sillas disponibles, intenta:

SELECT NOMBREPROPIETARIO, APELLIDOPROPIETARIO
FROM PROPIETARIOS_ANTIGÜEDADES
WHERE EXISTS
    (SELECT *
     FROM ANTIGÜEDADES
     WHERE PRODUCTO = 'Silla');

Si hay alguna silla en la columna Antigüedades, la subconsulta devolverá una o varias filas, haciendo la cláusula EXISTS verdadera, haciendo que SQL liste los antigüos propietarios. Si no ha habido sillas, ninguna fila será devuelta por la subconsulta.

ALL es otra construcción poco usual , como las consultas ALL pueden ser usadas con diferentes y simples métodos, veamos un ejemplo de consulta:

SELECT ID_COMPRADOR, PRODUCTO
FROM ANTIGÜEDADES
WHERE PRECIO = ALL
    (SELECT PRECIO
     FROM ANTIGÜEDADES);

Esto devolverá el precio de producto más alto (o más de un producto si hay un empate), y su comprador. La subconsulta devuelve una lista de todos los precios de la tabla Antigüedades, y la consulta de salida va fila por fila de la tabla Antigüedades y si el precio es mayor o igual a todos (o ALL) precios en la lista, es listado, dando el precio del producto más caro. La razón de "=" es que el mayor precio en la lista puede ser igual al de la lista, ya que este producto está en la lista de precios.

UNION & Uniones de salida

Hay ocasiones donde puedes querer ver los resultados de múltiples consultas a la vez combinando sus salidas; usa UNION. Por ejemplo, si queremos ver todos los ID_COMPRADOR de la tabla de Antigüedades junto con los ID_PROPIETARIO de la tabla de PEDIDOS, usaremos:

SELECT ID_COMPRADOR
FROM ANTIGÜEDADES
UNION
SELECT ID_PROPIETARIO
FROM PEDIDOS;

SQL requiere que la lista de Select (de columnas) coincida, columna por columna, en el tipo de datos. En este caso ID_comprador y ID_Propietario son del mismo tipo (integer). Además, SQL elimina automáticamente los duplicados cuando se usa UNION (como si ellos fuera dos "conjuntos"); en las consultas simples, tienes que usar DISTINCT.

La unión de salida es usada cuando una consulta de unión está "unida" con filas no incluidas en la unión, y son especialmente útiles si las "flags" son incluidas. Primero observa la consulta:

SELECT ID_PROPIETARIO, 'está en ambos Pedidos & Antigüedades'
FROM PEDIDOS, ANTIGÜEDADES
WHERE ID_PROPIETARIO = ID_COMPRADOR
UNION
SELECT ID_COMPRADOR, 'sólo está en Antigüedades'
FROM ANTIGÜEDADES
WHERE ID_COMPRADOR NOT IN
    (SELECT ID_PROPIETARIO
     FROM PEDIDOS);

Esta consulta hace una unión para listar todos los propietarios que están en ambas tablas, y pone una línea etiqueta después de ID repitiendo la cita. La UNION une esta lista con al siguiente lista. La segunda lista es generada primero listando aquellos ID que no están en la tabla Pedidos, generando una lista de ID excluidos de la consulta de unión. Entonces, cada fila en la tabla Antigüedades es escaneada, y si el ID_comprador no está en esta lista de exclusión, es listado con su cita etiqueta. Debe haber un modo más sencillo de hacer esta lista, pero es difícil generar la informativa cita de texto.

Este concepto es muy útil en situaciones donde la clave primaria está relacionada con una clave ajena, pero el valor de la clave ajena para algunas claves primarias es NULL. Por ejemplo, en una tabla, la clave primaria es vendedor, y en otra tabla es clientes, con el nombre de los vendedores en la misma fila. Sin embargo, si un vendedor no tiene clientes, el nombre de esta persona no aparecerá en la tabla de clientes. La unión de salida es usada si el listado de todos los vendedores va ha ser impreso, junto con sus clientes, aunque el vendedor no esté en la tabla de clientes, pero está en la tabla de vendedores. En otro caso, el vendedor será listado con cada cliente.