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

ou encore

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");
  • 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.
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)
  • 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.
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)
  • 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.
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
       )
       ;
  • 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)
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)
  • 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.
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)
  • 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).