1. Obtención de los datos: una relación de la población total de los municipios de la provincia de Jaén contabilizada en los años 1970, 1981, 1986, 1991 y 1993.
2. Creación de la tabla de carga de datos.
3. Redacción del programa de control.
4. Carga de los datos a una tabla de la base de datos.
5. Realización de consultas sobre los datos.

Obtención de los datos

Los datos que nos interesan se encuentran en la dirección de Internet http://www.cs.us.es/cursos/bd/datos/jaen.dat y se han obtenido a partir de las siguientes fuentes:

• Anuario estadístico de la provincia de Jaén. Instituto de Estudios Gienenses, Diputación Provincial de Jaén, 1992.
• Población de derecho de los municipios españoles. Rectificación del Padrón Municipal de Habitantes a 1 de Enero de 1993. Instituto Nacional de Estadística (INE), 1994.

Cada línea del fichero contiene la información relativa a un único municipio: nombre del municipio, población en 1970, población en 1981, población en 1986, población en 1991 y población en 1993. Los datos están dispuestos en un formato libre, sin ocupar unas posiciones fijas en cada línea del fichero, sino dispuestos secuencialmente y separados por comas.

Ibros, 3914, 3163, 3252, 3121, 3206 Iruela (La), 3570, 2360, 2374, 2101, 2146 Iznatoraf, 2062, 1842, 1422, 1266, 1277 Jabalquinto, 3165, 2841, 2765, 2540, 2722 Jaen, 77317, 95783, 102933, 103260, 110042 Jamilena, 3202, 3063, 3079, 3115, 3181 Jimena, 2324, 1965, 1891, 1669, 1671

Los datos pueden transladarse a la cuenta de usuario utilizando la opción de grabación de fichero de texto del programa navegador.

Creación de la tabla de carga de datos

CREATE TABLE pjaen( num NUMBER(3) PRIMARY KEY, nombre CHAR(30) NOT NULL, p1970 NUMBER(6) NOT NULL, p1981 NUMBER(6) NOT NULL, p1986 NUMBER(6) NOT NULL, p1991 NUMBER(6) NOT NULL, p1993 NUMBER(6) NOT NULL);

Redacción del programa de control

La redacción del fichero de control requiere utilizar un editor de texto, por ejemplo emacs. En nuestro caso se redactará el archivo carga-jaen.ctl con el siguiente contenido:

LOAD DATA APPEND INTO TABLE pjaen FIELDS TERMINATED BY ',' (num SEQUENCE(COUNT,1), nombre, p1970, p1981, p1986, p1991, p1993)

Mediante APPEND se indica que los datos procedentes del archivo de entrada se añadirán a los que ya existieran en la tabla. La cláusula INTO TABLE especifica el nombre de la tabla en la que se van a insertar los datos leidos. Como el formato del fichero de datos no es fijo, no se especifica para cada atributo a leer las posiciones de la línea del fichero de datos en que se encuentra, sino que simplemente se especifican en el orden adecuado. Mediante la cláusula FIELDS TERMINATED BY se indica que los atributos se finalizan mediante un carácter de coma.

No es necesario indicar el tipo de los datos almacenados en el fichero de entrada pues el programa cargador procede a su conversión automática según el atributo del registro lógico en cuestión. Para el atributo num se genera automáticamente un número, con valor inicial el número de filas que ya estuvieran almacenadas en la tabla (cero si la tabla está vacía) que se incrementa en uno cada vez que se inserta una nueva fila.

Carga de los datos

sqlload usuario/palabra_clave CONTROL=carga-jaen.ctl, DATA=jaen.dat

En este caso la ejecución de SQL*Loader genera únicamente el fichero carga-jaen.log. No se ha generado un fichero con la relación de datos de entrada erróneos pues no se ha producido ningún error ni tampoco se ha descartado ningún dato del fichero de datos pues no se ha especificado ninguna condición a tal efecto en el fichero de control (cláusulas WHEN). Todo esto puede comprobarse examinando los diversos contenidos del fichero de resultados.

96 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null.

La información más interesante especificada en el archivo de resultados es el número total de filas insertadas en la base de datos. Puede comprobarse que la información ha sido realmente insertada en la tabla realizando una consulta desde SQL*Plus.

SELECT nombre, p1970, p1993 FROM pjaen;

Consultas sobre los datos

SELECT SUM(p1970), SUM(p1981), SUM(p1986), SUM(p1991), SUM(p1993) FROM pjaen; SELECT AVG(p1970), AVG(p1981), AVG(p1986), AVG(p1991), AVG(p1993) FROM pjaen;

Consultas con subconsultas anidadas

Determinar el número de municipios cuya población en 1993 no supera en más de un diez por ciento la media poblacional de la provincia.

