IN Institut Supérieur d’Informatique – Année Universitaire 2023-2024
ISI
S T I TUT
SUPERIEUR Mastère Professionnel en Systèmes Intelligents et IoT (SIIoT) - M1
INFORMATIQUE Bases de données Evoluées – Correction de l’activité 1.1
الـمعهـد العـالـي لإلعـالمــيـةR. ZAAFRANI, 22/01/2024
1) CREATE TABLE PREsentation (
MED VARCHAR(2),
FORmat NUMBER(1),
Prix NUMBER(2),
Constraint PREsentation_PK PRIMARY KEY (MED,FORmat),
Constraint PREsentation_MED_FK FOREIGN KEY (MED)
REFERENCES coûte(MED)
);
CREATE TABLE Coûte (
MED VARCHAR(2),
Prix NUMBER(2),
Constraint coûte_PK PRIMARY KEY (MED)
);
CREATE TABLE Soigne (
MED VARCHAR(2),
MAL VARCHAR(2),
Constraint soigne_PK PRIMARY KEY (MED,MAL),
Constraint soigne_MED_FK FOREIGN KEY (MED) REFERENCES
coûte(MED)
);
Q1) liste ordonnée des médicaments avec l’ensemble des maladies qu’ils soignent.
SELECT MED,MAL
FROM soigne
ORDER BY MED;
Q2) liste des maladies associées aux médicaments sous le format le moins cher.
SELECT [Link], [Link], Min([Link]) AS MinDePrix
FROM Soigne AS S, PREsentation AS P
WHERE [Link]=[Link]
GROUP BY [Link], [Link];
Q3) liste des formats ordonnées selon leur moyenne de prix.
SELECT FORmat, AVG(Prix)
FROM PREsentation
GROUP BY FORmat
ORDER BY AVG(Prix);
Q4) maladies associées aux médicaments les plus chers.
SELECT DISTINCT MAL
FROM soigne
WHERE MED IN (SELECT MED
FROM PREsentation
Where Prix = (Select Max(Prix) FROM PREsentation));
Q5) liste des maladies encadrées par les coûts les plus élevés et les coûts les plus
bas des médicaments la soignant.
SELECT [Link], Min([Link]), Max([Link])
FROM Soigne AS S, PREsentation AS P
WHERE [Link]=[Link]
GROUP BY [Link];
Q6) Resserrer les prix des formats pour chaque médicament : augmenter le prix
le plus bas de 10%, diminuer le prix le plus haut de 15 %.
UPDATE PREsentation P1 SET Prix = Prix*1.1
WHERE Prix=(Select Min(prix)
FROM PREsentation P2
Where [Link]= [Link]
Group By [Link]
Having Count(*) > 1); -- Médicaments ayant au moins 2 formats
UPDATE PREsentation P1 SET Prix = Prix*0.85
WHERE Prix=(Select Max(prix)
FROM PREsentation P2
Where [Link]= [Link]
Group By [Link]
Having Count(*) > 1); -- Médicaments ayant au moins 2 formats
Q7) Eliminer tous les médicaments les plus chers (dans leurs différents formats).
DELETE FROM PREsentation DELETE FROM PREsentation
WHERE Med In(Select Med WHERE Med In(Select Med
From PREsentation From PREsentation
Where Prix=(Select Max(prix) Where Prix>= All(Select prix
FROM PREsentation)); FROM PREsentation));
Q8) Médicaments soignant plus de deux maladies.
SELECT MED
FROM Soigne
GROUP BY MED
HAVING COUNT(*) >= 2;
