Exercices

Nous avons maintenant vu une bonne partie des fonctions MySQL relatives aux données temporelles. N'oubliez pas de consulter la documentation officielle au besoin, car celle-ci contient plus de détails et d'autres fonctions. Je vous propose maintenant quelques exercices pour passer de la théorie à la pratique. Cela va vous permettre de retenir déjà une partie des fonctions, mais surtout, cela va replacer ces fonctions dans un véritable contexte puisque nous allons bien sûr travailler sur notre table Animal. Bien entendu, certaines questions ont plusieurs réponses possibles, mais je ne donnerai qu'une seule solution. Ne soyez donc pas étonnés d'avoir trouvé une requête faisant ce qui est demandé mais différente de la réponse indiquée.

Commençons par le format

1. Sélectionner tous les animaux nés en juin.

1
2
3
SELECT id, date_naissance, nom
FROM Animal 
WHERE MONTH(date_naissance) = 6;

2. Sélectionner tous les animaux nés dans les huit premières semaines d'une année.

1
2
3
SELECT id, date_naissance, nom
FROM Animal 
WHERE WEEKOFYEAR(date_naissance) < 9;

3. Afficher le jour (en chiffres) et le mois de naissance (en toutes lettres) des tortues et des chats nés avant 2007 (en deux colonnes).

1
2
3
4
5
SELECT DAY(date_naissance), MONTHNAME(date_naissance) 
FROM Animal 
INNER JOIN Espece ON Animal.espece_id = Espece.id
WHERE Espece.nom_courant IN ('Chat', 'Tortue d''Hermann')
AND YEAR(date_naissance) < 2007;

4. Même chose qu'à la question précédente, mais en une seule colonne.

1
2
3
4
5
SELECT DATE_FORMAT(date_naissance, '%e %M') 
FROM Animal 
INNER JOIN Espece ON Animal.espece_id = Espece.id
WHERE Espece.nom_courant IN ('Chat', 'Tortue d''Hermann')
AND YEAR(date_naissance) < 2007;

5. Sélectionner tous les animaux nés en avril, mais pas un 24 avril, triés par heure de naissance décroissante (heure dans le sens commun du terme, donc heure, minutes, secondes) et afficher leur date de naissance suivant le même format que l'exemple ci-dessous.

Format : 8 janvier, à 6h30PM, en l'an 2010 après J.C.

1
2
3
4
5
SELECT DATE_FORMAT(date_naissance, '%e %M, à %lh%i%p, en l''an %Y après J.C.') AS jolie_date
FROM Animal
WHERE MONTH(date_naissance) = 4
AND DAY(date_naissance) <> 24
ORDER BY TIME(date_naissance) DESC;

Passons aux calculs

1. Moka était censé naître le 27 février 2008. Calculer le nombre de jours de retard de sa naissance.

1
2
3
SELECT DATEDIFF(date_naissance, '2008-02-27')  AS retard
FROM Animal 
WHERE nom = 'Moka';

2. Afficher la date à laquelle chaque perroquet (espece_id = 4) fêtera son 25e anniversaire.

1
2
3
SELECT DATE(ADDDATE(date_naissance, INTERVAL 25 YEAR)) AS Anniversaire
FROM Animal 
WHERE espece_id = 4;

On ne demandait que la date (on fête rarement son anniversaire à l'heure pile de sa naissance), d'où l'utilisation de la fonction DATE().

3. Sélectionner les animaux nés dans un mois contenant exactement 29 jours.

1
2
3
SELECT id, date_naissance, nom
FROM Animal 
WHERE DAY(LAST_DAY(date_naissance)) = 29;

4. Après douze semaines, un chaton est sevré (sauf exception bien sûr). Afficher la date à partir de laquelle les chats (espece_id = 2) de l'élevage peuvent être adoptés (qu'il s'agisse d'une date dans le passé ou dans le futur).

1
2
3
SELECT id, nom, DATE(DATE_ADD(date_naissance, INTERVAL 12 WEEK)) AS sevrage
FROM Animal 
WHERE espece_id = 2;

5. Rouquine, Zira, Bouli et Balou (id 13, 18, 20 et 22 respectivement) font partie de la même portée. Calculer combien de temps, en minutes, Balou est né avant Zira.