SELECT COUNT(*) FROM pjaen WHERE p1993 <= (SELECT 1.1 * AVG(p1993) FROM pjaen);

Determinar el municipio con mayor crecimiento absoluto de población en el periodo 1970 a 1993.

SELECT nombre FROM pjaen WHERE p1993-p1970 = (SELECT MAX(p1993-p1970) FROM pjaen);

Determinar el municipio con mayor crecimiento relativo de población en el periodo 1970 a 1993.

SELECT nombre FROM pjaen WHERE (p1993-p1970)/p1970 = (SELECT MAX((p1993-p1970)/p1970) FROM pjaen);

Consultas con variables tupla

Las variables tupla o fila se asocian con una tabla concreta y se definen en la cláusula FROM de una sentencia SQL escribiendo el nombre de la variable tupla inmediatamente después del nombre de la tabla con la que se asocia. Estas variables son de gran utilidad para comparar dos tuplas de una misma tabla, lo que permite realizar consultas a una tabla con condiciones formuladas en términos de la misma tabla.

Determinar si hay al menos dos municipios con igual población en 1993.

SELECT s.nombre, t.nombre FROM pjaen s, pjaen t WHERE s.p1993 = t.p1993 AND s.nombre != t.nombre;

La comparación de los nombres de las dos variables tuplas es necesaria para no producir en la salida cada municipio emparejado consigo mismo, caso en el que trivialmente coinciden las poblaciones en 1993.

Determinar, para cada municipio, el número de otros municipios que poseen mayor o igual población (considerar los datos de 1993).

SELECT s.nombre, COUNT(*) FROM pjaen s, pjaen t WHERE t.p1993 >= s.p1993 GROUP BY s.nombre;

Considerando los datos poblacionales de 1993, determinar los municipios tales que al menos hay otros 80 municipios con mayor población.

SELECT s.nombre, COUNT(*) FROM pjaen s, pjaen t WHERE t.p1993 > s.p1993 GROUP BY s.nombre HAVING COUNT(*) >= 80;

Consultas con variables de sustitución

Las variables de sustitución proporcionan un mecanismo para la utilización de valores variables en una sentencia SQL. Se trata de valores que en principio han de ser comunicados por el usuario cada vez que se ejecuta la sentencia. Su sintaxis es de la forma &nombre (hay que asignar un valor que se pierde al finalizar la ejecución de la sentencia) o bien &&nombre (si la variable ya tiene valor se utiliza, si no hay que asignar un valor). En la realidad la sentencia SQL que se ejecuta es la resultante de sustituir todas las apariciones de variables de este tipo en la sentencia original por sus correspondientes valores. Estas variables son realmente cadenas que pueden almacenar cantidades, nombres de atributos, funciones y operaciones, etc. Por ejemplo: asignando el índice 100 a la población total en 1970, calcular el valor del índice de población para los restantes años.

En primer lugar es conveniente asignar la población total de la provincia a una variable de sustitución denominada pt1970 de la siguiente forma:

SELECT &&pt1970 FROM pjaen; Enter value for pt1970: SUM(p1970) old 1: SELECT &&pt1970 FROM pjaen new 1: SELECT SUM(p1970) FROM pjaen SUM(P1970) ---------- 668221

Los dos caracteres & indican que la variable ha de conservar su valor una vez ejecutada la sentencia SQL y que por lo tanto podrá ser reutilizada posteriormente en otras sentencias.

El valor del índice para cada uno de los otros años puede calcularse a partir de la población total de ese año y del valor almacenado en la variable pt1970. Para referenciar al año concreto puede utilizarse una variable de sustitución a la que se ha de asignar valor en cada ejecución sucesiva de la sentencia.

SELECT SUM(&1) / &&pt1970 * 100 AS indice FROM pjaen;

El valor que ha de asignarse a la variable de sustitución &1 en cada ejecución es el nombre de la columna o atributo para el que se desea calcular su suma en toda la tabla. Para ejecutar sucesivas veces la misma sentencia lo más cómodo es utilizar el comando RUN propio de SQL*Plus.

