Devoir SQL - Le Tour de France 2023

Durée: 55 minutes

Le Tour de France est une course cycliste qui se déroule chaque année. Chaque jour, les coureurs s'affrontent pour remporter l'étape du jour, ce qui détermine un classement d'étape. Le coureur avec le temps cumulé le plus bas sur l'ensemble des étapes mène le classement général. Chaque participant est repéré par un dossard et appartient à une équipe. En 2023, 22 équipes de 8 coureurs, soit 176 cyclistes ont pris le départ du tour.

Dans cet exercice, on pourra utiliser les mots clés suivants du langage SQL :

SELECT, FROM, WHERE, JOIN, ON, INSERT INTO, VALUES, UPDATE, SET, DELETE, MIN, MAX, OR, AND et ORDER BY.

On considère la base de données du tour de France 2023 dont le schéma relationnel est donné ci-dessous :

graph LR
    subgraph Equipes
        E["<u>nomEquipe</u><br/>directeurSportif"]
    end

    subgraph Coureurs
        C["<u>numDossard</u><br/>nomCoureur<br/>#nomEquipe"]
    end

    subgraph Etapes
        Et["<u>numEtape</u><br/>typeEtape<br/>villeDepart<br/>villeArrivee<br/>dateEtape"]
    end

    subgraph Temps
        T["<u>#numDossard, #numEtape</u><br/>tempsRealise"]
    end

    E -.-> C
    C -.-> T
    Et -.-> T

1. Expliquer pourquoi, dans la relation Temps, il est nécessaire de prendre le couple (numDossard, NumEtape) comme clé primaire.

Correction

Dans la relation Temps, chaque enregistrement correspond au temps réalisé par un coureur (identifié par son numDossard) lors d'une étape spécifique (identifiée par son numEtape). Un coureur peut participer à plusieurs étapes, et chaque étape peut avoir plusieurs coureurs. Par conséquent, pour identifier de manière unique chaque enregistrement dans la table Temps, il est nécessaire d'utiliser le couple (numDossard, numEtape) comme clé primaire. Cela garantit qu'il n'y aura pas de doublons pour un même coureur dans une même étape.

2. Expliquer ce que renvoie la requête SQL suivante :

SELECT nomCoureur
FROM Coureurs
WHERE Equipe = 'Cofidis';

Correction

La requête SQL renvoie la liste des noms des coureurs qui appartiennent à l'équipe nommée 'Cofidis'. Elle sélectionne la colonne nomCoureur de la table Coureurs pour tous les enregistrements où la colonne Equipe est égale à 'Cofidis'.

3. Écrire une requête SQL permettant d'obtenir les dates de toutes les étapes de type 'contre-la-montre' du tour de France 2023.

Correction

SELECT dateEtape
FROM Etapes
WHERE typeEtape = 'contre-la-montre';

4. Écrire une requête SQL permettant d'obtenir le nom du directeur sportif du coureur BARDET Romain.

Correction

SELECT E.directeurSportif
FROM Equipes E
JOIN Coureurs C ON E.nomEquipe = C.nomEquipe
WHERE C.nomCoureur = 'BARDET Romain';

5. À la fin de la cinquième étape, on veut actualiser la table Temps avec les données du jour. Expliquer pourquoi la suite des deux requêtes SQL ci-dessous provoque une erreur.

INSERT INTO Temps VALUES (1, 5, 14267);
INSERT INTO Etapes VALUES(5, 'Montagne', 'Pau', 'Laruns', 05/07/2023);

Correction

La première requête tente d'insérer un enregistrement dans la table Temps pour l'étape numéro 5 avant que cette étape n'existe dans la table Etapes. Comme la table Temps fait référence à la table Etapes via la clé étrangère numEtape, l'insertion échoue car il n'y a pas d'entrée correspondante pour l'étape 5 dans la table Etapes au moment de l'insertion.

6. Expliquer quelle modification est à effectuer pour apporter une solution au problème constaté à la question précédente.

Correction

Pour résoudre le problème, il faut d'abord insérer l'étape 5 dans la table Etapes avant d'insérer les temps correspondants dans la table Temps. Ainsi, la requête correcte serait de d'abord exécuter :

INSERT INTO Etapes VALUES(5, 'Montagne', 'Pau', 'Laruns', '2023-07-05');

puis d'exécuter la requête d'insertion dans la table Temps :

INSERT INTO Temps VALUES (1, 5, 14267);

7. Suite à une erreur de chronométrage, on doit corriger le temps du coureur portant le dossard numéro 12 lors de l'étape 3. Son nouveau temps est de 15450 secondes. Écrire une requête SQL permettant de mettre à jour cette information dans la base de données.

Correction

UPDATE Temps
SET tempsRealise = 15450
WHERE numDossard = 12 AND numEtape = 3;

8. Le coureur PINOT Thibaut décide d'abandonner la course après l'étape 10. Écrire une requête SQL permettant de supprimer tous ses temps enregistrés dans la table Temps.

Correction

DELETE FROM Temps
WHERE numDossard = (SELECT numDossard FROM Coureurs WHERE nomCoureur = 'PINOT Thibaut');

9. Expliquer pourquoi il est important de supprimer d'abord les temps de PINOT Thibaut dans la table Temps avant de pouvoir le supprimer de la table Coureurs.

Correction

Il est important de supprimer d'abord les enregistrements de PINOT Thibaut dans la table Temps car cette table contient une clé étrangère qui référence la table Coureurs. Si on tente de supprimer le coureur de la table Coureurs sans avoir supprimé ses temps dans la table Temps, cela violerait l'intégrité référentielle de la base de données, ce qui entraînerait une erreur. En supprimant d'abord les enregistrements dans Temps, on s'assure que toutes les références au coureur sont éliminées avant de le supprimer de la table Coureurs.

10. Écrire une requête SQL donnant le temps total en course mis par BARDET Romain depuis le départ du tour de France 2023.

Correction

SELECT SUM(T.tempsRealise) AS tempsTotal
FROM Temps T
JOIN Coureurs C ON T.numDossard = C.numDossard
WHERE C.nomCoureur = 'BARDET Romain';