Skip to content

Modification de bases de données

Questions

  • Dans la base de données ''ol'', le match Nantes-Olympique Lyonnais a été inversé: il s'est déroulé à Nantes et non à Lyon. Corrigez cela.

  • Modifiez le but de Faubert (Match Bordeaux-Lyon): il a marqué son but à la 90+1ème minute et non à la 91ème.

  • Insérez de nouvelles données: Match Sochaux-Lyon du 30 septembre 2006, résultat: 0-1 pour Lyon, but de Wiltord à la 78ème minute.

  • Vérifiez l'insertion du match en calculant le nombre de buts marqués par l'OL ainsi que le nombre de buts marqués par Wiltord.

  • Créez la table des cartons (''Cartons''). Un carton peut être jaune ou rouge (pensez à ''%%ENUM%%'' ou à créer une table des couleurs), il a été donné lors d'un match à un joueur à un moment.

  • Insérez les cartons suivants:

    • Match Sochaux-Lyon: carton jaune pour Fred 88ème.
    • Match Lyon-Lille: Govou 17ème (jaune), Fred 36ème (jaune).
    • Match Lyon-Troyes: Cris 88ème (jaune), Juninho 90ème (jaune)
    • Match Nantes-Lyon: Berthod 25ème, Kallstrom 43ème (jaunes)
    • Match Nice-Lyon: Malouda 30ème, Tiago 44ème (jaunes)
    • Match Bordeaux-Lyon: Fred 14ème, Juninho 46ème, Cris 48ème, Tiago 58ème (jaunes)
    • Match Lyon-Toulouse: Cacapa 62ème (jaune)
  • Un joueur est suspendu automatiquement pour le match suivant s'il a reçu trois cartons jaunes au cours des dix derniers matchs ou s'il a reçu un carton rouge lors du dernier match. Quels sont les joueurs suspendus pour le prochain match.

  • Vérifiez votre dernière requête SQL en modifiant les tables de la manière suivante:

    • ajoutez un carton à Fred pour le match Nantes-Lyon.
    • ajoutez un nouveau joueur: Bruno Cheyrou d'Auxerre, né le 10/05/1978, Milieu, deux cartons jaunes lors du match Nancy-Auxerre du 17/09/2006 (13ème et 15ème) et donc 1 carton rouge (15ème).
  • Nettoyer la base en enlevant toute référence à des joueurs non lyonnais (joueurs+buts+cartons).

Réponses

  • Dans la base de données ''ol'', le match Nantes-Olympique Lyonnais a été inversé: il s'est déroulé à Nantes et non à Lyon. Corrigez cela.

<code Mysql| Inversion des équipes > mysql> select * from Matchs; +---------+----------+----------+---------------+ | MatchID | Domicile | Visiteur | Date_de_match | | ------- | -------- | -------- | ------------- | +---------+----------+----------+---------------+ | 1 | 1 | 2 | 2006-08-04 | | - | - | - | ---------- | | 3 | 1 | 19 | 2006-08-12 | | 4 | 1 | 8 | 2006-09-09 | | 5 | 1 | 13 | 2006-09-23 | | 6 | 11 | 1 | 2006-08-20 | | 7 | 14 | 1 | 2006-08-26 | | 8 | 9 | 1 | 2006-09-16 | +---------+----------+----------+---------------+ 7 rows in set (0.00 sec)

mysql> update Matchs set domicile = (select clubid from Club where nom="FC Nantes"), Visiteur = (select clubid from Club where Nom="Olympique Lyonnais") where Domicile = (select clubid from Club where Nom="Olympique Lyonnais") and Visiteur = (select clubid from Club where nom="FC Nantes"); Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from Matchs; +---------+----------+----------+---------------+ | MatchID | Domicile | Visiteur | Date_de_match | | ------- | -------- | -------- | ------------- | +---------+----------+----------+---------------+ | 1 | 2 | 1 | 2006-08-04 | | - | - | - | ---------- | | 3 | 1 | 19 | 2006-08-12 | | 4 | 1 | 8 | 2006-09-09 | | 5 | 1 | 13 | 2006-09-23 | | 6 | 11 | 1 | 2006-08-20 | | 7 | 14 | 1 | 2006-08-26 | | 8 | 9 | 1 | 2006-09-16 | +---------+----------+----------+---------------+ 7 rows in set (0.00 sec) </code>

  • Modifiez le but de Faubert (Match Bordeaux-Lyon): il a marqué son but à la 90+1ème minute et non à la 91ème.

