Práctica I Mysql (Consultas a varías tablas)

Como ya os habréis imaginado las consultas a una sola tabla no pueden responder todas las preguntas que se podemos hacer a un diseño. Sin ir más lejos, en base de datos world podríamos preguntar, cual es el idioma oficial y el nombre nativo del país con la ciudad que tiene más habitantes.

Para resolver esa pregunta tendríamos que consultar tres tablas, la tabla de  los idiomas (idioma oficial), la tabla países (nombre nativo) y la tabla ciudades (población).

Tenemos varías formas de intentarlo, usando variables, con select anidados, pero la forma que usa habitualmente para hacer esto es la siguiente.

Mysql permite sin ningún problema hacer consultas sobre más de una tabla, de tal forma que podemos hacer:

select City.countrycode,Country.name from City,Country;

De esa forma nos saldrán todos los códigos de países y todos nombres de paises, pero si lo probamos veremos que la inforamición mostrada es incoherente.

Falta un pequeño detalle, las claves foraneas, cada cidad se relaciona con un país mediante su código de país, por tanto deberemos hacer:

select City.countrycode,Country.name from City,Country where countrycode=code;

Esa técnica que acabamos de usar se denomina join (de los que usaremos de manera implícita el inner join) y básicamente consiste en:

  • Con esta operación se calcula el producto cruzado de todos los registros; así cada registro en la tabla A es combinado con cada registro de la tabla B; pero sólo permanecen aquellos registros en la tabla combinada que satisfacen las condiciones que se especifiquen. Este es el tipo de JOIN más utilizado por lo que es considerado el tipo de combinación predeterminado.

Realiza estas consultas en la base de datos world

  1. Enumera todos los idiomas que se hablan en USA
  2. Obtén la superficie de cada país y el  número de ciudades.
  3. Averigua la longevidad media en todos los países que hablan Español.
  4. Cuantas ciudades tenemos en Spain.
  5. ¿Cómo puedes averiguar el número de habitantes de cualquier país que no reside en una ciudad?
  6. ¿Qué países tienen por idioma oficial el inglés?
  7. De todas las ciudades que tenemos en un país que sus habitantes llaman España, cuales tienen más de 10.000 habitantes?.
  8. Saca cada país con su nombre completo y el número de distritos.
  9. Saca cada ciudad con el país al que corresponde, ordenado por ciudad.
  10. Obtén una lista con los siguientes campos: Ciudad, poblacion, país, superficie, idioma oficial.
  11. Obtén una lista con los siguientes campos: Ciudad, poblacion, país, superficie, idioma oficial. Agrupada por países.
  12. Lee el artículo de Wikipedia y define: inner join, theta join y equi-join. Prueba con alguno de los ejercicios anteriores.
  13. Obtén el nombre de la capital de todos los países.
  14. Di el nombre de la capital del país más grande.
  15. Di el nombre de la capital del país con más esperanza de vida.
  16. Di el nombre de la capital del país con más población.
  17. Lista todos los países con sus capitales y la lengua oficial
  18. Lista todos los países con más de 1 millón de habitantes con sus capitales y la lengua oficial
  19. Lista todos los países con más de 1 millón de habitantes con sus capitales y sus lenguas no oficiales.
  20. Cuantos idiomas tiene cada país.
  21. ¿Tenemos algún país con dos lenguas oficiales? (hacer con having)
  22. Saca el jefe de gobierno de un país cuya capital es Madrid.
  23. Convierte el modelo relacional de la base de datos World a un modelo referencial, donde podamos tener claves foráneas. La integridad reverencial deberá cumplir al menos:
    1. No podrán insertarse ciudades que no correspondan a ningún país
    2. No podremos tener lenguajes que no correspondan a un país.
    3. Si borro un país borraremos todas las ciudades y lenguajes de ese país.
    4. Toda capital deberá ser Ciudad del país al que corresponde.
  24. Una vez hecho esto, exporta tu modelo a SQL. Borra la base de datos world y vuelve a importarla.

Las Vistas

Vamos a introducir el concepto de vista para irnos familiarizarnos con ellas.

  • Una vista corresponde a un conjunto de columnas de una o varía tablas.
  • Cuando se modifican las tablas originales la vista cambia automáticamente.
  • En lenguaje poco técnico podríamos decir que una vista es una consulta usada como tabla

Creación de Vistas

CREATE VIEW nombre_ciudades_poblacion AS SELECT name as nombre_ciudad,population as poblacion FROM City;
Select * from nombre_ciudades_poblacion;

Ejercicios

  • Crea las vistas de los ejercicios, 5,6,8,10,13,14 y 15.
  • Resuelve el problema de los distritos (entrada anterior, la de consultas sobre una sola tabla) usando vistas.

Relaciones de una tabla consigo mismo

Una tabla puede relacionarse consigo mismo usando alias para tablas de esta manera, veamos algunos ejemplos:

Supongamos la tabla siguiente:

Queremos obtener un conjunto de resultados con el nombre del empleado y el nombre de su jefe:

SELECT Emple.Nombre, Jefes.Nombre

FROM   Empleados Emple, Empleados Jefe

WHERE

Emple.SuJefe = Jefes.Id

Para probar esto:

  1. Crea la tabla de empleados, en una base de datos que se llame empleados
  2. Lanza la consulta

Otras consultas

  • Saca todas las ciudades cuya población sea mayor que la de algún país.
  • Saca todos los países que tengan más población que españa (self-join).
  • Ahora solo piensa en las posibles soluciones para:
    • Saca todos los países más pequeños que Francia (self-join).
    • Saca la ciudad con más habitantes usando (self-join).

One Response to “ Práctica I Mysql (Consultas a varías tablas) ”

  1. Julian dice:

    Perdon se ha colado una parte que no debía.

    ENUNCIADO
    Saca todas las ciudades cuya población sea mayor que la de algún país.
    CODIGO
    select name from city where population>(Select min(population) from country);
    ENUNCIADO
    Saca todos los países que tengan más población que españa (self-join).
    CODIGO
    select name from country where population>(select population from country where code=’esp’);

Deja un comentario

Disculpa, debes iniciar sesión para escribir un comentario.