Plats et ustensiles : requêtes SQL sur la préparation culinaire

Exercice 2 - Requêtes SQL sur la préparation culinaire

L'objectif de cet exercice est de manipuler le langage SQL à travers des requêtes de création, d'interrogation et de mise à jour.

Attention : Le corrigé fourni dans le document source contient plusieurs erreurs de syntaxe, des confusions logiques et de fausses règles méthodologiques. Ce document rétablit le code SQL correct et fonctionnel, tout en expliquant les pièges à éviter.

Question 1 - Création de la table Utiliser

La table Utiliser est une table d'association entre les tables Plat et Ustensile. La proposition du document source contient une erreur de frappe sur le type de données (NT au lieu de INT). Voici la syntaxe SQL corrigée.

CREATE TABLE Utiliser ( 
    Codeplat INT NOT NULL,
    CodeUst INT NOT NULL,
    PRIMARY KEY (Codeplat, CodeUst),
    FOREIGN KEY (Codeplat) REFERENCES Plat (Codeplat),
    FOREIGN KEY (CodeUst) REFERENCES Ustensile (CodeUst)
);

Question 2 - Nom du plat le plus cher

Le document source propose deux méthodes correctes dans leur logique. Il est effectivement indispensable de passer par une sous-requête, car les fonctions d'agrégation (comme MAX) ne peuvent pas être utilisées directement dans une clause WHERE.

Méthode 1 : Utilisation de la fonction MAX() (Recommandée)

SELECT Nomplat
FROM Plat
WHERE cout = (SELECT MAX(cout) FROM Plat);

Méthode 2 : Utilisation de NOT EXISTS Le plat le plus cher est celui pour lequel il n'existe aucun autre plat ayant un coût strictement supérieur.

SELECT Nomplat
FROM Plat P1
WHERE NOT EXISTS (
    SELECT 1
    FROM Plat P2
    WHERE P2.cout > P1.cout
);

Question 3 - Liste des ingrédients pour les plats sans temps de repos

Correction majeure du document source : L'affirmation "Chaque fois lorsqu’on a « pour chaque » dans la question il faut faire « group by »" est totalement fausse. L'utilisation de GROUP BY sert à agréger des données (calculer une somme, une moyenne, etc.). Ici, nous voulons afficher le détail de chaque ingrédient. Utiliser un GROUP BY sans fonction d'agrégation déclenchera une erreur SQL.

De plus, le document source contient deux autres erreurs :

1. En SQL, on vérifie l'absence de valeur avec IS NULL, et non = Null.
2. La jointure proposée lie NomIngr (une chaîne de caractères) à Codeplat (un entier), ce qui n'a aucun sens. La jointure doit se faire sur le nom de l'ingrédient.

Puisque la table Nécessiter contient déjà le nom de l'ingrédient et la quantité, il n'est même pas obligatoire de faire une jointure avec la table Ingrédient.

SELECT p.Nomplat, n.NomIngredient, n.quantité
FROM Plat p
JOIN Nécessiter n ON p.Codeplat = n.codeplat
WHERE p."temps de repos" IS NULL;