<code mysql|But de Faubert> mysql> select * from Joueurs J, Buts B where J.Nom="Faubert" and J.JoueurID = B.ButeurID; +----------+--------+--------+---------+--------+-------------------+-------------+--------+-------+----------+--------+-------------+-----+---------+ | JoueurID | Numero | Prenom | Nom | Poste | Date_de_naissance | Nationalite | ClubID | ButID | ButeurID | Minute | Additionnel | CSC | MatchID | | -------- | ------ | ------ | --- | ----- | ----------------- | ----------- | ------ | ----- | -------- | ------ | ----------- | --- | ------- | +----------+--------+--------+---------+--------+-------------------+-------------+--------+-------+----------+--------+-------------+-----+---------+ | 30 | 18 | Julien | Faubert | Milieu | 1983-08-01 | Fra | 11 | 10 | 30 | 91 | NULL | 0 | 6 | | -- | -- | ------ | ------- | ------ | ---------- | --- | -- | -- | -- | -- | ---- | - | - | +----------+--------+--------+---------+--------+-------------------+-------------+--------+-------+----------+--------+-------------+-----+---------+ 1 row in set (0.00 sec)

mysql> update Buts set Minute=90, Additionnel = 1 where ButeurID=(select JoueurID from Joueurs where Nom="Faubert") ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from Joueurs J, Buts B where J.Nom="Faubert" and J.JoueurID = B.ButeurID; +----------+--------+--------+---------+--------+-------------------+-------------+--------+-------+----------+--------+-------------+-----+---------+ | JoueurID | Numero | Prenom | Nom | Poste | Date_de_naissance | Nationalite | ClubID | ButID | ButeurID | Minute | Additionnel | CSC | MatchID | | -------- | ------ | ------ | --- | ----- | ----------------- | ----------- | ------ | ----- | -------- | ------ | ----------- | --- | ------- | +----------+--------+--------+---------+--------+-------------------+-------------+--------+-------+----------+--------+-------------+-----+---------+ | 30 | 18 | Julien | Faubert | Milieu | 1983-08-01 | Fra | 11 | 10 | 30 | 90 | 1 | 0 | 6 | | -- | -- | ------ | ------- | ------ | ---------- | --- | -- | -- | -- | -- | - | - | - | +----------+--------+--------+---------+--------+-------------------+-------------+--------+-------+----------+--------+-------------+-----+---------+ 1 row in set (0.00 sec) </code>

ou encore <code mysql| Autre solution> mysql > update Buts set Minute=90, Additionnel=1 where matchid=(select matchid from Matchs where Domicile=(select clubid from Club where Nom like '%bordeaux%') and Visiteur=(select clubid from Club where Nom like '%lyon%')) and buteurid=(select joueurid from Joueurs where Nom="Faubert"); </code>

  • Insérez de nouvelles données: Match Sochaux-Lyon du 30 septembre 2006, résultat: 0-1 pour Lyon, but de Wiltord à la 78ème minute.

<code mysql|insertion du match Sochaux-Lyon> mysql> insert Matchs -> set Domicile=(select clubid from Club where Nom="FC Sochaux"), -> Visiteur=(select clubid from Club where Nom="Olympique Lyonnais"), -> Date_de_match="2006-09-30"; Query OK, 1 row affected (0.00 sec)

mysql> insert Buts set ButeurID=(select joueurid from Joueurs where nom="Wiltord"), Minute=78, MatchID=(select MatchID from Matchs where Domicile = (select clubid from Club where Nom="FC Sochaux") and Visiteur = (select clubid from Club where Nom="Olympique Lyonnais") ); Query OK, 1 row affected (0.00 sec)

</code>

  • Vérifiez l'insertion du match en calculant le nombre de buts marqués par l'OL ainsi que le nombre de buts marqués par Wiltord.

<code mysql|Reprise de code du TD précédent> mysql> SELECT count() FROM Buts B, Club C, Joueurs J WHERE B.ButeurID=J.JoueurID and J.ClubID=C.ClubID and C.Nom='Olympique Lyonnais' and B.CSC=FALSE; +----------+ | count() | | -------- | +----------+ | 20 | | -- | +----------+ 1 row in set (0.00 sec)

