Mesures du réchauffement climatique⚓︎
D'après 2022, Centres étrangers, J1, Ex. 4
Rappel sur le langage SQL
Types de données
| Type | Description |
|---|---|
CHAR(t) |
Texte fixe de t caractères |
VARCHAR(t) |
Texte de t caractères variables |
TEXT |
Texte de \(65\,535\) caractères maximum |
INT |
Nombre entier de \(-2^{31}\) à \(2^{31} - 1\) (signé) ou de \(0\) à \(2^{32} - 1\) (non signé) |
FLOAT |
Réel à virgule flottante (approximation) |
DATE |
Date format AAAA-MM-JJ |
Quelques exemples de syntaxe SQL :
- Insérer des enregistrements :
INSERT INTO Table1 (attribut1, attribut2) VALUES (valeur1 , valeur2);
- Modifier des enregistrements :
UPDATE Table1 SET attribut1=valeur1, attribut2=valeur2 WHERE Selecteur;
- Supprimer des enregistrements :
DELETE FROM Table1 WHERE Selecteur;
- Sélectionner des enregistrements :
SELECT attributs FROM Table1 WHERE Selecteur;
- Sélectionner des enregistrements dans un ordre ascendant :
SELECT attributs FROM Table1 WHERE Selecteur ORDER BY attribut ASC;
- Sélectionner des enregistrements sans doublon :
SELECT DISTINCT attributs FROM Table1 WHERE Selecteur;
- Effectuer une jointure :
SELECT attributs FROM Table1 JOIN Table2 ON Table1.cle1=Table2.cle2 WHERE Selecteur;
Dans le cadre d'une étude sur le réchauffement climatique, un centre météorologique rassemble des données. On considère que la base de données contient deux relations (tables). La relation Centres qui contient l'identifiant des centres météorologiques, la ville, la latitude, la longitude et l'altitude du centre. La relation Mesures qui contient l'identifiant de la mesure, l'identifiant du centre, la date de la mesure, la température, la pression et la pluviométrie mesurées.
Le schéma relationnel de la relation Centres est le suivant :
Centres(id_centre: INT, nom_ville: VARCHAR, latitude: FLOAT, longitude: FLOAT, altitude: INT)
Le schéma relationnel de la relation Mesures est le suivant :
Mesures(id_mesure: INT, id_centre: INT, date_mesure: DATE, temperature: FLOAT, pression: INT, pluviometrie: INT)
On fournit ci-dessous le contenu des deux relations.
Relation Centres
id_centre |
nom_ville |
latitude |
longitude |
altitude |
|---|---|---|---|---|
213 |
'Amiens' |
49.894 |
2.293 |
60 |
138 |
'Grenoble' |
45.185 |
5.723 |
550 |
263 |
'Brest' |
48.388 |
-4.49 |
52 |
185 |
'Tignes' |
45.469 |
6.909 |
2594 |
459 |
'Nice' |
43.706 |
7.262 |
260 |
126 |
'Le Puy-en-Velay' |
45.042 |
3.888 |
744 |
317 |
'Gérardmer' |
48.073 |
6.879 |
855 |
Relation Mesures
id_mesure |
id_centre |
date_mesure |
temperature |
pression |
pluviometrie |
|---|---|---|---|---|---|
1566 |
138 |
'2021-10-29' |
8.0 |
1015 |
3 |
1568 |
213 |
'2021-10-29' |
15.1 |
1011 |
0 |
2174 |
126 |
'2021-10-30' |
18.2 |
1023 |
0 |
2200 |
185 |
'2021-10-30' |
5.6 |
989 |
20 |
2232 |
459 |
'2021-10-31' |
25.0 |
1035 |
0 |
2514 |
213 |
'2021-10-31' |
17.4 |
1020 |
0 |
2563 |
126 |
'2021-11-01' |
10.1 |
1005 |
15 |
2592 |
459 |
'2021-11-01' |
23.3 |
1028 |
2 |
3425 |
317 |
'2021-11-02' |
9.0 |
1012 |
13 |
3430 |
138 |
'2021-11-02' |
7.5 |
996 |
16 |
3611 |
263 |
'2021-11-03' |
13.9 |
1005 |
8 |
3625 |
126 |
'2021-11-03' |
10.8 |
1008 |
8 |
1.a. Proposer une clé primaire pour la relation Mesures. Justifier votre choix.
Réponse
id_centren'est pas unique, elle ne peut pas servir de clé primaire.date_mesureetpluviometrienon plus.temperatureetpressionsont uniques, certes, pour l'instant, mais probablement pas ensuite, c'est un mauvais choix.
id_mesure permet d'identifier de manière unique chaque mesure de la table ; c'est la bonne clé primaire pour cette relation.
1.b. Avec quel attribut peut-on faire une jointure entre la relation Centres et la relation Mesures ?
Réponse
La clé étrangère id_centre dans la table Mesures fait référence de manière unique à la clé primaire id_centre de la table Centres.
Une jointure pourra être sans équivoque en utilisant l'égalité de ces deux champs.
2.a. Qu'affiche la requête suivante ?
SELECT * FROM Centres WHERE altitude > 500;
Réponse
La requête affiche tous les champs de la table Centres pour lesquels l'altitude est strictement supérieure à 500 m.
id_centre |
nom_ville |
latitude |
longitude |
altitude |
|---|---|---|---|---|
138 |
'Grenoble' |
45.185 |
5.723 |
550 |
185 |
'Tignes' |
45.469 |
6.909 |
2594 |
126 |
'Le Puy-en-Velay' |
45.042 |
3.888 |
744 |
317 |
'Gérardmer' |
48.073 |
6.879 |
855 |
2.b. On souhaite récupérer le nom de la ville des centres météorologiques situés à une altitude comprise entre 700 m et 1200 m, inclus. Écrire la requête SQL correspondante.
Réponse
SELECT nom_ville FROM Centres WHERE altitude >= 700 AND altitude <= 1200;
nom_ville |
|---|
'Le Puy-en-Velay' |
'Gérardmer' |
2.c. On souhaite récupérer la liste des longitudes et des noms des villes des centres météorologiques dont la longitude est supérieure à 5.0 La liste devra être triée par ordre alphabétique des noms de ville. Écrire la requête SQL correspondante.
Réponse
SELECT longitude, nom_ville
FROM Centres
WHERE longitude > 5.0
ORDER BY nom_ville ASC;
longitude |
nom_ville |
|---|---|
6.879 |
'Gérardmer' |
5.723 |
'Grenoble' |
7.262 |
'Nice' |
6.909 |
'Tignes' |
3.a. Qu'affiche la requête suivante ?
SELECT * FROM Mesures WHERE date_mesure = '2021-10-30';
Réponse
La requête affiche tous les champs des enregistrements de la table Mesures pour la date du 30 octobre 2021.
id_mesure |
id_centre |
date_mesure |
temperature |
pression |
pluviometrie |
|---|---|---|---|---|---|
2174 |
126 |
'2021-10-30' |
18.2 |
1023 |
0 |
2200 |
185 |
'2021-10-30' |
5.6 |
989 |
20 |
3.b. Écrire une requête SQL permettant d'ajouter une mesure prise le 8 novembre 2021 dans le centre numéro 138, où la température était de 11°C, la pression de 1013 hPa et la pluviométrie de 0 mm. La donnée dont l'attribut est id_mesure aura pour valeur 3650.
Réponse
INSERT INTO Mesures (id_mesure, id_centre, date_mesure, temperature, pression, pluviometrie)
VALUES (3650, 138, '2021-11-08', 11.0, 1013, 0);
4.a. Expliquer ce que renvoie la requête SQL suivante ?
SELECT * FROM Centres WHERE latitude = (SELECT MIN(latitude) FROM Centres);
Réponse
La requête imbriquée SELECT MIN(latitude) FROM Centres; renvoie 43.706 qui est la plus petite latitude parmi celle de la table Centres. Elle correspond à la latitude de Nice, la ville du centre le plus au Sud de cette table.
Cette requête renvoie donc tous les champs du centre situé le plus au sud parmi ceux de la table Centres.
id_centre |
nom_ville |
latitude |
longitude |
altitude |
|---|---|---|---|---|
459 |
'Nice' |
43.706 |
7.262 |
260 |
4.b. Écrire une requête SQL donnant la liste des villes dans lesquelles on a enregistré une température inférieure à 10°C en octobre 2021. On utilisera le mot clé DISTINCT afin d'éviter d'avoir des doublons. On rappelle que l'on peut utiliser les opérateurs de comparaison avec les dates.
Réponses
SELECT DISTINCT nom_ville
FROM Centres
JOIN Mesures
ON Centres.id_centre = Mesures.id_centre
WHERE Mesures.temperature < 10.0
AND Mesures.date_mesure >= '2021-10-01'
AND Mesures.date_mesure < '2021-11-01';
nom_ville |
|---|
'Grenoble' |
'Tignes' |