Université Abdou Moumouni de Niamey
Faculté des Sciences et Techniques
Département de Math-Info
TDIF611-serie 2
1. Utiliser le script [Link] pour créer et peupler la base de données démo.
2. Un moment après la création de la table employes on s'est rendu compte de la
nécessité d'ajouter une colonne supplémentaire à cette table pour stocker le
comportement de chaque employé. Cette colonne peut prendre quatre valeurs
possibles à savoir "leader","innovant","intelligent" et "adaptatif". Par défaut un
employé est intelligent. Modifier à base du SQL le schéma de la table employes pour
incorporer la définition de la nouvelle colonne.
3. Créer la table table_a_suprimer et la peupler en se servant du code SQL suivant.
create table table_a_supprimer(
id INTEGER,
nom VARCHAR(15),
prenom VARCHAR(20)
);
insert INTO table_a_supprimer(id,nom,prenom)VALUES(1,Issoufou, Laouali),(2,Issa,
Abdo),(3,Iro, Maman),(4,Souley, Moussa);
Quelle commande SQL utiliser pour vider le contenu de cette table ? comment
supprimer le contenu de cette table ensemble avec son schéma.
4. Supposons que tous les employés dont le salaire va au-delà de 100000.00 sont
considérés comme les employés les mieux-payés. Quels sont donc les employés les
mieux-payés ?
5. Créer une table qui va contenir les noms, les prénoms et le salaire des employés
ayant plus de 100000.00 avec le schéma ci-dessous
create table emp_mieux_paye(
nom VARCHAR(15),
prenom VARCHAR(15,
salaire DECIMAL(10,2)
);
6. Comment se servir la requête précédemment utilisée lorsqu’on voulait déterminer
les employés les mieux-payés pour peupler la table emp_mieux_paye ?
7. L'employé qui pour a pour identifiant 3 vient de trouver ailleurs un emploi plus
juteux que son emploi actuel. Ainsi il décide de quitter la boite pour renouer avec
l'entreprise la plus offrant. De ce fait, l'entreprise qu'il quitte décide de le railler
complètement de leur base de données. Quelle commande SQL permettra de le
faire ?
8. L’employé qui a pour identifiant 10 vient d'être nommé à un grand poste de
responsabilité et par conséquent il aura maintenant 200000.00 comme salaire.
Comment accomplir cette modification du salaire à base de SQL ?
9. Quel le nombre total d'employés dans cette entreprise ?
10. Quel est le salaire moyen dans cette entreprise ?
11. Quel est l'employé qui a le plus grand salaire ?
12. Quels sont les employés qui sont dans le département de ressources humaines RH ou
dans la fabrication.
13. Afficher le nom, le prénom et le salaire de tous les employés en les triant par ordre
croissant du salaire.
14. Afficher le département et le nombre d'employés pour chaque département ?
15. Lister le nom du département et le nombre d'employés pour les départements qui
ont plus d'un employé.
16. Lister le nom, le prénom et le salaire des employés dont le salaire est supérieur à la
moyenne.
17. Lister pour chaque département le nombre d'employés dont le salaire est supérieur à
la moyenne.
18. Quels sont les employés dont le nom commence a ?
19. Quels sont les employés dont le nom fini par la lettre u ?
20. Quels sont les employés dont le nom contient la lettre a ?
21. Quels sont les employés dont le nom contient la lettre o en deuxième position ?
22. Quels sont les employés dont le nom commence par a et fini par la lettre u ?
23. Quel est le nom, prénom et salaire de l'employé qui a le plus gros salaire ?
CORRECTION :
Ajouter la colonne comportement
ALTER TABLE employes
ADD comportement VARCHAR(20)
DEFAULT 'intelligent'
CHECK (comportement IN ('leader','innovant','intelligent','adaptatif'));
3. Création et suppression de table
Création de la table
CREATE TABLE table_a_supprimer(
id INTEGER,
nom VARCHAR(15),
prenom VARCHAR(20)
);
Insertion des données
INSERT INTO table_a_supprimer(id, nom, prenom)
VALUES
(1,'Issoufou','Laouali'),
(2,'Issa','Abdo'),
(3,'Iro','Maman'),
(4,'Souley','Moussa');
Vider le contenu de la table
DELETE FROM table_a_supprimer;
ou
TRUNCATE TABLE table_a_supprimer;
Supprimer la table avec son schéma
DROP TABLE table_a_supprimer;
4. Employés les mieux-payés
SELECT *
FROM employes
WHERE salaire > 100000.00;
5. Création de la table emp_mieux_paye
CREATE TABLE emp_mieux_paye(
nom VARCHAR(15),
prenom VARCHAR(15),
salaire DECIMAL(10,2)
);
6. Peupler la table emp_mieux_paye
INSERT INTO emp_mieux_paye(nom, prenom, salaire)
SELECT nom, prenom, salaire
FROM employes
WHERE salaire > 100000.00;
7. Supprimer l’employé d’identifiant 3
DELETE FROM employes
WHERE id = 3;
8. Modifier le salaire de l’employé 10
UPDATE employes
SET salaire = 200000.00
WHERE id = 10;
9. Nombre total d’employés
SELECT COUNT(*) AS nombre_employes
FROM employes;
10. Salaire moyen
SELECT AVG(salaire) AS salaire_moyen
FROM employes;
11. Employé ayant le plus grand salaire
SELECT *
FROM employes
WHERE salaire = (SELECT MAX(salaire) FROM employes);
12. Employés du département RH ou fabrication
SELECT *
FROM employes
WHERE departement IN ('RH', 'fabrication');
13. Afficher nom, prénom et salaire par ordre croissant
SELECT nom, prenom, salaire
FROM employes
ORDER BY salaire ASC;
14. Département et nombre d’employés
SELECT departement, COUNT(*) AS nombre_employes
FROM employes
GROUP BY departement;
15. Départements ayant plus d’un employé
SELECT departement, COUNT(*) AS nombre_employes
FROM employes
GROUP BY departement
HAVING COUNT(*) > 1;
16. Employés dont le salaire est supérieur à la moyenne
SELECT nom, prenom, salaire
FROM employes
WHERE salaire > (
SELECT AVG(salaire)
FROM employes
);
17. Nombre d’employés par département ayant un salaire supérieur à la moyenne
SELECT departement, COUNT(*) AS nombre_employes
FROM employes
WHERE salaire > (
SELECT AVG(salaire)
FROM employes
)
GROUP BY departement;
18. Employés dont le nom commence par "a"
SELECT *
FROM employes
WHERE nom LIKE 'a%';
19. Employés dont le nom finit par "u"
SELECT *
FROM employes
WHERE nom LIKE '%u';
20. Employés dont le nom contient la lettre "a"
SELECT *
FROM employes
WHERE nom LIKE '%a%';
21. Employés dont le nom contient "o" en deuxième position
SELECT *
FROM employes
WHERE nom LIKE '_o%';
22. Employés dont le nom commence par "a" et finit par "u"
SELECT *
FROM employes
WHERE nom LIKE 'a%u';
23. Nom, prénom et salaire de l’employé ayant le plus gros salaire
SELECT nom, prenom, salaire
FROM employes
WHERE salaire = (
SELECT MAX(salaire)
FROM employes
);
DECLAMCHEUR:
Structure générale d’un déclencheur
CREATE TRIGGER nom_declencheur
BEFORE | AFTER INSERT | UPDATE | DELETE
ON nom_table
FOR EACH ROW
BEGIN
-- instructions SQL
END;
Exemple
CREATE TRIGGER verifier_note
BEFORE INSERT
ON Eleves
FOR EACH ROW
BEGIN
IF [Link] < 0 THEN
SET [Link] = 0;
END IF;
END;
.
1. Déclencheur après ajout d’un élève
Quand on ajoute un élève, un message est enregistré.
CREATE TRIGGER ajout_eleve
AFTER INSERT
ON Eleves
FOR EACH ROW
BEGIN
INSERT INTO Historique(message)
VALUES ('Nouvel élève ajouté');
END;
2. Déclencheur avant suppression
Empêche la suppression d’un étudiant mineur.
CREATE TRIGGER verifier_age
BEFORE DELETE
ON Eleves
FOR EACH ROW
BEGIN
IF [Link] < 18 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Suppression interdite';
END IF;
END;
3. Déclencheur après modification
Enregistre les changements de notes.
CREATE TRIGGER modifier_note
AFTER UPDATE
ON Eleves
FOR EACH ROW
BEGIN
INSERT INTO Historique(message)
VALUES ('La note a été modifiée');
END;
Types de déclencheurs
BEFORE INSERT → avant ajout
AFTER INSERT → après ajout
BEFORE UPDATE → avant modification
AFTER UPDATE → après modification
BEFORE DELETE → avant suppression
AFTER DELETE → après suppression
VIEW::::::
Structure générale
CREATE VIEW nom_vue AS
SELECT colonnes
FROM table
WHERE condition;
Exemple 1 : afficher seulement les élèves de 3e
CREATE VIEW vue_3e AS
SELECT nom, note
FROM Eleves
WHERE classe = '3e';
Cette vue affiche :
le nom
la note
des élèves de 3e.
Exemple 2 : vue des élèves ayant une bonne note
CREATE VIEW bons_eleves AS
SELECT nom, note
FROM Eleves
WHERE note >= 15;
Pour afficher la vue
SELECT * FROM bons_eleves;