SQL - Base de données de séismes

/* L’ensemble des noms des villes du Japon ("Japan"). */
SELECT nom FROM villes WHERE pays = "Japan" ;

/* L’ensemble des noms des villes ayant plus de dix millions d’habitants et leur
   population. */
SELECT nom, population FROM villes WHERE population >= 1e7 ;

/* L’ensemble distinct des codes iso3 triés par ordre croissant. */
SELECT DISTINCT iso3 FROM villes ORDER BY iso3 ;

/* L’ensemble des villes de France et leur population arrondies au millier
   inférieur, triées par ordre décroissant de population. */
SELECT nom, floor(population / 1000) * 1000 AS pop_M
    FROM villes
    WHERE pays = "France"
    ORDER BY population DESC ;
 
/* Le nom et le pays des 10 villes les plus peuplées du monde. */
SELECT nom, pays FROM villes ORDER BY population DESC LIMIT 10 ;

/* L'altitude moyenne des stations. */
SELECT AVG(population) FROM villes.

/* Le nom et la population de chaque pays, ordonnés par ordre décroissant de
   population. La population de chaque pays sera obtenue en sommant la
   population de chaque ville du pays. */
SELECT pays, SUM(population) AS pop_pays
    FROM villes
    GROUP BY pays
    ORDER BY pop_pays DESC ;
 
/* Pour chaque tranche de dix mille habitants, la borne inférieur de la tranche
   et le nombre de villes ayant une population dans cette tranche. */
SELECT FLOOR(population / 1e5) AS tranche, COUNT(*)
    FROM villes
    GROUP BY tranche ;
 
/* Le nom des pays ayant une population plus grande que 10 millions
   d'habitants. */
SELECT pays, SUM(population) as pays_pop
    FROM villes GROUP BY pays HAVING pay_pop > 1e7 ;
 
/* Le nom et le nombre de villes ayant plus de cent mille habitants des pays
   ayant plus de 30 villes de plus de 100.000 habitants. Les résultats seront
   ordonnés par ordre lexicographique de nom de ville. */
SELECT pays, COUNT(*) AS nb_ville FROM villes WHERE population >= 1e5
    GROUP BY pays HAVING nb_ville >= 30
    ORDER BY pays;
 
/* Le nom du pays ayant le plus grand nombre de villes. */
SELECT pays
    FROM (SELECT pays, COUNT(*) AS nb_ville
               FROM villes
               GROUP BY pays
               ORDER BY nb_ville DESC LIMIT 1) AS t ;
             
/* Pour chaque pays le nom et le statut de capitale de la ville avec le plus
   d’habitant et son statut de capitale. */
SELECT pays, nom, capitale
     FROM villes AS v1
     WHERE population = (SELECT MAX(population)
                             FROM villes AS v2
                             WHERE v2.pays = v1.pays) ;

/* La date de chaque séisme et le pays dans lequel est la ville la plus proche
   du séisme. */
SELECT se.date, v.pays
    FROM seismes AS se
    JOIN villes AS v ON se.city = v.id ;
 
/* Pour chaque séisme, sa date, son identifiant et l'altitude de la station qui
   l'a enregistré. */
SELECT se.date, se.id, st.altitude
    FROM seismes AS se
    JOIN stations AS st ON se.station = st.code ;
 
/* La date et la magnitude de chaque séisme et l’état nation dans lequel il a
   été enregistré. */
SELECT se.date, se.magnitude, st.etat
    FROM seismes AS se
    JOIN stations AS st ON se.station = st.code ;
    ORDER BY MAX(magnitude) DESC LIMIT 12 ;
 
/* Les identifiants des séismes qui ont eu lieu proche d'une capitale
   administrative, ainsi que le nom de cette capitale.. */
SELECT se.id, v.nom
    FROM seismes AS se
    JOIN villes AS v ON se.city = v.id
    WHERE v.capitale = "admin" ;
 
/* Le nom des villes qui ont connue un séisme lié à l'activité minière. */
SELECT DISTINCT v.nom
    FROM seismes AS se
    JOIN villes AS v ON se.city = v.id
    WHERE se.type = "mining" ;
 
/* L'ensemble des paires de nom de ville et de code de stations telles que la
   station a enregistré un séisme proche de la ville. */
SELECT DISTINCT st.code, v.nom
    FROM seismes AS se
    JOIN villes AS v ON se.city = v.id
    JOIN station AS st ON se.station = st.code ;
 
/* Les codes des stations ouvertes qui ont enregistré un séisme dont la ville la
   plus proche possède plus de 5000 habitants. */
SELECT DISTINCT st.code
    FROM seismes AS se
    JOIN villes AS v ON se.city = v.id
    JOIN station AS st ON se.station = st.code
    WHERE v.population >= 5000 ;
 