(Note : Si le SGBD n'accepte pas les espaces dans les noms de colonnes sans échappement, les guillemets doubles "temps de repos" garantissent la validité de la requête selon le standard SQL).

Question 4 - Plats nécessitant plus de 4 ustensiles

L'idée du document source est bonne (utiliser HAVING COUNT), mais la requête proposée est invalide. En SQL, toute colonne sélectionnée dans le SELECT qui n'est pas soumise à une fonction d'agrégation doit figurer dans la clause GROUP BY. Le document source a oublié d'inclure Nomplat dans le GROUP BY.

SELECT p.Nomplat, p.Codeplat
FROM Plat p
JOIN Utiliser u ON p.Codeplat = u.codeplat
GROUP BY p.Codeplat, p.Nomplat
HAVING COUNT(u.CodeUst) > 4;

Question 5 - Ingrédients utilisés dans tous les plats

Correction majeure du document source : La requête fournie dans le document source répond à la question "Quels sont les ingrédients qui ne sont utilisés dans aucun plat ?" (ce qui est la question 6).

Pour trouver les ingrédients utilisés dans tous les plats, il s'agit d'une opération de division relationnelle. Il faut utiliser une double négation. La logique est la suivante : "Trouver les noms des ingrédients tels qu'il n'existe aucun plat pour lequel cet ingrédient n'est pas utilisé (nécessité)."

SELECT i.NomIngr
FROM Ingrédient i
WHERE NOT EXISTS (
    SELECT p.Codeplat
    FROM Plat p
    WHERE NOT EXISTS (
        SELECT n.codeplat
        FROM Nécessiter n
        WHERE n.codeplat = p.Codeplat 
          AND n.NomIngredient = i.NomIngr
    )
);

Question 6 - Supprimer les ingrédients non utilisés

La logique NOT IN du document source est correcte, mais il y a une erreur sur le nom de la table : il s'agit de Nécessiter et non de Nécessaire.

DELETE FROM Ingrédient
WHERE NomIngr NOT IN (
    SELECT NomIngredient 
    FROM Nécessiter
);

(Note : La clause NOT IN est sensible aux valeurs NULL. Dans ce schéma, NomIngredient fait partie de la clé primaire de Nécessiter, il ne peut donc pas être NULL, ce qui rend cette requête parfaitement sûre).

Question 7 - Plats de même niveau que le Couscous et utilisant exactement les mêmes ustensiles

Correction majeure du document source : Le document source tente de comparer le résultat de deux sous-requêtes retournant plusieurs lignes avec le signe égal : (SELECT ...) = (SELECT ...). Cela génère une erreur fatale dans tous les systèmes SQL. On ne peut pas comparer des ensembles de lignes avec =.

Pour s'assurer que le plat X possède exactement les mêmes ustensiles que le Couscous, il faut vérifier deux conditions :

1. Il n'y a aucun ustensile utilisé par le Couscous qui ne soit pas utilisé par le plat X (inclusion).
2. Il n'y a aucun ustensile utilisé par le plat X qui ne soit pas utilisé par le Couscous (absence d'ustensiles supplémentaires).

Voici la requête relationnelle correcte :

SELECT p.Nomplat
FROM Plat p
JOIN Plat c ON c.Nomplat = 'Couscous' 
           AND p.CodeNiveau = c.CodeNiveau
WHERE p.Codeplat <> c.Codeplat -- On exclut le Couscous lui-même du résultat final
  -- Condition 1 : Tous les ustensiles du Couscous sont dans le plat p
  AND NOT EXISTS (
      SELECT u1.CodeUst
      FROM Utiliser u1
      WHERE u1.codeplat = c.Codeplat
        AND u1.CodeUst NOT IN (
            SELECT u2.CodeUst 
            FROM Utiliser u2 
            WHERE u2.codeplat = p.Codeplat
        )
  )
  -- Condition 2 : Tous les ustensiles du plat p sont dans le Couscous
  AND NOT EXISTS (
      SELECT u3.CodeUst
      FROM Utiliser u3
      WHERE u3.codeplat = p.Codeplat
        AND u3.CodeUst NOT IN (
            SELECT u4.CodeUst 
            FROM Utiliser u4 
            WHERE u4.codeplat = c.Codeplat
        )
  );

Méthode

Face à une épreuve de requêtes SQL sur papier, voici la démarche à adopter :

1. Repérer les clés primaires et étrangères : Tracez visuellement les liens entre les tables. Par exemple, comprenez que Utiliser relie Plat et Ustensile. Cela permet de savoir immédiatement quelles tables devront être jointes.
2. Ne pas sur-utiliser le GROUP BY : Méfiez-vous de la formulation "pour chaque". Elle ne signifie pas systématiquement GROUP BY. Posez-vous la question : "Est-ce que je cherche à calculer une somme, une moyenne ou à compter des éléments ?". Si la réponse est non, un GROUP BY n'est généralement pas nécessaire.
3. Maitriser la division relationnelle : Les questions contenant le mot "tous" (comme la question 5) impliquent presque toujours une double négation avec NOT EXISTS. Apprenez la structure par cœur : SELECT ... WHERE NOT EXISTS (SELECT ... WHERE NOT EXISTS (...)).
4. Tester la logique sur des ensembles : On ne compare jamais deux listes (sous-requêtes) avec un simple =. Pour comparer deux ensembles d'éléments (comme à la question 7), il faut soit passer par des comptages croisés (COUNT), soit utiliser des opérateurs ensemblistes comme NOT EXISTS ou EXCEPT pour prouver que les différences entre les deux ensembles sont vides.