A ve ahora el cambio anterior de B. B todavía no ve el cambio de A.
· B cierra y abre la tabla.
Ahora B puede ver los cambios de A
· B emite la instrucción “Select * from Mascotas where Edad=4”
B obtiene los datos de Lulú y Tom.
· B agrega un registro a la tabla de Mascotas, con los siguientes valores: ‘Paco’, ‘Loro’, ‘Apátrida’, 10, 79. Hace Commit. A actualiza la vista cerrando y abriendo la tabla.
A no ve los cambios hasta que no
cierre su propia transacción.
· A borra el registro de Tom. Hace Commit.
B tiene que cerrar y abrir la tabla
para ver los cambios.
¿Qué pasa si B modifica el registro que A acaba de borrar?
Bloqueos pesimistas
A partir de Firebird 1.5, se puede usar un modo de bloqueo pesimista, indicando explícitamente en la sentencia SELECT que se desea bloquear los registros resultado de la consulta. Veremos algunos ejemplos cuando estudiemos la sentencia SELECT más adelante; por ahora simplemente digamos que este agregado se hizo para simplificar la solución de algunos problemas. No es la forma recomendada de trabajar con los datos, y tiene además varias restricciones. Citando las notas de la versión 1.5:
"No
está disponible en subconsultas, ni para joins. No se puede especificar con el
operador DISTINCT, la cláusula GROUP BY o cualquier otra operación de
agregación de registros. No puede utilizarse con o en una vista, ni con tablas
externas, ni con los resultados de un procedimiento almacenado llamado desde un
SELECT."
Mantener el contexto
Una característica que a veces es molesta: cuando cerramos una transacción ya sea con Commit o con Rollback, se cierran todos los controles de datos asociados a la misma. Firebird libera los recursos de la transacción, por lo que los componentes de datos se desconectan. Hay que volver a abrir uno por uno todos los componentes de acceso a datos que trabajen con esa transacción.
Para evitar el tener que abrir nuevamente todo, podemos indicar a Interbase que termine la transacción pero que mantenga los recursos internos -el contexto de la transacción. De esta manera mantenemos los componentes conectados aunque aceptamos o cancelamos la transacción. Las operaciones se denominan CommitRetaining y RollbackRetaining.
Hay un problema con esta manera de proceder: mientras no cerremos totalmente la transacción, se evita que el Recolector de Basura de Interbase trabaje. El accionar del servidor se vuelve más lento por la proliferación de versiones –la mayoría ya obsoletas- de registros. Esto no debería ser un problema a menos que la aplicación se mantenga funcionando de esa manera durante horas, días, tal vez semanas... en cuyo caso podemos acomodar las cosas haciendo una copia de seguridad (backup) seguida de una restauración sobre la misma base (restore). Cuando se hace un Backup se eliminan los datos temporales (la “basura”) y al recuperar la Base de Datos ésta queda limpia de polvo y paja.
Integridad referencial
La integridad referencial se refiere al estado de las referencias entre tablas. Es importante que las referencias se mantengan, para asegurar que los datos se puedan acceder correctamente. Por ejemplo, si tenemos en una tabla los datos de una factura, y en otra el detalle de los productos pedidos. En la tabla de detalle seguramente tendremos solamente una referencia a la factura si hemos normalizado la base de datos. El valor del campo de referencia (por ejemplo, el Nro de factura) se debe corresponder al valor de algún campo clave en la tabla de facturas, con lo cual podemos recuperar el resto de los datos. Ahora pensemos qué sucedería si se modifica el valor del campo de enlace en cualquiera de las puntas de la relación.
· Si modificamos el nro de factura en la tabla de detalle, estaremos referenciando a otra factura: esto puede estar bien, de hecho es la única manera de corregir un error en la asignación.
· Si modificamos el nro. de factura en la tabla de facturas, pues… eso sí puede representar un problema, ya que todos los registros de detalle quedarían huérfanos, sin que sepamos a qué factura corresponden. Esto no se puede permitir, ya que no tendremos forma de restaurar la relación.
<<<Gráfico con la relación del ejemplo, con un diagrama E/R>>>
Las comprobaciones que impidan estos problemas y mantengan la integridad referencial se pueden llevar a cabo en el cliente o en el servidor; hablaremos ahora de esta última opción.
Firebird implementa la restricción denominada clave externa en forma declarativa: se crea la restricción como una propiedad más de una tabla, y el servidor se encargará de hacerla cumplir cuando sea necesario. En contraste, podemos hacer un control activo escribiendo triggers que controlen explícitamente las referencias.
Las restricciones de clave externa (foreign key) se declaran en la tabla detalle de la relación, en la tabla que referencia a la maestra. En nuestro ejemplo, la tabla de facturas sería la maestra, y la de detalles…
Para declarar una restricción de clave externa necesitamos lo siguiente:
· Un campo o conjunto de campos del mismo tipo en las dos tablas. Los valores de estos campos en la tabla detalle referenciarán los valores de los campos correspondientes en la tabla maestra (el nro. de factura del detalle, en nuestro ejemplo)
· Una restricción de unicidad sobre los campos de la relación en la tabla maestra. Puede ser una clave primaria o una restricción UNIQUE; cualquiera de las dos posibilidades implica que los campos de la relación deben ser declarados como no null (no nulables).
· NO es necesario un índice sobre los campos de la tabla detalle; sí es conveniente para acelerar las operaciones de selección de datos sobre las dos tablas, por lo que Firebird crea un índice automáticamente sobre estos campos.
Firebird permite especificar la acción a tomar cuando se intenta violar la integridad referencial (por ejemplo cuando borramos un cliente que está referenciado en una factura), tanto para las operaciones de borrado como de actualización. Las opciones son (los nombres son parte de la sintaxis de la instrucción SQL de declaración):
· No action: la operación se rechaza de plano.
· Cascade: la acción se realiza también en los registros referenciados, automáticamente. Por ejemplo si cambiamos el nro. de un cliente y éste tiene hechas dos facturas, en las dos facturas se actualizará el nro. de cliente para que la referencia siga siendo válida. Si borramos el cliente, se borrarán las facturas que lo referencian –sin ningún aviso por parte del servidor, así que cuidado!
· Null: el campo de la tabla maestra en el que se referencia al dato modificado o borrado toma el valor NULL, es decir, pierde la referencia.
· Default: el campo de la tabla maestra en el que se referencia al dato modificado o borrado toma el valor por defecto declarado para ese campo.
Las más usadas son las dos primeras. Firebird incluso nos permite indicar una acción para un caso y otra para el restante, por ejemplo una combinación muy usada es NoAction en borrados, Cascade en modificaciones. El servidor entonces mantendrá las referencias aunque cambiemos el campo en la tabla de detalle, mientras que impedirá que eliminemos registros de detalle que estén siendo referenciados.
Esta forma de mantener la integridad referencial es muy práctica y valiosa, aunque a veces lleva a un comportamiento no óptimo en el que las operaciones llevan más tiempo del necesario. Esto es debido a la selectividad de los índices, tema que trataremos más adelante. En esos casos en que el comportamiento no sea el adecuado, tendremos que recurrir a una verificación activa de las relaciones usando triggers. Lo veremos también más adelante.
En la práctica, las restricciones de integridad referencial se aplican generalmente cuando se va a poner en producción el sistema, no antes. ¿Por qué? Pues porque el servidor no nos dejará modificar la estructura de una tabla que forme parte de una restricción de integridad referencial, y es muy común que la estructura varíe en el período de diseño y programación del sistema. Así, se crean todas las tablas y demás objetos, se trabaja con esta base de datos, y finalmente se agregan las restricciones para que no haya sorpresas. Es muy fácil generar un archivo script con las instrucciones SQL necesarias para declarar las restricciones a posteriori de la creación de las tablas. No obstante, hay que tener en cuenta que las restricciones se comprueban en el momento de su declaración –esto es, si tenemos datos que no cumplan con las restricciones no se podrán crear estas últimas.
Instalación y puesta en marcha
La instalación y puesta en marcha de Firebird es muy simple. Se incluye un programa instalador al estilo experto que nos guiará paso a paso en la instalación; sólo tendremos que seleccionar qué partes instalar y adónde.
A continuación veremos el proceso completo desde la descarga de los archivos de Internet, la instalación paso por paso y daremos un primer saludo a las herramientas que se incluyen con el paquete.
Descarga desde Internet
Firebird es de código abierto, y como tal se lo puede encontrar en Internet para su descarga libre y gratuita. No obstante, es un motivo común de confusión la existencia de distintas versiones, tanto de Firebird como de Interbase. Aquí trataré de aclarar el lío.
Se puede descargar el código fuente de FB/IB, en cuyo caso será necesario compilarlo, o bien el paquete de instalación ya compilado –lo que denominaremos formato binario. El proceso de compilación de los fuentes quedará para otros escritos; aquí me limitaré a hablar del gestor ya compilado.
El primer sitio donde Interbase estuvo disponible en su version Open Source fue, por supuesto, el de Borland. No he podido verificar si sigue disponible, por lo que pienso que no.
Hay otro sitio relacionado con Interbase desde sus inicios: http://mers.com[ERC3] . Es una compañía canadiense que desarrolla software usando herramientas de Borland. El primer grupo de soporte a usuarios de Firebird estuvo localizado en sus servidores hasta que el presidente de la compañía, Robert Schiek, fue contratado por Borland donde se desempaña ahora como administrador de la comunidad de Interbase (http://bdn.borland.com/interbase[ERC4] ). La lista de soporte ahora funciona en Yahoo! Groups, pero todavía hay un índice de las preguntas y respuestas en Mers, que se puede consultar con un buscador. En el sitio de la compañía se puede encontrar la versión 6.0.2 de Interbase Open, para Windows y Linux.
Y llegamos a Firebird. El proyecto comenzó apenas una semana después de la liberación de Interbase, en SourceForge (http://sourceforge.net/projects/firebird) [ERC5] -donde todavía se lo puede encontrar. El sitio oficial es ahora http://www.firebirdsql.org[ERC6] , un sitio derivado de IBPhoenix (http://www.ibphoenix.com[ERC7] ), de donde se pueden obtener las distintas versiones de Firebird así como herramientas, artículos, etc. La versión actual (estable) es la 1.0.2.908[ERC8] , y se puede descargar también una versión 1.5 Alpha (no para producción, inestable y sin garantías). Se espera que pronto se ponga en marcha la versión beta de Firebird 1.5.
Entonces, hagamos un resumen:
|
Versión |
URL para descarga |
|
Interbase 6.0.2 |
|
|
Firebird 1.0.3 |
|
|
Firebird 1.5.1 |
La instalación es muy sencilla… cuando todo
funciona :-P. en Windows, corremos el archivo ejecutable;
en Linux, instalamos el paquete rpm o bien ejecutamos el script si descargamos
la version no compilada. Las opciones son mínimas y se reducen a seleccionar
los componentes a instalar. Cuando hay algun problema, bueno… habrá que empezar
a preguntar. El mejor lugar para encontrar respuestas es el foro de soporte firebird-support
en yahoogroups.com.
Probar la instalación
Con la distribución estándar de Firebird se incluye una utilidad para ejecutar sentencias SQL contra el servidor, y mostrar sus resultados. Esta utilidad es de línea de comandos y se encuentra en el archivo isql.exe.
Se puede usar isql para probar la instalación del servidor de la siguiente manera:
- Abra una ventana de terminal en la carpeta bin de la distribución de Firebird
- Ejecute el programa escribiendo isql
- Isql responderá con un indicador (prompt) especial 'SQL>' como se ve en la siguiente imagen:
- Escriba
Connect "localhost:c:\archivos
de programa\firebird\examples\employee.gdb" user "sysdba"
password "masterkey";
para conectar con la base de datos de ejemplo. Note que puede cortar la
sentencia en dos o más líneas simplemente presionando <Enter>; isql
indicará que una línea continúa una sentencia anterior cambiando el indicador
(prompt) por 'CON>'. Las
sentencias se terminan con punto y coma (;).
- Si la conexión se puede establecer,
isql responderá con un mensaje indicando que está ahora conectado a la
base de datos. A partir de ese momento se pueden ejecutar sentencias SQL
sobre esa base de datos, o usar algunos comandos específicos de isql como Show Tables. En la parte de herramientas hablaremos más en
profundidad sobre la utilidad isql.
Herramientas
IBServer
IBGuardian
ISQL
Gbak
IBConsole
IBConsole es un producto realizado utilizando acceso directo a la API de Interbase (con los componentes IB Express, de los que hablaremos luego), por lo que su rendimiento es muy bueno. También es simple de usar y nos da la posibilidad de realizar las tareas administrativas comunes como ser: creación y destrucción de bases de datos; administración de usuarios y permisos; realización de backups; verificación de integridad de la Base de Datos; ejecución de sentencias y scripts SQL, etc.
A partir de la versión 1.5 de Firebird esta utilidad no se incluye más en la distribución. Se puede encontrar en codecentral.borland.com en sus versiones para Windows y Linux; no obstante, no se recomienda IBConsole como herramienta de administración para bases de datos Firebird, en parte porque está compilado con el conjunto de componentes IBX que no garantizan compatibilidad con las nuevas versiones de Firebird.
Accederemos a una Base de Datos de ejemplo que se incluye en el paquete (si no instaló los ejemplos, hágalo ahora; la base de datos de empleados utiliza técnicas que vale la pena estudiar). Usaremos la base de datos llamada Employee.gdb o su versión internacional (que contiene caracteres no ingleses) llamada Intlemp.gdb. Cualquiera de las dos sirve para nuestros ejemplos porque lo único que cambia es el contenido de las tablas, no su definición.
Para usar la base de datos, debemos registrarla en IBConsole. Los pasos necesarios son los siguientes:
· 
registrar el servidor local con la opción correspondiente del menú
‘Server’
·
en el menú contextual del árbol de objetos de la izquierda
seleccionamos ‘Register…’
· en el cuadro de diálogo que aparece a continuación, completamos la información de registro de la base de datos y aceptamos. La nueva Base de Datos debería aparecer en el árbol de objetos.
Hasta ahora lo único que hemos hecho es crear un alias para la Base de Datos; es decir,
registrar con IBConsole la ubicación física del archivo y los datos de
conexión, así como un nombre más descriptivo que el nombre del archivo. Para
acceder a los datos debemos conectarnos
a la base de datos. Normalmente en el momento del registro IBConsole conecta
automáticamente con la base de datos, como podemos observar en el árbol que se
abre y el gráfico de la BD tiene una marca verde.
De aquí en adelante, podemos conectarnos haciendo simplemente doble Click sobre el nodo en el árbol o seleccionando la opción pertinente del menú contextual.
El solo hecho de lograr la conexión ya indica que el servidor está funcionando correctamente. Todavía se pueden presentar problemas en el acceso a través de la red, pero nos ocuparemos de eso a su debido tiempo.
Ejecutar sentencias SQL en IBConsole
Para ejecutar cualquier sentencia SQL utilizamos la herramienta
conocida como Interactive SQL. En IBConsole está integrada al producto, y se
accede desde el menú Tools o el botón 
. La
ventana tiene el siguiente aspecto:
Las sentencias SQL se escriben en el editor superior, se ejecutan
con CTRL+E o el botón 
, y el
resultado se obtiene inmediatamente en la parte inferior (datos, plan de
ejecución y estadísticas de ejecución).
Por ejemplo, la siguiente imagen muestra el resultado de pedir el contenido de la tabla employee:
IBOConsole
Esta herramienta es como un ‘clon’ de IBConsole, pero construida con los componentes IB Objects. Es gratuita y se puede descargar de <<<Ver el enlace real en IBPhoenix>>>, y en general es más estable que las versiones de IBConsole que he probado. Se maneja de la misma manera, la interfaz es prácticamente igual, y podemos esperar que sea útil con las nuevas versiones de Firebird, ya que los componentes IBO soportan todas las características de estos servidores.
IB_SQL
SQL
El lenguaje de consulta
Tal vez el problema mayor que se presenta a los programadores ‘viejos’, los que usaban sistemas de datos orientados a archivos como Clipper, Dbase o Cobol, sea el de cambiar la forma de acceder a los datos. Lo que antes requería recorrer toda una tabla buscando coincidencias o armar tablas temporales con datos de distintas tablas relacionadas, ahora se hace con una sola sentencia de un lenguaje especial diseñado para eso: el Lenguaje Estructurado de Consulta o SQL.
Una de las características que distinguen a este lenguaje es su orientación a conjuntos de datos. Así por ejemplo, en SQL no pedimos ‘recorre todos los registros y cuando encuentres uno cuyo campo Nombre comience con A, me lo muestras’; en su lugar, pedimos ‘una tabla temporal formada por todos los registros que cumplan la condicion: el campo Nombre empieza con A’. Lleva un tiempo acostumbrarse a pensar en conjuntos de datos, pero se logra. Trataremos aquí de dar la mayor cantidad posible de ejemplos y ejercicios para que esta forma de pensar se haga casi natural.
SQL son las iniciales de “Structured Query Language” o Lenguaje Estructurado de Consulta. Es un lenguaje no procedural inventado en IBM a principios de los años 70, para implementar el modelo relacional de Codd. Inicialmente se le llamó SEQUEL (Structured English Query Language), luego pasó a ser SEQUEL/2 y finalmente SQL (esperemos que no siga la tendencia y en unos años se termine llamando S). Hoy en día es el lenguaje de consulta más utilizado por los gestores de Bases de Datos de todos los tamaños, desde Dbase o Paradox pasando por Oracle, Informix o SQL Server, hasta los gestores de datos utilizados en supercomputadoras.
Hay definidos tres estándares oficiales por el Instituto Nacional de Estándares Americano (American National Standards Institute, ANSI): SQL-86, SQL-89 y SQL-92.
La mayoría de los gestores de consultas implementan “a su manera” las recomendaciones del estándar. Estudiaremos los comandos más comunes, que se aplican a los motores de consulta más utilizados, y cuando sea pertinente comentaremos diferencias en distintos servidores.
A continuación veremos en detalle el lenguaje SQL que implementa Firebird. Los ejemplos utilizan la base de datos de ejemplo que viene con el servidor, llamada EMPLOYEE.GDB, y se ejecutan en IBConsole o IBOConsole.
Cursores vs conjuntos
Los gestores de datos tipo Dbase o Paradox trabajaban con el concepto de cursor; el lenguaje SQL trabaja con conjuntos.
Los cursores se pueden ver como una cinta sobre la que se encuentran los registros, con un señalador que se mueve adelante y atrás para marcar el registro activo o actual. Las operaciones sobre más de un registro se realizan recorriendo el cursor con el señalador y actuando sobre los registros a medida que son señalados.
Veamos dos operaciones típicas con este modelo:
· Selección de un subconjunto de registros, que cumplen un criterio determinado, por ejemplo mostrar los productos de un rubro
El señalador se posiciona en el primer registro; comprueba si ese registro cumple con el criterio; si lo cumple se muestra, si no se saltea; el señalador pasa al siguiente registro, y repite la comparación hasta que no hay más registros en el cursor.
· Modificación de un subconjunto de registros, por ejemplo modificar el precio de todos los productos de determinado rubro
La operatoria es prácticamente la misma, sólo que en lugar de mostrar los registros que cumplen el criterio se editan, se cambia el precio, y se vuelven a grabar. Hay que tener en cuenta que si el cursor está ordenado por algún índice, el cambio puede hacer que los registros se reubiquen… y el señalador seguirá al registro que modificó, por lo que puede ir a parar al final del cursor, o recomenzar al principio! En el ejemplo de los precios, seguramente el orden será por rubro por lo que la modificación de precios no afectará la posición de los registros.
Un servidor SQL como Firebird, en cambio, trabajará sobre conjuntos de registros en una sola operación. Así, las mismas operaciones del ejemplo anterior se harían mucho más simples:
· Selección de un subconjunto de registros que cumplen determinado criterio
Simplemente se emite una sentencia SELECT y el servidor generará un cursor compuesto solamente con los registros que cumplen el criterio. Para mostrar estos registros se hace el mismo bucle anterior, pero solamente se recorren los registros que cumplen el criterio.
· Modificación de un subconjunto de registros que cumplen un criterio
Se emite una sentencia UPDATE que incluye la operación a realizar y el criterio de selección, y el servidor modificará el subconjunto que cumpla el criterio sin más intervención nuestra.
![Cuadro de texto: Nota: en todos los listados de sintaxis que figuran en esta sección, se utilizan algunos símbolos con significados especiales (no se deben incluir en la sentencia):
• Corchetes “[ ]”: sección opcional. No es indispensable para la correcta declaración de la sentencia
• Barra vertical “|”: separa opciones mutuamente excluyentes. Se debe utilizar una sola de ellas.
• Llaves “{ }”: agrupa opciones excluyentes.](Firebird_archivos/image021.gif)
Selección de registros: SELECT
La sentencia estrella de SQL es, sin ninguna duda, SELECT. Esta instrucción permite obtener datos de una o varias tablas, con un poder y flexibilidad increíbles. Si SQL no tuviera más que esta instrucción, igualmente valdría la pena aprenderlo.
La sintaxis básica de SELECT es como sigue[6]:
SELECT columnas | *
FROM tablas
[WHERE condición]
[ORDER BY
columnas]
Hay muchas variaciones y agregados sobre este esqueleto básico, pero todo a su tiempo.
En palabras, esta instrucción devolverá una tabla virtual compuesta por las columnas especificadas (el asterisco * se reemplaza por todas las columnas de todas las tablas), con las filas de las tablas dadas que cumplan la condición (opcional), y todo ordenado por las columnas indicadas (opcional).
Por ejemplo, para obtener los nombres de los empleados podemos escribir:
SELECT FIRST_NAME
FROM EMPLOYEE
Y veremos en la ventana de ISQL:
La sentencia SQL desaparece del editor, que
queda listo para escribir otra; pero puede recuperarse con el botón 
o la opción de menú Query|Previous.
Algunas preguntas comunes a esta altura:
·¿Importa si las instrucciones se escriben en mayúsculas o minúsculas?
No. Para el servidor es lo mismo ‘SELECT’ que ‘select’. En la parte que sí hay que tener cuidado es en los nombres de campos y tablas, ya que hay dialectos SQL que distinguen entre mayúsculas y minúsculas. En Firebird, con el dialecto 1 de SQL no hay diferencias; con dialecto 3, las hay únicamente cuando se usan los nombres entre comillas dobles (“”).
·¿En qué orden se recuperan los registros si no especificamos una cláusula ORDER BY?
En ‘orden natural’, lo que significa que no podemos asegurar nada. El motor de consultas obtiene los datos en el orden que sea más eficiente.
·¿Es posible indicar algún formato en el resultado, por ejemplo el ancho de las columnas?
No. El lenguaje SQL es un lenguaje de consulta, no considera la parte de presentación de los resultados.
·¿Se pueden usar espacios en los nombres de campos y tablas?
Depende del servidor. En Interbase, únicamente podemos usar espacios con Dialecto 3 y si encerramos el nombre entre comillas dobles. Los servidores actuales tienden a ser consecuentes con esta norma: si está entre comillas, puede contener cualquier caracter. No obstante, recomendamos no utilizar caracteres especiales ni espacios debido a los potenciales problemas con los diferentes conjuntos de caracteres. En general se reemplazan los espacios por el caracter de subrayado (underscore, ‘_’) como en el campo FIRST_NAME usado en el ejemplo.
Vamos a extender la tabla de resultados
para que muestre otras columnas: el apellido (last_name) y el país de trabajo
(job_country). La
sentencia queda como sigue
SELECT FIRST_NAME, LAST_NAME, JOB_COUNTRY
FROM EMPLOYEE
¿Cómo haría para que aparezca primero el apellido y después el nombre?
Cuando queremos todas las columnas de la tabla en el resultado, en el mismo orden que están definidas, podemos usar el comodín ‘*’ (asterisco):
SELECT *
FROM EMPLOYEE
Notemos en el resultado de esta sentencia que hay una columna –la última- que combina el contenido de los campos First_Name y Last_Name para mostrar el nombre completo. En el caso de esta tabla, este campo está definido así, como un campo calculado por el servidor. Nosotros podemos lograr el mismo efecto usando operadores para construir campos nuevos, no existentes en la tabla original.
Por ejemplo, vamos a mostrar una columna con el Nro. de empleado (emp_no), un guión, el nombre y el apellido:
SELECT emp_no || ' – ' || first_name || ' ' ||
last_name
FROM employee
Hemos utilizado aquí el operador de concatenación ‘||’ (dos barras verticales), que permite juntar cadenas de caracteres. El resultado es otra cadena. Notemos que podemos usar tanto nombres de campos como cadenas constantes, con comillas simples.
Un problema común cuando se utilizan columnas formadas por expresiones es el nombre de estas columnas. Por ejemplo, la columna generada en el ejemplo anterior se llama ‘F_1’. ¿Quién puso este nombre? El servidor. ¿Es posible cambiarlo? Si, asignando un alias al campo por medio de la partícula ‘as’:
SELECT emp_no || ' - ' || first_name || ' ' ||
last_name AS Empleado
FROM employee
Si ejecutan esta sentencia, verán el mismo resultado que antes pero ahora la columna se llama ‘Empleado’, lo cual es mucho más fácil de recordar.
Antes de continuar sumergiéndonos en las complejidades de SELECT, realice los siguientes ejercicios para afirmar lo anterior.
Ejercicios
1) Obtenga los siguientes datos (y en este orden) de los empleados: Nombre, Apellido, Nro. de empleado, salario y país de trabajo.
2) Ahora muestre tres columnas llamadas ‘Apellido’, ‘Nombre’, ‘Salario’. En la primera muestre el apellido, en la segunda el nombre, y en la tercera la información siguiente (los ángulos indican que es el nombre de un campo):
<Job_code>, nivel <job_grade> (U$S
<salary>)
3) De la tabla de proyectos: una columna llamada ‘Proyecto’ con el siguiente formato
(<proj_id>) <proj_name>
otra columna con el tipo de producto (product), y una tercera con el Nro. de empleado del líder del proyecto (team_leader)
Orden en la sala
El orden en que el servidor devuelve los datos depende de varios factores; en general, no se puede asumir un orden determinado por lo que hay que indicarlo explícitamente usando la cláusula ORDER BY.
La cláusula ORDER BY es muy simple de usar: es seguida por una lista de campos (que deben figurar en la lista de columnas a mostrar) por los que se ordenará.
Por ejemplo, para obtener datos de los empleados ordenados por país de trabajo, podemos hacer
SELECT *
FROM employee
ORDER BY
job_country
Como podrán ver, hay muchos empleados que trabajan en cada país, que no guardan entre sí un orden predecible. Así, si tenemos que buscar un empleado por nombre en el listado anterior –suponiendo que sabemos en qué país trabaja, tendríamos que recorrer todos los empleados de ese país hasta encontrar el que buscamos.
Para simplificar la búsqueda, podemos
indicar al servidor que queremos los resultados ordenados, si, por país; pero
más todavía, el conjunto de empleados de cada país lo queremos ordenado por
apellido y nombre. Pruebe
la siguiente sentencia:
SELECT *
FROM employee
ORDER BY job_country, last_name, first_name
Ya se comienza a notar la potencia del lenguaje. Esta instrucción nos devuelve una tabla con todos los empleados organizados por país, dentro de cada país por apellido, y si hay apellidos repetidos, se ordenan por nombre.
Una variación posible es el sentido de la ordenación: si es ascendente o descendente. Por defecto Firebird devuelve los datos ordenados en forma ascendente; para indicar lo contrario agregamos la partícula ‘desc’ después del nombre del campo de ordenación. Por ejemplo,
SELECT *
FROM employee
ORDER BY job_country desc
Nos devolverá los empleados ordenados en forma descendente por país de trabajo.
Este modificador es válido sólo para la columna a la cual sigue; cada una de las columnas de ordenamiento debe tener su indicador de dirección o se tomará el ascendente por defecto. También se puede explicitar el orden ascendente usando ‘asc’.
SELECT *
FROM employee
ORDER BY job_country desc, last_name asc, first_name
desc
Devolverá un conjunto ordenado por país en forma descendente, dentro de cada país los empleados se ordenarán por apellido en forma ascendente, y si hay apellidos repetidos, se ordenarán por nombre en forma descendente.
NOTA: como veremos en los ejemplos siguientes, la cláusula ORDER BY siempre se coloca al final de la instrucción, después de todas las demás cláusulas optativas.
Filtrados y búsquedas
Todos los ejemplos que hemos hecho hasta ahora devuelven el total de los registros de la tabla en cuestión. Como se habrán imaginado, el lenguaje SQL tiene una cláusula que sirve para reducir el conjunto de registros resultado a un subconjunto que cumpla con ciertas condiciones. Para hacer uso de esta opción solamente tenemos que agregar a la sentencia SELECT…FROM la cláusula WHERE <condición>.
La condición debe ser una expresión que devuelva un valor booleano: aquellos registros para los cuales la expresión se evalúe a Verdadero aparecerán en el conjunto resultado, el resto no.
Por ejemplo, si queremos un listado de los empleados que trabajan en Canada solamente, haríamos
SELECT *
FROM employee
WHERE job_country = 'Canada'
Podemos ordenar el resultado agregando al final la cláusula order by:
SELECT *
FROM employee
WHERE job_country = 'Canada'
ORDER BY last_name, first_name
Esta instrucción nos permitirá encontrar rápidamente un empleado de Canadá. Pero podemos también usar los operadores lógicos para afinar la búsqueda; por ejemplo, si queremos ver los empleados de Canada de categoría 4, haríamos
SELECT *
FROM employee
WHERE (job_country = 'Canada') AND (job_grade = 4)
Notemos que el ‘4’ no está rodeado de comillas; es un número, no una cadena. Los paréntesis no son necesarios en este ejemplo, pero pueden servir para aclarar el orden de evaluación.
El orden de evaluación puede determinar el resultado de una expresión: por ejemplo, no es lo mismo evaluar 1+2*3 de izquierda a derecha (1+2=3, 3*3=9) que asociando por precedencia como hacemos en matematicas: 1+(2*3) = 7. La siguiente tabla indica el orden de precedencia de los operadores en Firebird. Algún servidor puede diferir con este orden, pero en general todos lo consideran de la misma manera. La precedencia va de mayor a menor, leídos de arriba hacia abajo (por ejemplo, se evalúa primero una concatenación que una multiplicación; pero esta última se evalúa antes que una suma).
|
Tipo |
Operador |
Comentario |
|
Cadenas |
|| |
Concatenación |
|
Matemáticos |
* / + - |
Multiplicación División Suma Resta |
|
Comparación |
=,
== <>,[ERC9]
!=, ~=, ^= > < >= <= !>,
~>, ^> !<,
~<, ^< |
Igualdad Desigualdad Mayor Menor Mayor o igual Menor o igual No mayor No menor |
|
Lógicos |
NOT AND OR |
Negación Conjunción (‘Y’) Disjunción (‘O’) |
Es muy importante conocer el orden de precedencia de los operadores, para no cometer errores que pueden ser muy difíciles de detectar. Por ejemplo, si el resultado de una consulta tiene 1000 registros usando el criterio ‘WHERE n > 1+2*3’ y 1010 registros usando el criterio ‘WHERE n > (1+2)*3’, ¿cuál es el correcto? No sería fácil decidirlo. Y si deciden mal, y le entregan al jefe la lista de los empleados que cumplen la condición errónea, y éstos son despedidos… bueno, no digan que no les avisé.
Ejercicios
1) Realizar una consulta que devuelva los nombres y apellidos de los empleados que cobran más de U$S 100.000 (afortunados ellos).
2) Realizar una consulta que devuelva una tabla como la siguiente, con los datos ordenados por País, categoría, apellido y nombre:
|
Empleado |
País |
Categoría |
Salario |
|
<first_name><espacio><last_name> |
<job_country> |
<job_grade> |
<salary> |
3) Agregar a la tabla anterior una columna con el código de trabajo (job_code) entre las de Categoría y Salario. Llamar a la columna ‘Codigo_trabajo’.
4) Listar aquellos empleados que hayan sido contratados en 1990 (hire_date)
El último ejercicio pone el dedo en una llaga: el manejo de fechas.
Fechas/Horas en SQL
Nuevamente, cada servidor tiene sus convenciones con respecto al formato de las fechas. En Firebird tenemos varios tipos de campos para almacenar fechas y horas:
Dialecto 1
DATE: almacena fecha y hora, como dos números enteros de 32 bits. Equivalente al tipo timestamp del dialecto 3
Dialecto 3
DATE: almacena una fecha como un número entero que representa la cantidad de días pasados desde el 1 de Enero de 100. Puede almacenar hasta el día 29 de febrero de 32768. Un valor 0 para la fecha indicará el día 17 de Noviembre de 1858.
TIME: almacena una hora como un número entero. El rango permitido va desde las 00:00 AM hasta las 23:59.9999 PM, esto es, 23:59:59.5964 PM.
TIMESTAMP: almacena fecha y hora, como dos números enteros de 32 bits; la parte de fecha es igual al tipo DATE, la parte de hora igual a un campo de tipo TIME
Entonces, ¿cómo podemos usar fechas en criterios de selección? Ciertamente, no calculando la cantidad de días pasados desde el ‘día cero’, 17 de Noviembre de 1858. Firebird puede convertir un tipo de dato en otro, y particularmente con las fechas hace la conversión automáticamente si el formato del texto con la fecha coincide con alguno de los reconocidos:
CCYY-MM-DD
MM-DD-CCYY ó MM-DD-YY
MMM-DD-CCYY ó MMM-DD-YY
DD-MMM-CCYY ó DD-MMM-YY
CCYY-MM-DD HH:Min:SS:nnnn
MM-DD-CCYY HH:Min:SS:nnnn ó MM-DD-YY HH:Min:SS:nnnn
MMM-DD-CCYY HH:Min:SS:nnnn ó MMM-DD-YY HH:Min:SS:nnnn
DD-MMM-CCYY HH:Min:SS:nnnn ó DD-MMM-YY HH:Min:SS:nnnn
donde
CC = siglo
YY = año
MM = mes
DD = día
HH = hora
Min = minutos
SS = segundos
nnnn = milisegundos
El separador de las fechas puede ser '-', '/' o '.'. Para las fechas se puede usar el formato americano (MM/DD) o el europeo (DD.MM), reconociéndose por el separador: el punto para el formato europeo, cualquier otro para el americano.
Por ejemplo, para ver todos los empleados contratados en 1990 podemos escribir:
SELECT *
FROM employee
WHERE (hire_date >= '1990-01-01')
AND
(hire_date < '1991-01-01')
Se definen en Firebird algunas literales que son reemplazadas por los valores que representan en el momento de la evaluación:
NOW: fecha y hora actuales
TODAY: fecha actual
YESTERDAY: fecha de ayer
TOMORROW: fecha de mañana
Estas literales se utilizan encerradas entre comillas simples, como en el siguiente ejemplo:
SELECT *
FROM employee
WHERE hire_date < 'TODAY'
Que nos devolverá todos los empleados contratados antes del día de hoy.
Asimismo, cuando trabajamos en Dialecto 3 podemos usar las funciones current_date, current_time y current_datetime (sin comillas) para referirnos a la fecha y hora actuales en los tres formatos: DATE, TIME y TIMESTAMP respectivamente.
Podemos tener una idea del tratamiento que da Firebird a las fechas creando una tabla con un campo de cada tipo, y probando los distintos valores. Las instrucciones de creación e inserción no han sido explicadas aún, pero considero importante el ejemplo en este momento.
CREATE TABLE prueba ( clave integer not null primary key, hora time, fecha date, todo timestamp)
Vamos a crear una tabla llamada Prueba, con los siguientes campos:
|
Campo |
Tipo de dato |
|
Clave |
Integer |
|
Hora |
Time |
|
Fecha |
Date |
|
Todo |
Timestamp |
La sentencia SQL necesaria aparece en el cuadro de la derecha. No nos preocupemos por ahora de esta sentencia; lo único que tenemos que saber es que creará la tabla deseada. Ahora emitimos los siguientes comandos, uno por vez, para llenar la tabla con datos:
INSERT INTO prueba VALUES
(1,'09:00','TODAY','NOW')
INSERT INTO prueba VALUES
(2,'10:00','YESTERDAY','yesterday')
INSERT INTO prueba VALUES
(3,'11:00','tomorrow','time')
INSERT INTO prueba VALUES
(4,'12:00',current_date,'now')
INSERT INTO prueba VALUES
(5,'13:00','2002-05-24','now')
INSERT INTO prueba VALUES
(5,'13:00','2002-05-24','2002-05-24 12:35')
Si ahora pedimos los datos introducidos, con un SELECT común
SELECT *
FROM prueba
veremos cómo interpretó Firebird cada uno de los literales y funciones usados, en cada tipo de campo. También podemos probar otras combinaciones para ganar un entendimiento mayor de las expresiones que son válidas y las que no.
Ejercicios
En la base de datos donde creó la tabla Pruebas anterior:
1) devuelva los registros donde <fecha> es menor que hoy
2) devuelva los registros donde <todo> es menor que el momento actual
3) devuelva los registros donde <fecha> sea igual a la fecha de ayer
4) devuelva los registros donde <hora> es menor que las 12 del mediodía, ordenados por fecha
Criterios de selección
Hay una serie de funciones que se pueden usar en las expresiones de criterios de selección. Como de costumbre, estas funciones dependen del servidor de bases de datos, y veremos aquí en detalle las de Firebird.
· <campo> LIKE <cadena>: comparación de cadenas con comodines. Es similar al operador de igualdad (=) pero solamente puede usarse con cadenas y permite el uso del comodín ‘%’ para indicar ‘cualquier carácter (cero o más)’ y ‘_’ para reemplazar un solo carácter que sin embargo debe existir. Se distingue entre mayúsculas y minúsculas.
Por ejemplo, la siguiente sentencia devolverá todos los empleados cuyo nombre empieza con ‘A’ (mayúscula):
SELECT *
FROM employee
WHERE first_name LIKE 'A%'
Mientras que la siguiente sentencia devolverá todos los empleados que contengan una ‘a’ minúscula en cualquier posición de su apellido:
SELECT *
FROM employee
WHERE last_name LIKE '%a%'
Pruebe a cambiar la ‘a’ de la condición por una ‘A’.
· <campo> STARTING WITH <cadena>: devuelve TRUE si la cadena comparada comienza con el valor pasado. Es equivalente a LIKE <cadena%>; pero el optimizador de consultas puede usar un índice (si existe) con STARTING WITH, mientras que con LIKE no.
Como ejemplo, veamos el mismo ejemplo que antes: los empleados cuyo nombre comienza con ‘A’:
SELECT *
FROM employee
WHERE first_name STARTING WITH 'A'
· <campo> CONTAINING <cadena>: devuelve TRUE si la cadena comparada contiene el valor dado. Es equivalente a LIKE <%cadena%>, pero tiene la ventaja de poder usarse con campos BLOB.
Como ejemplo, veamos el mismo ejemplo que antes: los empleados cuyo apellido contiene una ‘a’:
SELECT *
FROM employee
WHERE last_name CONTAINING
'a'
También existe la versión negada, es decir que devuelve TRUE cuando el valor NO contiene la cadena:
SELECT *
FROM employee
WHERE last_name NOT
CONTAINING 'a'
· <campo> BETWEEN <valor inferior> AND <valor superior>: devuelve TRUE cuando el valor comparado (que puede ser de cualquier tipo salvo BLOB) se encuentra entre <valor inferior> y <valor superior>, ambos inclusive. Estos límites deben ser del mismo tipo que el valor a comparar.
Por ejemplo, la siguiente sentencia devolverá los datos de los empleados que cobran entre U$S 50.000 y U$S 72.000:
SELECT *
FROM employee
WHERE salary BETWEEN
50000 AND 72000
La siguiente instrucción traerá los empleados contratados en 1990 (equivalente a la que vimos en un ejemplo anterior):
SELECT *
FROM employee
WHERE hire_date BETWEEN
'1990-01-01' AND '1990-12-31'
Y la siguiente recuperará todos los empleados cuyo nombre empiece con ‘A’, ‘B’ o ‘C’:
SELECT *
FROM employee
WHERE first_name BETWEEN 'A' AND 'Cz'
Notemos el uso de ‘Cz’ para el límite superior; cualquier cadena que empiece con ‘C’ y siga con un carácter común será menor o igual que esta cadena, por lo que entran en la selección todos los apellidos comenzados en C.
La versión negada devuelve TRUE cuando el valor no está en el rango dado:
SELECT *
FROM employee
WHERE hire_date NOT
BETWEEN '1990-01-01' AND
'1990-12-31'
Devolverá los empleados que no fueron contratados en 1990.
· <campo> IN <conjunto>: devuelve TRUE si el valor existe en el conjunto dado. El conjunto puede ser especificado por extensión, como una lista de valores separados por comas y encerrados entre paréntesis, o como una subconsulta que devuelva un solo campo del tipo correcto.
Por ejemplo, para ver aquellos empleados de USA, Canada y Alemania (Germany) podemos hacer:
SELECT *
FROM employee
WHERE job_country IN ('USA', 'Canada', 'Germany')
Mientras que para ver los empleados que son líderes de algún proyecto podríamos hacer:
SELECT *
FROM employee
WHERE emp_no IN
(
SELECT
team_leader
FROM
project)
La consulta interior, encerrada entre paréntesis, se denomina subconsulta y es una poderosa posibilidad de SQL en la que ahondaremos en breve.
También existe la versión negada, como se habrán imaginado:
SELECT *
FROM employee
WHERE emp_no NOT
IN (
SELECT
team_leader
FROM
project)
Devuelve los empleados que no son líderes de proyectos.
· <campo> IS NULL: devuelve TRUE si el valor es nulo; también puede usarse la forma <valor> IS NOT NULL, que devolverá TRUE en el caso contrario. Estas instrucciones fueron creadas especialmente para lidiar con los valores nulos, y generalmente no equivalen a comparar simplemente un campo con el valor NULL… aunque también depende del servidor. Veamos un par de ejemplos:
SELECT *
FROM employee
WHERE phone_ext IS NULL
Devuelve los empleados cuya extensión de teléfono es desconocida. Pruebe a ejecutar la siguiente sentencia, que parece ser equivalente:
SELECT *
FROM employee
WHERE phone_ext = NULL
Este comportamiento es parte de las extravagancias de los nulos, de las que hablaremos muchas veces.
Si queremos ver los que sí tienen almacenada una extensión, haremos
SELECT *
FROM employee
WHERE phone_ext IS
NOT NULL
(1.5) CASE
A partir de la versión 1.5 de Firebird, se puede generar una consulta condicional cuyas columnas tomarán valor
en base al resultado de la evaluación de una expresión. La sintaxis es la
siguiente:
Se pueden clasificar las sentencias CASE en dos formas:
1)
simple: se
define la expresión después de la palabra CASE y los distintos valores se
asignan en base a los distintos resultados de la evaluación de esta expresión
CASE < expresion valor >
<clausula WHEN simple>...
[ <clausula else> ]
END
<<<Ejemplo>>>
2)
con
búsqueda: para cada valor se especifica una expresión
CASE <clausula WHEN buscada>...
[ <clausula else> ]
END
<<<Ejemplo>>>
Funciones
Existen en todos los servidores algunas funciones que toman un parámetro entre paréntesis -usualmente el contenido de un campo- y devuelven el valor modificado. Se pueden usar tanto en la especificación de las columnas a devolver como en los criterios.
Firebird es bastante parco en cuanto a las funciones predefinidas; tenemos solamente un puñado:
UPPER(<cadena>): devuelve la misma cadena con todos los caracteres en mayúsculas.
CAST(<valor> as <tipo>): convierte el valor al tipo dado… si puede.
GEN_ID(<nombre generador>,<incremento>): incrementa el valor del generador en la cantidad pedida y devuelve el resultado. El generador queda incrementado.
SUBSTRING(cadena FROM inicio [FOR n]): devuelve n caracteres de cadena a
partir del caracter nro. inicio.
Si no se especifica la cláusula FOR, se devuelven todos los caracteres desde inicio hasta el final.
<<<OJO: creo que hay un problema cuando se usan
juegos de caracteres de más de un byte… ver los mensajes de los foros de
Firebird y probar con fss_unicode y
iso8859_1>>>
En dialecto 3 disponemos de la función EXTRACT para obtener una parte de una fecha:
EXTRACT({year | month | day} FROM
<campo fecha>)
Por ejemplo, para obtener los empleados que fueron contratados en el mes de enero (de cualquier año) haríamos
SELECT *
FROM employee
WHERE extract(month from hire_date) = 1
EMP_NO FIRST_NAME LAST_NAME PHONE_EXT HIRE_DATE …
-------------------------------------------------------------------- …
11 K. J. Weston 34 17/01/1990 …
20 Chris Papadopoulos 887 01/01/1990 …
141 Pierre Osborne 03/01/1994 …
(1.5) NULLIF(expr1,expr2)
devuelve NULL si expr1 es igual a expr2, caso contrario devuelve expr1.
<<<Ejemplos>>>
(1.5) COALESCE(expr1, expr2, expr3, … exprN) devuelve el valor de la primera expresión no nula.
<<<Ejemplos>>>
Funciones de agregación
Las siguientes funciones operan sobre un conjunto de registros (que puede ser la tabla entera), devolviendo un solo valor por cada grupo:
AVG(<campo>): devuelve el valor medio del campo en el grupo de registros considerado.
COUNT(<campo> | *): devuelve la cantidad de registros del grupo. Si usamos la primera versión, con el nombre de un campo entre los paréntesis, devolverá la cantidad de filas en que este campo sea no nulo; la segunda versión, con un asterisco, devuelve la cantidad total de registros del grupo. Si no se encuentra ninguna fila que cumpla con las condiciones, devuelve 0.
COUNT(distinct <campo>): igual que el anterior, pero cuenta sólo los registros con valores diferentes en el campo pedido.
MAX(<campo>): devuelve el valor máximo del campo en el grupo.
MIN(<campo>): devuelve el valor mínimo del campo en el grupo.
SUM(<campo>): devuelve la suma de los valores del campo en el grupo. No considera los valores nulos. Si no encuentra nada para sumar (no hay registros o todos son nulos) no devuelve 0, como uno esperaría: simplemente devuelve un nulo.
Por ejemplo, la consulta siguiente devolverá el monto total de remuneraciones anuales, sumando el campo SALARY de todos los registros de la tabla EMPLOYEE:
select sum(salary)
from employee
SUM
-----------
115522468
Pero SQL puede dar mucha más información que esa; por ejemplo, sería bueno conocer el total pagado en sueldos por departamento, es decir que la suma actuara sobre subconjuntos de registros.
Para especificar los límites de cada subconjunto de registros usamos la cláusula GROUP BY seguida de una expresión sobre campos de la tabla. Los registros se agruparán según los valores de esos campos, de manera que todos los integrantes de un grupo tengan el mismo valor en esos campos. Por ejemplo, la consulta indicada antes de totales pagados en salarios por departamento puede escribirse como sigue:
select sum(salary) as TotalPorDepto
from employee
group by dept_no
TOTALPORDEPTO
--------------
266643
155262.5
130442.81
13480000
95779.69
110000
…
Aún mejor, podemos mostrar el nro. de departamento junto a cada total:
select dept_no as Depto, sum(salary) as TotalPorDepto
from employee
group by dept_no
DEPTO TOTALPORDEPTO
--------------------
000 266643
100 155262.5
110 130442.81
115 13480000
120 95779.69
121 110000
…
Trate de visualizar mentalmente el resultado de la sentencia anterior sin la cláusula ORDER BY. Si Ud. puede, lo felicito; yo no sabría cómo armar las filas, mezclando nros. de departamento (que se repiten) y totales por cada departamento diferente. El intérprete SQL tampoco lo sabe, y se queja amargamente:
select dept_no as Depto, sum(salary) as TotalPorDepto
from employee
Dynamic SQL Error
SQL error code = -104
invalid column reference
como hicimos antes, debemos especificar que los registros se agruparán por nro. de departamento; entonces si, cada fila de la salida corresponde a un grupo y todos los registros del grupo tienen el mismo valor en dept_no, por lo que no hay ambigüedades.
La regla sintáctica es, entonces:
??????
Suponga ahora que queremos ver solamente aquellos departamentos en los cuales se pagan más de 250.000 dolares al año en sueldos. En este caso la restricción no se puede aplicar con una cláusula WHERE, ya que la condición a cumplir utiliza el total por departamento calculado sobre los subconjuntos. Para aplicar una condición de filtrado después del agrupamiento de los registros, existe la partícula HAVING:
select dept_no as depto, sum(salary) as TotalPorDepto
from employee
group by dept_no
having sum(salary)>200000
DEPTO TOTALPORDEPTO
--------------------
000 266643
115 13480000
123 390500
125 99000000
621 276739.5
623 287758.25
671 219465.19
Esos son todos los departamentos que cumplen con nuestra condición.
Ahora cambiamos la pregunta: queremos saber
cuáles son los sueldos que se pagan por departamento, pero solamente en USA.
Esta condición sí se debe aplicar registro por registro, ya que puede haber un
departamento con empleados en distintos países. La sentencia resultante es:
select dept_no as depto, sum(salary) as TotalPorDepto
from employee
where job_country='USA'
group by dept_no
DEPTO TOTALPORDEPTO
--------------------
000 266643
100 155262.5
110 130442.81
130 189042.94
180 107377.5
600 132900
…
Y finalmente, juntamos las dos condiciones: queremos ver los departamentos en los que se pagan más de U$S 200000 en total, contando solamente los empleados que trabajan en USA:
select dept_no as depto, sum(salary) as TotalPorDepto
from employee
where job_country='USA'
group by dept_no
having sum(salary)>200000
DEPTO TOTALPORDEPTO
--------------------
000 266643
621 276739.5
623 287758.25
671 219465.19
Solamente quedan esos departamentos después de todos los filtros. Observe el orden de las distintas palabras clave en la sentencia: este orden debe respetarse.
Como ejemplo final, veamos esta misma lista pero ordenada de mayor a menor por monto:
select dept_no as depto, sum(salary) as TotalPorDepto
from employee
where job_country='USA'
group by dept_no
having sum(salary)>200000
order by 2 desc
Note que usamos el índice del campo de agregado. Hasta la versión 1.02 de Firebird ésta era la única manera de hacerlo, ya que no se podían usar expresiones en la cláusula ORDER BY. En Firebird 1.5 esto se ha corregido, por lo que la sentencia anterior puede escribirse (igual que en la mayoría de los gestores de Bases de Datos):
select dept_no as depto, sum(salary) as TotalPorDepto
from employee
where job_country='USA'
group by dept_no
having sum(salary)>200000
order by sum(salary) desc
Notemos la potencia del lenguaje; imagínese lo que tendría que hacer para obtener este resultado usando un cursor, recorriendo los registros uno por uno…
Hay casos en los que no nos basta con una consulta de selección. Pensemos por ejemplo cómo podríamos obtener una tabla con registros en los que figure cierto valor, que se debe obtener de otra consulta. Un caso típico se da en las relaciones 'muchos-a-muchos', en las que tenemos dos tablas relacionadas entre sí a través de una tercera.
Por ejemplo, tomemos el caso de las tablas Employee y Project de la base de datos Employee.gdb que hemos trabajado antes. Estas tablas no se referencian entre sí directamente: no hay un campo en la tabla de empleados que referencie a un proyecto en particular –si este fuera el caso, cada empleado podría estar solamente en un proyecto a la vez. Lo mismo pasa con los proyectos: si hubiera un campo para almacenar un número de empleado, solamente ese empleado podría estar en ese proyecto… La situación real es más compleja. Los empleados pueden estar en más de un proyecto, y los proyectos pueden tener más de un empleado. Para esto se necesita una tercera tabla que contenga las relaciones. En Employee.gdb esta tabla se llama Employee_Project.
La tabla Employee_Project tiene la siguiente estructura:
CREATE
TABLE EMPLOYEE_PROJECT (
EMP_NO EMPNO NOT NULL,
PROJ_ID PROJNO NOT NULL,
PRIMARY KEY (EMP_NO,PROJ_ID)
);
Como vemos, solamente tiene dos campos: uno referencia a un empleado, el otro a un proyecto. Cada registro de esta tabla relaciona a un empleado con un proyecto, y la clave primaria es la conjunción de los dos campos para permitir repeticiones en cualquiera de ellos por separado pero no en los dos juntos (un mismo empleado no se puede poner en un mismo proyecto dos veces).
Ahora pensemos en la siguiente pregunta: ¿cuáles son los nombres de los proyectos en los cuales participa Bruce Young? Intente obtener una respuesta con SQL antes de seguir leyendo.
Podemos ejecutar la siguiente secuencia de acciones:
1) buscar el nro. de empleado de Bruce Young, ya que éste es el campo que existe en la tabla intermedia Employee_Project. Es muy simple:
select emp_no
from employee
where first_name='Bruce'
and last_name='Young'
EMP_NO
-----------
4
2) buscar los proyectos en que participa este empleado:
select *
from employee_project
where emp_no=4
EMP_NO PROJ_ID
-------------------
4 VBASE
4 MAPDB
3) obtener los nombres de los proyectos 'VBASE' y 'MAPDB'
select Proj_id,Proj_Name
from project
where proj_id in ('VBASE','MAPDB')
PROJ_ID PROJ_NAME
-----------------------------
VBASE Video Database
MAPDB MapBrowser port
Esto fue fácil, dirá usted. Sí. Pero no resulta difícil encontrarse relaciones de este tipo en las que un registro de una tabla se relaciona con cientos o miles de registros de la otra… en ese caso, sería muy engorroso y tal vez imposible de realizar en un tiempo prudencial la escritura de todos los 'ID' de la segunda tabla en el conjunto enviado al operador IN.
Esta es una de las
aplicaciones de las subconsultas, como ya vimos en la sección de los criterios
de selección: el argumento para el operador IN puede ser el resultado de una
consulta. En este caso,
quedaría
select Proj_id,Proj_Name
from project
where proj_id in (select Proj_Id from employee_project where emp_no=4)
PROJ_ID PROJ_NAME
-----------------------------
VBASE Video Database
MAPDB MapBrowser port
El resultado es el mismo, como era de esperar. Lo que hemos hecho es juntar varios pasos en uno solo.
Y la pregunta de examen: ¿se puede obtener el mismo resultado con una sola sentencia? El resultado es afirmativo. Se pueden ejecutar todas las operaciones de selección en una sola como la siguiente:
select Proj_id,Proj_Name
from project
where proj_id in
(select Proj_Id from
employee_project where emp_no=
(select emp_no from employee
where first_name='Bruce' and last_name='Young')
)
Observe especialmente que cada subconsulta va encerrada entre paréntesis.
Este no es, claro, el único caso en que se utilizan las subconsultas. Prácticamente cualquier problema que requiera la ejecución de sentencias de selección intermedias, cuyos resultados luego se usarán para obtener la respuesta, se puede escribir usando subconsultas.
Comparación con el resultado de una subconsulta
Los siguientes operadores se pueden usar con subconsultas:
<valor> ALL <subconsulta>: devuelve TRUE si el valor es igual a todos los devueltos por la subconsulta
<value> ANY | SOME <subconsulta>: devuelve TRUE si el valor comparado es igual a alguno de los devueltos por la subconsulta.
EXISTS <subconsulta>: devuelve TRUE si la subconsulta devuelve al menos una fila.
Ejemplo: determinar si hay al menos un proyecto en el que trabaje Leslie Johnson
select 'Si' as Hay_un_proyecto
from project
where exists
(select Proj_Id from
employee_project where emp_no=8)
HAY_UN_PROYECTO
----------------
Si
Si
Si
Si
Si
Si
Pruebe con un nro de empleado distinto, por ejemplo 1 (no existe) o 2 (no está asignado a ningún proyecto).
La respuesta es un tanto repetitiva… el hecho es que se mostrará un 'Si' por cada proyecto existente en la tabla 'Project', dado que la subconsulta no está relacionada con la consulta exterior. Entonces, por cada registro de la tabla Project se evalúa la subconsulta y ésta siempre es la misma, siempre da el mismo resultado. Veremos dos maneras de evitar las repeticiones:
1) Usando una tabla con un solo registro en la consulta externa. En Firebird / Interbase existe una tabla así en todas las bases de datos: es la que almacena características de la base de datos en sí, y por consiguiente sólo tiene un registro. Se llama rdb$database. La consulta anterior quedaria de la siguiente manera
select 'Si' as Hay_un_proyecto
from rdb$database
where exists
(select Proj_Id from
employee_project where emp_no=8)
Esta consulta devuelve un solo 'Si' si hay al menos un proyecto que contenga al empleado, y NULL en caso que no haya ninguno. Es bastante eficiente ya que la subconsulta se ejecuta una sola vez.
2) Usando una subconsulta correlacionada. En este caso la subconsulta se relaciona con la consulta exterior a través de algún campo, con lo que la evaluación de la subconsulta en cada registro de la exterior puede arrojar un resultado diferente.
select 'Si' as Hay_un_proyecto
from project
where exists
(select Proj_Id
from employee_project