Bases de données

  select nom from villes where pays = "Japan" ;

  select nom, population from villes where population >= 1e7 ;

  select distinct iso3 from villes order by iso3 ;

  select nom, floor(population / 1000) * 1000 as pop_M from villes 
  where pays = "France" order by population desc ; 

  select nom, pays from villes order by population desc limit 10 ;

  select pays, sum(population) as pop_pays from villes group by pays order by pop_pays desc ;

  select pays, count(*) as nb_ville from villes where population >= 1e5 
      group by pays having nb_ville >= 30 order by pays;

  select pays from 
      (select pays, count(*) as nb_ville from villes group by pays order by nb_ville desc limit 1) as t ;

  select floor(population / 1e5) as tranche, count(*) from villes group by tranche ;

  select pays, nom, capitale from villes as v1
      where population = (select max(population) from villes as v2 where v2.pays = v1.pays) ;

  select date, pays from seismes join villes on city = villes.id ;

  select date, magnitude, etat from seismes join stations on station = code ;

  select s1.id from seismes as s1 join 
      (select code, max(magnitude) as max_mag from seismes as s2 join 
           stations on s2.station = code and ouverture = "Open" group by code ) as t on 
      s1.station = t.code and s1.magnitude = t.max_mag ;

  select id from seismes as s1 join  
      ( select villes.id as id_ville, max(date) as date_recente from seismes as s2 join villes 
           on s2.city = villes.id and pays = "Japan" group by s2.city ) as t 
      on t.id_ville = city and s1.date = date_recente;

  (select ceil(latitude), ceil(longitude) from stations) intersect (select ceil(latitude), ceil(longitude) from villes) ;

  (select ceil(latitude), ceil(longitude) from villes) except (select ceil(latitude), ceil(longitude) from seismes) ;

  select pays, avg(magnitude) as avg_mag from seismes join villes on city = villes.id group by pays order by avg_mag desc limit 1 ;

  select pays from 
      (select pays, count(*) as nb_seisme from seismes join villes on city = villes.id 
          group by pays order by nb_seisme desc limit 12) as t ;

  select nom from 
      (select nom, max(magnitude) as max_mag from seismes join villes on city = villes.id 
          group by city order by max_mag desc limit 12) as t ;

  select t.station, etat from stations as s join
  ( select station, count(*) as nb_seisme from seismes where magnitude >= 4 
       group by station order by nb_seisme desc limit 12 ) as t on t.station = s.code;

  select s1.code as station_1, s2.code as station_2, 
      power(s1.latitude - s2.latitude, 2) + power(s1.longitude - s2.longitude, 2) as dist from 
          stations as s1 join stations as s2 on s1.code != s2.code order by dist limit 1 ;

  select id_s from 
      (select sum(population) as total_pop, s.id as id_s from seismes as s join villes as v 
          on power(v.latitude - s.latitude, 2) + pow(v.longitude - s.longitude, 2) <= 1e4 
          group by s.id order by total_pop limit 12) as t ;