Exercice2 (Série SQL)
Soit la base de données relative à la préparation des plats de cuisine:
Plat (Codeplat, Nomplat, coût, temps de préparation, temps de cuisson, temps de repos,
nbrpersonnes, #CodeNiveau)
Niveau (CodeNiveau, Libellé)
Ingrédient (NomIngr, Apport calorique)
Ustensile (CodeUst, NomUstensile, capacité)
Nécessiter (codeplat, NomIngredient, quantité)
Utiliser (codeplat, CodeUst)
Répondre aux questions suivantes dans le langage SQL
1. Créer la table Utiliser
Dans la table Utiliser la clé primaire est composée de deux champs codeplat et codeUst. Le
champ codeplat est clé primaire dans la table Plat ; le champ CodeUst est clé primaire dans la
table Ustensile. Par conséquent codeplat et CodeUst devront être définis comme clé primaire
de la table Utiliser et clés secondaires se référant aux tables Plat et Ustensile.
CREATE TABLE Utiliser
( Codeplat INT NOT NULL,
Publicité
CodeUst NT NOT NULL,
PRIMARY KEY (Codeplat, CodeUst),
FOREIGN KEY (Codeplat) References Plat (Codeplat),
FOREIGN KEY (CodeUst) References Ustensile (CodeUst));
2. Donner le nom du plat le plus cher.
Le plat le plus cher c’est le plat ayant le coût maximal.
SELECT NomPlat
FROM Plat P
WHERE P.Cout = (SELECT MAX(Cout)
FROM Plat PP);
Il faut faire attention ne pas écrire
SELECT NomPlat
FROM Plat P
WHERE P.Cout = MAX(Cout) C’est faux.
Les fonctions MAX, MIN, AVG, COUNT et SUM doivent être écrite dans un SELECT.
Ou bien : Le plat le plus cher est e plat il n’existe pas un autre plat avec un coût supérieur
SELECT NomPlat
Publicité
FROM Plat P
WHERE NOT EXISTS (SELECT NomPlat
FROM Plat PP
WHERE P.Cout < PP.Cout);
1
3. Donner pour chaque plat ne nécessitant pas un temps de repos avant d'être servi,
le nom, la liste des ingrédients et leurs quantités nécessaires à sa préparation.
Chaque fois lorsqu’on a « pour chaque » dans la question il faut faire « group by » dans la
requête.
SELECT NomPlat, NomIngrédient, quantité
FROM Plat, Ingrédient, Nécessiter
WHERE Plat.Temps de repos = Null
AND Plat.Codeplat = Nécessiter.Codeplat
AND Ingrédient.NomIngr=Nécessiter.Codeplat
GROUP BY Nomplat;
4. Donner les noms des plats nécessitant plus que 4 ustensiles pour leur préparation.
SELECT nomplat, Codeplat
Publicité
FROM Plat, Utiliser
WHERE Plat.Codeplat=Utiliser.Codeplat
GROUP BY Plat.Codeplat
HAVING COUNT(*) > 4;
5. Donner les noms des ingrédients utilisés dans tous les plats.
SELECT NomIngr
FROM Ingrédient
WHERE NOT EXISTS (SELECT Codeplat
FROM Nécessiter
WHERE Ingrédient.NomIng=Nécessiter.NomIngr);
6. Supprimer tous les ingrédients non utilisés pour la préparation d'aucun
plat.
Les ingrédients non utilisés pour la préparation d’aucun plat sont des ingrédients qui existent
dans la table Ingrédient et n’existe pas dans la table Nécessaire.
DELETE FROM Ingrédient
WHERE NomIngr NOT IN (SELECT NomIngr
FROM Nécessaire);
Publicité
7. Donner les noms des plats ayant le même niveau de difficulté que le plat Couscous et
utilisant les mêmes ustensiles dans leur préparation.
SELECT P.NomPlat
FROM Plat P, Plat PP
WHERE P.CodeNiveau = PP.CodeNiveau
AND PP.Nomplat = "Couscous"
AND (SELECT U.CodeUst
FROM Utiliser U
WHERE U.CodePlat=PP.Codeplat)
=
(SELECT UU.CodeUst
FROM Utiliser UU
WHERE UU.CodePlat=P.Codeplat);
2