---
title: BDR - grTP1 - Grp_2
tags: grTP1
---
[//]: <> (Ne pas modifier ce qui est au dessus SVP.)

# TP3
### Exercice 1
1. Quels sont les noms des recettes de plus de 90 calories par personne ?

SELECT nom_recette
FROM recette
WHERE (calories_recette/nb_personnes) > 90;

2. Quels sont les descriptifs des produits présents dans le réfrigérateur ?

SELECT descriptif_produit
FROM produit
JOIN rangement
	USING (num_rangement)
WHERE lower(nom_rangement) = 'réfrigérateur';

3. Quels sont les noms des recettes composées de fruits de mer ?

SELECT nom_recette
FROM recette
JOIN composition
	USING (num_recette)
JOIN ingredient
	USING (num_ingredient)
JOIN type_ingredient
	USING (num_type_ingredient)
WHERE lower(nom_type_ingredient) = 'fruit de mer';

4. Quels sont les noms des recettes n’utilisant pas de fruits de mer ?

SELECT nom_recette
FROM recette
EXCEPT
SELECT nom_recette
FROM recette
JOIN composition
	USING (num_recette)
JOIN ingredient
	USING (num_ingredient)
JOIN type_ingredient
	USING (num_type_ingredient)
WHERE lower(nom_type_ingredient) = 'fruit de mer';

5. Les questions suivantes ne font intervenir qu’une partie des ingrédients : ceux de type « matière grasse ». Nous allons donc créer une vue pour simplifier ces requêtes.

i. Écrivez une requête SELECT retournant les ingrédients du type « matière grasse » (seules les colonnes de la table ingredient seront retournées)

SELECT *
FROM ingredient
JOIN type_ingredient
	USING (num_type_ingredient)
WHERE lower(nom_type_ingredient) = 'matière grasse';

ii. À partir de cette requête, créez une vue ingredient_gras

CREATE VIEW ingredient_gras AS (
	SELECT num_ingredient, nom_ingredient, num_type_ingredient
	FROM ingredient
	JOIN type_ingredient
		USING (num_type_ingredient)
	WHERE lower(nom_type_ingredient) = 'matière grasse'
);

iii. Affichez le contenu de la vue

SELECT *
FROM ingredient_gras;

iv. Quelles sont les recettes ayant plus de deux
ingrédients gras différents ?

SELECT nom_recette
FROM recette
JOIN composition
	USING (num_recette)
LEFT JOIN ingredient_gras
	USING (num_ingredient)
GROUP BY nom_recette
HAVING count(nom_ingredient) >= 2;

v. Quels sont les rangements ayant des ingrédients gras ?

SELECT nom_rangement
FROM produit
JOIN rangement
	USING (num_rangement)
LEFT JOIN ingredient_gras
    USING (num_ingredient)
GROUP BY nom_rangement
HAVING count(nom_ingredient) > 0;

vi. Pour tous les ingrédients gras, donnez en quelles quantités ils sont présents chez la famille (à partir de la table produit). Nous voulons afficher tous les ingrédients, même ceux qui ne sont pas disponible chez la famille.

SELECT MIN(num_ingredient) AS num_ingredient, nom_ingredient, SUM(quantite_produit) AS quantite_produit_gras
FROM ingredient_gras
LEFT JOIN produit
    USING (num_ingredient)
GROUP BY nom_ingredient;

vii. Si votre requête précédente renvoie des quantités NULL, modifiez la en remplaçant ces valeurs NULL par des 0.

SELECT MIN(num_ingredient) AS num_ingredient, nom_ingredient, COALESCE(SUM(quantite_produit),0) AS quantite_produit_gras
FROM ingredient_gras
LEFT JOIN produit USING (num_ingredient)
GROUP BY nom_ingredient;

6. En utilisant la méthodologie des questions 5.i à 5.iii, créez une vue donnant la quantité de matière grasse dans chaque recette.

SELECT 
	num_recette,  
	COALESCE(SUM(
		CASE  
            WHEN nom_type_ingredient = 'matière grasse' THEN quantite_composition 
            ELSE 0 
        END
	), 0) AS quantite_gras
FROM recette
	JOIN composition USING(num_recette)
	JOIN ingredient USING(num_ingredient)
	JOIN type_ingredient USING(num_type_ingredient)
GROUP BY num_recette;

CREATE VIEW quantite_gras AS (
	SELECT 
	num_recette,  
	COALESCE(SUM(
		CASE  
            WHEN nom_type_ingredient = 'matière grasse' THEN quantite_composition 
            ELSE 0 
        END
	), 0) AS quantite_gras
FROM recette
	JOIN composition USING(num_recette)
	JOIN ingredient USING(num_ingredient)
	JOIN type_ingredient USING(num_type_ingredient)
GROUP BY num_recette
);

SELECT *
FROM quantite_gras
ORDER BY num_recette;

7. Pour chaque recette, si la quantité de matière grasse est
◦ > à 120 : affichez « recette trop grasse »
◦ < à 50 mais > à 0 : affichez « recette légère »
◦ = à 0 : affichez « recette non grasse »
◦ sinon : affichez « recette moyennement grasse »

SELECT num_recette, nom_recette,
	CASE WHEN quantite_gras > 120 THEN 'recette trop grasse'
	     WHEN quantite_gras < 50 AND quantite_gras > 0 THEN 'recette légère'
	     WHEN quantite_gras = 0 THEN 'recette non grasse'
	     ELSE 'recette moyennement grasse'
	END AS contenu_gras
FROM quantite_gras
JOIN recette
    USING (num_recette);

8. Quels sont les noms des recettes qui utilisent du jaune d’œuf ou de la viande rouge ou les deux à la fois?

SELECT nom_recette
FROM recette
JOIN composition
	USING (num_recette)
JOIN ingredient
	USING (num_ingredient)
JOIN type_ingredient
    USING (num_type_ingredient)
WHERE lower(nom_ingredient) IN ('jaune d''oeuf','oeuf entier') OR lower(nom_type_ingredient) = 'viande rouge';

9. Quels sont les types d’ingrédient des produits qui seront périmés entre début novembre 2015 et fin décembre 2015 ?

SELECT DISTINCT nom_type_ingredient
FROM type_ingredient
	JOIN ingredient USING(num_type_ingredient)
	JOIN produit USING(num_ingredient)
WHERE 
	date_peremption BETWEEN '01/11/2015' AND '31/12/2015';

10. Quels sont les noms des rangements qui contiennent au moins un ingrédient de type légume et au moins un ingrédient de type fruit ?

SELECT DISTINCT nom_rangement
FROM rangement
	JOIN produit USING(num_rangement)
	JOIN ingredient USING(num_ingredient)
	JOIN type_ingredient USING(num_type_ingredient)
WHERE nom_type_ingredient = 'fruit'
INTERSECT
SELECT DISTINCT nom_rangement
FROM rangement
	JOIN produit USING(num_rangement)
	JOIN ingredient USING(num_ingredient)
	JOIN type_ingredient USING(num_type_ingredient)
WHERE nom_type_ingredient = 'légume';

11. Quels sont les noms des rangements dans lesquels nous trouvons des ingrédients nécessaires à la recette des crêpes ?

SELECT DISTINCT nom_rangement
FROM rangement
	JOIN produit USING(num_rangement)
	JOIN ingredient USING(num_ingredient)
	JOIN composition USING(num_ingredient)
	JOIN recette USING(num_recette)
WHERE nom_recette = 'crêpes';

12. Combien y a-t-il de recettes dans la base de données ?

SELECT count(*) AS Nombre_Recettes
FROM recette;

13. Combien de recettes utilisent de l’œuf ?

SELECT COUNT(DISTINCT num_recette) AS Nombre_Recettes
FROM recette
JOIN composition
	USING (num_recette)
JOIN ingredient
	USING (num_ingredient)
WHERE nom_ingredient LIKE '%oeuf%';

14. Triez les type d’ingrédients par ordre croissant du nombre d’ingrédients associés et affichez le nom du type, le nombre d’ingrédients, et la quantité totale dans les rangements.

SELECT num_type_ingredient, COUNT(DISTINCT num_ingredient) AS nb_ingredients, COALESCE(SUM(quantite_produit),0) AS quantite_produits
FROM type_ingredient
JOIN ingredient
	USING (num_type_ingredient)
LEFT JOIN produit
    USING (num_ingredient)
LEFT JOIN rangement
    USING (num_rangement)
GROUP BY num_type_ingredient
ORDER BY nb_ingredients;

15. Donnez pour chaque recette le nombre d’ingrédients différents qu’elle contient et la quantité moyenne de ces ingrédients (les unités n’étant pas cohérentes entre les ingrédients, cette quantité ne sera pas réaliste). Vous pourrez arrondir à deux chiffres après la virgule.

SELECT 
	nom_recette,
	COUNT(DISTINCT num_ingredient) AS nb_ingredients,
	ROUND(AVG(quantite_composition), 2) AS quantite_moyenne
FROM recette
	JOIN composition USING(num_recette)
	JOIN ingredient USING(num_ingredient)
GROUP BY nom_recette;

16. Donnez pour chaque recette le nombre d’ingrédients nécessaires et le nombre de rangements où on trouve au moins un de ces ingrédients.

SELECT 
	nom_recette,
	COUNT(DISTINCT num_ingredient) AS nb_ingredients,
	COUNT(DISTINCT num_rangement) AS nb_rangements
FROM recette
	JOIN composition USING(num_recette)
	JOIN ingredient USING(num_ingredient)
	JOIN produit USING(num_ingredient)
	JOIN rangement USING(num_rangement)
GROUP BY nom_recette;

17. Donnez la(es) nom(s) de la(es) recette(s) la(les) plus riche(s) (celle(s) qui contien(nen)t le plus de calories par personne).

SELECT nom_recette
FROM recette
WHERE 
	calories_recette/nb_personnes = (
        SELECT MAX(calories_recette/nb_personnes) 
        FROM recette
);

18. Donnez les noms des recettes ayant un ingrédient commun avec la recette des crêpes.

SELECT DISTINCT nom_recette
FROM recette
	JOIN composition USING(num_recette)
WHERE 
	num_ingredient IN (
		SELECT num_ingredient
		FROM recette
			JOIN composition USING(num_recette)
		WHERE nom_recette = 'crêpes'
);

19. Créez une vue appelée liste_courses contenant tous les ingrédients dont il ne reste qu’un seul ou aucun produit. La vue devra contenir le nom des ingrédients et le nom du type de l’ingrédient.

CREATE VIEW liste_courses AS (
	SELECT DISTINCT
		nom_ingredient,
		nom_type_ingredient
	FROM type_ingredient
		RIGHT JOIN ingredient USING(num_type_ingredient)
		FULL JOIN produit USING(num_ingredient)
	WHERE COALESCE(quantite_produit, 0) <= 1
);

20. Affichez le contenu de la vue précédente en triant par ordre alphabétique du nom des types d’ingrédient et pour un même type d’ingrédient par ordre alphabétique des ingrédients.

SELECT nom_ingredient, nom_type_ingredient
FROM liste_courses
ORDER BY nom_type_ingredient, nom_ingredient;

21. Créez une vue qui contient les ingrédients pour lesquels nous avons des produits. Pour chaque ingrédient nous voulons son prix de revient pour 100 grammes en se basant sur le prix du produit le moins cher. La vue contiendra trois attributs : la référence de l’ingrédient, le nom de l’ingrédient et son coût unitaire. Affichez la.

CREATE VIEW ingredient_prix AS(
	SELECT 
		num_ingredient,
		nom_ingredient,
		MIN(prix_produit)/MIN(quantite_produit) AS prix
	FROM ingredient
		JOIN produit USING(num_ingredient)
	GROUP BY num_ingredient, nom_ingredient
);

22. Créez une vue contenant les numéros des recettes pour lesquelles nous avons tous les ingrédients (il existe au moins un produit pour chaque ingrédient). Affichez la.

CREATE VIEW recettes_possibles AS (
	SELECT num_recette
	FROM recette

	EXCEPT

	SELECT num_recette
	FROM recette
		JOIN composition USING(num_recette)
		JOIN ingredient USING(num_ingredient)
	WHERE
		num_ingredient IN (
			SELECT DISTINCT
				num_ingredient
			FROM type_ingredient
				RIGHT JOIN ingredient USING(num_type_ingredient)
				FULL JOIN produit USING(num_ingredient)
			WHERE COALESCE(quantite_produit, 0) = 0
	)
);

23. Utilisez les vues précédentes pour calculer le coût des recettes réalisables (celles dont l’utilisateur possède tous les ingrédients). Affichez le nom des recettes et leur coût.

SELECT 
	nom_recette,
	ROUND(SUM(quantite_composition*prix), 2) AS cout_recette
FROM recettes_possibles
	JOIN recette USING(num_recette)
	JOIN composition USING(num_recette)
	JOIN ingredient_prix USING(num_ingredient)
GROUP BY nom_recette
ORDER BY cout_recette;