mysql> SELECT J.Nom, count(J.Nom) FROM Joueurs J, Buts B WHERE B.ButeurID=J.JoueurID GROUP BY J.Nom ORDER BY 2 desc; +-----------+--------------+ | Nom | count(J.Nom) | | --- | ------------ | +-----------+--------------+ | Fred | 5 | | ---- | - | | Malouda | 4 | | Benzema | 3 | | Cris | 2 | | Wiltord | 2 | | Juninho | 2 | | Mendes | 2 | | Fabinho | 1 | | Squillaci | 1 | | Faubert | 1 | | Vahirua | 1 | | Ciani | 1 | | Boukhari | 1 | +-----------+--------------+ 13 rows in set (0.01 sec) </code>

  • Créez la table des cartons (''Cartons''). Un carton peut être jaune ou rouge (pensez à ''%%ENUM%%''), il a été donné lors d'un match à un joueur à un moment.

<code mysql|Table Cartons> mysql> create TABLE Cartons ( CartonID INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (CartonID), Couleur ENUM('Jaune', 'Rouge'), Minute INTEGER NOT NULL, JoueurID INTEGER NOT NULL, FOREIGN KEY (JoueurID) REFERENCES Joueurs, MatchID INTEGER NOT NULL, FOREIGN KEY (MatchID) REFERENCES Matchs ) ; </code>

  • Insérez les cartons suivants:
    • Match Sochaux-Lyon: carton jaune pour Fred 88ème.
    • Match Lyon-Lille: Govou 17ème (jaune), Fred 36ème (jaune).
    • Match Lyon-Troyes: Cris 88ème (jaune), Juninho 90ème (jaune)
    • Match Nantes-Lyon: Berthod 25ème, Kallstrom 43ème (jaunes)
    • Match Nice-Lyon: Malouda 30ème, Tiago 44ème (jaunes)
    • Match Bordeaux-Lyon: Fred 14ème, Juninho 46ème, Cris 48ème, Tiago 58ème (jaunes)
    • Match Lyon-Toulouse: Cacapa 62ème (jaune)