/* Les deux stations les plus proches du monde. */
SELECT s1.code AS station_1, s2.code AS station_2,
    FROM stations AS s1, stations AS s2
    WHERE s1.code != s2.code
    ORDER BY ACOS(SIN(s1.latitude)*SIN(s2.latitude)
             + COS(s1.latitude)*COS(s2.latitude)*COS(s2.longitude−s1.longitude))
             * 6.371
    LIMIT 1 ;

/* Le pays qui a en moyenne les séismes de plus grande magnitude. */
SELECT v.pays, AVG(se.magnitude) as avg_mag
    FROM seismes AS se
    JOIN villes AS v ON se.city = v.id
    GROUP BY v.pays
    ORDER BY avg_mag DESC LIMIT 1 ;
 
/* Les 12 pays qui ont connu le plus de séismes. */
SELECT v.pays
    FROM seismes AS se
    JOIN villes AS v ON se.city = v.id
    GROUP BY v.pays
    ORDER BY COUNT(*) DESC LIMIT 12 ;
 
/* Les 12 villes ayant connu les séismes de plus grande magnitude. */
SELECT v.nom
    FROM seismes AS se
    JOIN villes AS v ON se.city = v.id
    GROUP BY se.city
    ORDER BY MAX(magnitude) DESC LIMIT 12 ;
 
/* Les villes qui en moyenne subissent des séismes de magnitude
   supérieure à 5, ainsi que la dîte moyenne. */
SELECT v.nom, AVG(se.magnitude) AS avg_mag
    FROM seismes AS se
    JOIN villes AS v ON se.city = v.id
    GROUP BY se.city
    HAVING avg_mag >= 5 ;
 
/* Le nom des pays qui ont connu au moins un séisme de magnitude 7 et qui
   possède plus de 10 millions d'habitants. */
SELECT v.pays
    FROM seismes AS se
    JOIN villes AS v ON se.city = v.id
    GROUP BY v.pays
    HAVING SUM(v.population) >= 1e7 AND MAX(se.magnitude) >= 7 ;
 
/* Pour chaque ville, son nom et l'altitude de la plus haute station qui a
   enregistré un séisme proche de cette ville. */
SELECT v.nom, MAX(st.altitude)
    FROM seismes AS se
    JOIN villes AS v ON se.city = v.id
    JOIN stations AS st ON se.station = st.code
    GROUP BY v.id
 
/* Les codes des 12 stations qui ont enregistré le plus de séismes de magnitude
   plus grande que 4, et les états dans lesquels elles se trouvent. */
SELECT st.code, st.etat
    FROM seismes AS se
    JOIN stations AS st ON se.station = st.code
    WHERE se.magnitude >= 4
    GROUP BY st.code
    ORDER BY COUNT(*) DESC LIMIT 12 ;

/* La latitude et la longitude arrondies à l’entier supérieur auxquelles il y a
   à la fois une ville et une station. */
(SELECT CEIL(latitude), CEIL(longitude) FROM stations)
    INTERSECT
(SELECT CEIL(latitude), CEIL(longitude) FROM villes) ;

/* La latitude et la longitude arrondies à l’entier supérieur auxquelles il y a
   une ville, mais aucun séisme enregistré. */
(SELECT CEIL(latitude), CEIL(longitude) FROM villes)
    EXCEPT
(SELECT CEIL(latitude), CEIL(longitude) FROM seismes) ;

/* L'identifiant et la date du séisme le plus fort enregistré par chaque station
   actuellement ouverte. */
SELECT t_max_s.code, se1.id, se1.date
    FROM seismes as se1
    JOIN (SELECT st.code, MAX(se2.magnitude) as max_mag
              FROM seismes AS se2
              JOIN stations AS st ON se2.station = st.code
              WHERE ouverture = "Open"
              GROUP BY code ) AS t_max_s ON se1.station = t_max_s.code
    WHERE s1.magnitude = t.max_mag ;
 
/* Pour chaque ville Japonaise ("Japan" dans la table), le nom de la ville,
   l'identifiant et la date du séisme le plus récent dont cette ville est le
   plus proche. */
SELECT t_ville_date.nom_ville, se1.id, t_ville_date.date_recente
    FROM seismes AS se1
    JOIN (SELECT v.nom AS nom_ville, v.id AS id_ville,
                 MAX(se2.date) as date_recente
              FROM seismes AS s2
              JOIN villes AS v ON se2.city = v.id
              WHERE pays = "Japan"
              GROUP BY se2.city ) AS t_ville_date
         ON t_ville_date.id_ville = se1.city
    WHERE se1.date = t_ville_date.date_recente;