1
2
3
4
5
6
7
8
SELECT TIMESTAMPDIFF(MINUTE, 
                  (SELECT date_naissance 
                  FROM Animal 
                  WHERE nom = 'Balou'), 
                  (SELECT date_naissance 
                  FROM Animal 
                  WHERE nom = 'Zira')) 
      AS nb_minutes;

Il fallait ici penser aux sous-requêtes, afin d'obtenir les dates de naissance de Balou et Zira pour les utiliser dans la même fonction TIMESTAMPDIFF().

Et pour finir, mélangeons le tout

Et quand on dit "tout", c'est tout ! Par conséquent, il est possible (et même fort probable) que vous ayez besoin de notions et fonctions vues dans les chapitres précédents (regroupements, sous-requêtes, etc.) pour résoudre ces exercices.

1. Rouquine, Zira, Bouli et Balou (id 13, 18, 20 et 22 respectivement) font partie de la même portée. Calculer combien de temps, en minutes, s'est écoulé entre le premier né et le dernier né de la portée.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
SELECT TIMESTAMPDIFF(MINUTE, 
            (
              SELECT MIN(date_naissance) 
          FROM Animal 
          WHERE id IN (13, 18, 20, 22)
            ), 
            (
          SELECT MAX(date_naissance) 
          FROM Animal 
          WHERE id IN (13, 18, 20, 22)
            )
          ) AS nb_minutes;

Presque le même exercice qu'au-dessus, à ceci près qu'il fallait utiliser les fonctions d’agrégation.

2. Calculer combien d'animaux sont nés durant un mois pendant lequel les moules sont les plus consommables (c'est-à-dire les mois finissant en "bre").

1
2
3
SELECT COUNT(*)
FROM Animal
WHERE MONTHNAME(date_naissance) LIKE '%bre';

Il faut bien sûr avoir préalablement défini que le nom des mois et des jours doit être exprimé en français. Je rappelle la requête à utiliser : SET lc_time_names = 'fr_FR';

3. Pour les chiens et les chats (espece_id = 1 et espece_id = 2 respectivement), afficher les différentes dates de naissance des portées d'au moins deux individus (format JJ/MM/AAAA), ainsi que le nombre d'individus pour chacune de ces portées. Attention, il n'est pas impossible qu'une portée de chats soit née le même jour qu'une portée de chiens (il n'est pas non plus impossible que deux portées de chiens naissent le même jour, mais on considère que ce n'est pas le cas).

1
2
3
4
5
SELECT DATE_FORMAT(date_naissance, '%d/%m/%Y'), COUNT(*) as nb_individus
FROM Animal
WHERE espece_id IN (1, 2)
GROUP BY DATE(date_naissance), espece_id
HAVING nb_individus > 1;

Il faut regrouper sur la date (et uniquement la date, pas l'heure) puis sur l'espèce pour éviter de mélanger chiens et chats. Une simple clause HAVING permet ensuite de sélectionner les portées de deux individus ou plus.

4. Calculer combien de chiens (espece_id = 1) sont nés en moyenne chaque année entre 2006 et 2010 (sachant qu'on a eu au moins une naissance chaque année).

1
2
3
4
5
6
7
8
9
SELECT AVG(nb)
FROM (
  SELECT COUNT(*) AS nb
  FROM Animal
  WHERE espece_id = 1
  AND YEAR(date_naissance) >= 2006
  AND YEAR(date_naissance) <= 2010
  GROUP BY YEAR(date_naissance)
) AS tableIntermedaire;

Ici, il fallait penser à faire une sous-requête dans la clause FROM. Si vous n'avez pas trouvé, rappelez-vous qu'il faut penser par étapes. Vous voulez la moyenne du nombre de chiens nés chaque année ? Commencez par obtenir le nombre de chiens nés chaque année, puis seulement demandez-vous comment faire la moyenne.

5. Afficher la date au format ISO du 5e anniversaire des animaux dont on connaît soit le père, soit la mère.

1
2
3
4
SELECT DATE_FORMAT(DATE_ADD(date_naissance, INTERVAL 5 YEAR), GET_FORMAT(DATE, 'ISO')) AS dateIso
FROM Animal
WHERE pere_id IS NOT NULL
OR mere_id IS NOT NULL;

Pour cette dernière question, il fallait juste imbriquer plusieurs fonctions différentes. À nouveau, si vous n'avez pas réussi, c'est sans doute parce que vous ne décomposez pas le problème correctement.