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).