PDF de programación - Sistemas de Información II - Práctica III: Consultas en SQL

Sistemas de Informaci´on II – Pr´actica III: Consultas en SQL

Carlos Castillo / Victor Pascual – [email protected]

1. Uso de su propia base de datos

Recuerda que nos conectaremos primero a otro servidor. Abre un terminal en Linux y ejecuta:

ssh 193.145.45.40 -l XXXXXXX
[email protected]’s password: YYYYYYY

XXXXXXX e YYYYYYY fueron entregados durante la clase de pr´acticas. A continuaci´on nos conectare-

mos a la base de datos, usando:

mysql si2_NN -u si2_NN -p
Enter password: (tu password)

Lo primero es verificar que los datos de la pr´actica anterior sigan ah´ı:

mysql> SELECT * FROM comunitat;

El objetivo de esta pr´actica es aprender m´as comandos SQL relacionados con seleccionar datos. Adem´as
se incluyen algunas actividades menos gu´ıadas que en la pr´actica anterior, la idea es intentar inferir c´omo
hacer algunas operaciones, siguiendo la l´ogica de los comandos SQL que hemos visto hasta ahora.

2. SQL IN

La instrucci´on SQL IN corresponde a la relaci´on matem´atica de pertenencia ∈. Ejemplo:

mysql> SELECT m_id,nom from municipi WHERE nom IN (’Biel’,’Zuera’);

Lo interesante es que lo que se encuentra dentro de la cl´ausula IN (. . .) puede ser a su vez una consulta

SELECT, ejemplo:

SELECT DISTINCT nom FROM comunitat WHERE ca_id IN

(

SELECT ca_id
FROM municipi
WHERE nom LIKE ’Santa Mar_a del %’

);

Como sabemos, los espacios y saltos de l´ınea no son relevantes.
Actividad: para cada uno de los siguientes requerimientos, escribir una consulta que usando IN, en-

tregue:

Todas las comunidades con un municipio de menos de 1.000 habitantes.

Todas las comunidades con un municipio que tenga superficie entre 2.000 y 3.500.

Todas las comunidades con un municipio que empiece en la letra z.

Reescriba tambi´en esta ´ultima consulta sin usar IN, el resultado debe ser el mismo.

1

3. SQL HAVING

La instrucci´on SQL HAVING se usa para especificar una condici´on, y por tanto funciona igual que WHERE,

pero se utiliza despu´es de haber ejecutado una funci´on de agregaci´on, ejemplo:

mysql> SELECT comunitat.nom, SUM(municipi.poblacio2003)

FROM municipi,comunitat
WHERE comunitat.ca_id=municipi.ca_id
GROUP BY comunitat.nom
HAVING SUM(municipi.poblacio2003) > 5000000;

+---------------------+----------------------------+
| nom
| SUM(municipi.poblacio2003) |
+---------------------+----------------------------+
|
| Andaluc´ıa
7606848 |
6704146 |
| Catalunya
|
| Comunidad de Madrid |
5718942 |
+---------------------+----------------------------+

Actividad: escribir una consulta que, usando HAVING, entregue:

El nombre de la comunidad y el n´umero de municipios de las comunidades con m´as de 1000 municipios
(es s´olo uno).

El nombre de la comunidad y la superficie promedio de sus municipios de las comunidades con entre
500 y 800 municipios.

El nombre de la comunidad y la densidad de poblaci´on de las comunidades con m´as de 1000 habitantes
por unidad de superficie en promedio (son s´olo dos, y no son Madrid y Catalunya).

4. SQL UPDATE

Para actualizar datos en una tabla, se utiliza la instrucci´on UPDATE. La sintaxis es la siguiente:

UPDATE nombretabla
SET columna=valor

WHERE condici´on

Primero, vamos a crear una tabla comunitat2 para no da˜nar los datos originales.
Actividad: crear una tabla comunitat2. La operaci´on SHOW COLUMNS FROM comunitat2 debe entregar

un resultado id´entico a SHOW COLUMNS FROM comunitat.
Ahora, para copiar datos de una tabla a otra, se usa:

INSERT INTO comunitat2 (ca_id,nom)

(

SELECT ca_id,nom FROM comunitat

);

En cualquier caso puede ser ´util saber que para borrar una tabla se usa:

DROP TABLE comunitat2;

Y para vaciar una tabla (borrar los datos sin borrar el esquema de la tabla), se usa:

TRUNCATE comunitat2;

2

Una vez que tenemos una tabla comunitat2 con los mismos datos que comunitat, podemos actualizar

datos.

Actividad: crear una tabla municipi2 con los mismos datos que municipi, a continuaci´on, realizar las

siguientes actualizaciones (en las copias, no en los originales):

Poner la poblaci´on1991 en 0 para todos los municipios que empiecen con la letra “a”.

Poner la superficie a la mitad en todos los municipios que tengan m´as de 2000 habitantes.

Actividad: crear una tabla municipi cat que tenga todos los datos de los municipios de Catalunya.

5. SQL DELETE

Por ´ultimo, para borrar filas de una tabla, se utiliza DELETE. Esta instrucci´on funciona igual que UPDATE

y es incluso m´as sencilla.

DELETE FROM tabla

WHERE condici´on

Actividad: escribir una consulta que haga lo siguiente (en la tabla municipi2):

Borrar un municipio espec´ıfico de la tabla (cualquiera, pero que borre s´olo uno).

Borrar los municipios que no tengan habitantes en el 2003.

6. Actividad dif´ıcil

Esta actividad es guardar un dato derivado en la tabla comunitat2.
Actividad: agregar a la tabla comunitat2 una columna con poblaci´on y una columna con superficie, y
llenar esa columna con los datos reales obtenidos de sumar la superficie y poblaci´on total de los municipios.

Se puede hacer con 4 instrucciones:

Una instrucci´on para agregar la columna de poblaci´on.

Una instrucci´on para agregar la columna de superficie.

Una operaci´on UPDATE que se combina con un SELECT para copiar el dato de poblaci´on total.

Una operaci´on UPDATE que se combina con un SELECT para copiar el dato de superficie total.

Hint: en el SELECT s´olo debe mencionar una tabla en la cl´ausula FROM, y debe incluir una cl´ausula WHERE

para que correspondan correctamente las tablas municipi y comunitat2.

Los datos deben extraerse de la tabla municipi porque la tabla municipi2 contiene informaci´on falsa

despu´es de las actividades de UPDATE.

7. RESULTADO

Lo que se debe entregar son todos los comandos SQL que ejecuten todas las actividades de esta sesi´on

(no la salida de mysql).

El resultado de esta pr´actica debe entregarse hoy mismo v´ıa Campus Global.

3