SQL> SELECT SUM(&1) / &&pt1970 * 100 AS indice FROM pjaen; Enter value for 1: p1981 old 1: SELECT SUM(&1) / &&pt1970 * 100 AS indice FROM pjaen new 1: SELECT SUM(p1981) / SUM(p1970) * 100 AS indice FROM pjaen INDICE ---------- 95.7499091 SQL> RUN 1* SELECT SUM(&1) / &&pt1970 * 100 AS indice FROM pjaen Enter value for 1: p1986 old 1: SELECT SUM(&1) / &&pt1970 * 100 AS indice FROM pjaen new 1: SELECT SUM(p1986) / SUM(p1970) * 100 AS indice FROM pjaen INDICE ---------- 97.1457048 SQL> RUN 1* SELECT SUM(&1) / &&pt1970 * 100 AS indice FROM pjaen Enter value for 1: p1991 old 1: SELECT SUM(&1) / &&pt1970 * 100 AS indice FROM pjaen new 1: SELECT SUM(p1991) / SUM(p1970) * 100 AS indice FROM pjaen INDICE ---------- 95.4224725 SQL> RUN 1* SELECT SUM(&1) / &&pt1970 * 100 AS indice FROM pjaen Enter value for 1: p1993 old 1: SELECT SUM(&1) / &&pt1970 * 100 AS indice FROM pjaen new 1: SELECT SUM(p1993) / SUM(p1970) * 100 AS indice FROM pjaen INDICE ---------- 98.1865281

Se desea calcular el número de municipios cuya población ha aumentado entre dos periodos consecutivos de tiempo (1970 a 1981, 1981 a 1986, 1986 a 1991, 1991 a 1993).

SELECT COUNT(*) FROM pjaen WHERE &1 > &2;

En cada ejecución de la sentencia se ha de asignar un valor a las variables de sustitución &1 y &2: el nombre de los atributos que representan la población de un municipio en los dos años considerados. Un posible ejemplo de ejecución, utilizando repetidas veces el comando RUN, sería el siguiente:

SQL> SELECT COUNT(*) FROM pjaen WHERE &1 > &2; Enter value for 1: p1981 Enter value for 2: p1970 old 1: SELECT COUNT(*) FROM pjaen WHERE &1 > &2 new 1: SELECT COUNT(*) FROM pjaen WHERE p1981 > p1970 COUNT(*) ---------- 12 SQL> RUN 1* SELECT COUNT(*) FROM pjaen WHERE &1 > &2 Enter value for 1: p1986 Enter value for 2: p1981 old 1: SELECT COUNT(*) FROM pjaen WHERE &1 > &2 new 1: SELECT COUNT(*) FROM pjaen WHERE p1986 > p1981 COUNT(*) ---------- 47 SQL> RUN 1* SELECT COUNT(*) FROM pjaen WHERE &1 > &2 Enter value for 1: p1991 Enter value for 2: p1986 old 1: SELECT COUNT(*) FROM pjaen WHERE &1 > &2 new 1: SELECT COUNT(*) FROM pjaen WHERE p1991 > p1986 COUNT(*) ---------- 22 SQL> RUN 1* SELECT COUNT(*) FROM pjaen WHERE &1 > &2 Enter value for 1: p1993 Enter value for 2: p1991 old 1: SELECT COUNT(*) FROM pjaen WHERE &1 > &2 new 1: SELECT COUNT(*) FROM pjaen WHERE p1993 > p1991 COUNT(*) ---------- 69

Se define el índice de evolución de la población como la población en 1991 dividida por la población en 1986. El índice puede expresarse como un porcentaje simplemente multiplicando por 100. Los municipios pueden clasificarse en una de cinco categorías según el valor del índice.

Se desea calcular el número de municipios que corresponden a cada una de las posibles clasificaciones.

SELECT COUNT(*) FROM pjaen WHERE 100*p1991/p1986 BETWEEN &1 AND &2;

Las variables de sustitución &1 y &2 han de tomar los valores correspondientes al extremo inferior y superior, respectivamente, del intervalo porcentual del índice para cada una de las cinco clases definidas. Por ejemplo, para calcular el número de municipios clasificados como muy regresivos y los clasificados como regresivos:

SQL> SELECT COUNT(*) FROM pjaen WHERE 100*p1991/p1986 BETWEEN &1 AND &2; Enter value for 1: 0 Enter value for 2: 80 old 1: SELECT COUNT(*) FROM pjaen WHERE 100*p1991/p1986 BETWEEN &1 AND &2 new 1: SELECT COUNT(*) FROM pjaen WHERE 100*p1991/p1986 BETWEEN 0 AND 80 COUNT(*) ---------- 6 SQL> RUN 1* SELECT COUNT(*) FROM pjaen WHERE 100*p1991/p1986 BETWEEN &1 AND &2 Enter value for 1: 80 Enter value for 2: 95 old 1: SELECT COUNT(*) FROM pjaen WHERE 100*p1991/p1986 BETWEEN &1 AND &2 new 1: SELECT COUNT(*) FROM pjaen WHERE 100*p1991/p1986 BETWEEN 80 AND 95 COUNT(*) ---------- 41

Análogamente se calcula el número de municipios que pertenecen a la categoría estacionario, progresivo y muy progresivo, en función del valor del índice de evolución de la población.