STI
Systèmes & Technologies de l'Informatique
Classe : 4e Sciences de l'Informatique
S
Série : Gestion des données
Nom du prof : M Ladhari Tijani
STI
Anglais
Objectifs
❖ Les commandes SQL : LDD + LMD ( INSERT, UPDATE, SELECT)
Exercice 1 20 min
Soit la base de données relative à la gestion d'activités sportives, nommée "Club" décrite par la repré-
sentation textuelle suivante :
ACTIVITE ( CodeAct, NomAct, Prix)
ENTRAINEUR(CodeEnt, NPEnt, Tel )
ADHERENT(CodeAdh, NPAdh, GenreAdh, DateNais, Ville)
Soit la description des tables suivante :
NOM DESCRIPTION TYPE TAILLE CONTRAINTE
CodeAct Code de l'activité Texte 8
NomAct Nom de l'activité Texte 20 Obligatoire
Prix Prix mensuelle Decimal 6,3
CodeEnt Code de l'entraineur Texte 4
NPEnt Nom et Prénom de l’entraineur Texte 20 Obligatoire
Tel Téléphone de l’entraineur Texte 8
CodeAdh Code adhérent Entier 3
NPAdh Nom et Prénom de l'adhèrent Texte 20 Obligatoire
GenreAdh Genre de l'adhérent Caractère 1 Par défaut "H"
DateNais Date naissance de l'adhèrent Date
Ville Ville de l’adhérant Texte 30
1. Démarrer XAMPP (ou WampServer), puis créer la base de données "Club" ainsi que ses trois
tables en mode assisté
2. Modifier le type de la colonne NomAct en chaîne de 50 caractères.
ALTER TABLE ACTIVITE
MODIFY NomAct VARCHAR(50) NOT NULL;
3. Renommer la colonne GenreAdh en Genre.
ALTER TABLE ADHERENT
CHANGE GenreAdh Genre CHAR(1) DEFAULT 'H';
4. Définir une contrainte sur la table ACTIVITE afin de garantir que le prix de chaque activité soit
strictement supérieur à zéro
ALTER TABLE ACTIVITE
ADD CONSTRAINT chk1 CHECK (Prix > 0);
5. Définir une contrainte sur la table ADHERENT afin de garantir que la colonne Genre ne
puisse contenir que les valeurs 'H' (Homme) ou 'F' (Femme).
ALTER TABLE ADHERENT
1
STI
Anglais
ADD CONSTRAINT chk2 CHECK (Genre IN ('H','F'));
6. Écrire la requête SQL permettant de s’assurer que l’année de naissance de l’adhérent soit ≥
2010.
ALTER TABLE ADHERENT
ADD CONSTRAINT chk3 CHECK (YEAR(DateNais) >= 2010);
OU
ALTER TABLE ADHERENT
ADD CONSTRAINT chk_date_naissance
CHECK (DateNais >= '2010-01-01');
7. Le concepteur de la base de données propose les deux règles de gestion suivantes :
• R1 : Un entraîneur encadre plusieurs activités.
• R2 : Une activité est encadrée par un seul entraîneur.
a) Donner la nouvelle représentation textuelle de la base de données.
b) Ajouter une relation entre la table ENTRAINEUR et ACTIVITE (mode assisté).
ACTIVITE (CodeAct, NomAct, Prix, CodeEnt#)
ENTRAINEUR (CodeEnt, NPEnt, Tel)
ADHERENT (CodeAdh, NPAdh, GenreAdh, DateNais, Ville)
ALTER TABLE ACTIVITE
ADD CodeEnt CHAR(4),
ADD CONSTRAINT fk1 FOREIGN KEY (CodeEnt) REFERENCES
ENTRAINEUR(CodeEnt);
8. Le concepteur de la base de données propose également les règles suivantes :
• R3 : Un adhérent peut pratiquer plusieurs activités.
• R4 : Une activité est pratiquée par plusieurs adhérents.
a) Donner la nouvelle représentation textuelle de la base de données.
b) Écrire les commandes SQL nécessaires pour apporter ces modifications.
ACTIVITE (CodeAct, NomAct, Prix, CodeEnt#)
ENTRAINEUR (CodeEnt, NPEnt, Tel)
ADHERENT (CodeAdh, NPAdh, GenreAdh, DateNais, Ville)
2
STI
Anglais
INSCRIPTION (CodeAdh, CodeAct, DateIns)
CREATE TABLE INSCRIPTION (
CodeAdh INT(3),
CodeAct CHAR(8),
DateIns DATE,
PRIMARY KEY (CodeAdh, CodeAct, DateIns),
FOREIGN KEY (CodeAdh) REFERENCES ADHERENT(CodeAdh),
FOREIGN KEY (CodeAct) REFERENCES ACTIVITE(CodeAct)
);
9. En utilisant le mode commande, remplir la table ENTRAINEUR avec les deux lignes
suivantes :
CodeEnt NPEnt Tel
E001 Toumi Sarra 99887766
E002 Soussi Karim -
INSERT INTO ENTRAINEUR (CodeEnt, NPEnt, Tel)
VALUES ('E001', 'Toumi Sarra', '99887766');
INSERT INTO ENTRAINEUR (CodeEnt, NPEnt, Tel)
VALUES ('E002', 'Soussi Karim', NULL);
OU
INSERT INTO ENTRAINEUR (CodeEnt, NPEnt) VALUES ('E002', 'Soussi Karim');
10. En utilisant le mode assisté, remplir les autres tables avec des données aléatoires.
11. On veut augmenter les prix de toutes les activités de 10%
Écrire la commande SQL permettant d’apporter cette modification.
UPDATE ACTIVITE
SET Prix = Prix * 1.10;
3
STI
Anglais
12. Le numéro de téléphone de l’entraîneur dont le code est E002 devient 22334455.
Écrire la commande SQL permettant d’apporter cette modification.
UPDATE ENTRAINEUR
SET Tel = '22334455'
WHERE CodeEnt = 'E002';
13. Afficher la liste complète des adhérents inscrits au club
SELECT * FROM ADHERENT;
14. Afficher le nom et le prénom de tous les entraîneurs.
SELECT NPEnt
FROM ENTRAINEUR;
15. Afficher le prix mensuel de l’activité dont le nom est "Foot".
SELECT Prix
FROM ACTIVITE
WHERE NomAct = 'Foot';
16. Afficher les informations des adhérents résidant dans l’une des villes suivantes : Tunis, Sfax
ou Kef.
SELECT *
FROM ADHERENT
WHERE Ville IN ('Tunis', 'Sfax', 'Kef');
17. Afficher les adhérents dont le nom contient la lettre "a" en deuxième position.
SELECT *
FROM ADHERENT
WHERE NPAdh LIKE '_a%';
18. Pour chaque activité, afficher son nom ainsi que le nom et le prénom de l’entraîneur qui
l’encadre.
SELECT [Link], [Link]
FROM ACTIVITE A, ENTRAINEUR E
WHERE [Link] = [Link];
19. Afficher les noms et prénoms des adhérents de genre Femme qui pratiquent l’activité "Judo".
SELECT [Link]
FROM ADHERENT AD, INSCRIPTION I, ACTIVITE A
WHERE [Link] = [Link]
AND [Link] = [Link]
4
STI
Anglais
AND [Link] = 'F'
AND [Link] = 'Judo';
20. Afficher les noms des activités pratiquer dans le club sans doublons
SELECT DISTINCT NomAct
FROM ACTIVITE A, INSCRIPTION I;
WHERE [Link] = [Link] ;
21. Afficher le nom et prénom de l’adhérent, le nom de l’activité, ainsi que le nom de
l’entraineur, pour les adhérents de la ville de Tunis et dont l’activité coûte plus de 50.
SELECT [Link], [Link], [Link]
FROM ADHERENT AD, INSCRIPTION I, ACTIVITE A, ENTRAINEUR E
WHERE [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = 'Tunis'
AND [Link] > 50;