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:

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.

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

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


Categorías: MySQL
Conceptos importantes: :
1 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’);

Leave a Reply

You must be logged in to post a comment.