<code mysql|Insertion du premier carton (sert de test)> mysql> insert Cartons (Couleur, Minute, JoueurID, MatchID) Values ('Jaune', 88, (select JoueurID from Joueurs where Nom="Fred"), (select MatchID from Matchs where Domicile=(select ClubID from Club where Nom="FC Sochaux") and Visiteur=(select ClubID from Club where Nom="Olympique Lyonnais"))); Query OK, 1 row affected (0.00 sec) insert Cartons (Couleur, Minute, JoueurID, MatchID) Values ('Jaune', 17, (select JoueurID from Joueurs where Nom="Govou"), (select MatchID from Matchs where Domicile=(select ClubID from Club where Nom="Olympique Lyonnais") and Visiteur=(select ClubID from Club where Nom="Lille OSC"))), ('Jaune', 36, (select JoueurID from Joueurs where Nom="Fred"), (select MatchID from Matchs where Domicile=(select ClubID from Club where Nom="Olympique Lyonnais") and Visiteur=(select ClubID from Club where Nom="Lille OSC"))), ('Jaune', 88, (select JoueurID from Joueurs where Nom="Cris"), (select MatchID from Matchs where Domicile=(select ClubID from Club where Nom="Olympique Lyonnais") and Visiteur=(select ClubID from Club where Nom="ES Troyes AC"))), ('Jaune', 90, (select JoueurID from Joueurs where Nom="Juninho"), (select MatchID from Matchs where Domicile=(select ClubID from Club where Nom="Olympique Lyonnais") and Visiteur=(select ClubID from Club where Nom="ES Troyes AC"))), ('Jaune', 25, (select JoueurID from Joueurs where Nom="Berthod"), (select MatchID from Matchs where Domicile=(select ClubID from Club where Nom="FC Nantes") and Visiteur=(select ClubID from Club where Nom="Olympique Lyonnais"))), ('Jaune', 43, (select JoueurID from Joueurs where Nom="Kallstrom"), (select MatchID from Matchs where Domicile=(select ClubID from Club where Nom="FC Nantes") and Visiteur=(select ClubID from Club where Nom="Olympique Lyonnais"))), ('Jaune', 30, (select JoueurID from Joueurs where Nom="Malouda"), (select MatchID from Matchs where Domicile=(select ClubID from Club where Nom="OGC Nice") and Visiteur=(select ClubID from Club where Nom="Olympique Lyonnais"))), ('Jaune', 44, (select JoueurID from Joueurs where Nom="Mendes"), (select MatchID from Matchs where Domicile=(select ClubID from Club where Nom="OGC Nice") and Visiteur=(select ClubID from Club where Nom="Olympique Lyonnais"))), ('Jaune', 14, (select JoueurID from Joueurs where Nom="Fred"), (select MatchID from Matchs where Domicile=(select ClubID from Club where Nom="Girondins Bordeaux") and Visiteur=(select ClubID from Club where Nom="Olympique Lyonnais"))), ('Jaune', 46, (select JoueurID from Joueurs where Nom="Juninho"), (select MatchID from Matchs where Domicile=(select ClubID from Club where Nom="Girondins Bordeaux") and Visiteur=(select ClubID from Club where Nom="Olympique Lyonnais"))), ('Jaune', 48, (select JoueurID from Joueurs where Nom="Cris"), (select MatchID from Matchs where Domicile=(select ClubID from Club where Nom="Girondins Bordeaux") and Visiteur=(select ClubID from Club where Nom="Olympique Lyonnais"))), ('Jaune', 58, (select JoueurID from Joueurs where Nom="Mendes"), (select MatchID from Matchs where Domicile=(select ClubID from Club where Nom="Girondins Bordeaux") and Visiteur=(select ClubID from Club where Nom="Olympique Lyonnais"))), ('Jaune', 62, (select JoueurID from Joueurs where Nom="Cacapa"), (select MatchID from Matchs where Domicile=(select ClubID from Club where Nom="Olympique Lyonnais") and Visiteur=(select ClubID from Club where Nom="Toulouse FC"))) ; Query OK, 13 rows affected (0.00 sec) Records: 13 Duplicates: 0 Warnings: 0 mysql> select J.Nom, C.Minute, C1.Nom, C2.Nom
from Joueurs J, Cartons C, Club C1, Club C2, Matchs M where C.JoueurID = J.JoueurID
and M.MatchID = C.MatchID
and C1.ClubID = M.Domicile
and C2.ClubID = M.Visiteur order by C.MatchID, C.Minute; +-----------+--------+--------------------+--------------------+ | Nom | Minute | Nom | Nom | | --- | ------ | --- | --- | +-----------+--------+--------------------+--------------------+ | Berthod | 25 | FC Nantes | Olympique Lyonnais | | ------- | -- | --------- | ------------------ | | Kallstrom | 43 | FC Nantes | Olympique Lyonnais | | Cacapa | 62 | Olympique Lyonnais | Toulouse FC | | Cris | 88 | Olympique Lyonnais | ES Troyes AC | | Juninho | 90 | Olympique Lyonnais | ES Troyes AC | | Govou | 17 | Olympique Lyonnais | Lille OSC | | Fred | 36 | Olympique Lyonnais | Lille OSC | | Fred | 14 | Girondins Bordeaux | Olympique Lyonnais | | Juninho | 46 | Girondins Bordeaux | Olympique Lyonnais | | Cris | 48 | Girondins Bordeaux | Olympique Lyonnais | | Mendes | 58 | Girondins Bordeaux | Olympique Lyonnais | | Malouda | 30 | OGC Nice | Olympique Lyonnais | | Mendes | 44 | OGC Nice | Olympique Lyonnais | | Fred | 88 | FC Sochaux | Olympique Lyonnais | +-----------+--------+--------------------+--------------------+ 14 rows in set (0.00 sec)

</code>

  • Un joueur est suspendu automatiquement pour le match suivant s'il a reçu trois cartons jaunes au cours des dix derniers matchs ou s'il a reçu un carton rouge lors du dernier match. Quels sont les joueurs suspendus pour le prochain match.

<code mysql|Reponse partielle (il faudrait limiter le nombre de matchs à 10 dans le order by date_de_match) + pas de gestion des cartons rouges> mysql> select J.Nom, count(C.Couleur) as N from Cartons C, Joueurs J where C.MatchID in (select MatchID from Matchs order by date_de_match) and J.JoueurID=C.JoueurID group by C.JoueurID having n=3; +------+---+ | Nom | N | | --- | - | +------+---+ | Fred | 3 | | ---- | - | +------+---+ 1 row in set (0.00 sec) </code>

  • Vérifiez votre dernière requête SQL en modifiant les tables de la manière suivante:

    • ajoutez un carton à Fred pour le match Nantes-Lyon.
    • ajoutez un nouveau joueur: Bruno Cheyrou d'Auxerre, né le 10/05/1978, Milieu, deux cartons jaunes lors du match Nancy-Auxerre du 17/09/2006 (13ème et 15ème) et donc 1 carton rouge (15ème).
  • Nettoyer la base en enlevant toute référence à des joueurs non lyonnais (joueurs+buts+cartons).