1.
Écrivez une commande SQL pour créer une table commandes avec les champs suivants : NumCommande
(clé primaire, auto-incrément), DateCommande, Montant, NumClient (étrangère clé référençant NumClient
dans la table clients), et Statut (VARCHAR 20). Assurez-vous de gérer correctement les suppressions en
cascade.
create table commandes(NumCommande int primary key auto_increment, DateCommande date, Montant
float,NumClient int,statut varchar(20),constraint fk_c foreign key(NumClient) references fournisseur(id) on
delete cascade);
2. Ajoutez une contrainte à la table produits pour s'assurer que le prix est toujours supérieur à 0.
ALTER TABLE produits
ADD CONSTRAINT CHK_Prix CHECK (prix > 0);
3. Mettez à jour la table employes pour augmenter le salaire de 10% pour les employés qui travaillent dans le
département avec le code 'IT' et qui ont un salaire inférieur à 3000.
update employes set salaire = salaire + (salaire * 0.1) where id_depart = ‘IT’ and salaire < 3000;
4. Écrivez une requête pour trouver le nom et le prénom des employés qui n'ont pas de commission ou qui ont
une commission inférieure à 5%.
select nom,prenom from employes where commission is null or commission < 5;
5. En utilisant une jointure, sélectionnez tous les employés avec le nom de leur service et la date de la dernière
commande pour chaque employé (supposez qu'il existe une table commandes liée aux employés).
Select [Link],nom_service,max(date_commande) from employes e inner join service s on e.id_service = [Link]
Inner join commandes c on [Link] = [Link] group by [Link], nom_service;
6. Calculez le salaire moyen par service et affichez seulement ceux qui ont un salaire moyen supérieur à 5000.
Select id_service,avg(salaire) as salaireMoyen from employes group by id_service having salaireMoyen >
5000 ;
7. Écrivez une requête pour afficher les trois produits les plus chers qui ont été commandés cette semaine, en
incluant le nom du client qui a passé la commande.
SELECT [Link], [Link], [Link]
FROM produits p
JOIN commandes co ON [Link] = [Link]
JOIN clients c ON [Link] = [Link]
WHERE YEARWEEK([Link]) = YEARWEEK(CURDATE())
ORDER BY [Link] DESC
LIMIT 3;
8. Sélectionnez le nom et le prénom des employés qui ont un anniversaire ce mois-ci.
Select nom,prenom from employes where month(date_naissance) = month(curdate());
9. Écrivez une procédure stockée qui prend un ID de produit et retourne la quantité en stock pour ce produit.
Délimiter //
Create procedure ps1(IN id int)
Begin
Select qte from produit where id = id;
END//
Delimiter ;
10. Créez un trigger qui vérifie, avant l'insertion d'un nouvel employé, si le salaire proposé respecte la moyenne
des salaires du département auquel l'employé sera affecté.
DELIMITER //
CREATE TRIGGER CheckSalaireMoyenAvantInsertion
BEFORE INSERT ON employes FOR EACH ROW
BEGIN
DECLARE salaireMoyen DECIMAL(10, 2);
SELECT AVG(SalaireEmp) INTO salaireMoyen
FROM employes
WHERE CodeService = [Link];
IF [Link] < salaireMoyen THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Le salaire proposé est inférieur à la moyenne du département.';
END IF;
END //
DELIMITER ;
