# Bases de données

## Table of Contents

use seismes;

## 1. Villes

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) ;

## 2. Séismes

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;

Celle ci est trop longue pour être exécutée.

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 ;